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