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

Maybe you could try a more explicit option

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

Answer 3

I am adding this answer as I spent quite a while trying to diagnose this issue and maybe it will help someone.

I am using Entity Framework 6.1.3 and noticed that when I used the following query it would never return anything, even though there was items in the database that matched this condition.

dbContext.Items.Where(n => n.TypeID == null)

When I looked at the query it was executing it was using the same as the op:

SELECT 
CAST(NULL AS int) AS [C1], 
CAST(NULL AS int) AS [C2], 
CAST(NULL AS varchar(100)) AS [C3], 
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

I knew that this meant that EF thought that this field could never be null and was returning an empty set and as I was using code first entity framework design I looked at the model declaration for tblItems but didnt see any issue here, I even tried adding .Optional() to the property definition with no luck.

It turned out that I had not configured the relationship between tblItems and tblType correctly on the tblType side and was using .HasRequired() instead of .HasOptional() when defining the relationship between the two tables.

Summary: If you are seeing this default query being used, and the table has relationships with other table(s), make sure both sides of the relationships are defined correctly in your model.