Sorting Primary Entities on Properties of Doubly-Nested Entities-CodePudding

I'm using Entity Framework Core with ASP.Net MVC. My business object model consists, in part, of Jobs (the primary entities), each of which contains one or more Projects, and each Project has zero or more Schedules (which link to sets of Departments, but that's not important here). Each schedule has a StartDate and and EndDate.

Here is a (simplified) class diagram (which reflects the database schema as you would expect):

I want to sort the Jobs list by the earliest StartDateTime value in the Schedule entity. I haven't been able to come up with a LINQ chain that accomplishes this. For the time being, I have cobbed the functionality I want by using ADO.Net directly in my controller to assemble the Jobs list based on the following SQL Statement:

@"SELECT
    Jobs.JobId, 
    Jobs.JobName, 
    Jobs.jobNumber,
    MIN(ProjectSchedules.StartDateTime) AS StartDateTime
FROM 
    Jobs INNER JOIN Projects ON Jobs.JobID = Projects.JobID
         LEFT JOIN ProjectSchedules ON Projects.ProjectID = ProjectSchedules.ProjectID
GROUP BY Jobs.JobId, Jobs.JobName, Jobs.JobNumber 
ORDER BY StartDateTime"

I would prefer to use EF Core properly, rather than to do an end-run around it. What would be the LINQ statements to generate this SQL statement (or an equivalent one)?

CodePudding user response：

First we need to retrieve all the entities we can do this with the Include statement, and we use theninclude to retrieve entities one further down.

dbcontext.Jobs.Include(j => j.Projects).ThenInclude(p => p.Schedules)

Now that we have all the entities you can do all the sorting, grouping or whatever else you wish to do.

To me it sounds like you want to Orderby on schedule.startdatetime.

CodePudding user response：

You need a query that "collects" all StartDateTimes in schedules of projects per job, takes their lowest value then sorts by that value:

context.Jobs
    .OrderBy(j => j.Projects
        .SelectMany(p => p.Schedules)
        .Select(s => s.StartDate).OrderBy(d => d).First())
    .Select(j => new { ... })

As you see, there's not even a Min function in there. The function could be used, but it may perform worse, because it has to evaluate all StartDate values, while the ordering could make use of an ordered index.

Either way, for comparison, this is with Min():

context.Jobs
    .OrderBy(j => j.Projects
        .SelectMany(p => p.Schedules)
        .Select(s => s.StartDate).Min())