EF Queries degraded on upgrading from net core 2.1 to net 6

Question

I've been upgrading my AWS Lambdas from .NET Core 2.1 to .NET 6 and have been experiencing issues with the Entity Framework experiencing slower queries than what was produced with .NET Core 2.1. I've zoomed in on a specific query that is taking entirely too long (2+ seconds) where the same code executed in less than 100-200ms with .NET Core 2.1

I use the following dependencies for .NET 6

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.1" />
  </ItemGroup>

I generate a dbContext model using:

Scaffold-DbContext "server=server.com;port=3306;user=auser;password=pass;database=adatabase" Pomelo.EntityFrameworkCore.MySql -OutputDir DBModels -f

The code that now executes much slower:

vActiveSessions = _context.BiddingSessions
            .AsNoTracking()
            .Include("Bids.Character")
            .Include("IdItemNavigation")
            .Where(x => x.ClientId == "99IK2BLeIEWdV6bF9jFmcQ==" && x.State == 0).ToList();

The issue appears to be with .Include("Bids.Character") as when I am debugging and remove that include the query executes quickly. I used LINQPad 7 to verify and took the SQL it generates and ran it in mysql workbench and confirmed the query runs and executes slow there as well. The SQL that gets produced definitely looks more complicated than I expected:

SELECT `b`.`id`, `b`.`clientId`, `b`.`AllowDeletes`, `b`.`Auctioneer`, `b`.`AutoAdjustBids`, `b`.`BidType`, `b`.`CreatedTimestamp`, `b`.`Duration`, `b`.`EndTimestamp`, `b`.`id_item`, `b`.`ItemQuantity`, `b`.`MaximumBid`, `b`.`MinimumBid`, `b`.`notes`, `b`.`State`, `b`.`UpdatedTimestamp`, `i`.`id_item`, `t`.`id`, `t`.`session_id`, `t`.`character_id`, `t`.`Rank`, `t`.`Timestamp`, `t`.`UpdatedTimestamp`, `t`.`User`, `t`.`Value`, `t`.`id_character`, `t`.`clientId`, `t`.`active`, `t`.`class`, `t`.`deleted`, `t`.`gender`, `t`.`guild`, `t`.`id_associated`, `t`.`level`, `t`.`main_change`, `t`.`name`, `t`.`race`, `t`.`rank0`, `i`.`game_itemid`, `i`.`id_game`, `i`.`name`
FROM `BiddingSessions` AS `b`
LEFT JOIN `items` AS `i` ON `b`.`id_item` = `i`.`id_item`
LEFT JOIN (
    SELECT `b0`.`id`, `b0`.`session_id`, `b0`.`character_id`, `b0`.`Rank`, `b0`.`Timestamp`, `b0`.`UpdatedTimestamp`, `b0`.`User`, `b0`.`Value`, `c`.`id_character`, `c`.`clientId`, `c`.`active`, `c`.`class`, `c`.`deleted`, `c`.`gender`, `c`.`guild`, `c`.`id_associated`, `c`.`level`, `c`.`main_change`, `c`.`name`, `c`.`race`, `c`.`rank` AS `rank0`
    FROM `Bids` AS `b0`
    INNER JOIN `characters` AS `c` ON `b0`.`character_id` = `c`.`id_character`
) AS `t` ON `b`.`id` = `t`.`session_id`
WHERE (`b`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`b`.`State` = 0)
ORDER BY `b`.`id`, `b`.`clientId`, `i`.`id_item`, `t`.`id`, `t`.`session_id`, `t`.`character_id`, `t`.`id_character`

Essentially, the table setup is this:

BidSession --> Bids --> Character where Bids is a collection of Bids and each Bid only has 1 Character associated with it.

I'm not sure if somehow my fkeys or primary keys are causing issues. The biggest confusion is that this ran perfectly fine in .NET Core 2.1 and I'm at a loss now.

I can post the BidSession/Bids/Character table schemas if that is helpful as well.

Edit: When using AsSplitQuery it produces this SQL:

SELECT `b`.`id`, `b`.`clientId`, `b`.`AllowDeletes`, `b`.`Auctioneer`, `b`.`AutoAdjustBids`, `b`.`BidType`, `b`.`CreatedTimestamp`, `b`.`Duration`, `b`.`EndTimestamp`, `b`.`id_item`, `b`.`ItemQuantity`, `b`.`MaximumBid`, `b`.`MinimumBid`, `b`.`notes`, `b`.`State`, `b`.`UpdatedTimestamp`, `i`.`id_item`, `i`.`game_itemid`, `i`.`id_game`, `i`.`name`
FROM `BiddingSessions` AS `b`
LEFT JOIN `items` AS `i` ON `b`.`id_item` = `i`.`id_item`
WHERE (`b`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`b`.`State` = 0)
ORDER BY `b`.`id`, `b`.`clientId`, `i`.`id_item`
GO

SELECT `t`.`id`, `t`.`session_id`, `t`.`character_id`, `t`.`Rank`, `t`.`Timestamp`, `t`.`UpdatedTimestamp`, `t`.`User`, `t`.`Value`, `t`.`id_character`, `t`.`clientId`, `t`.`active`, `t`.`class`, `t`.`deleted`, `t`.`gender`, `t`.`guild`, `t`.`id_associated`, `t`.`level`, `t`.`main_change`, `t`.`name`, `t`.`race`, `t`.`rank0`, `b`.`id`, `b`.`clientId`, `i`.`id_item`
FROM `BiddingSessions` AS `b`
LEFT JOIN `items` AS `i` ON `b`.`id_item` = `i`.`id_item`
INNER JOIN (
    SELECT `b0`.`id`, `b0`.`session_id`, `b0`.`character_id`, `b0`.`Rank`, `b0`.`Timestamp`, `b0`.`UpdatedTimestamp`, `b0`.`User`, `b0`.`Value`, `c`.`id_character`, `c`.`clientId`, `c`.`active`, `c`.`class`, `c`.`deleted`, `c`.`gender`, `c`.`guild`, `c`.`id_associated`, `c`.`level`, `c`.`main_change`, `c`.`name`, `c`.`race`, `c`.`rank` AS `rank0`
    FROM `Bids` AS `b0`
    INNER JOIN `characters` AS `c` ON `b0`.`character_id` = `c`.`id_character`
) AS `t` ON `b`.`id` = `t`.`session_id`
WHERE (`b`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`b`.`State` = 0)
ORDER BY `b`.`id`, `b`.`clientId`, `i`.`id_item`

It still looks like that second query is too inefficient but I'm no expert.

.NET Core 2.1 EF SQL that gets produced:

SELECT `x`.`id`, `x`.`clientId`, `x`.`AllowDeletes`, `x`.`Auctioneer`, `x`.`AutoAdjustBids`, `x`.`BidType`, `x`.`CreatedTimestamp`, `x`.`Duration`, `x`.`EndTimestamp`, `x`.`id_item`, `x`.`ItemQuantity`, `x`.`MaximumBid`, `x`.`MinimumBid`, `x`.`notes`, `x`.`State`, `x`.`UpdatedTimestamp`, `x.IdItemNavigation`.`id_item`, `x.IdItemNavigation`.`game_itemid`, `x.IdItemNavigation`.`id_game`, `x.IdItemNavigation`.`name`
FROM `BiddingSessions` AS `x`
LEFT JOIN `items` AS `x.IdItemNavigation` ON `x`.`id_item` = `x.IdItemNavigation`.`id_item`
WHERE (`x`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`x`.`State` = 0)
ORDER BY `x`.`id`
GO

SELECT `x.Bids`.`id`, `x.Bids`.`character_id`, `x.Bids`.`Rank`, `x.Bids`.`session_id`, `x.Bids`.`Timestamp`, `x.Bids`.`UpdatedTimestamp`, `x.Bids`.`User`, `x.Bids`.`Value`, `b.Character`.`id_character`, `b.Character`.`clientId`, `b.Character`.`active`, `b.Character`.`class`, `b.Character`.`deleted`, `b.Character`.`gender`, `b.Character`.`guild`, `b.Character`.`id_associated`, `b.Character`.`level`, `b.Character`.`main_change`, `b.Character`.`name`, `b.Character`.`race`, `b.Character`.`rank`
FROM `Bids` AS `x.Bids`
INNER JOIN `characters` AS `b.Character` ON `x.Bids`.`character_id` = `b.Character`.`id_character`
INNER JOIN (
    SELECT DISTINCT `x0`.`id`
    FROM `BiddingSessions` AS `x0`
    LEFT JOIN `items` AS `x.IdItemNavigation0` ON `x0`.`id_item` = `x.IdItemNavigation0`.`id_item`
    WHERE (`x0`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`x0`.`State` = 0)
) AS `t` ON `x.Bids`.`session_id` = `t`.`id`
ORDER BY `t`.`id`

Looks like .NET 2.1 not only splits the query into two, but it performs a completely different query for the 2nd one. and it is much faster than what .NET 6 produced (2 seconds vs 500ms)

Answer 1

EF Core 2 would fall back to client evaluation for some queries.

It looks like you are referring to 2 child tables. Forcing EF Core to build a single query will result in a cartesian product of every permutation of child records.

You probably want to tell EF Core to split the query;

_context.BiddingSessions
            .AsNoTracking()
            .AsSplitQuery()
            .Include("Bids.Character")
            .Include("IdItemNavigation")

EF Core will raise diagnostic messages for suspect queries. To catch these errors quickly, you can force all diagnostics to throw an error, whitelisting only those messages you wish to ignore;

services.AddDbContextPool<...>(o => o
    .UseSqlServer(connectionString, options =>
    {
        // ...
    })
    .ConfigureWarnings(w => w
#if DEBUG
        .Default(WarningBehavior.Throw)
#endif
        .Ignore(CoreEventId.SensitiveDataLoggingEnabledWarning)
        .Ignore(CoreEventId.PossibleIncorrectRequiredNavigationWithQueryFilterInteractionWarning)
        .Ignore(SqlServerEventId.SavepointsDisabledBecauseOfMARS)
    )
);