include group by in c# linq query

Question

I have this linq query which I want to include group by some specific fields:

 from x in db.Schedule
 join y in db.Schedule on x.ID equals y.ID - 1
 join z in db.Locations on x.Line.ToString() + '-' + x.Expedition equals z.LocationCode
 where Convert.ToInt32(y.StopOrder) >= Convert.ToInt32(x.StopOrder) && x.NameOfTown == departingBusStation && dest.Contains(x.Line)
 where x.NameOfTown == departingBusStation
 select new { x.NameOfLine, x.DepartureTime, x.DestBusStationCode, x.StopOrder, z.LocationID }

In that linq query I want to add a group by x.DestBusStationCode and x.DepartureTime but modifying the query to something like this:

 from x in db.Schedule
 join y in db.Schedule on x.ID equals y.ID - 1
 join z in db.Locations on x.Line.ToString() + '-' + x.Expedition equals z.LocationCode
 where Convert.ToInt32(y.StopOrder) >= Convert.ToInt32(x.StopOrder) && x.NameOfTown == departingBusStation && dest.Contains(x.Line)
 where x.NameOfTown == departingBusStation
 orderby x.DepartureTime ascending
 group x by new {x.DepartureTime, x.DestBusStationCode}
 select new { x.NameOfLine, x.DepartureTime, x.DestBusStationCode, x.StopOrder, z.LocationID }

But I'm getting multiple errors with that approach.

Instead of just downvoting, at least tell the threadstarter WHY it was downvoted? — Recipe, Oct 30 '14 at 16:28
@Recipe Users are not required to comment when downvoting, but not even including the error message that the code generates sounds like a good place to start for me. Combine that with a lack of explanation of what the code should do and the question is very much unanswerable. — Servy, Oct 30 '14 at 16:40
screenshot with errors I'm getting: http://i.gyazo.com/67b2da21e7b42589b7035940959f637c.png — Laziale, Oct 30 '14 at 16:43
@Laziale Include the errors you have, as text, in your question, not as a link to an image in a comment. — Servy, Oct 30 '14 at 16:59

score 1 · Accepted Answer · answered Oct 30 '14 at 16:44

Once you group by something you can only have those properties in your select plus any aggregate value such as Count and Sum. This is because you are saying give me a single row where the grouped by properties are the same, so other properties may have multiple values for the group. The following would group by DepartureTime and DestBusStationCode

from x in db.Schedule
                 join y in db.Schedule on x.ID equals y.ID - 1
                 join z in db.Locations on x.Line.ToString() + '-' + x.Expedition equals z.LocationCode
                 where Convert.ToInt32(y.StopOrder) >= Convert.ToInt32(x.StopOrder) && x.NameOfTown == departingBusStation && dest.Contains(x.Line)
                 where x.NameOfTown == departingBusStation
                 orderby x.DepartureTime ascending
                 group x by new {x.DepartureTime, x.DestBusStationCode} grp
                 select new { grp.Key.DepartureTime, grp.Key.DestBusStationCode }

You can no longer include NameOfLine, StopOrder and LocationId in your results because these properties may differ among any given group with the same DepartueTime andDestBusStationCode`.

Thanks @ben-robinson, so what are my options to select the fields I have but not have duplicate rows based on DepartureTime and DestBusStationCode? — Laziale, Oct 30 '14 at 16:50
If I understand you correctly then you can't. If you have for example 2 records with the same DepartureTime and DestBusStationCode each record may have different NameOfLine, StopOrder and LocationId. — Ben Robinson, Oct 30 '14 at 17:05

include group by in c# linq query

1 Answers1