Entity Framework Core In Depth

// EF Core shines when:
//   • CRUD + relationships (1:N, N:N) with change tracking
//   • Rapid development, migrations, provider portability
//   • LINQ composability in application code

// Raw SQL / Dapper shines when:
//   • Complex reporting queries, bulk ETL, hand-tuned plans
//   • Stored procedures already own the logic

// Hybrid is common — EF Core for 90%, FromSqlRaw for hot paths
var topCustomers = await db.Customers
    .FromSqlRaw("EXEC dbo.GetTopCustomers @Year", yearParam)
    .AsNoTracking()
    .ToListAsync(ct);

// dotnet add package Microsoft.EntityFrameworkCore
// dotnet add package Microsoft.EntityFrameworkCore.SqlServer
// dotnet add package Microsoft.EntityFrameworkCore.Design   // CLI migrations
// dotnet add package Microsoft.EntityFrameworkCore.Tools    // Package Manager

// Program.cs — register DbContext (scoped by default)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default"))
           .EnableSensitiveDataLogging(builder.Environment.IsDevelopment())
           .EnableDetailedErrors(builder.Environment.IsDevelopment()));

public sealed class AppDbContext(DbContextOptions<AppDbContext> options)
    : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public ICollection<Order> Orders { get; set; } = [];
}

// DbContext is SCOPED — one per HTTP request in ASP.NET Core
public class CustomerService(AppDbContext db)
{
    public async Task<Customer> CreateAsync(string name, CancellationToken ct)
    {
        var customer = new Customer { Name = name };
        db.Customers.Add(customer);           // State = Added
        await db.SaveChangesAsync(ct);        // INSERT — Id populated
        return customer;
    }

    public async Task UpdateAsync(int id, string name, CancellationToken ct)
    {
        var customer = await db.Customers.FindAsync([id], ct)
            ?? throw new KeyNotFoundException();
        customer.Name = name;                 // State = Modified
        await db.SaveChangesAsync(ct);        // UPDATE changed columns only
    }
}

// ❌ Don't inject DbContext into singletons — use IServiceScopeFactory

// First migration
dotnet ef migrations add InitialCreate --project src/Infrastructure --startup-project src/Api

// Apply to local database
dotnet ef database update --project src/Infrastructure --startup-project src/Api

// Generate idempotent SQL script for CI/CD
dotnet ef migrations script --idempotent -o deploy/migrate.sql

// Remove last migration (if not applied to prod)
dotnet ef migrations remove

// List pending migrations
dotnet ef migrations list

// Apply migrations on startup (dev/small apps — use scripts in prod)
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();
}

// Design-time factory — EF CLI needs DbContext without running the app
public sealed class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer("Server=.;Database=AppDev;Trusted_Connection=True;TrustServerCertificate=True")
            .Options;
        return new AppDbContext(options);
    }
}

public sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders");
        builder.HasKey(o => o.Id);

        builder.Property(o => o.OrderNumber)
            .HasMaxLength(20)
            .IsRequired();

        builder.Property(o => o.Total)
            .HasPrecision(18, 2);

        builder.HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .HasForeignKey(o => o.CustomerId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.HasIndex(o => o.OrderNumber).IsUnique();
    }
}

// Owned type — Address columns live on Customer table
builder.OwnsOne(c => c.Address, a =>
{
    a.Property(p => p.Street).HasMaxLength(200);
    a.Property(p => p.City).HasMaxLength(100);
});

// Enum stored as string
builder.Property(o => o.Status)
    .HasConversion<string>()
    .HasMaxLength(32);

// Value object — Money as Amount + Currency columns
builder.Property<Money>("_total")
    .HasConversion(
        m => $"{m.Amount}|{m.Currency}",
        s => ParseMoney(s));

// Eager load — one round-trip (or split to avoid cartesian explosion)
var orders = await db.Orders
    .AsSplitQuery()
    .Include(o => o.Customer)
    .Include(o => o.Lines)
        .ThenInclude(l => l.Product)
    .Where(o => o.OrderDate >= since)
    .ToListAsync(ct);

// Projection — best performance: SELECT only needed columns
var dtos = await db.Orders
    .Where(o => o.CustomerId == customerId)
    .Select(o => new OrderSummaryDto(
        o.Id,
        o.OrderNumber,
        o.Total,
        o.Lines.Count))
    .ToListAsync(ct);

// Read-only queries — skip change tracker overhead
var customers = await db.Customers
    .AsNoTracking()
    .Where(c => c.IsActive)
    .ToListAsync(ct);

// Global default for read-heavy apps
options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

// Explicit tracking when you need updates
var order = await db.Orders
    .AsTracking()
    .FirstAsync(o => o.Id == id, ct);
order.Status = OrderStatus.Shipped;
await db.SaveChangesAsync(ct);

// ❌ N+1 — loop calling db inside foreach
// ✅ Fix — Include, projection, or batch load IDs first

public interface IAuditable
{
    DateTimeOffset CreatedAt { get; set; }
    DateTimeOffset? ModifiedAt { get; set; }
    string? CreatedBy { get; set; }
}

public sealed class AuditInterceptor(ICurrentUser user) : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData, InterceptionResult<int> result)
    {
        var now = DateTimeOffset.UtcNow;
        foreach (var entry in eventData.Context!.ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added)
            {
                entry.Entity.CreatedAt = now;
                entry.Entity.CreatedBy = user.Id;
            }
            if (entry.State == EntityState.Modified)
                entry.Entity.ModifiedAt = now;
        }
        return base.SavingChanges(eventData, result);
    }
}

// Register: options.AddInterceptors(new AuditInterceptor(...));

public sealed class SlowQueryInterceptor(ILogger<SlowQueryInterceptor> log)
    : DbCommandInterceptor
{
    private const int ThresholdMs = 200;

    public override DbDataReader ReaderExecuted(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
    {
        if (eventData.Duration.TotalMilliseconds > ThresholdMs)
            log.LogWarning("Slow query ({Ms}ms): {Sql}",
                eventData.Duration.TotalMilliseconds, command.CommandText);
        return base.ReaderExecuted(command, eventData, result);
    }
}

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
    DateTimeOffset? DeletedAt { get; set; }
}

// In configuration
builder.HasQueryFilter(e => !e.IsDeleted);

// Soft delete instead of Remove()
public async Task DeleteAsync(int id, CancellationToken ct)
{
    var entity = await db.Customers.FindAsync([id], ct)
        ?? throw new KeyNotFoundException();
    entity.IsDeleted = true;
    entity.DeletedAt = DateTimeOffset.UtcNow;
    await db.SaveChangesAsync(ct);
}

// Bypass filter when needed (admin restore)
var all = await db.Customers.IgnoreQueryFilters().ToListAsync(ct);

public interface ITenantEntity
{
    Guid TenantId { get; set; }
}

public sealed class AppDbContext(
    DbContextOptions<AppDbContext> options,
    ITenantProvider tenant) : DbContext(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasQueryFilter(o => o.TenantId == tenant.CurrentTenantId);
    }
}

// ⚠️ Always set TenantId on INSERT — filter won't help on SaveChanges
// ⚠️ IgnoreQueryFilters() in background jobs that cross tenants

// Mark all expired sessions inactive — one UPDATE statement
var affected = await db.Sessions
    .Where(s => s.ExpiresAt < DateTimeOffset.UtcNow && s.IsActive)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(s => s.IsActive, false)
        .SetProperty(s => s.ModifiedAt, DateTimeOffset.UtcNow),
        ct);

// ❌ No interceptors/SaveChanges on ExecuteUpdate
// ❌ No entity-level validation — business rules must be in SQL or pre-check

// Purge audit logs older than retention — no memory spike
await db.AuditLogs
    .Where(l => l.CreatedAt < cutoff)
    .ExecuteDeleteAsync(ct);

// Compare to load-then-remove (bad at scale)
// var old = await db.AuditLogs.Where(...).ToListAsync();  // loads millions
// db.AuditLogs.RemoveRange(old);                          // N DELETE or one big txn

// Cache the compiled delegate — avoids re-parsing LINQ every call
private static readonly Func<AppDbContext, int, Task<Customer?>> GetCustomerById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Customers.FirstOrDefault(c => c.Id == id));

public async Task<Customer?> GetByIdAsync(int id, CancellationToken ct)
    => await GetCustomerById(db, id);

// Enable SQL logging in dev
options.LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging(isDev);

// Always paginate — never ToList() on unbounded tables
var page = await db.Products
    .AsNoTracking()
    .OrderBy(p => p.Id)
    .Skip((pageNum - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

// DbContext pooling — reuse internal state (still scoped per request)
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connString));

// Index columns in WHERE, JOIN, ORDER BY — verify with actual execution plans

// Resilience — SQL retry on transient failures
options.UseSqlServer(connString, sql =>
    sql.EnableRetryOnFailure(maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(5), errorNumbersToAdd: null));

// Health check
builder.Services.AddHealthChecks()
    .AddDbContextCheck<AppDbContext>("database");

// OpenTelemetry — trace EF commands (Package: OpenTelemetry.Instrumentation.EntityFrameworkCore)
// Application Insights — dependency tracking auto-captures SQL

// Transaction boundaries — one SaveChanges per business operation
await using var tx = await db.Database.BeginTransactionAsync(ct);
try
{
    await db.SaveChangesAsync(ct);
    await PublishDomainEventsAsync(ct);
    await tx.CommitAsync(ct);
}
catch { await tx.RollbackAsync(ct); throw; }