OrderBy(x => x.Name == null) throws an error

[mariojsnunes]

Getting this error:
{"Incorrect syntax near the keyword 'IS'.\r\nInvalid usage of the option NEXT in the FETCH statement."}

On this query:

IEnumerable<UserProfileIndex> searchResults = await query
    .OrderBy(x => x.Name == null)
    .ThenBy(x => x.Name)
    .Skip(req.Skip)
    .Take(req.Take)
    .ListAsync();

Using sql server.

[sebastienros]

I assume it's because of .OrderBy(x => x.Name == null) which probably only accepts keys like x.Name and not a statement like this one.

So these could probably be evaluated like expressions.

[xeron333]

Hello i try with this exaple : in Program.cs

var query2 = session
.QueryIndex<ArticleByNom>();
IEnumerable<ArticleByNom>searchResults=await query2
.OrderBy(x =>x.NomIsNull)
.ThenBy(x=>x.Nom)
.Skip(req.Skip)
.Take(req.Take)
.ListAsync();
foreach (var a in searchResults)
{
Console.WriteLine($"Nom trié : {a.Nom}");
}

with ;

ArticleByNom.cs ;
public class ArticleByNom : MapIndex
{
public string Nom { get; set; }
public int NomIsNull {get;set;}

}

and ;
ArticleByNomProvider.cs ;

public class ArticleByNomProvider : IndexProvider<Article>
{
public override void Describe(DescribeContext<Article> context)
{
context.For<ArticleByNom>()
.Map(article => new ArticleByNom
{
Nom = article.Nom ,
NomIsNull = article.Nom==null ? 1 : 0
});
}
}

And Document ;

Id Type Content Version
1 YesSql.Samples.FullText.Models.Article, YesSql.Samples.FullText {"Nom":"Premier","Content":"This is a first fox"} 1
2 YesSql.Samples.FullText.Models.Article, YesSql.Samples.FullText {"Nom":"Second","Content":"This is a! test, of A tokenizer\u002B\u002B behavior."} 1
3 YesSql.Samples.FullText.Models.Article, YesSql.Samples.FullText {"Nom":"","Content":"YesSql est un moteur de base de donn\u00E9es orient\u00E9 documents."} 1
4 YesSql.Samples.FullText.Models.Article, YesSql.Samples.FullText {"Nom":"Quatri\u00E8me","Content":"This is a fourth fox"} 1

The result of the display is:
Nom trié :
Nom trié : Quatrième
Nom trié : Second

Why is the NonNull value always 1 in the Document? whereas:

3 YesSql.Samples.FullText.Models.Article, YesSql.Samples.FullText {"Name":"","Content":"YesSql is a document-oriented database engine."} 1

The Name field is null.

Why does the display show Non-null first when I'm sorting by non-null values ​​(non-null=1, so it should be displayed last,? in any case)

.OrderBy(x=> x => x.Nom)
.ThenBy(x => x.NomIsNull)

I have the same results
If you can help me thanks

[xeron333]

Sorry, I just found the solution:

.OrderBy(x => x.NomIsNull)
.ThenByDescending(x => x.Nom)

but ;
3 YesSql.Samples.FullText.Models.Article, YesSql.Samples.FullText {"Name":"","Content":"YesSql is a document-oriented database engine."} 1

and the others are also equal to 1(null=1) in public class ArticleByNomProvider

it's always equal to 1, do you know why?

[xeron333]

Finally, I understood: you have to look at the ArticleByNom table, and there you can clearly see the index results:
Id DocumentId Name NameIsNull
1 2 Second 0
2 3 1
3 4 Fourth 0

where the 3rd value is equal to 1 (null), the Document table sets this value to 1 by default.