9

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.

Community
  • 1
  • 1
Jim S
  • 1,069
  • 3
  • 10
  • 17

2 Answers2

18

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.

aifarfa
  • 3,939
  • 2
  • 23
  • 35
  • 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 Jan 23 '13 at 16:23
  • 1
    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. – Jim S Jan 23 '13 at 16:40
5

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.

Joe
  • 80,724
  • 18
  • 127
  • 145