LINQ Left join on nullable column giving null reference exception

Question

I'm working on a linq query that has a left join involving a nullable column. This join is being done between a BackgroundColors table with an int Id column, and a table called MenuFolders which has a int? column called BackgroundColorId. In MenuFolders, every row has its BackgroundColorId set to null.

Every left join in my query works fine up to the join on these two tables. When I uncomment the backgroundColors left join with menuFolders , the query generates a NullReferenceException - "Object reference not set to an instance of an object." But I thought .DefaultIfEmpty() is supposed to take care of that. Here is my code. Keep in mind that the SQL equivalent is working just fine when run on SQL Server:

  var folderStructure = (from fa in folderAncestorsLanguage
                         from mf in menuFolders.Where(x => x.Id == fa.Id)
                         from mtf in menuTlbrMenuFolders.Where(x => (x.MenuToolbarId == toolbarId && x.MenuFolderId == fa.Id)).DefaultIfEmpty()
                         from mbc in backgroundColors.Where(x => x.Id == mf.BackgroundColorId).DefaultIfEmpty()//Left Join that is causing an exception
                         from lmf in languageMenuFolders.Where(x => x.MenuFolderId == mf.Id).DefaultIfEmpty()
                         where (mf.StatusId == 1)
                         select new
                         {
                             Id = mf.Id,
                             Name = lmf.Name,
                             DefaultName = mf.Name,
                             Description = mf.Description,
                             FolderId = fa.ParentFolderId,
                             OrderIndex = mf.OrderIndex,
                             IconUrl = mf.IconUrl,
                             IsFramework = mf.IsFramework,
                             BackgroundColor = mbc.HexCode == null ? null : mbc.HexCode,
                             IsModifiable = mf.IsModifiable,
                             iconCls = mf.iconCls
                         }).ToList();

I've also tried doing this query using standard linq instead of lambda expressions, but it still gives me the same error.

I took a look at this link earlier for help answering this question, but its answer didn't work for me: LINQ Join query (with nullable ref between table)

EDIT: I tried changing values in the BackgroundColorId column from null to integer values, and I'm still getting the same error.

When using the DefaultIfEmpty extension, the default value will be null if the object is a class, however, you can optionally specify a default value as a method parameter. That being said, in this case, I don't believe that you are using the DefaultIfEmpty extension properly -- I'm not sure what the rest of your code looks like, but I'm guessing that you could probably do without it. See: [Enumerable.DefaultIfEmpty](https://msdn.microsoft.com/en-us/library/bb360179(v=vs.110).aspx) — Algemist, Aug 10 '16 at 14:20
Try `var test = backgroundColors.Where(x => x.Id == 1).ToList();`. Are you getting the same exception? — Ivan Stoev, Aug 10 '16 at 14:21
@Ivan Stoev I tried it and `test` was set equal to the first row in the BackgroundColors table — C-Scholl20, Aug 10 '16 at 14:24
See, usually questions like this are closed as duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it), which most likely will happen also with yours. Since the query looks ok, you are the only one who can debug and see what the problem is. Can you post the exception stack trace? — Ivan Stoev, Aug 10 '16 at 14:30
\usp_MENU_MenuStructure_Get.cs:line 203 at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at EntityFramework.usp_MENU_MenuStructure_Get.Execute(Dictionary`2 parameters) in d:\Dev\src\Framework\Server\EntityFrameworkModel\Programmability\StoredProcedures\usp_MENU_MenuStructure_Get.cs:line 197 at EntityFramework.DatabseProgrammabilityBase.Execute(IDbDataParameter[] parameters) — C-Scholl20, Aug 10 '16 at 14:59
Line 197 is where `folderStructure` is defined, 203 is where the `select` clause starts. I tried commenting out any references to mbc and mf in my select to see if mf was nullified in that join. No luck. — C-Scholl20, Aug 10 '16 at 15:00
So these are actually EF queryables. And as I see, there is stored procedure involved which seem to throw NRE. — Ivan Stoev, Aug 10 '16 at 15:32
I'm in the process of converting a SQL stored procedure into an EF implementation. My LINQ query is essentially an attempt to return the same data as the SQL version in the form of a `List`. — C-Scholl20, Aug 10 '16 at 15:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120617/discussion-between-c-scholl20-and-ivan-stoev). — C-Scholl20, Aug 10 '16 at 16:28

score 3 · Accepted Answer · answered Aug 10 '16 at 19:38

var folderStructure = (from fa in folderAncestorsLanguage
                     from mf in menuFolders.Where(x => x.Id == fa.Id)
                     from mtf in menuTlbrMenuFolders.Where(x => (x.MenuToolbarId == toolbarId && x.MenuFolderId == fa.Id)).DefaultIfEmpty()
                     from mbc in backgroundColors.Where(x => x.Id == mf.BackgroundColorId).DefaultIfEmpty()//Left Join that is causing an exception
                     from lmf in languageMenuFolders.Where(x => x.MenuFolderId == mf.Id).DefaultIfEmpty()
                     where (mf.StatusId == 1)
                     select new
                     {
                         Id = mf.Id,
                         Name = (lmf == null) ? null : lmf.Name,
                         DefaultName = mf.Name,
                         Description = mf.Description,
                         FolderId = fa.ParentFolderId,
                         OrderIndex = mf.OrderIndex,
                         IconUrl = mf.IconUrl,
                         IsFramework = mf.IsFramework,
                         BackgroundColor = (mbc == null) ? null : mbc.HexCode,
                         IsModifiable = mf.IsModifiable,
                         iconCls = mf.iconCls
                     }).ToList();

I eventually figured it out, some of my joins resulted in tables that while they weren't empty, there were null rows, which I was referencing, thus causing the exception. Hopefully this helps someone else if they run into this issue.

LINQ Left join on nullable column giving null reference exception

1 Answers1