Linq to SQL check for null strangeness

Question

I have a Linq to SQL query that behaves (in my opinion) very strange when I check for null values.

There is a record in the DB as shown by the last Linq, but why does the 1st two queries not show the record?

//Check
(record.SomeID == null ? "Yes" : "No"); //This prints Yes

//LINQ
var q = (from t in mySQLTable
         where t.PKID == record.PKID && 
         t.SomeID == record.SomeID
         select t).FirstOrDefault();
 //This prints nothing.  I.e. no records found

var q2 = (from t in mySQLTable
          where t.PKID == record.PKID &&
          t.SomeID == (record.SomeID == null ? null : record.SomeID)
          select t).FirstOrDefault();
 //This also prints nothing.  I.e. no records found

 var q3 = (from t in mySQLTable
           where t.PKID == record.PKID &&
           t.SomeID == null
           select t).FirstOrDefault();
 //This prints 1 record

Run profiler and compare result SQLs, i think you will understand what's going wrong — Nigrimmist, Aug 08 '16 at 06:59
@user3185569 http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server — Rumpelstinsk, Aug 08 '16 at 06:59
I think the first one is generating a query like `SomeID = NULL` while the last is generating something like `SomeID IS NULL` — Zein Makki, Aug 08 '16 at 06:59
@Rumpelstinsk you should have written `null = null` because `==` made it look like a C# syntax. — Zein Makki, Aug 08 '16 at 07:00
NULL is not a value. Null is a placeholder for the absence of a reference to an object. — vassilag, Aug 08 '16 at 07:05

score 1 · Accepted Answer · answered Aug 08 '16 at 07:11

1

You can overcome this issue using the below query:

bool isNull = record.SomeID == null;

var q = (from t in mySQLTable
         where t.PKID == record.PKID 
         && ( (isNull && t.SomeID == null)
             ||
              (!isNull && t.SomeID == record.SomeID)
            )
         select t).FirstOrDefault();

answered Aug 08 '16 at 07:11

Zein Makki

28,200
5
47
57

Yes, that is working. This however can become quite difficult to read (and lots of work) if there a number of ID's that you are checking. Maybe an extension method might be the answer if this are used frequently. Thanks. – Cameron Castillo Aug 08 '16 at 07:46

Linq to SQL check for null strangeness

1 Answers1