LINQ Query Grouping

Question

I have three tables:

• Program
  
  • Id
  • Name
• timetable
  • Programid
  • AvailableSpots
• ScheduleDates
  • ScheduleId
  • Startdate
  • Endate

A program is an event type, and Schedule is a collection of ScheduleDates because programs run for several days.

I want to display a list as shown below:

// 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 start / end date so that I can display all the programs occurring over this period of time.

I am not familiar with LINQ, as far as I can understand it:

 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 gives me a list of grouped schedule lists and their associated dates, and I can do .First and .Last to get January 1-14 .

From now on, I'm not sure how I could group them by matching start and end dates and then grouping the elements according to matches.

Answer 1

You can group an anonymous object containing items that interest you:

 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 the properties Startdate and Enddate (both DateTime s), and the elements are anonymous types with Program , Schedule and Date elements. You can configure this to include only the parts you care about.

When used as objects, anonymous types implement the GetHasCode() and Equals tags, which consider them equal if each of their respective properties is equal (similar to the default equality for struct s, but compiled, rather than through reflection, to make it work better).

When used with other query providers (e.g. linq2sql, etc.). The supplier should be aware of this, and therefore pass the grouping to the database, although sometimes it is imperfect at the same time (it still works, but not all the work performed at the database level).