Twitter <%=Resources.labels.subscribe %>

LINQ to SQL Null check in Where Clause

  • 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! :)

    Tags:

    Permalink |

Comments (5) -
Paladin
Paladin United States
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.  



Reply

Brent
Brent United States
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...

Reply

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

That was absolutely what I was looking for!!!

ThankS!!

Reply

dan@stan.com
dan@stan.com Sweden
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);

Reply

antonio
antonio Mexico
4/18/2009 3:26:27 PM #

It seems that this does not work when the property to compare is the foreign key in a relationship even when it allows nulls.

Reply

Pingbacks and trackbacks (4)+

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Intro

My name is Brent Lamborn. I´m 35 years old and I live in Nebraska. I've been coding with the .NET Framework since 2003. I'm a former Marine and I have 5 boys.


View Brent Lamborn's LinkedIn profileView Brent Lamborn's profile

Sponsored By

Categories

Tag Cloud

Blog Roll