Complex Types and JSON Columns

using Microsoft.EntityFrameworkCore;

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Flattened, this fails at model build:
        //   "... this mapping requires at least one required property - to allow
        //    distinguishing between 'null' and empty values ..."
        // modelBuilder.Entity<Customer>().ComplexProperty(c => c.Preferences);

        // Fix 1: a JSON column, where NULL and {} are different values.
        modelBuilder.Entity<Customer>().ComplexProperty(c => c.Preferences, p => p.ToJson());

        // Fix 2 (alternative): keep columns but make one member required,
        // for example a non-nullable Language with a default.
    }
}

public sealed class MarketingPreferences
{
    public string? Language { get; set; }
    public bool? Newsletter { get; set; }
    public bool? Sms { get; set; }
}

public sealed class Customer
{
    public int Id { get; set; }
    public required string Email { get; set; }
    public MarketingPreferences? Preferences { get; set; }
}

CREATE TABLE "Customers" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "Location_Latitude" double precision NOT NULL,
    "Location_Longitude" double precision NOT NULL,
    "ShippingAddress_City" character varying(100) NOT NULL,
    "ShippingAddress_Country" character(2) NOT NULL,
    "ShippingAddress_PostalCode" text,
    "ShippingAddress_Street" text NOT NULL,
    billing_address jsonb,
    CONSTRAINT "PK_Customers" PRIMARY KEY ("Id")
);

CREATE TABLE "Orders" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "CustomerId" integer NOT NULL,
    "Lines" jsonb NOT NULL,
    CONSTRAINT "PK_Orders" PRIMARY KEY ("Id")
);

-- db.Customers.Where(c => c.BillingAddress != null && c.BillingAddress.City == "Berlin")
SELECT c."Id", c."Name", c.billing_address, c."Location_Latitude", c."Location_Longitude",
       c."ShippingAddress_City", c."ShippingAddress_Country", c."ShippingAddress_PostalCode", c."ShippingAddress_Street"
FROM "Customers" AS c
WHERE c.billing_address IS NOT NULL AND (c.billing_address ->> 'City') = 'Berlin';

-- Needed before that filter runs on a hot path:
CREATE INDEX ix_customers_billing_city ON "Customers" ((billing_address ->> 'City'));

using Microsoft.EntityFrameworkCore;

public static class OrderLines
{
    // Lines live in one jsonb column, so this is a single-row read and a single-column write.
    // The write replaces the whole document, so two concurrent calls would each drop the
    // other's line without the xmin check: the loser gets DbUpdateConcurrencyException.
    public static async Task<bool> AddLineAsync(ShopDb db, int orderId, string sku, int quantity, decimal unitPrice, CancellationToken ct)
    {
        var order = await db.Orders.SingleAsync(o => o.Id == orderId, ct);

        var existing = order.Lines.FindIndex(l => l.Sku == sku);
        if (existing >= 0)
            order.Lines[existing] = order.Lines[existing] with { Quantity = order.Lines[existing].Quantity + quantity };
        else
            order.Lines.Add(new OrderLine(sku, quantity, unitPrice));

        try
        {
            await db.SaveChangesAsync(ct);  // UPDATE "Orders" SET "Lines" = @p0 WHERE "Id" = @p1 AND xmin = @p2
            return true;
        }
        catch (DbUpdateConcurrencyException)
        {
            return false;                   // the caller retries with a fresh context or returns 409
        }
    }

    // Aggregates over the collection are translated with jsonb_to_recordset.
    public static Task<int> CountOrdersWithSkuAsync(ShopDb db, string sku, CancellationToken ct) =>
        db.Orders.CountAsync(o => o.Lines.Any(l => l.Sku == sku), ct);
}

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

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Order>(order =>
        {
            order.ComplexCollection(o => o.Lines, lines => lines.ToJson());
            order.Property(o => o.Version).IsRowVersion();   // xmin on Npgsql
        });
}

public sealed record OrderLine(string Sku, int Quantity, decimal UnitPrice);

public sealed class Order
{
    public int Id { get; set; }
    public List<OrderLine> Lines { get; set; } = [];
    public uint Version { get; set; }
}