Brent Lamborn | LINQ to SQL Null check in Where Clause

<< Bye Bye Twitter | Microsoft Buys Powerset >>

LINQ to SQL Null check in Where Clause

25. June 2008

After testing several LINQ queries for ways to do a null comparison in the where clause, I found only one way to do it that returns the result set as though I had used "IS NULL" in a standard SQL query. The key is to use the object.Equals() method:

   1:private ProductDataContext db = new  ProductDataContext();

2:

   3:/// <summary>

   4:/// Return all products for a given manufacturer

   5:/// </summary>

   6:/// <param name="manufacturerName"></param>

   7:/// <returns></returns>

   8:public IQueryable<Product> GetProdByManu(String manufacturerName, int? catId)

9:{

10:

  11:    var products = from o in db.Products

  12:                   where o.manufacturerName == manufacturerName

  13:                   && object.Equals(o.categoryID, catId)

  14:                   select o;

15:

16:

  17:    if(products.Count() > 0)

  18:    {

  19:        return products;

  20:    }

  21:    else

  22:    {

  23:        return null;

  24:    }

  25:}

The above method GetProductsByManu accepts a parameter catID , a nullable integer. When calling the method if I pass null as the value for catID , LINQ will correctly return products where categoryID is null in the database. I find that if I use == null as show below, the results are inaccurate:

   1:    var products = from o in db.Products

   2:                             where o.manufacturerName == manufacturerName

   3:                             && o.categoryID == null

   4:                             select o;

In the above case, even though my tables contain products with a null categoryID, those products are not returned by LINQ. I'm not sure exactly why this is, and maybe someone with more LINQ experience than I can shed some light in the comments. I just know from now on, when doing a check for null in a LINQ where clause, object.Equals() is what I'll be using.

Hope this helps someone else with the same issues I had! :)

snippets linq

Comments

Paladin

7/1/2008 6:47:25 AM #

Interesting.

I saw the bad results I was getting with a variable with the value of null, and wound up testing the variable first and coding two different LINQ queries. I hadn't thought of object.Equals.

I tested this out in LINQPad.

int? c = ... ;

var q = from t in Tbl
where ...
select t;

First, considering the LINQ

where t.Col == c

When the variable's value is non-null, the generated SQL looks like this:

WHERE [t0].[Col] = @p0

but when the variable's value is null, it still looks like this

WHERE [t0].[Col] = @p0

Now, considering the LINQ

where object.Equals(t.Col == c)

When the variable's value is non-null, the generated SQL looks like this:

WHERE ([t0].[Col] IS NOT NULL) AND ([t0].[Col] = @p0)

but when the variable's value is null, the SQL looks like this

WHERE [t0].[ProductLineCategoryIdParent] IS NULL

So it looks as if LINQ is examining the contents of the variable and generating a different SQL statement depending upon the variable's value.

But suppose you have a variable

List<int?> cList = new List<int?> { 1,2 } ;

and a LINQ query

where t.cList.Contains(c)

This will work (generate the correct answers) as long as cList does not contain a null.

Brent

7/1/2008 6:59:29 AM #

"So it looks as if LINQ is examining the contents of the variable and generating a different SQL statement depending upon the variable's value. "

Wow, yeah it does look like it. Good find.

I also have not used LINQPad before. Looks like a great tool, downloading it now...

Jaccso

1/30/2009 9:52:54 AM #

That was absolutely what I was looking for!!!

ThankS!!

dan@stan.com

2/5/2009 2:00:49 AM #

Thanx it made my day
   var choosen = (from g in warrantyList
                           where g.Affar != null && g.Affar.part.Count() >0
                           join i in instituteList on g.Affar.part[0].Organisationsnummer equals i.Id
                           select i);