Linq to Sql - Incorrect syntax by keyword 'IS'

Question

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 typeNullable<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?

@MB - Just put a `ToString()` instead of the `ToList() - see [this](http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework) — Gilad Green, Oct 07 '16 at 15:08
@MB what version of SQL server are you using? Is it a SQL server 2008? — user449689, Oct 07 '16 at 15:13
@MB - in the link I added they also say this: `ToTraceString` — Gilad Green, Oct 07 '16 at 15:16
I don;t have the classes needed for that method. It was for ado.net — M B, Oct 07 '16 at 15:17
@MB - using [ToString](http://stackoverflow.com/questions/1577231/sql-query-generated-by-linq-to-sql-statement) should work. Please show how you tried to use it — Gilad Green, Oct 07 '16 at 15:20
@MB - your update is a follow-up question. And as you said - for your original question vamsi's answer did the trick — Gilad Green, Oct 07 '16 at 15:27

Igor · Accepted Answer · 2016-10-07T15:47:36.753

2

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

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

edited Oct 07 '16 at 15:47

answered Oct 07 '16 at 15:22

Igor

60,821
10
100
175

@GiladGreen - they are both datetime fields. Op wants all null entries first and then sorted on AssignedOn descending. This should do that I think. – Igor Oct 07 '16 at 15:27
Looking good, but any way to sort the `AccpetedOn == null` records thenBy `AssignedOn`? – M B Oct 07 '16 at 15:29
@MB - sure, just take that one step further (see update) with another null check. This assumes you want to sort on either type descending. – Igor Oct 07 '16 at 15:32
No, like this. If `AcceptedON == null` then by maxvalue then `assingedon` else by `assignedon` – M B Oct 07 '16 at 15:36
@MB - you wrote `assignedon ` x2, in your comment. Which one of those is actually `AssignedOn` and which is `AcceptedOn` ? – Igor Oct 07 '16 at 15:38
thos with acceptedOn value and thosse without should both be sorted by `assignedOn` as well – M B Oct 07 '16 at 15:39
@MB - What I have above is: 1. Empty `AcceptedOn` first 2. If `AssignedOn` has a value then sort by that next (descending) 3. Sort the rest on `AcceptedOn` (descending) – Igor Oct 07 '16 at 15:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/125194/discussion-between-igor-and-m-b). – Igor Oct 07 '16 at 15:55
Got it! used original ternary and then added a `ThenByDescneding(a=>a.AssignedOn)`. THis ordered everything then by the `assignedOn` column – M B Oct 07 '16 at 15:58

score 1 · Answer 2 · answered Oct 07 '16 at 15:15

1

Try if this works

 _ctx.Leads.Include(a => a.LeadAttachments)
        .Where(s => s.Name.ToLower().StartsWith(filter))
        .OrderBy(a=>a.AcceptedOn ?? DateTime.MinValue)
        .ThenByDescending(a => a.AssignedOn)
        .Skip(offSet)
        .Take(12)
        .ToList()

answered Oct 07 '16 at 15:15

vamsi

352
1
3
11

this works but what i really want is first all null `acceptedOn` and then rest ordered by `AssignedOn`. Is that possible – M B Oct 07 '16 at 15:18
1

@MB that is exactly what my answer is doing. If acceptedon is null it defaults it to datetime.minimum value which is 00.00.0000 so those records are first in the list followed by next sort order – vamsi Oct 07 '16 at 15:20

score 0 · Answer 3 · answered Oct 07 '16 at 15:18

0

Your problem is that you are using .HasValue, which returns a bool, when you should be using .Value, which returns the actual value if not null.

Try this, I'm sure it will work...

.OrderBy(a=>a.AcceptedOn.Value)

answered Oct 07 '16 at 15:18

Avrohom Yisroel

8,555
8
50
106

You don't need to add the `.Value`. you can just use the column itself – M B Oct 07 '16 at 15:19
This is not the same thing – Gilad Green Oct 07 '16 at 15:19

score 0 · Answer 4 · answered Oct 07 '16 at 16:02

This is what ended up working perfectly for me

_ctx.Leads.Include(a => a.LeadAttachments)
    .Where(s => s.Name.ToLower().StartsWith(filter))
    .OrderByDescending(a=> a.AcceptedOn == null 
        ? DateTime.MaxValue 
        : a.AssignedOn).ThenByDescending(a=>a.AssignedOn)
    .Skip(offSet)
    .Take(12)
    .ToList();

Explanation:

First sort: get null AcceptedOn first
Then: order all records by AssignedOn

Linq to Sql - Incorrect syntax by keyword 'IS'

4 Answers4