CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-6
Inge Schepers
Inge Schepers

Reputation: 33

How to sort LINQ / Entity Framework query by grandchild property

I am running into a problem with a LINQ query. I have the following data structure (Entity Framework classes mapped to a SQL Server DB):

Event has an EventBlocks property with 0 or more EventBlock objects as children. EventBlock has a Details property with 0 or more EventBlockDetail objects as children. EventBlockDetail has a DetailDate and a StartTime.

I need to show the next 3 upcoming events on a website. Sort order is determined by the event's earliest start date and time, which is stored in Event -> EventBlock -> EventBlockDetail. If the next 2 or 3 upcoming EventBlockDetails happen to belong to the same event, that event should only be shown once, and another event should show up in the top 3.

Is there any way to accomplish this with a single LINQ query, so I won't have to do a lot of looping and comparing after getting ALL the event data from the database?

To illustrate what I am trying to do, here is a fictitious query, which will obviously not work because the first OrderBy doesn't know which property to order by. 

var result = this.Context.Events.Where(
    e => e.WebsiteId == websiteId && 
        e.EventBlocks.All(
            b => b.Details.All(
                d => d.DetailDate >= DateTime.Now
            )
        )
    ).OrderBy( 
        e => e.EventBlocks.OrderBy(
            b => eb.Details.Min(d => d.DetailDate)).ThenBy(
            b => eb.Details.Min(d => d.StartTime))
    ).Take(nrOfEvents).ToList();

Upvotes: 1

Views: 802

Answers (1)

juharr
juharr

Reputation: 32266

You should be able to select the event and the earliest date and time by ordering all the events details by the date and time and taking the first one. Then you can use that detail for filtering and ordering the events. 

var result = Context.Events
    .Where(e => e.WebsiteId == websiteId)
    .Select(e => new 
    { 
        Event = e, 
        StartDetails = e.EventBlocks
            .SelectMany(b => b.Details)
            .OrderBy(d => d.DetailDate) 
            .ThenBy(d => d.StartTime)
            .FirstOrDefault()
    })
    .Where(x => x.StartDetails.DetailDate >= DateTime.Now)
    .OrderBy(x => x.StartDetails.DetailDate)
    .ThenBy(x => x.StartDetails.StartTime)
    .Take(nrOfEvents)
    .Select(x => x.Event)
    .ToList();

Note that the x => x.StartDetails.DetailDate >= DateTime.Now filter would potentially remove something that hasn't started yet if the DetailDate has time truncated. In which case you might actually want to filter on DateTime.Today but also filter if the time is before the current time if the date is today. For example if the detail has a date of 2019-09-25 (presumably stored with a time of midnight) then comparing to 2019-09-25 at 8am (if that's the current date time) would fail even if the detail's start time is after 8am.

So maybe that filter should be the following assuming StartTime is a Timespan or something similar based on whatever StartTime is.

.Where(x => x.StartDetails.DetailDate > DateTime.Today ||
            (x.StartDetails.DetailDate == DateTime.Today &&
            x.StartDetails.StartTime >= DateTime.Now.TimeOfDay))

This would make more sense and be a more simple query if the date and time where stored in one column instead of two.

Upvotes: 1

Related Questions