How to group by in LINQ?

Question

I need to return the last 30 days of a speciefic user daily appointments and check if the user made at least 8 hours of appointments for each day.

in sql i can do that with this command:

select IDAppointment,IDUser, SUM(DurationInHours) from Note where AppointmentDate > *lastmonth and IDUser = @userID group by IDUser,IDAppointment,AppointmentDate

and after that i get the result and validate the DurationInHours(double type).

Is it possible to do it using LINQ? Get the list of the last month user appointments and validate it day by day

Thanks!

possible duplicate of [Group by in LINQ](http://stackoverflow.com/questions/7325278/group-by-in-linq) — Brian Maupin, Jul 30 '13 at 16:43

NinjaNye · Accepted Answer · 2013-08-01T17:15:04.717

This should be roughly there although this is off the top of my head as not at an IDE.

var result = context.Notes
                    .Where(n => [Your where clause])
                    .GroupBy(n => new { n.IDUser, n.IDAppointment, n.AppointmentDate})
                    .Select(g => new {
                                   g.Key.IDAppointment,
                                   g.Key.IDUser,
                                   g.Sum(n => n.DurationInHours)});

UPDATE:

For reference your where clause will be something like this... (again off the top of my head)

DateTime lastMonth = DateTime.Today.AddMonths(-1);
int userId = 1 // TODO: FIX
var result = context.Notes.Where(n => n.AppointmentDate > lastMonth
                                   && n.IDUser = userId)

Resulting in....

DateTime lastMonth = DateTime.Today.AddMonths(-1);
int userId = 1 // TODO: FIX
var result = context.Notes
                    .Where(n => n.AppointmentDate > lastMonth
                             && n.IDUser = userId)
                    .GroupBy(n => new { n.IDUser, n.IDAppointment, n.AppointmentDate})
                    .Select(g => new {
                                   g.Key.IDAppointment,
                                   g.Key.IDUser,
                                   g.Sum(n => n.DurationInHours)});

Should note that the OP mentions `30 days`, not `1 month`, which is different, but it is the same idea. — Bob., Jul 30 '13 at 18:02
It almost work, but my repository return a List by default. And the groupby method changes the type of it. How can i convert the "result" to a Note object again? — gog, Jul 30 '13 at 18:10
@ggui if you turn it back into a list of notes you will loose the grouping and sum. You could add the notes to the anonymous result by adding `Notes = g` after the sum. This would let you select the notes that make up each group. — NinjaNye, Jul 30 '13 at 21:17

score 2 · Answer 2 · edited Sep 07 '13 at 21:55

Here is a solution which I tested.

DateTime lastMonth = DateTime.Today.AddMonths(-1);
int selectedUserId = 2; 

var notes = new List<Note>(
    new Note[] {
        new Note() { 
            AppointmentDate = new DateTime(2013,7,30){}, 
            IDAppointment = 1, IDUser = 1, DurationInHours = 1
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,30){}, 
            IDAppointment = 1, IDUser = 1, DurationInHours = 2
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,30){}, 
            IDAppointment = 1, IDUser = 1, DurationInHours = 3
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,28){}, 
            IDAppointment = 2, IDUser =  2, DurationInHours = 2
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,28){}, 
            IDAppointment = 2, IDUser =  2, DurationInHours = 3
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,27){}, 
            IDAppointment = 2, IDUser =  2, DurationI nHours = 4
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,26){}, 
            IDAppointment = 3, IDUser =  3, DurationInHours = 3
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,25){}, 
            IDAppointment = 3, IDUser =  3, DurationInHours = 4
        },
        new Note() {
            AppointmentDate = new DateTime(2013,7,24){}, 
            IDAppointment = 3, IDUser =  3, DurationInHours = 5
        }
    }
);

var results = from n in notes
              group n by new {n.IDUser, n.IDAppointment, n.AppointmentDate}
              into g
              where g.Key.AppointmentDate > lastMonth && 
                    g.Key.IDUser == selectedUserId
              select new {
                  g.Key.IDAppointment, 
                  g.Key.IDUser, 
                  TotalHours = g.Sum(n => n.DurationInHours)
              };

The summation property needed to be given a name explicitly (i.e. TotalHours) or else you get error CS0746: Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

thats right matzoi, but i cant figure how to use the list that is returns to make the validation and send an email to the user. But thanks — gog, Jul 30 '13 at 18:26
If what you are asking is how to cast the results into elements of type Note, you only need to construct those objects appropriately in the select clause. Like so... var results = from n in notes group n by new {n.IDUser, n.IDAppointment, n.AppointmentDate} into g where g.Key.AppointmentDate > lastMonth && g.Key.IDUser == selectedUserId select new Note() { IDAppointment = g.Key.IDAppointment, IDUser = g.Key.IDUser, DurationInHours = g.Sum(n => n.DurationInHours) };` — matzoi, Jul 30 '13 at 19:19

How to group by in LINQ?

2 Answers2