What's New in EF Core 11? MaxBy, MinBy, and SQLite Migrations

EF Core 11 translates MaxByAsync and MinByAsync into SQL that returns the bid with the highest or lowest amount. The SQLite plan goes from a table scan and sort to a covering index when you index item and amount together. You can create the auction database using the new dotnet ef database update --add option.

Which Bid Had the Highest Amount?

An auction page needs the winning bid's ID to link to its details. MaxAsync(b => b.Amount) gives you the amount and leaves you looking for the row. With EF Core 11, MaxByAsync(b => b.Amount) gives you the bid, including its ID. The database orders the matching rows and returns one.

EF Core 11 runs on .NET 11. To see the SQL, create a console app and use Microsoft's SQLite provider. Pin the tool and both packages to the GA version so you can reproduce the output below.

dotnet new console -n Orders -f net11.0
cd Orders
dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 11.0.0
dotnet add package Microsoft.EntityFrameworkCore.Design --version 11.0.0
dotnet new tool-manifest
dotnet tool install dotnet-ef --version 11.0.0

Replace Program.cs with this auction app. It stores bids in bids.db and logs each SQL command.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

await using var db = new OrdersDb();
if (!await db.Bids.AnyAsync())
{
    db.Bids.AddRange(
        new Bid { Item = "Desk", Amount = 125 },
        new Bid { Item = "Desk", Amount = 90 },
        new Bid { Item = "Lamp", Amount = 40 });
    await db.SaveChangesAsync();
}

var deskBids = db.Bids.Where(b => b.Item == "Desk").AsNoTracking();
var highest = await deskBids.MaxByAsync(b => b.Amount);
var lowest = await deskBids.MinByAsync(b => b.Amount);
Console.WriteLine($"Desk: bid #{highest?.Id} leads at {highest?.Amount}; low {lowest?.Amount}");

public class Bid
{
    public int Id { get; set; }
    public string Item { get; set; } = "";
    public int Amount { get; set; }
}

public class OrdersDb : DbContext
{
    public DbSet<Bid> Bids => Set<Bid>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=bids.db")
            .LogTo(Console.WriteLine, [RelationalEventId.CommandExecuted]);
}

Create the Database and Inspect the SQL

With EF Core 11, database update takes a migration name and an --add flag. From the project directory, run the following against the new local database.

dotnet ef database update CreateBids --add
dotnet run

The first command writes the migration and model snapshot to Migrations/, compiles the migration, and applies it to bids.db. The program then inserts the three bids and prints Desk: bid #1 leads at 125; low 90. SQLite received this SQL for the highest bid.

SELECT "b"."Id", "b"."Amount", "b"."Item"
FROM "Bids" AS "b"
WHERE "b"."Item" = 'Desk'
ORDER BY "b"."Amount" DESC
LIMIT 1

The lowest bid uses the same query with ascending order. Asking for both extremes sends two commands to SQLite. The same SQL translation works for synchronous MaxBy and MinBy.

What Happens With Ties or No Bids?

Two bidders can offer the same amount. The generated SQL only orders by Amount, so it leaves the winner of that tie unspecified. If your rule awards the win to the smallest bid ID among equal amounts, spell it out.

var winner = await deskBids
    .OrderByDescending(b => b.Amount)
    .ThenBy(b => b.Id)
    .FirstOrDefaultAsync();

An item can also have no bids yet. Filtering this example to Item == "Missing" before calling MaxByAsync returned null. That lets the auction page distinguish an empty result from a bid of zero. For a report comparing two exports, a full outer join can also find bids whose item is missing from the catalog.

Does LIMIT 1 Avoid Sorting the Auction?

A one-row result doesn't tell you how many rows SQLite examined. With only the primary key, prefixing the logged query with EXPLAIN QUERY PLAN showed a table scan and a temporary B-tree for the sort.

Add this method to OrdersDb. The composite index groups bids by item and orders each item's entries by amount.

protected override void OnModelCreating(ModelBuilder builder)
    => builder.Entity<Bid>().HasIndex(b => new { b.Item, b.Amount });

This time, generate the migration and inspect its SQL before updating the database. The two migration names delimit the changes you want to review.

dotnet ef migrations add IndexBids
dotnet ef migrations script CreateBids IndexBids > index-bids.sql

The script contains CREATE INDEX "IX_Bids_Item_Amount" ON "Bids" ("Item", "Amount"). After dotnet ef database update, the same query produced this plan on SQLite.

SEARCH Bids USING COVERING INDEX IX_Bids_Item_Amount (Item=?)

SQLite can now use the item to locate the corresponding index entries and read them in descending amount order. If you're preparing a production schema change, use a reviewable migration script and check the plan on your actual provider.

Summary

If any bid at the highest price will do, MaxByAsync is enough. If the auction promises the win to the lowest bid ID at that price, put that tie-breaker in the query. Check the plan before deploying the query. A LIMIT 1 result can still require a sort over every bid for the item.