Search code examples
c#linqentity-framework-6linq-to-entitieslinq-expressions

Proper way to query DataContext using Group by that has fields with Nulls


I'm using Entity Framework, And I'm querying the Data using a Group By clause. In the Group by there are multiple columns that have to be used. Some of the columns can have nulls. The issue is that when it comes back it has all the proper group counts but it does not add anything to the groups. So the Group is a Group of 0 items. here is the code below.

 using (_context = new DbContext())
                {

                    var groups = await _context.vw_PersonItem
                    .AsExpandable()
                    .Where(x => x.PersonName != "")
                    .GroupBy(x => new
                    {
                        x.PersonName,
                        x.Addressline1,
                        x.State,
                        x.Zip,
                        x.FavoriteColor   //< This Can Be Null
                    })
                    .ToListAsync(); 




                  int uniqueCount = 1;

                    foreach (var grp in groups)   // <  It has groups with 0 items
                    {
                            uniqueCount++;

                    }
};

It doesn't throw an error, and it does count the number of grouped items properly but, where it finds a null in one of the Group By Fields, the Group has a count of 0?
Any Advice?


Solution

  • as example:

    .GroupBy(x => x?.FavoriteColor ?? -1)
    

    just modified answer to add another syntax option

    GroupBy(x => x.FavoriteColor == null ? -1 : x.FavoriteColor)