CODEWITHSUNDEEP
c#linqentity-framework-6linq-to-entitieslinq-expressions
Joe Ricklefs
Joe Ricklefs

Reputation: 660

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?

Upvotes: 1

Views: 423

Answers (2)

Joe Ricklefs
Joe Ricklefs

Reputation: 660

I did a bunch more digging on this and the issue with the Groups with 0 items, utmately ended up being that the Grouping was being done on the Object using Nulls, howerver the joins behind the scenes were using inner joins. So it would create the proper number groups but wouldn't just not populate them because of the Referential Integrity on the Database. I ended up creating a View with the Proper Relations for this and after handling the nulls as suggested below I have it workin

Upvotes: 0

Power Mouse
Power Mouse

Reputation: 1441

as example: 

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

just modified answer to add another syntax option

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

Upvotes: 2

Related Questions