CODEWITHSUNDEEP
c#linq
Brandon
Brandon

Reputation: 69973

Grouping a LINQ query

I have three tables:

A Program is a type of event and a Schedule is a collection of ScheduleDates because the programs take place over several days.

I want to be able to display a list like the following:

// 1st is the earliest ScheduleDate and 14th is the latest. The records in between
// are ignored.
January 1-14
  Program 1    5 spots left
  Program 2    10 spots left

January 10-24
  Program 1    0 spots left

January 20-31
  Program 3    10 spots left

I need to go through all the schedules, and group them by the Start/End Date so that I can display all the programs occurring in that time period.

I'm not that familiar with LINQ, as far as I can get is this:

var schedules = from program in _db.Programs
                join schedule in _db.Schedules on program.Id equals schedule.ProgramId
                join date in _db.ScheduleDates on schedule.Id equals date.ScheduleId
                // a few where clauses
                orderby date.Startdate
                group date by date.Schedule into sch
                select sch;

I'm not sure if this is correct, but it does get me a grouped list of my Schedules and their associated Dates, and I can do a .First and .Last to get the January 1-14.

From here though, I'm not sure how I could then group them by matching Start/End dates and then group the items under the matches.

Upvotes: 4

Views: 174

Answers (1)

Jon Hanna
Jon Hanna

Reputation: 113232

You can group by an anonymous object that contains the elements you care about:

var schedules = from program in _db.Programs
            join schedule in _db.Schedules on program.Id equals schedule.ProgramId
            join date in _db.ScheduleDates on schedule.Id equals date.ScheduleId
            // a few where clauses
            orderby date.Startdate
            group new{Program = program, Schedule = schedule, Date = date} by new{date.Schedule.Startdate, date.Schedule.Enddate};

The above returns an IEnumerable<IGrouping> where the key is an anonymous type with a Startdate and Enddate property (both DateTimes), and the elements are anonymous types with Program, Schedule and Date elements. You can tweak this to include only those details you care about.

When used as objects, anonymous types implement a GetHasCode() and Equals that considers them equal if each of their corresponding properties are equal (similar to the default equality for structs, but compiled-in rather than through reflection so it performs better).

When used with other query providers (e.g. linq2sql, etc). The provider should be aware of this, and so pass the grouping down to the database, though sometimes it's imperfect at this (still works but not all the work done at the database layer).

Upvotes: 3

Related Questions