EF Core Performance

using System.Buffers.Text;
using System.Text;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContextPool<CatalogDb>(o => o
    .UseNpgsql(builder.Configuration.GetConnectionString("Catalog"))
    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

var app = builder.Build();

// Hot path: called on every product page view.
app.MapGet("/products/{sku}", async Task<Results<Ok<ProductView>, NotFound>> (string sku, CatalogDb db, CancellationToken ct) =>
    await CatalogQueries.BySku(db, sku, ct) is { } product ? TypedResults.Ok(product) : TypedResults.NotFound());

// Keyset pagination: newest orders first, constant cost per page.
app.MapGet("/orders", async (string? after, CatalogDb db, CancellationToken ct) =>
{
    const int pageSize = 50;
    var query = db.Orders.AsQueryable();

    if (OrderCursor.TryDecode(after, out var cursor))
    {
        query = query.Where(o => o.PlacedAt < cursor.PlacedAt
            || (o.PlacedAt == cursor.PlacedAt && o.Id < cursor.Id));
    }

    var page = await query
        .OrderByDescending(o => o.PlacedAt).ThenByDescending(o => o.Id)
        .Take(pageSize + 1)
        .Select(o => new OrderListItem(o.Id, o.CustomerId, o.PlacedAt, o.Total))
        .ToListAsync(ct);

    var hasMore = page.Count > pageSize;
    if (hasMore) page.RemoveAt(pageSize);
    var next = hasMore ? OrderCursor.Encode(page[^1].PlacedAt, page[^1].Id) : null;
    return TypedResults.Ok(new OrderPage(page, next));
});

app.Run();

public record ProductView(int Id, string Sku, string Name, decimal Price, int Stock);
public record OrderListItem(long Id, int CustomerId, DateTimeOffset PlacedAt, decimal Total);
public record OrderPage(List<OrderListItem> Items, string? Next);

public static class CatalogQueries
{
    public static readonly Func<CatalogDb, string, CancellationToken, Task<ProductView?>> BySku =
        EF.CompileAsyncQuery((CatalogDb db, string sku, CancellationToken ct) =>
            db.Products
                .Where(p => p.Sku == sku)
                .Select(p => new ProductView(p.Id, p.Sku, p.Name, p.Price, p.Stock))
                .FirstOrDefault());
}

public readonly record struct OrderCursor(DateTimeOffset PlacedAt, long Id)
{
    public static string Encode(DateTimeOffset placedAt, long id) =>
        Base64Url.EncodeToString(Encoding.UTF8.GetBytes($"{placedAt.UtcTicks}:{id}"));

    public static bool TryDecode(string? value, out OrderCursor cursor)
    {
        cursor = default;
        if (string.IsNullOrEmpty(value)) return false;
        try
        {
            var parts = Encoding.UTF8.GetString(Base64Url.DecodeFromChars(value)).Split(':');
            if (parts.Length != 2 || !long.TryParse(parts[0], out var ticks) || !long.TryParse(parts[1], out var id)) return false;
            cursor = new OrderCursor(new DateTimeOffset(ticks, TimeSpan.Zero), id);
            return true;
        }
        catch (FormatException)
        {
            return false;
        }
    }
}

public class Product
{
    public int Id { get; set; }
    public string Sku { get; set; } = "";
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
}

public class Order
{
    public long Id { get; set; }
    public int CustomerId { get; set; }
    public DateTimeOffset PlacedAt { get; set; }
    public decimal Total { get; set; }
}

public class CatalogDb(DbContextOptions<CatalogDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().HasIndex(p => p.Sku).IsUnique();
        // Matches the ORDER BY of the keyset query exactly.
        modelBuilder.Entity<Order>().HasIndex(o => new { o.PlacedAt, o.Id }).IsDescending(true, true);
    }
}