Session 04: EF Core Advanced & Data Access Patterns

Mục tiêu: Master EF Core internals, viết queries hiệu quả, và áp dụng đúng patterns cho data layer.

1. Query Pipeline Internals

EF Core Query Flow

LINQ Expression
      │
      ▼
Expression Tree (System.Linq.Expressions)
      │
      ▼
Query Pipeline (IQueryTranslationPreprocessor)
      │
      ▼
SQL Generation (IQuerySqlGenerator)
      │
      ▼
Database Command → ADO.NET → SQL Server/PostgreSQL
      │
      ▼
Result Materialization → Entity/DTO objects

Expression Tree Translation

csharp
// EF Core dịch LINQ → SQL tại runtime
var orders = await db.Orders
    .Where(o => o.Status == OrderStatus.Active)     // → WHERE Status = 1
    .Include(o => o.Items)                          // → LEFT JOIN OrderItems
    .OrderByDescending(o => o.CreatedAt)            // → ORDER BY CreatedAt DESC
    .Take(10)                                       // → TOP 10 / LIMIT 10
    .ToListAsync();

// ⚠️ KHÔNG phải mọi C# expression đều translate được
// ❌ Client Evaluation (performance killer!)
var orders = await db.Orders
    .Where(o => MyCustomMethod(o.Name))  // 💥 Không translate → load ALL rows!
    .ToListAsync();

// .NET 8+: Mặc định throw exception nếu client evaluation
// Trước đó: silently load tất cả rồi filter trên client

Query Debugging

csharp
// 1. Xem SQL generated
var query = db.Orders.Where(o => o.Total > 100);
var sql = query.ToQueryString();  // Trả về SQL string

// 2. Logging SQL queries
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging()   // Hiện parameter values (DEV only!)
           .EnableDetailedErrors());       // Chi tiết hơn khi lỗi

2. Performance Optimization Techniques

Compiled Queries

csharp
// Compiled query: cache expression tree, tránh recompile mỗi lần gọi
public class OrderRepository
{
    // Compiled: ~40% faster cho queries gọi nhiều lần
    private static readonly Func<AppDbContext, Guid, Task<Order?>> _getById =
        EF.CompileAsyncQuery((AppDbContext db, Guid id) =>
            db.Orders
                .Include(o => o.Items)
                .FirstOrDefault(o => o.Id == id));

    private static readonly Func<AppDbContext, OrderStatus, int, IAsyncEnumerable<Order>>
        _getByStatus = EF.CompileAsyncQuery(
            (AppDbContext db, OrderStatus status, int limit) =>
                db.Orders
                    .Where(o => o.Status == status)
                    .OrderByDescending(o => o.CreatedAt)
                    .Take(limit));

    public Task<Order?> GetByIdAsync(Guid id) => _getById(_db, id);

    public async Task<List<Order>> GetByStatusAsync(OrderStatus status, int limit = 20)
    {
        var results = new List<Order>();
        await foreach (var order in _getByStatus(_db, status, limit))
            results.Add(order);
        return results;
    }
}

Split Queries

csharp
// ❌ Cartesian explosion: Include nhiều collections
var orders = await db.Orders
    .Include(o => o.Items)          // 10 items
    .Include(o => o.Payments)       // 3 payments
    .Include(o => o.StatusHistory)  // 20 entries
    .ToListAsync();
// → 1 query nhưng 10 × 3 × 20 = 600 rows!

// ✅ Split Queries: tách thành nhiều SQL queries
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .Include(o => o.StatusHistory)
    .AsSplitQuery()               // 4 queries riêng biệt
    .ToListAsync();

// ⚠️ Trade-off: nhiều roundtrips nhưng ít data transfer
// ⚠️ Split queries KHÔNG đảm bảo consistency giữa các queries

// Global config:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connStr, sqlOptions =>
        sqlOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)));

No-Tracking Queries

csharp
// Read-only queries: tắt change tracking → ~30% faster
var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

// No-tracking with identity resolution (tránh duplicate entities)
var orders = await db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .ToListAsync();

// Global default: no-tracking cho read-heavy apps
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connStr)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
// Khi cần tracking: db.Orders.AsTracking().First(...)

Projection - Tốt Nhất Cho Performance

csharp
// ✅ LUÔN project khi chỉ cần một số fields
var orderDtos = await db.Orders
    .Where(o => o.Status == OrderStatus.Active)
    .Select(o => new OrderListDto
    {
        Id = o.Id,
        CustomerName = o.Customer.FullName,    // JOIN tự động
        Total = o.Total,
        ItemCount = o.Items.Count,             // Subquery tự động
        CreatedAt = o.CreatedAt
    })
    .ToListAsync();
// → Chỉ SELECT cần thiết, no tracking, no Include needed
// → Đây là CÁCH TỐI ƯU NHẤT cho queries

// AutoMapper ProjectTo:
var dtos = await db.Orders
    .Where(o => o.Status == OrderStatus.Active)
    .ProjectTo<OrderListDto>(_mapper.ConfigurationProvider)
    .ToListAsync();

Raw SQL & Interpolated Queries

csharp
// Raw SQL với parameterized queries (SQL injection safe)
var orders = await db.Orders
    .FromSqlInterpolated(
        $"SELECT * FROM Orders WHERE Total > {minTotal} AND Status = {status}")
    .Include(o => o.Items)   // Có thể chain LINQ sau FromSql!
    .OrderBy(o => o.CreatedAt)
    .ToListAsync();

// Stored Procedure
var report = await db.Database
    .SqlQueryRaw<MonthlyReport>(
        "EXEC sp_MonthlyReport @Year = {0}, @Month = {1}", year, month)
    .ToListAsync();

// Non-query commands
await db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Price = Price * {multiplier} WHERE CategoryId = {categoryId}");

// Bulk operations (.NET 7+ EF Core)
await db.Orders
    .Where(o => o.Status == OrderStatus.Expired)
    .ExecuteUpdateAsync(s => s
        .SetProperty(o => o.Status, OrderStatus.Cancelled)
        .SetProperty(o => o.CancelledAt, DateTime.UtcNow));

await db.AuditLogs
    .Where(l => l.CreatedAt < cutoffDate)
    .ExecuteDeleteAsync();
// → 1 SQL statement, KHÔNG load entities vào memory!

3. Interceptors

csharp
// SaveChanges Interceptor: audit trail tự động
public class AuditInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken ct = default)
    {
        var context = eventData.Context!;

        foreach (var entry in context.ChangeTracker
            .Entries<IAuditable>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.Entity.CreatedAt = DateTime.UtcNow;
                    entry.Entity.CreatedBy = GetCurrentUser();
                    break;
                case EntityState.Modified:
                    entry.Entity.ModifiedAt = DateTime.UtcNow;
                    entry.Entity.ModifiedBy = GetCurrentUser();
                    break;
            }
        }

        return base.SavingChangesAsync(eventData, result, ct);
    }
}

// Soft Delete Interceptor
public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken ct = default)
    {
        foreach (var entry in eventData.Context!.ChangeTracker
            .Entries<ISoftDeletable>()
            .Where(e => e.State == EntityState.Deleted))
        {
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTime.UtcNow;
        }

        return base.SavingChangesAsync(eventData, result, ct);
    }
}

// Command Interceptor: query timing
public class QueryTimingInterceptor : DbCommandInterceptor
{
    private readonly ILogger<QueryTimingInterceptor> _logger;

    public override ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        DbDataReader result,
        CancellationToken ct = default)
    {
        if (eventData.Duration.TotalMilliseconds > 200)
        {
            _logger.LogWarning(
                "Slow query ({Duration}ms): {CommandText}",
                eventData.Duration.TotalMilliseconds,
                command.CommandText);
        }
        return new ValueTask<DbDataReader>(result);
    }
}

// Registration
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
    options.UseSqlServer(connectionString)
           .AddInterceptors(
               sp.GetRequiredService<AuditInterceptor>(),
               sp.GetRequiredService<SoftDeleteInterceptor>(),
               sp.GetRequiredService<QueryTimingInterceptor>()));

4. Advanced Modeling

Owned Types & Value Objects

csharp
// Value Object: Address
public class Address
{
    public string Street { get; private set; } = null!;
    public string City { get; private set; } = null!;
    public string PostalCode { get; private set; } = null!;
    public string Country { get; private set; } = null!;

    private Address() { } // EF Core

    public static Address Create(string street, string city, string postalCode, string country)
    {
        ArgumentException.ThrowIfNullOrWhiteSpace(street);
        return new Address { Street = street, City = city, PostalCode = postalCode, Country = country };
    }
}

// Value Object: Money
public record Money
{
    public decimal Amount { get; init; }
    public string Currency { get; init; } = "VND";

    public Money(decimal amount, string currency)
    {
        if (amount < 0) throw new ArgumentException("Amount must be non-negative");
        Amount = amount;
        Currency = currency;
    }
}

// Cấu hình Owned Types
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders");

        // Owned: lưu inline trong cùng table
        builder.OwnsOne(o => o.ShippingAddress, sa =>
        {
            sa.Property(a => a.Street).HasColumnName("ShippingStreet").HasMaxLength(200);
            sa.Property(a => a.City).HasColumnName("ShippingCity").HasMaxLength(100);
            sa.Property(a => a.PostalCode).HasColumnName("ShippingPostalCode").HasMaxLength(20);
            sa.Property(a => a.Country).HasColumnName("ShippingCountry").HasMaxLength(50);
        });

        // Complex Property (.NET 8+): cho Value Objects
        builder.ComplexProperty(o => o.Total, t =>
        {
            t.Property(m => m.Amount).HasColumnName("TotalAmount").HasPrecision(18, 2);
            t.Property(m => m.Currency).HasColumnName("TotalCurrency").HasMaxLength(3);
        });
    }
}

Table Splitting

csharp
// 2 entities, 1 table - tách read model
public class OrderSummary
{
    public Guid Id { get; set; }
    public OrderStatus Status { get; set; }
    public decimal Total { get; set; }
}

public class OrderDetail
{
    public Guid Id { get; set; }
    public string ShippingAddress { get; set; } = null!;
    public string Notes { get; set; } = null!;
    // ... heavy columns
}

// Cấu hình: cả 2 map vào "Orders" table
modelBuilder.Entity<OrderSummary>().ToTable("Orders");
modelBuilder.Entity<OrderDetail>().ToTable("Orders");
modelBuilder.Entity<OrderSummary>()
    .HasOne<OrderDetail>()
    .WithOne()
    .HasForeignKey<OrderDetail>(d => d.Id);

// Queries nhanh hơn: chỉ load OrderSummary cho list view
var summaries = await db.Set<OrderSummary>().ToListAsync();

Global Query Filters

csharp
// Soft delete filter - tự động apply cho MỌI query
modelBuilder.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);
modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);

// Multi-tenant filter
modelBuilder.Entity<Order>().HasQueryFilter(
    o => o.TenantId == _currentTenant.Id);

// Tắt filter khi cần (admin, reports):
var allOrders = await db.Orders.IgnoreQueryFilters().ToListAsync();

5. Concurrency Handling

csharp
// Optimistic Concurrency với RowVersion
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;

    // Hoặc dùng ConcurrencyToken cho specific fields:
    // [ConcurrencyCheck]
    // public decimal Price { get; set; }
}

// Xử lý conflict
public async Task UpdatePriceAsync(int productId, decimal newPrice)
{
    var maxRetries = 3;
    for (int i = 0; i < maxRetries; i++)
    {
        try
        {
            var product = await _db.Products.FindAsync(productId);
            product!.Price = newPrice;
            await _db.SaveChangesAsync();
            return; // Success
        }
        catch (DbUpdateConcurrencyException ex)
        {
            var entry = ex.Entries.Single();
            
            // Strategy 1: Database wins
            await entry.ReloadAsync();
            // product giờ có giá trị mới từ DB
            
            // Strategy 2: Client wins
            // entry.OriginalValues.SetValues(await entry.GetDatabaseValuesAsync());
            
            // Strategy 3: Merge
            // var dbValues = await entry.GetDatabaseValuesAsync();
            // ... custom merge logic
        }
    }
    throw new InvalidOperationException("Max retries exceeded");
}

6. Migrations Strategy

bash
# Development workflow
dotnet ef migrations add InitialCreate --project Infrastructure --startup-project Web
dotnet ef database update

# Production: KHÔNG dùng database update
# Generate SQL script thay vì:
dotnet ef migrations script --idempotent --output migrations.sql
# Review script → apply qua CI/CD pipeline

# Bundle migrations (.NET 6+)
dotnet ef migrations bundle --self-contained
# Tạo executable: efbundle.exe
# Deploy: ./efbundle --connection "Server=..."

csharp
// Data seeding trong migrations
public partial class SeedRoles : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "Roles",
            columns: new[] { "Id", "Name" },
            values: new object[,]
            {
                { 1, "Admin" },
                { 2, "User" },
                { 3, "Viewer" }
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(table: "Roles", keyColumn: "Id", keyValues: new object[] { 1, 2, 3 });
    }
}

7. Repository & Unit of Work: Cần Hay Không?

Khi Nào KHÔNG Cần (Anti-Pattern)

csharp
// ❌ Generic repository wrapping EF Core = vô nghĩa
public class GenericRepository<T> : IRepository<T> where T : class
{
    private readonly DbContext _context;
    
    public async Task<T?> GetByIdAsync(int id)
        => await _context.Set<T>().FindAsync(id);
    
    public async Task<List<T>> GetAllAsync()
        => await _context.Set<T>().ToListAsync();
    
    // ... CHỈ wrap lại DbContext methods → Leaky abstraction!
}

// DbContext ĐÃ LÀ Unit of Work + Repository pattern rồi!
// SaveChangesAsync = Commit (Unit of Work)
// DbSet<T> = Repository

Khi Nào CẦN

csharp
// ✅ Specification Pattern - encapsulate query logic
public abstract class Specification<T>
{
    public abstract Expression<Func<T, bool>> ToExpression();
    
    public List<Expression<Func<T, object>>> Includes { get; } = new();
    public Expression<Func<T, object>>? OrderBy { get; protected set; }
    public int? Take { get; protected set; }
    public int? Skip { get; protected set; }
}

public class ActiveOrdersByCustomerSpec : Specification<Order>
{
    private readonly Guid _customerId;

    public ActiveOrdersByCustomerSpec(Guid customerId, int page = 1, int size = 20)
    {
        _customerId = customerId;
        Includes.Add(o => o.Items);
        OrderBy = o => o.CreatedAt;
        Skip = (page - 1) * size;
        Take = size;
    }

    public override Expression<Func<Order, bool>> ToExpression()
        => o => o.CustomerId == _customerId && o.Status == OrderStatus.Active;
}

// Repository cho phép swap data source (testing, migration)
public interface IOrderRepository
{
    Task<Order?> GetByIdAsync(Guid id, CancellationToken ct = default);
    Task<List<Order>> ListAsync(Specification<Order> spec, CancellationToken ct = default);
    Task AddAsync(Order order, CancellationToken ct = default);
}

Tổng Kết Session 04

Bài tập: Thêm AuditInterceptor và SoftDeleteInterceptor vào project hiện tại. Benchmark query performance trước/sau khi dùng AsNoTracking() và Select() projection.