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.

Answer 1

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.