M B M B - 1 month ago 10
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))
.OrderBy(a=>a.AcceptedOn.HasValue)
.ThenByDescending(a => a.AssignedOn)
.Skip(offSet)
.Take(12)
.ToList()


I get this error when adding
.OrderBy(a=>a.AcceptedOn.HasValue)



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
Nullable<DateTime>
.

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

UPDATE:


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

All records that have a
null
value for
AcceptedOn
should be first

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


Is this possible?

Answer

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)
        .Skip(offSet)
        .Take(12)
        .ToList();

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
Comments