houman_ag
houman_ag

Reputation: 237

Using OrderBy in Linq

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

Answers (1)

Brian Rogers
Brian Rogers

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

Related Questions