Concurrency and transactions

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.14
#:package System.Security.Cryptography.Pkcs@10.0.14
#:property PublishAot=false
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

// Two DbContexts stand for two requests at the same moment. Every run builds the database "concurrent" anew.
await using (var setup = new Playground())
{
    await setup.Database.EnsureDeletedAsync();
    await setup.Database.EnsureCreatedAsync();
    setup.Notes.Add(new Note { Text = "Write the quote" });
    setup.Contacts.Add(new Contact { Name = "Mara Feld" });
    await setup.SaveChangesAsync();
}

switch (args.FirstOrDefault())
{
    case "unit":
        await AllOrNothingAsync();
        break;
    case "lost":
        await LostUpdateAsync();
        break;
    case "version":
        await RowVersionAsync();
        break;
    case "double":
        await DoubleBookingAsync();
        break;
    default:
        Console.WriteLine("Usage: dotnet run concurrent.cs -- unit | lost | version | double");
        break;
}

static async Task AllOrNothingAsync()
{
    await using var db = new Playground();
    db.Bookings.Add(new Booking { Name = "Anna Berger", Start = Playground.TuesdayAtTen });
    db.Bookings.Add(new Booking { Name = "Ben Dorn", Start = Playground.TuesdayAtTen.AddHours(1) });
    db.Bookings.Add(new Booking { Name = "Carla Engel", Start = Playground.TuesdayAtTen });
    try
    {
        await db.SaveChangesAsync();
    }
    catch (DbUpdateException e)
    {
        Console.WriteLine($"{e.GetType().Name}: {e.InnerException?.Message}");
    }

    await using var check = new Playground();
    Console.WriteLine($"Bookings in the database: {await check.Bookings.CountAsync()}");
}

static async Task LostUpdateAsync()
{
    await using var anna = new Playground();
    await using var ben = new Playground();
    Note forAnna = await anna.Notes.SingleAsync();
    Note forBen = await ben.Notes.SingleAsync();

    forAnna.Text = "Write the quote, due Friday";
    await anna.SaveChangesAsync();
    forBen.Text = "Write the quote for Kranich";
    await ben.SaveChangesAsync();

    await using var check = new Playground();
    Console.WriteLine($"Note: {(await check.Notes.SingleAsync()).Text}");
}

static async Task RowVersionAsync()
{
    await using var anna = new Playground();
    await using var ben = new Playground();
    Contact forAnna = await anna.Contacts.SingleAsync();
    Contact forBen = await ben.Contacts.SingleAsync();

    forAnna.Name = "Mara Feld-Ost";
    await anna.SaveChangesAsync();
    forBen.Name = "Mara Feldt";
    try
    {
        await ben.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException e)
    {
        Console.WriteLine(e.Message);
    }

    await using var check = new Playground();
    Console.WriteLine($"Contact: {(await check.Contacts.SingleAsync()).Name}");
}

static async Task DoubleBookingAsync()
{
    await using var anna = new Playground();
    await using var ben = new Playground();
    bool freeForAnna = !await anna.Bookings.AnyAsync(b => b.Start == Playground.TuesdayAtTen);
    bool freeForBen = !await ben.Bookings.AnyAsync(b => b.Start == Playground.TuesdayAtTen);
    Console.WriteLine($"free for Anna: {freeForAnna}, free for Ben: {freeForBen}");

    anna.Bookings.Add(new Booking { Name = "Anna Berger", Start = Playground.TuesdayAtTen });
    ben.Bookings.Add(new Booking { Name = "Ben Dorn", Start = Playground.TuesdayAtTen });
    await anna.SaveChangesAsync();
    try
    {
        await ben.SaveChangesAsync();
        Console.WriteLine("Ben booked too.");
    }
    catch (DbUpdateException e) when (e.InnerException is SqlException { Number: 2601 or 2627 } sql)
    {
        Console.WriteLine($"Ben: rejected, SQL Server error {sql.Number}");
    }
}

public sealed class Note
{
    public int Id { get; set; }
    public required string Text { get; set; }
}

public sealed class Contact
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public byte[]? Version { get; set; }
}

public sealed class Booking
{
    public Guid Id { get; set; }
    public required string Name { get; set; }
    public DateTimeOffset Start { get; set; }
}

public sealed class Playground : DbContext
{
    public static readonly DateTimeOffset TuesdayAtTen = new(2025, 3, 25, 10, 0, 0, TimeSpan.FromHours(1));

    public DbSet<Note> Notes => Set<Note>();

    public DbSet<Contact> Contacts => Set<Contact>();

    public DbSet<Booking> Bookings => Set<Booking>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        string kladde = Environment.GetEnvironmentVariable("ConnectionStrings__Kladde") ?? "Server=localhost,1433";
        var builder = new SqlConnectionStringBuilder(kladde) { InitialCatalog = "concurrent" };
        options.UseSqlServer(builder.ConnectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Note>().Property(n => n.Text).HasMaxLength(500);
        modelBuilder.Entity<Contact>().Property(c => c.Name).HasMaxLength(100);
        modelBuilder.Entity<Contact>().Property(c => c.Version).IsRowVersion();
        modelBuilder.Entity<Booking>().Property(b => b.Name).HasMaxLength(100);
        modelBuilder.Entity<Booking>().HasIndex(b => b.Start).IsUnique();
    }
}