Question Using LINQ to SQL group, sum and aggreate all together

[Narazana]

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


Here's what I tried so far:

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 welcome.

Thanks.

 

[jmcilhinney]

What you're asking for is not really possible. That's basically a pivot query, which you're not going to do with LINQ. When you write a LINQ query you need to specify exactly how many properties the items in the result set have. You don't know that when you write the query so you can't write the query. What you could do would be to have two properties in your result set and have one of them be an array or collection, which can contain an indeterminate number of monthly totals for each student.

 

[Narazana]

Advice on how to solve this problem

What you're asking for is not really possible. That's basically a pivot query, which you're not going to do with LINQ. When you write a LINQ query you need to specify exactly how many properties the items in the result set have. You don't know that when you write the query so you can't write the query. What you could do would be to have two properties in your result set and have one of them be an array or collection, which can contain an indeterminate number of monthly totals for each student.

I'm stuck with this for 2 days now. Please give me some advice on how to solve this problems. If possible, please give links to other sites that might help me with this. Thanks

 

[jmcilhinney]

As I said, there is no solution to the problem as stated. You have to change your expectation. I made a suggestion in my previous post. Are you happy to go that way? If so then you need to say so. We only know what you tell us.

 

[Narazana]

As I said, there is no solution to the problem as stated. You have to change your expectation. I made a suggestion in my previous post. Are you happy to go that way? If so then you need to say so. We only know what you tell us.

Thanks for reply. Please ignor my sample code of LINQ to SQL above. If there's another way for this problem, I'm all for it. All I need right now is to get the result like the following , given the data sample above:

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

Any sugestion is very welcome.