Linq query works with null but not int? in where clause.

Question

I have a linq query function like (simplified):

public IList<Document> ListDocuments(int? parentID)
{
    return (
        from doc in dbContext.Documents
        where doc.ParentID == parentID
        select new Document
        {
            ID = doc.ID,
            ParentID = doc.ParentID,
            Name = doc.SomeOtherVar
        }).ToList();
}

Now for some reason when I pass in null for the parentID (currently only have data with null parentIDs) and I get no results.

I copy and paste this query into LinqPad and run the following:

from doc in dbContext.Documents
where doc.ParentID == null
select doc

I get back a result set as expected...

The actually query has left join's and other joins but I have removed them and tested it and get the same result so the joins are not affecting anything. The app and LinqPad are both connected to the same DB as well.

Edit: Tested with just 'null' in the appliction query and it returns the result as expected so the issue is using null vs int?. I have updated question to make it more useful to others with the same issue to find this thread.

Answer 1

Literal null values are handled differently than parameters which could be null. When you explicitly test against null, the generated SQL will use the IS NULL operator, but when you're using a parameter it will use the standard = operator, meaning that no row will match because in SQL null isn't equal to anything. This is one of the more annoying .NET/SQL semantic mismatches in LINQ to SQL. To work around it, you can use a clause like:

where doc.ParentID == parentID || (doc.ParentID == null && parentID == null)

Answer 2

You can use also something like ...

from doc in dbContext.Documents
where doc.IsParentIDNull()
select doc

It worked for me! hope it work for you!