How to improve poor performance of EF Core SQL query that sorts on a child collection

Question

My issue is with the queries that EF Core generates for fetching ordered items from a child collection of a parent.

I have a parent class which has a collection of child objects. I'm using Entity Framework Core 5.0.5 (code first) against a SQL Server database. I've tried to boil down the scenario, so let's call it an Owner with a collection of Pets. I often want a list of owners with their oldest pet, so I'll do something like

Context.Owners
.Select(owner =>
    new {
       Owner = owner,
       OldPet = owner.Pets.OrderBy(pet => pet.Age).LastOrDefault()   
    })
.Where(owner.Id == 1);

This worked fine before (on ef6) and works functionally now. However, the issue I have is that now EF Core translates these sub collection queries into something apparently cleverer, something like

 SELECT *
  FROM [Owners] AS [c]
  LEFT JOIN (
      SELECT *
      FROM (
          SELECT [c0].[Id] ... , ROW_NUMBER() OVER(PARTITION BY [c0].[OwnerId] ORDER BY [c0].[Age] DESC) AS [row]
          FROM [Pets] AS [c0]
      ) AS [t]
      WHERE [t].[row] <= 1
  ) AS [t0] ON [c].[Id] = [t0].[OwnerId]

The problem I'm having is that it seems to perform terribly. Looking at the execution plan it's doing a clustered index seek on the pets table, then sorting them. The 'number of rows read' is massive and the 'sorting' takes tens or hundreds of milliseconds.

The way EF6 does the same functionality seemed way more performant in this sort of scenario.

Is there a way to change the behaviour so I can choose? Or a way to rewrite this type of query such that I don't have this problem? I've tried many variations of using GroupBy etc and still have the same result.

user_10646316 · Answer

If you are doing FirstOrDefault in projection, EF Core has to create such join, which uses Window Function ROW_NUMBER. To get desired SQL it is better to rewrite your query to be more predictable for LINQ translator:

var query =
    from owner in Context.Owners
    from pet in owner.Pets
    where owner.Id == 1
    orderby pet.Age descending
    select new 
    {
        Owner = owner,
        OldPet = pet
    }

var result = query.FirstOrDefault();

How to improve poor performance of EF Core SQL query that sorts on a child collection

1 Answers