M B M B - 10 months ago 61
C# Question

Linq to Sql - Incorrect syntax by keyword 'IS'

I am trying to order my query by null values on one column and then by another column as seen in this post.

_ctx.Leads.Include(a => a.LeadAttachments)
.Where(s => s.Name.ToLower().StartsWith(filter))
.ThenByDescending(a => a.AssignedOn)

I get this error when adding

An exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.Core.dll but was not handled in user code

Additional information: Incorrect syntax near the keyword 'IS'.

Invalid usage of the option NEXT in the FETCH statement.

Both my order by columns are of type

What is wrong with this query? Is there a better way of sorting?


Thanks to @vamsi answer, I am not having this error. Now I need this to sort like the following:

All records that have a
value for
should be first

All records that have a value for both dates should be sorted by

Is this possible?

Answer Source

From comment:

but what i really want is first all null acceptedOn and then rest ordered by AssignedOn. Is that possible

_ctx.Leads.Include(a => a.LeadAttachments)
        .Where(s => s.Name.ToLower().StartsWith(filter))
        .OrderByDescending(a=> a.AcceptedOn == null 
            ? DateTime.MaxValue 
            : a.AssignedOn == null 
                ? a.AcceptedOn 
                : a.AssignedOn)

Sort priority in code explained

  1. Records with a null value for AcceptedOn appear at the top
  2. Records that have a value for both AcceptedOn AND AssignedOn will be sorted by AssignedOn in descending order
  3. Remaining records that only have a value for AcceptedOn will be sorted last in descending order