Nullable Field and SQL Is Null Issue

Question

There are tons of Q&A on stackoverflow related to my question , but I cannot deduce the reasoning of the issue and the solution that works best in this scenario;

So I've a method that allows you to pass a parentID, and based on the value records will be filtered using a LINQ query. The field in the database allows NULL values. Now If I compare fields using == operator in the where clause, the sql emitted is wrong (it doesn't uses IS NULL for the comparison), and hence query yields 0 results. I solved this using Object.Equals() method. That worked, but now I get an exception on passing a NON NULL value, an integer

Unable to create a constant value of type 'System.Object'. Only primitive types or enumeration types are supported in this context.

So I wrote a simple method

using (TestEntities context = new Entities())
{
    return from c in context.ItemMappings
           where c.ParentID.Equals(parentID)
           select new ItemDTO
           {
               ItemID = c.Item.ItemID,
               ItemName = c.Item.ItemName,
               ItemType = new ItemTypeDTO
               {
                   TypeID = c.Item.Type.TypeID,
                   TypeName =c.Item.Type.TypeName
               };
}

score 3 · Accepted Answer · answered Mar 25 '13 at 16:05

3

Yes your problem would also occur if it's SQL. You have to handle null explicitly, and this should work:

Where (parentID == null && c.ParentID == null) || (parentID == c.ParentID)

This assumes that you want a null to match. If you want null to return all the results unfiltered, instead do:

Where (parentID == null) || (parentID == c.ParentID)

I've had trouble with this even sometimes, and found the way LINQ would translate correctly all the time was to do:

Where (parentID == null) || (parentID != null && parentID == c.ParentID)

This is because even in SQL, if you do where ParentID = @ParentID, a null match returns no results, and you have to use ISNULL to escape it to blank.

answered Mar 25 '13 at 16:05

Brian Mains

50,520
35
148
257

I'll try that in a while, and I know by looking at it that it will work. BTW, I understand the reasoning that I'll get the same issue in SQL , but thought LINQ engine will emit the right SQL based on my value. I was confused more about the exception that I got while using Object.Equals – Kunal Mar 25 '13 at 16:11
I'm not sure of the particularities of that error; that one is a new one to me... Unfortunately EF does not look to make those kinds of optimizations in SQL queries. – Brian Mains Mar 25 '13 at 16:17

score 1 · Answer 2 · answered Apr 30 '14 at 11:35

1

To allow nullable You can also try Like this

  GSectionID = emp.SectionID ?? Guid.Empty,

answered Apr 30 '14 at 11:35

Mohammad Atiour Islam

5,380
3
43
48

score 1 · Answer 3 · answered May 05 '14 at 18:38

In EF6 you can use UseCSharpNullComparisonBehavior to solve this problem. You need to set the context option UseCSharpNullComparisonBehavior to true and it's going to be behave like C#.

objectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;

You can see more in the following link: http://entityframework.codeplex.com/workitem/145

Nullable Field and SQL Is Null Issue

3 Answers3