LINQ to Entities generating incorrect SQL

Question

I am filtering an IQueryable to return all entities that have the field UserId (a nullable int) set to null. The query generates the incorrect SQL and thus fails -- the statement is as follows -

var filtered = certificates.Where(c => !c.UserId.HasValue).Select(c => c.SubjectName);

and the generated SQL is --

SELECT 
CAST(NULL AS varchar(1)) AS [C1], 
CAST(NULL AS int) AS [C2], 
CAST(NULL AS datetime2) AS [C3], 
CAST(NULL AS datetime2) AS [C4], 
CAST(NULL AS bit) AS [C5], 
CAST(NULL AS datetime2) AS [C6], 
CAST(NULL AS int) AS [C7]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

Any idea WTF is going on? The idea is simple I just want to return all the rows where the field UserId is false. UserId is nullable and the table being queried has three rows that match the condition described, however the LINQ query returns 0.

Thanks!

Answer 1

This is the kind of query that EF generates when it knows for sure that the query won't return any results. Such a query minimizes database processing.

How can EF be so sure? This can only be when for all it knows UserId in the database is not nullable. This, in turn, can only be when there's also a User reference in Certificate (the POCO class) that is mapped as required. Look for something like

HasRequired(t => t.User).WithMany(t => t.Certificates)

in an EntityTypeConfiguration<Certificate>, or in an override of OnModelCreating in your DbContext. (In code-first it is possible to have a required reference, while the accompanying primitive Id property is a nullable type. In an edmx file this doesn't validate).

So I think you have to map User as optional if in the database the foreign key is nullable.

Answer 2

I believe the reason it is not working for you is that c.UserId has a value, it is just null. You should compare it to null instead:

var filtered = certificates.Where(c => c.UserId == null).Select(c => c.SubjectName);

edit: Accidently had the wrong if statement in there.

Answer 3

Maybe you could try a more explicit option

  var filtered = certificates.Where(c => c.UserId == null).Select(c => c.SubjectName);