Entity framework where, order and group

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?

Answer 1

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 };

Answer 2

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);

Answer 3

Hi Vivendi use this(Please edit according to your requirement)

var q = (from h in entity.Results
group h by new { h.UserID} into hh
select new {
    hh.Key.UserID,
    Score = hh.Sum(s => s.Points )
}).OrderByDescending(i => i.Points);

Output

total: 17

total: 12