NULL Values LINQ to Entities

Working with two tables where the foreign key could be NULL, I noticed LINQ to Entities showed interesting results when it involved NULL values.

There is a lot of information on the web writing about the workaround. In particular this site was very clear:

Checking for possibly null values in LINQ

---

For my benefit, I have included these workarounds.

This will work:

   1: protected class zPages

   2: {

   3:     public int id { get; set; }

   4:     public int? categoryId { get; set; }

   5:     public string page { get; set; }

   6:     public string err { get; set; }

   7: }

   8:  

   9: adminDBEntities contxt = new adminDBEntities();

  10:  

  11: IEnumerable<zPages> query = contxt.WebPages.Where(p => p.categoryID == null).Select(p => new zPages { id = p.ID, page = p.name, categoryId = p.categoryID });

Within the zPages class, I have a nullable int (int?), which allows integers to have null values. The above query works because the Where clause explicitly equates to null: p.categoryID == null. 

However, if a variable is null and this variable replaces the explicit null, this causes problems:

   1: string CategoryId = null;

   2: IEnumerable<zPages> query = contxt.WebPages.Where(p => p.categoryID == CategoryId).Select(p => new zPages { id = p.ID, page = p.name, categoryId = p.categoryID });

Because the LINQ to Entities query will translate to:

   1: exec sp_executesql N'SELECT 

   2: [Extent1].[ID] AS [ID], 

   3: [Extent1].[name] AS [name], 

   4: [Extent1].[categoryID] AS [categoryID]

   5: FROM [dbo].[WebPage] AS [Extent1]

   6: WHERE [Extent1].[categoryID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=NULL

Notice the query equates @p__linq__0=NULL which will always return false. Testing for NULL within a SQL statement is @p__linq__0 IS NULL, however LINQ to Entities does not, which is why when involving variables with NULL values, the result’s collection return incorrectly.

The workaround:

   1: IEnumerable<zPages> query = contxt.WebPages.Where(p => CategoryId == null ? p.categoryID == null : p.categoryID == CategoryId).Select(p => new zPages { id = p.ID, page = p.name, categoryId = p.categoryID });

NOTICE within Where clause: 

p => requestVals.categoryId == null ? p.categoryID == null : p.categoryID == requestVals.categoryId

 {code.logos.com} writes that the above workaround generates some scary SQL, so they give another workaround:

   1: IEnumerable<zPages> query = 

   2:     (CategoryId == null ?

   3:     contxt.WebPages.Where(p => p.categoryID == null) :

   4:     contxt.WebPages.Where(p => p.categoryID == CategoryId)).Select(p => new zPages { id = p.ID, page = p.name, categoryId = p.categoryID });

This blog is for my notes. Much of the code presented is from my search of the web, which I will give reference not to all but some important sites.