Entity framework where, order and group

c# entity-framework linq

Question

I'm using the following LINQ to select data from a table:

(from m in entity.Results
where m.Group == 0 ||
m.Group == 1
orderby m.Points descending
select m);

This gives me a result of all Users who are in Group 1 or 2. With that i can display the points they have. But this shows me the points they have in Group 1 and Group 2 separately.

How can i group them and display the total points they have? So instead of this (What i have now):

user1 - group1 - 10
user1 - group2 - 7
user2 - group1 - 7
user2 - group2 - 5

I want this:

user1 - total: 17
user2 - total: 12

How do i have to adjust my query to get a result set like that?

1
19
6/6/2012 11:33:45 AM

Accepted Answer

You need to group the users, then use Sum to calculate the TotalPoints:

from m in entity.Results
where m.Group == 0 || m.Group == 1
group m by m.User into g
let TotalPoints = g.Sum(m => m.Points)
orderby TotalPoints descending
select new { User = g.Key, Username = g.Key.Username, TotalPoints };
23
6/6/2012 12:39:56 PM

Popular Answer

entity.Results
      .Where(m => m.Group == 0 || m.Group == 1)
      .GroupBy(m => m.UserID)
      .Select(m => new { User = m.Key, TotalPoints = m.Sum(v => v.Points) })
      .OrderByDescending(m => m.TotalPoints);


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