LINQ to Entities group by and Count()

c# count entity-framework group-by linq

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.

1
8
5/23/2017 11:54:35 AM

Popular Answer

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.

17
1/23/2013 4:57:34 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow