Using LINQ to SQL group, sum and aggreate all together

Question

Hi,

I have two tables Students and Origami. Origami has Foreign Key of Students table. Each student can make one or more origami for each month.

Students sample data:

StudentId, FirstName, LastName

• 187 , John , Maslow
• 196 , Crystal , Hood
• 195 , Sarah , Lewis

Origami sample data:

OrigamiId, StudentId, CreationDate, NumberOfOrigami

• 1 , 187 , 5/17/2010 1:06:55 PM , 1
• 2 , 196 , 5/22/2010 1:31:28 PM , 2
• 3 , 187 , 6/18/2010 1:51:40 PM , 2
• 4 , 187 , 6/19/2010 2:13:35 PM , 1
• 5 , 196 , 7/17/2010 2:19:44 PM , 3
• 6 , 196 , 7/19/2010 2:23:02 PM , 2
• 7 , 195 , 7/20/2010 3:04:15 PM , 3

and many more records like that format.

I'd like to ge the total number of origami of each student monthly. Something that looks like the following:

• Name ,Jan/2010 , Feb/2010 and so on
• John Maslow , 2 , 3
• Crystal Hood , 4 , 5
• Sarah Lewis , 6 , 5

Thanks.

Update One

    Dim query = From st In db.Students _
                 Join or In db.Origami On or.StudentId Equals st.StudentId _
                 Group By or.StudentId Into TotalOrigami = Sum(or.NumberOfOrigami) _
                 Select StudentId, TotalOrigami

This query give only total origami of each student. But I want monthly count of origami for each student. Any suggestion is welcom.

Answer 1

It is possible to create an extra property that only holds the Month/Year on the origami object. and then use the query below

            var q = from st in db.Students 
                    join or in db.Origami on or.StudentId equals st.StudentID
                    group st by new { st.StudentId, or.MonthYear } into g
                    select new { g.Key.StudentId, g.Key.MontYear, Total = g.Count() };

I just saw that the example is in C# but I hope you will get through.

Answer 2

Here's working query:

Dim query = From st In db.Students _ 
                 Join ori In db.Origami On ori.StudentId Equals st.StudentId _ 
                 Group By key = New With {ori.Student, .MonthYear = (ori.CreationDate.Value.Month & "/" & tr.CreationDate.Value.Year)}  Into TotalOrigami = Sum(ori.NumberOfOrigami) _ 
                 Select key.Student.FirstName,key.Student.LastName,key.MonthYear,TotalOrigami