Reputation: 237
I have two tables. One is called Round
, which contains two columns called CompetitionId
and StartDate
(plus a bunch of other columns unimportant to this question). I also have a table called Competition
with a column named Id
. The Round.CompetitionId
is a foreign key to Competition.Id
(a one-to-many relation -- one competition could have many rounds).
So when Entity Framework translates these tables to C# classes, it gives me two classes, one Round
which has a reference to Competition
and one Competition
with an IEnumerable<Round>
.
Now I want to run a Linq query that returns all my competitions ordered by the StartDate
stored in Round
table but grouped together by the logical round number as determined by the date. So, for example, say I have two competitions each with 3 rounds. I want the two first-rounds (plus their competition info) to be returned, followed by the two second-rounds and so on.
Any ideas on how I can do this?
Upvotes: 2
Views: 88
Reputation: 129807
How about something like this:
var results = Competitions
.SelectMany(c =>
c.Rounds
.OrderBy(r => r.StartDate)
.Select((r, i) => new
{
RoundNumber = i + 1,
Round = r,
Competition = c
})
)
.OrderBy(r => r.RoundNumber)
.ThenBy(r => r.Round.StartDate)
.ThenBy(r => r.Competition.Id)
.ToList();
Then you can enumerate the results like this, for example:
foreach (var item in results)
{
Console.WriteLine(string.Format("{0:MMM dd, yyyy} - Round {1} of Competition {2}",
item.Round.StartDate, item.RoundNumber, item.Competition.Id));
}
Demo: https://dotnetfiddle.net/U515UK
Upvotes: 3