In Entity Framework Core, with one query, how do I select objects by the order of a parent object?

entity-framework entity-framework-core linq linq-to-sql

Question

I'm working in Entity Framework Core 2.0, and I'm trying to do something like this. I want a list of all the classes taught by the oldest teacher. I tried to make the two requests into one query, but I couldn't make it work. Is there a concise way to make this one query?

private async Task<Teacher> GetOldestTeacher(int schoolId)
{
    using (var db = new SchoolContext())
    {
        return await db.Teacher
            .Where(t => t.SchoolId == schoolId)
            .OrderByDescending(t => t.DateOfBirth)
            .FirstAsync();
    }
}

public async Task<IEnumerable<Class>> GetOldestTeachersClasses(int schoolId)
{
    var oldestTeacher = await GetOldestTeacher(schoolId);
    using (var db = new SchoolContext())
    {
        return await db.Class
            .Where(c => c.TeacherId == oldestTeacher.Id && c.SchoolId == schoolId)
            .ToListAsync();
    }
}

This isn't exactly my code, but it's close enough to what I'm shooting for. This works, but I'm looking to make it more efficient. Any help would be appreciated.

1
0
11/15/2017 11:12:43 PM

Accepted Answer

Assuming there exists a navigation property called Classes in the Teacher class, you could do this:

public async Task<IEnumerable<Class>> GetOldestTeachersClasses(int schoolId)
{
    using (var db = new SchoolContext())
    {
        return await db.Teacher
            .Where(t => t.SchoolId == schoolId)
            .OrderByDescending(t => t.DateOfBirth)
            .Take(1)
            .SelectMany(t => t.Classes)
            .ToListAsync();
    }
}

If you use First, you inevitably ask EF to immediately load the object into memory. Because of that, if you use that returned object to do the further parts of the query, you would actually be doing at least 2 roundtrips to the database and performing 2 queries instead of one despite in your C# code there being only one expression.

The trick here is that you filter the teachers just as you've already done, and instead of loading the first one using First(), you Take() the topmost one, according to the ordering requirements. The key difference is that Take will not actually load the object immediately - instead, it enables you to further specify the query which will be translated to one proper SQL query.

The reason for using SelectMany() is that semantically, there still can be more than one teachers as the Take() call actually returns an IQueryable<Teacher> object. But the semantics of your filter criteria ensures that in reality, there will only be 0 or 1 teacher, so collecting his/her classes using SelectMany will result in only his/her taught classes. You could also do the same by doing a Join as DevilSuichiro remarked in the comments.

However, make sure you verify the generated SQL. I personally don't yet have experience with EF Core 2.0 only with 1.x, which sometimes failed to translate rather trivial queries to proper SQL statements and instead performed a lot of work in my app. The last time I checked the 2.0 roadmap it was promised to significantly improve the translator, but I've written this code just from the top of my head so a proper verification is required.

3
11/16/2017 9:02:59 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow