DbContext

using Microsoft.EntityFrameworkCore;

var builder = Host.CreateApplicationBuilder(args);
builder.Services.AddPooledDbContextFactory<ShopDb>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Shop")));
builder.Services.AddHostedService<UnpaidOrderSweeper>();
builder.Build().Run();

public sealed class UnpaidOrderSweeper(
    IDbContextFactory<ShopDb> contexts, ILogger<UnpaidOrderSweeper> log) : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer = new PeriodicTimer(TimeSpan.FromMinutes(5));
        while (await timer.WaitForNextTickAsync(stoppingToken))
        {
            try
            {
                // New context each pass: the tracker never accumulates entities across passes.
                await using var db = await contexts.CreateDbContextAsync(stoppingToken);
                var cutoff = DateTimeOffset.UtcNow.AddHours(-24);
                var cancelled = await db.Orders
                    .Where(o => o.Status == "Pending" && o.PlacedAt < cutoff)
                    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, "Cancelled"), stoppingToken);
                log.LogInformation("Cancelled {Count} unpaid orders older than 24 hours", cancelled);
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                // An unhandled exception would stop the host; log it and try again on the next tick.
                log.LogError(ex, "Unpaid order sweep failed");
            }
        }
    }
}

public class Order
{
    public int Id { get; set; }
    public string Status { get; set; } = "Pending";
    public DateTimeOffset PlacedAt { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}

using Microsoft.EntityFrameworkCore;

public sealed class DashboardQueries(IDbContextFactory<ShopDb> contexts)
{
    // Throws InvalidOperationException: a second operation started before the first completed.
    public async Task<DashboardTotals> Broken(CancellationToken ct)
    {
        await using var db = await contexts.CreateDbContextAsync(ct);
        var count = db.Orders.CountAsync(ct);
        var unpaid = db.Orders.CountAsync(o => !o.Paid, ct);
        await Task.WhenAll(count, unpaid);
        return new DashboardTotals(count.Result, unpaid.Result);
    }

    // Fix: one context, and one connection, per concurrent branch.
    public async Task<DashboardTotals> Parallel(CancellationToken ct)
    {
        async Task<int> Count(bool onlyUnpaid)
        {
            await using var db = await contexts.CreateDbContextAsync(ct);
            return onlyUnpaid ? await db.Orders.CountAsync(o => !o.Paid, ct) : await db.Orders.CountAsync(ct);
        }

        var results = await Task.WhenAll(Count(false), Count(true));
        return new DashboardTotals(results[0], results[1]);
    }
}

public record DashboardTotals(int Orders, int Unpaid);

public class Order
{
    public int Id { get; set; }
    public bool Paid { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Xunit;

public sealed class ShopDbTests : IAsyncLifetime
{
    // The in-memory database lives as long as this open connection.
    private readonly SqliteConnection _connection = new("Data Source=:memory:");

    private ShopDb NewContext() =>
        new(new DbContextOptionsBuilder<ShopDb>().UseSqlite(_connection).Options);

    public async ValueTask InitializeAsync()
    {
        await _connection.OpenAsync();
        await using var db = NewContext();
        await db.Database.EnsureCreatedAsync();
    }

    public async ValueTask DisposeAsync() => await _connection.DisposeAsync();

    [Fact]
    public async Task Saved_order_is_read_back_from_the_database()
    {
        var ct = TestContext.Current.CancellationToken;
        int id;
        await using (var write = NewContext())
        {
            var order = new Order { CustomerId = 42, Status = "Pending" };
            write.Orders.Add(order);
            await write.SaveChangesAsync(ct);
            id = order.Id;
        }

        // A second context has an empty tracker, so this really queries SQLite.
        await using var read = NewContext();
        var loaded = await read.Orders.SingleAsync(o => o.Id == id, ct);
        Assert.Equal(42, loaded.CustomerId);
    }
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Status { get; set; } = "";
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}