LINQ to Entities group by and Count()

Question

I have the following LINQ-to-Entities query

from r in ctx.Rs
join p in ctx.Ps on r.RK equals p.RK
group r by r.QK into gr
select new { QK = (int)gr.Key, Num = gr.Count() }

that runs against this schema

Table P  Table R   Table Q
 PK*
 RK ----> RK*
 Text     QK ------> QK*
          Text       Text

and gives this message if there is any record in Q with no corresponding record in P: "The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."

The problem is the gr.Count() in the last line, but I cannot find a solution. I have tried to test gr for null, but cannot find a way that works.

I have seen a number of solutions to a similar problem using Sum() instead of Count(), but I have not been able to adapt them to my problem.

I tried changing my query to look like the one in Group and Count in Linq issue, but I just got a different message.

I also looked at Group and Count in Entity Framework (and a number of others) but the problem is different.

Solution 1

group Key can't be null

var results = ctx.Rs.Where(r => r.QK != null)
    .GroupBy(r => r.QK)
    .Select(gr => new { Key = (int)gr.Key, Count = gr.Count() }
    .ToList();

PS.

1. Mostly, You don't need 'JOIN' syntax in Entity Framework. see: Loading Related Entities

2. Writing descriptive-meaningful variable names would significantly improve Your codes and make it understandable. Readability does matter in real world production.

Solution 2

I'm having trouble reading your format. But can you try:

from r in ctx.Rs
join p in ctx.Ps.DefaultIfEmpty() on r.RK equals p.RK
group r by r.QK into gr
select new { QK = (int)gr.Key, Num = gr.Count(x => x.RK != null) }

With DefaultIfEmpty and x => x.RK != null being the changes.

Comments

[Jim S]

Thanks for answering. Unfortunately, this still gives the same message.

[Jim S]

Well, actually it's less than perfect. It runs fine, but gives the wrong answer because it includes Rs that are not in P. When I try to fix that with a join, I get the same message. Is there another way to exclude Rs that are not in P? Any other suggestions?

[Jim S]

I did get your solution to work and return the correct result. The key is the '.Where(r => r.QK != null)' in the first line of your solution. Thanks again.