CQRS in .NET: when to use it, trade‑offs

using System.Data;
using Dapper;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Config
var connString = builder.Configuration.GetConnectionString("App")
                ?? "Data Source=app.db"; // SQLite for a self-contained demo

// EF Core DbContext for writes
builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlite(connString));

// Dapper connection factory for reads
builder.Services.AddScoped<IDbConnection>(_ => new SqliteConnection(connString));

builder.Services.AddEndpointsApiExplorer();

var app = builder.Build();

// Ensure DB exists (demo). In prod: use migrations + Migrate().
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    db.Database.EnsureCreated();
}

// Health
app.MapGet("/ping", () => "pong");

// Commands (write side)

// Create Order
app.MapPost("/orders", async (CreateOrderDto dto, AppDbContext db) =>
{
    // Very basic validation; expand as needed
    if (string.IsNullOrWhiteSpace(dto.CustomerEmail))
        return Results.BadRequest("CustomerEmail is required");
    if (dto.Items is null || dto.Items.Count == 0)
        return Results.BadRequest("At least one item is required");
    if (dto.Items.Any(i => i.Quantity <= 0 || i.Price < 0))
        return Results.BadRequest("Invalid item quantity/price");

    var order = Order.Create(dto.CustomerEmail);
    foreach (var item in dto.Items)
        order.AddItem(item.Sku, item.Quantity, item.Price);

    await db.Orders.AddAsync(order);
    await db.SaveChangesAsync();

    return Results.Created($"/orders/{order.Id}", new { order.Id });
});

// Approve Order
app.MapPost("/orders/{id:guid}/approve", async (Guid id, AppDbContext db) =>
{
    var order = await db.Orders.Include(o => o.Items).FirstOrDefaultAsync(o => o.Id == id);
    if (order is null) return Results.NotFound();

    try
    {
        order.Approve();
        await db.SaveChangesAsync();
        return Results.Accepted($"/orders/{order.Id}");
    }
    catch (InvalidOperationException ex)
    {
        return Results.Conflict(new { message = ex.Message });
    }
});

// Queries (read side) using Dapper

// Get by Id
app.MapGet("/orders/{id:guid}", async (Guid id, IDbConnection conn) =>
{
    const string sql = @"
select 
  o.Id, o.CustomerEmail, o.Status, o.CreatedAt,
  i.Id as ItemId, i.Sku, i.Quantity, i.Price
from Orders o
left join OrderItems i on i.OrderId = o.Id
where o.Id = @Id
order by i.Id";

    var rows = await conn.QueryAsync<dynamic>(sql, new { Id = id });

    var first = rows.FirstOrDefault();
    if (first is null) return Results.NotFound();

    var result = new OrderReadDto
    {
        Id = first.ID,
        CustomerEmail = first.CUSTOMEREMAIL,
        Status = first.STATUS,
        CreatedAt = first.CREATEDAT,
        Items = rows
            .Where(r => r.ITEMID != null)
            .Select(r => new OrderItemReadDto
            {
                Id = r.ITEMID,
                Sku = r.SKU,
                Quantity = (int)r.QUANTITY,
                Price = (decimal)r.PRICE
            })
            .ToList()
    };

    return Results.Ok(result);
});

// List (latest N)
app.MapGet("/orders", async (int take, int skip, IDbConnection conn) =>
{
    take = take <= 0 || take > 100 ? 20 : take;
    skip = skip < 0 ? 0 : skip;

    const string sql = @"
select o.Id, o.CustomerEmail, o.Status, o.CreatedAt
from Orders o
order by o.CreatedAt desc
limit @Take offset @Skip";

    var orders = (await conn.QueryAsync<OrderListItemDto>(sql, new { Take = take, Skip = skip })).ToList();
    return Results.Ok(orders);
});

// Run
app.Urls.Add("http://0.0.0.0:8080");
app.Run();


// -------------------- Domain + Data --------------------

public sealed class Order
{
    public Guid Id { get; private set; } = Guid.NewGuid();
    public string CustomerEmail { get; private set; } = default!;
    public string Status { get; private set; } = OrderStatus.Pending;
    public DateTime CreatedAt { get; private set; } = DateTime.UtcNow;
    public List<OrderItem> Items { get; private set; } = new();

    private Order() { } // EF

    public static Order Create(string customerEmail)
    {
        if (string.IsNullOrWhiteSpace(customerEmail))
            throw new ArgumentException("CustomerEmail required", nameof(customerEmail));

        return new Order { CustomerEmail = customerEmail.Trim() };
    }

    public void AddItem(string sku, int quantity, decimal price)
    {
        if (string.IsNullOrWhiteSpace(sku)) throw new ArgumentException("SKU required", nameof(sku));
        if (quantity <= 0) throw new ArgumentException("Quantity must be > 0", nameof(quantity));
        if (price < 0) throw new ArgumentException("Price must be >= 0", nameof(price));

        Items.Add(new OrderItem { Sku = sku.Trim(), Quantity = quantity, Price = price });
    }

    public void Approve()
    {
        if (Status == OrderStatus.Approved) throw new InvalidOperationException("Order already approved");
        if (Status != OrderStatus.Pending) throw new InvalidOperationException($"Cannot approve from status {Status}");
        if (Items.Count == 0) throw new InvalidOperationException("Cannot approve order without items");

        Status = OrderStatus.Approved;
    }
}

public static class OrderStatus
{
    public const string Pending = "Pending";
    public const string Approved = "Approved";
}

public sealed class OrderItem
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Sku { get; set; } = default!;
    public int Quantity { get; set; }
    public decimal Price { get; set; }

    public Guid OrderId { get; set; }
    public Order Order { get; set; } = default!;
}

public sealed class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Order>(e =>
        {
            e.HasKey(x => x.Id);
            e.Property(x => x.CustomerEmail).IsRequired();
            e.Property(x => x.Status).IsRequired();
            e.Property(x => x.CreatedAt).HasConversion(
                v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc)); // keep UTC
            e.HasMany(x => x.Items)
             .WithOne(i => i.Order)
             .HasForeignKey(i => i.OrderId)
             .OnDelete(DeleteBehavior.Cascade);
            e.ToTable("Orders");
        });

        b.Entity<OrderItem>(e =>
        {
            e.HasKey(x => x.Id);
            e.Property(x => x.Sku).IsRequired();
            e.Property(x => x.Quantity).IsRequired();
            e.Property(x => x.Price).IsRequired();
            e.ToTable("OrderItems");
        });
    }
}

// -------------------- DTOs --------------------

public sealed record CreateOrderDto(string CustomerEmail, List<CreateOrderItemDto> Items);
public sealed record CreateOrderItemDto(string Sku, int Quantity, decimal Price);

public sealed class OrderReadDto
{
    public Guid Id { get; set; }
    public string CustomerEmail { get; set; } = default!;
    public string Status { get; set; } = default!;
    public DateTime CreatedAt { get; set; }
    public List<OrderItemReadDto> Items { get; set; } = new();
}

public sealed class OrderItemReadDto
{
    public Guid Id { get; set; }
    public string Sku { get; set; } = default!;
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public sealed class OrderListItemDto
{
    public Guid Id { get; set; }
    public string CustomerEmail { get; set; } = default!;
    public string Status { get; set; } = default!;
    public DateTime CreatedAt { get; set; }
}