IndianTechnoEra: Chapter 10: Production Best Practices and Architecture

Welcome to the final chapter of our EF Core journey! This chapter brings together everything we've learned and adds the essential practices for building production-ready applications. We'll explore clean architecture, repository patterns, DbContext lifecycle management, security, auditing, and deployment strategies. By the end, you'll be equipped to build robust, maintainable, and secure enterprise applications with EF Core.

---

10.1 Clean Architecture with EF Core

Clean Architecture separates concerns into distinct layers, making your application maintainable, testable, and scalable.

10.1.1 Project Structure

Solution/
├── src/
│   ├── Domain/                 # Domain layer (no dependencies)
│   │   ├── Entities/
│   │   ├── ValueObjects/
│   │   ├── Enums/
│   │   └── Interfaces/
│   │
│   ├── Application/            # Application layer (depends on Domain)
│   │   ├── DTOs/
│   │   ├── Interfaces/
│   │   ├── Services/
│   │   └── Commands/Queries/
│   │
│   ├── Infrastructure/         # Infrastructure layer (depends on Application)
│   │   ├── Data/
│   │   │   ├── Configurations/
│   │   │   ├── Migrations/
│   │   │   └── AppDbContext.cs
│   │   └── Repositories/
│   │
│   └── API/                    # Presentation layer (depends on Application)
│       ├── Controllers/
│       ├── Middleware/
│       └── Program.cs
│
└── tests/                      # Test projects
    ├── Domain.Tests/
    ├── Application.Tests/
    └── Integration.Tests/

10.1.2 Domain Layer (No EF Core Dependencies)

// Domain/Entities/Book.cs
// Pure domain entities - no EF Core attributes or dependencies

namespace Domain.Entities;

public class Book
{
    private readonly List<Review> _reviews = new();
    private readonly List<Category> _categories = new();

    public int Id { get; private set; }
    public string Title { get; private set; }
    public string? Isbn { get; private set; }
    public decimal Price { get; private set; }
    public DateTime? PublishedOn { get; private set; }
    public BookStatus Status { get; private set; }
    public bool IsDeleted { get; private set; }
    
    // Navigation properties as private fields
    private int? _authorId;
    public Author? Author { get; private set; }
    
    public IReadOnlyCollection<Review> Reviews => _reviews.AsReadOnly();
    public IReadOnlyCollection<Category> Categories => _categories.AsReadOnly();
    
    // Private constructor for EF Core
    private Book() { }
    
    // Public constructor for business logic
    public Book(string title, decimal price, string? isbn = null)
    {
        if (string.IsNullOrWhiteSpace(title))
            throw new ArgumentException("Title is required", nameof(title));
        
        if (price <= 0)
            throw new ArgumentException("Price must be positive", nameof(price));
        
        Title = title;
        Price = price;
        Isbn = isbn;
        Status = BookStatus.Draft;
    }
    
    // Business methods
    public void Publish()
    {
        if (Status == BookStatus.Published)
            throw new InvalidOperationException("Book is already published");
        
        Status = BookStatus.Published;
        PublishedOn = DateTime.UtcNow;
    }
    
    public void UpdatePrice(decimal newPrice)
    {
        if (newPrice <= 0)
            throw new ArgumentException("Price must be positive", nameof(newPrice));
        
        Price = newPrice;
    }
    
    public void AssignAuthor(Author author)
    {
        if (author == null)
            throw new ArgumentNullException(nameof(author));
        
        _authorId = author.Id;
        Author = author;
    }
    
    public void AddReview(Review review)
    {
        if (review == null)
            throw new ArgumentNullException(nameof(review));
        
        _reviews.Add(review);
    }
    
    public void SoftDelete()
    {
        IsDeleted = true;
    }
    
    public void Restore()
    {
        IsDeleted = false;
    }
}

// Domain/Enums/BookStatus.cs
public enum BookStatus
{
    Draft = 0,
    Published = 1,
    OutOfPrint = 2,
    Discounted = 3
}

10.1.3 Application Layer (Use Cases and DTOs)

// Application/DTOs/BookDto.cs
namespace Application.DTOs;

public class BookDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string? Isbn { get; set; }
    public decimal Price { get; set; }
    public DateTime? PublishedOn { get; set; }
    public string Status { get; set; }
    public string AuthorName { get; set; }
    public int ReviewCount { get; set; }
    public double AverageRating { get; set; }
}

public class CreateBookDto
{
    public string Title { get; set; }
    public string? Isbn { get; set; }
    public decimal Price { get; set; }
    public int? AuthorId { get; set; }
}

public class UpdateBookDto
{
    public string Title { get; set; }
    public decimal? Price { get; set; }
    public int? AuthorId { get; set; }
}

// Application/Interfaces/IBookRepository.cs
namespace Application.Interfaces;

public interface IBookRepository : IRepository<Book>
{
    Task<Book?> GetByIsbnAsync(string isbn, CancellationToken cancellationToken = default);
    Task<IEnumerable<Book>> GetPublishedBooksAsync(CancellationToken cancellationToken = default);
    Task<IEnumerable<Book>> GetBooksByAuthorAsync(int authorId, CancellationToken cancellationToken = default);
    Task<PagedResult<Book>> GetPagedAsync(int pageNumber, int pageSize, string? genre, CancellationToken cancellationToken = default);
}

// Application/Interfaces/IRepository.cs
namespace Application.Interfaces;

public interface IRepository<T> where T : class
{
    Task<T?> GetByIdAsync(int id, CancellationToken cancellationToken = default);
    Task<IEnumerable<T>> GetAllAsync(CancellationToken cancellationToken = default);
    Task<T> AddAsync(T entity, CancellationToken cancellationToken = default);
    Task UpdateAsync(T entity, CancellationToken cancellationToken = default);
    Task DeleteAsync(T entity, CancellationToken cancellationToken = default);
    Task<bool> ExistsAsync(int id, CancellationToken cancellationToken = default);
}

// Application/Services/BookService.cs
namespace Application.Services;

public class BookService
{
    private readonly IBookRepository _bookRepository;
    private readonly IUnitOfWork _unitOfWork;
    private readonly IMapper _mapper;

    public BookService(
        IBookRepository bookRepository,
        IUnitOfWork unitOfWork,
        IMapper mapper)
    {
        _bookRepository = bookRepository;
        _unitOfWork = unitOfWork;
        _mapper = mapper;
    }

    public async Task<BookDto?> GetBookByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        var book = await _bookRepository.GetByIdAsync(id, cancellationToken);
        return book != null ? _mapper.Map<BookDto>(book) : null;
    }

    public async Task<BookDto> CreateBookAsync(CreateBookDto createDto, CancellationToken cancellationToken = default)
    {
        // Check if ISBN already exists
        if (!string.IsNullOrWhiteSpace(createDto.Isbn))
        {
            var existing = await _bookRepository.GetByIsbnAsync(createDto.Isbn, cancellationToken);
            if (existing != null)
                throw new InvalidOperationException($"Book with ISBN {createDto.Isbn} already exists");
        }

        var book = new Book(createDto.Title, createDto.Price, createDto.Isbn);
        
        await _bookRepository.AddAsync(book, cancellationToken);
        await _unitOfWork.SaveChangesAsync(cancellationToken);
        
        return _mapper.Map<BookDto>(book);
    }

    public async Task UpdateBookAsync(int id, UpdateBookDto updateDto, CancellationToken cancellationToken = default)
    {
        var book = await _bookRepository.GetByIdAsync(id, cancellationToken);
        if (book == null)
            throw new NotFoundException($"Book with ID {id} not found");
        
        // Update only provided fields
        if (!string.IsNullOrWhiteSpace(updateDto.Title))
        {
            // In a real app, you'd have a method for title update
            // book.UpdateTitle(updateDto.Title);
        }
        
        if (updateDto.Price.HasValue)
            book.UpdatePrice(updateDto.Price.Value);
        
        await _bookRepository.UpdateAsync(book, cancellationToken);
        await _unitOfWork.SaveChangesAsync(cancellationToken);
    }

    public async Task PublishBookAsync(int id, CancellationToken cancellationToken = default)
    {
        var book = await _bookRepository.GetByIdAsync(id, cancellationToken);
        if (book == null)
            throw new NotFoundException($"Book with ID {id} not found");
        
        book.Publish();
        
        await _bookRepository.UpdateAsync(book, cancellationToken);
        await _unitOfWork.SaveChangesAsync(cancellationToken);
    }

    public async Task<PagedResult<BookDto>> GetPagedBooksAsync(
        int pageNumber, 
        int pageSize, 
        string? genre,
        CancellationToken cancellationToken = default)
    {
        var pagedBooks = await _bookRepository.GetPagedAsync(pageNumber, pageSize, genre, cancellationToken);
        
        return new PagedResult<BookDto>
        {
            Items = _mapper.Map<IEnumerable<BookDto>>(pagedBooks.Items),
            PageNumber = pagedBooks.PageNumber,
            PageSize = pagedBooks.PageSize,
            TotalCount = pagedBooks.TotalCount
        };
    }
}

10.1.4 Infrastructure Layer (EF Core Implementation)

// Infrastructure/Data/Configurations/BookConfiguration.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Infrastructure.Data.Configurations;

public class BookConfiguration : IEntityTypeConfiguration<Book>
{
    public void Configure(EntityTypeBuilder<Book> builder)
    {
        builder.ToTable("Books", "dbo");
        
        builder.HasKey(b => b.Id);
        
        // Configure private fields for EF Core
        builder.Property(b => b.Id)
            .HasColumnName("Id")
            .UseIdentityColumn();
        
        builder.Property(b => b.Title)
            .IsRequired()
            .HasMaxLength(200)
            .HasColumnName("Title");
        
        builder.Property(b => b.Isbn)
            .HasMaxLength(13)
            .HasColumnName("ISBN");
        
        builder.Property(b => b.Price)
            .HasColumnType("decimal(10,2)")
            .HasColumnName("Price");
        
        builder.Property(b => b.PublishedOn)
            .HasColumnType("date")
            .HasColumnName("PublishedOn");
        
        builder.Property(b => b.Status)
            .HasConversion<string>()
            .HasColumnName("Status");
        
        builder.Property(b => b.IsDeleted)
            .HasColumnName("IsDeleted")
            .HasDefaultValue(false);
        
        // Configure private fields
        builder.Property<int?>("_authorId")
            .HasColumnName("AuthorId")
            .UsePropertyAccessMode(PropertyAccessMode.Field);
        
        // Configure relationships
        builder.HasOne(b => b.Author)
            .WithMany(a => a.Books)
            .HasForeignKey("_authorId")
            .OnDelete(DeleteBehavior.SetNull);
        
        builder.HasMany(b => b.Reviews)
            .WithOne(r => r.Book)
            .HasForeignKey("BookId")
            .OnDelete(DeleteBehavior.Cascade);
        
        // Configure many-to-many
        builder.HasMany(b => b.Categories)
            .WithMany(c => c.Books)
            .UsingEntity<Dictionary<string, object>>(
                "BookCategories",
                j => j.HasOne<Category>().WithMany().HasForeignKey("CategoryId"),
                j => j.HasOne<Book>().WithMany().HasForeignKey("BookId"));
        
        // Global query filter for soft delete
        builder.HasQueryFilter(b => !b.IsDeleted);
        
        // Indexes
        builder.HasIndex(b => b.Isbn)
            .IsUnique()
            .HasDatabaseName("IX_Books_ISBN_Unique")
            .HasFilter("[ISBN] IS NOT NULL");
        
        builder.HasIndex(b => b.Status)
            .HasDatabaseName("IX_Books_Status");
        
        builder.HasIndex(b => new { b.Title, b.Price })
            .HasDatabaseName("IX_Books_Title_Price");
    }
}

// Infrastructure/Data/AppDbContext.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;

namespace Infrastructure.Data;

public class AppDbContext : DbContext, IUnitOfWork
{
    private readonly IMediator _mediator;
    
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Review> Reviews { get; set; }
    
    public AppDbContext(DbContextOptions<AppDbContext> options, IMediator mediator) 
        : base(options)
    {
        _mediator = mediator;
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply all configurations from this assembly
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
        
        base.OnModelCreating(modelBuilder);
    }
    
    public async Task<bool> SaveEntitiesAsync(CancellationToken cancellationToken = default)
    {
        // Dispatch domain events before saving
        await DispatchDomainEventsAsync();
        
        var result = await base.SaveChangesAsync(cancellationToken);
        
        return result > 0;
    }
    
    private async Task DispatchDomainEventsAsync()
    {
        var entities = ChangeTracker
            .Entries<EntityBase>()
            .Where(e => e.Entity.DomainEvents.Any())
            .Select(e => e.Entity)
            .ToList();
        
        var domainEvents = entities
            .SelectMany(e => e.DomainEvents)
            .ToList();
        
        entities.ForEach(e => e.ClearDomainEvents());
        
        foreach (var domainEvent in domainEvents)
        {
            await _mediator.Publish(domainEvent);
        }
    }
}

// Infrastructure/Repositories/BookRepository.cs
namespace Infrastructure.Repositories;

public class BookRepository : IBookRepository
{
    private readonly AppDbContext _context;
    private readonly DbSet<Book> _dbSet;
    
    public BookRepository(AppDbContext context)
    {
        _context = context;
        _dbSet = context.Set<Book>();
    }
    
    public async Task<Book?> GetByIdAsync(int id, CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .Include(b => b.Author)
            .Include(b => b.Categories)
            .Include(b => b.Reviews)
            .FirstOrDefaultAsync(b => b.Id == id, cancellationToken);
    }
    
    public async Task<IEnumerable<Book>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .AsNoTracking()
            .Include(b => b.Author)
            .OrderBy(b => b.Title)
            .ToListAsync(cancellationToken);
    }
    
    public async Task<Book> AddAsync(Book entity, CancellationToken cancellationToken = default)
    {
        var entry = await _dbSet.AddAsync(entity, cancellationToken);
        return entry.Entity;
    }
    
    public Task UpdateAsync(Book entity, CancellationToken cancellationToken = default)
    {
        _dbSet.Update(entity);
        return Task.CompletedTask;
    }
    
    public Task DeleteAsync(Book entity, CancellationToken cancellationToken = default)
    {
        _dbSet.Remove(entity);
        return Task.CompletedTask;
    }
    
    public async Task<bool> ExistsAsync(int id, CancellationToken cancellationToken = default)
    {
        return await _dbSet.AnyAsync(b => b.Id == id, cancellationToken);
    }
    
    public async Task<Book?> GetByIsbnAsync(string isbn, CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .FirstOrDefaultAsync(b => b.Isbn == isbn, cancellationToken);
    }
    
    public async Task<IEnumerable<Book>> GetPublishedBooksAsync(CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .Where(b => b.Status == BookStatus.Published)
            .OrderByDescending(b => b.PublishedOn)
            .ToListAsync(cancellationToken);
    }
    
    public async Task<IEnumerable<Book>> GetBooksByAuthorAsync(int authorId, CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .Where(b => b.Author != null && b.Author.Id == authorId)
            .OrderBy(b => b.Title)
            .ToListAsync(cancellationToken);
    }
    
    public async Task<PagedResult<Book>> GetPagedAsync(
        int pageNumber, 
        int pageSize, 
        string? genre,
        CancellationToken cancellationToken = default)
    {
        var query = _dbSet.AsNoTracking();
        
        if (!string.IsNullOrWhiteSpace(genre))
        {
            query = query.Where(b => b.Categories.Any(c => c.Name == genre));
        }
        
        var totalCount = await query.CountAsync(cancellationToken);
        
        var items = await query
            .Include(b => b.Author)
            .OrderBy(b => b.Title)
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync(cancellationToken);
        
        return new PagedResult<Book>
        {
            Items = items,
            PageNumber = pageNumber,
            PageSize = pageSize,
            TotalCount = totalCount
        };
    }
}

// Infrastructure/UnitOfWork.cs
namespace Infrastructure.Data;

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
    
    public UnitOfWork(AppDbContext context)
    {
        _context = context;
    }
    
    public async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        return await _context.SaveChangesAsync(cancellationToken);
    }
    
    public async Task<IDbContextTransaction> BeginTransactionAsync(CancellationToken cancellationToken = default)
    {
        return await _context.Database.BeginTransactionAsync(cancellationToken);
    }
}

---

10.2 DbContext Lifetime Management

10.2.1 Scoped Lifetime in Web Applications

// Program.cs - ASP.NET Core
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
    options.EnableSensitiveDataLogging(builder.Environment.IsDevelopment());
    options.EnableDetailedErrors(builder.Environment.IsDevelopment());
}, ServiceLifetime.Scoped); // Default is Scoped, which is correct for web apps

// In controller
[ApiController]
[Route("api/[controller]")]
public class BooksController : ControllerBase
{
    private readonly AppDbContext _context;
    
    public BooksController(AppDbContext context)
    {
        _context = context; // Injected per request, disposed after request
    }
}

10.2.2 DbContext Pooling for High-Performance Scenarios

// Enable DbContext pooling (reduces overhead of creating DbContext instances)
builder.Services.AddDbContextPool<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
}, poolSize: 128); // Maximum number of instances in pool

// DbContext pooling is beneficial for:
// - High-throughput web applications
// - Read-heavy scenarios
// - When DbContext creation overhead is measurable

10.2.3 Factory Pattern for Manual DbContext Creation

// For background services or console apps
public interface IDbContextFactory<TContext> where TContext : DbContext
{
    TContext CreateDbContext();
}

// Registration
builder.Services.AddDbContextFactory<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
});

// Usage in background service
public class BackgroundBookProcessor : BackgroundService
{
    private readonly IDbContextFactory<AppDbContext> _contextFactory;
    
    public BackgroundBookProcessor(IDbContextFactory<AppDbContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }
    
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            // Create a new DbContext for each operation
            await using var context = await _contextFactory.CreateDbContextAsync();
            
            var books = await context.Books
                .Where(b => b.Status == BookStatus.Published)
                .ToListAsync(stoppingToken);
            
            await ProcessBooksAsync(books);
            
            await Task.Delay(TimeSpan.FromMinutes(5), stoppingToken);
        }
    }
}

10.2.4 Dispose Pattern and Best Practices

// Always dispose DbContext properly
public class BookService : IDisposable
{
    private readonly AppDbContext _context;
    private bool _disposed;
    
    public BookService(AppDbContext context)
    {
        _context = context;
    }
    
    public async Task<List<Book>> GetBooksAsync()
    {
        return await _context.Books.ToListAsync();
    }
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
        if (!_disposed && disposing)
        {
            _context?.Dispose();
        }
        _disposed = true;
    }
}

// Using statement for manual DbContext
using var context = new AppDbContext();
var books = await context.Books.ToListAsync();
// Automatically disposed at the end of the scope

---

10.3 Security Best Practices

10.3.1 Preventing SQL Injection

// ALWAYS use parameterized queries - EF Core does this automatically
// GOOD - EF Core uses parameters
var books = await db.Books
    .Where(b => b.Title == title)
    .ToListAsync();

// GOOD - Parameterized raw SQL
var booksRaw = await db.Books
    .FromSqlInterpolated($"SELECT * FROM Books WHERE Title = {title}")
    .ToListAsync();

// BAD - String concatenation (NEVER DO THIS!)
// var sql = $"SELECT * FROM Books WHERE Title = '{title}'";
// var books = await db.Books.FromSqlRaw(sql).ToListAsync();

// SAFE - Using SqlParameter
var param = new SqlParameter("@title", title);
var booksSafe = await db.Books
    .FromSqlRaw("SELECT * FROM Books WHERE Title = @title", param)
    .ToListAsync();

10.3.2 Data Protection and Encryption

// Encrypt sensitive data in the database
using Microsoft.AspNetCore.DataProtection;

public class EncryptedValueConverter : ValueConverter<string, string>
{
    public EncryptedValueConverter(IDataProtector protector)
        : base(
            v => protector.Protect(v),  // Encrypt when writing to DB
            v => protector.Unprotect(v)) // Decrypt when reading from DB
    {
    }
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var dataProtector = _dataProtectionProvider.CreateProtector("BookSensitiveData");
    var converter = new EncryptedValueConverter(dataProtector);
    
    modelBuilder.Entity<Author>()
        .Property(a => a.Email)
        .HasConversion(converter);
}

// Always encrypt sensitive data like:
// - Personally Identifiable Information (PII)
// - Email addresses
// - Phone numbers
// - Credit card information
// - Social Security numbers

10.3.3 Row-Level Security

// Multi-tenant application with row-level security
public class MultiTenantDbContext : DbContext
{
    private readonly int _tenantId;
    
    public MultiTenantDbContext(DbContextOptions options, ITenantProvider tenantProvider)
        : base(options)
    {
        _tenantId = tenantProvider.GetTenantId();
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Global query filter for multi-tenancy
        modelBuilder.Entity<Book>()
            .HasQueryFilter(b => b.TenantId == _tenantId);
        
        modelBuilder.Entity<Author>()
            .HasQueryFilter(a => a.TenantId == _tenantId);
    }
}

// SQL Server Row-Level Security (RLS)
// Add to migration
migrationBuilder.Sql(@"
    CREATE SCHEMA security;
    GO
    
    CREATE FUNCTION security.tenantAccessPredicate(@TenantId int)
        RETURNS TABLE
        WITH SCHEMABINDING
    AS
        RETURN SELECT 1 AS accessResult
        WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);
    GO
    
    CREATE SECURITY POLICY tenantFilterPolicy
        ADD FILTER PREDICATE security.tenantAccessPredicate(TenantId) ON dbo.Books,
        ADD BLOCK PREDICATE security.tenantAccessPredicate(TenantId) ON dbo.Books;
    GO
");

10.3.4 Connection String Security

// NEVER hardcode connection strings
// BAD: var connectionString = "Server=prod;Database=Books;User=sa;Password=Admin123!";

// GOOD: Use configuration with secrets
// appsettings.json (with user secrets in development)
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=prod;Database=Books;User=sa;Password="
  }
}

// Use Azure Key Vault or AWS Secrets Manager in production
builder.Configuration.AddAzureKeyVault(
    new Uri("https://mykeyvault.vault.azure.net/"),
    new DefaultAzureCredential());

// Use managed identity for Azure SQL
// Server=tcp:myserver.database.windows.net;Authentication=Active Directory Managed Identity;Database=myDb;

// Use Windows Authentication for on-premises
// Server=myserver;Database=myDb;Trusted_Connection=True;

// Use environment variables
var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING");

---

10.4 Auditing and Logging

10.4.1 Automatic Audit with SaveChanges Interceptor

using Microsoft.EntityFrameworkCore.Diagnostics;

public class AuditInterceptor : SaveChangesInterceptor
{
    private readonly ICurrentUserService _currentUserService;
    
    public AuditInterceptor(ICurrentUserService currentUserService)
    {
        _currentUserService = currentUserService;
    }
    
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        UpdateAuditableEntities(eventData.Context);
        return result;
    }
    
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        UpdateAuditableEntities(eventData.Context);
        return ValueTask.FromResult(result);
    }
    
    private void UpdateAuditableEntities(DbContext? context)
    {
        if (context == null) return;
        
        var userId = _currentUserService.UserId;
        var now = DateTime.UtcNow;
        
        foreach (var entry in context.ChangeTracker.Entries<IAuditableEntity>())
        {
            if (entry.State == EntityState.Added)
            {
                entry.Entity.CreatedBy = userId;
                entry.Entity.CreatedAt = now;
            }
            
            if (entry.State == EntityState.Modified)
            {
                entry.Entity.ModifiedBy = userId;
                entry.Entity.ModifiedAt = now;
            }
        }
    }
}

// Auditable Entity Interface
public interface IAuditableEntity
{
    string CreatedBy { get; set; }
    DateTime CreatedAt { get; set; }
    string? ModifiedBy { get; set; }
    DateTime? ModifiedAt { get; set; }
}

// Register interceptor
builder.Services.AddSingleton<AuditInterceptor>();

builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    var connectionString = sp.GetRequiredService<IConfiguration>()
        .GetConnectionString("DefaultConnection");
    
    var auditInterceptor = sp.GetRequiredService<AuditInterceptor>();
    
    options.UseSqlServer(connectionString);
    options.AddInterceptors(auditInterceptor);
});

10.4.2 Shadow Properties for Audit

// Configure shadow properties in OnModelCreating
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        // Add shadow properties for audit
        entityType.AddProperty("CreatedBy", typeof(string));
        entityType.AddProperty("CreatedAt", typeof(DateTime));
        entityType.AddProperty("ModifiedBy", typeof(string));
        entityType.AddProperty("ModifiedAt", typeof(DateTime));
    }
    
    // Configure default values
    modelBuilder.Entity<Book>()
        .Property<DateTime>("CreatedAt")
        .HasDefaultValueSql("GETUTCDATE()");
}

// Update shadow properties in SaveChanges
protected override void OnBeforeSaveChanges()
{
    var entries = ChangeTracker
        .Entries()
        .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);
    
    var userId = _currentUserService.UserId;
    var now = DateTime.UtcNow;
    
    foreach (var entry in entries)
    {
        if (entry.State == EntityState.Added)
        {
            entry.Property("CreatedBy").CurrentValue = userId;
            entry.Property("CreatedAt").CurrentValue = now;
        }
        
        entry.Property("ModifiedBy").CurrentValue = userId;
        entry.Property("ModifiedAt").CurrentValue = now;
    }
}

10.4.3 Comprehensive Logging Configuration

// Program.cs
builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Database.Command", LogLevel.Warning);
builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Infrastructure", LogLevel.Error);
builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Query", LogLevel.Warning);

// For development, enable detailed logging
if (builder.Environment.IsDevelopment())
{
    builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Database.Command", LogLevel.Information);
    
    builder.Services.AddDbContext<AppDbContext>(options =>
    {
        options.UseSqlServer(connectionString);
        options.EnableSensitiveDataLogging();     // Shows parameter values
        options.EnableDetailedErrors();           // More detailed error messages
        options.LogTo(Console.WriteLine, LogLevel.Information);
    });
}
else
{
    builder.Services.AddDbContext<AppDbContext>(options =>
    {
        options.UseSqlServer(connectionString);
        // No sensitive data logging in production
    });
}

// Custom logger for performance monitoring
public class EfCorePerformanceLogger : ILogger
{
    private readonly ILogger _logger;
    private readonly Stopwatch _stopwatch = new();
    
    public EfCorePerformanceLogger(ILogger<EfCorePerformanceLogger> logger)
    {
        _logger = logger;
    }
    
    public IDisposable BeginScope<TState>(TState state) => null;
    
    public bool IsEnabled(LogLevel logLevel) => logLevel == LogLevel.Information;
    
    public void Log<TState>(
        LogLevel logLevel,
        EventId eventId,
        TState state,
        Exception exception,
        Func<TState, Exception, string> formatter)
    {
        if (eventId.Name == "Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted")
        {
            _stopwatch.Restart();
            var message = formatter(state, exception);
            _logger.LogInformation($"EF Core Query: {message}");
            _stopwatch.Stop();
            
            if (_stopwatch.ElapsedMilliseconds > 1000)
            {
                _logger.LogWarning($"Slow query detected: {_stopwatch.ElapsedMilliseconds}ms\n{message}");
            }
        }
    }
}

---

10.5 Handling Database Migrations in Production

10.5.1 Migration Strategies

// Strategy 1: Apply migrations at application startup
public class Program
{
    public static async Task Main(string[] args)
    {
        var host = CreateHostBuilder(args).Build();
        
        using (var scope = host.Services.CreateScope())
        {
            var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
            
            // Apply pending migrations
            await dbContext.Database.MigrateAsync();
        }
        
        await host.RunAsync();
    }
}

// Strategy 2: Generate SQL scripts and run manually
// dotnet ef migrations script --idempotent -o migrate.sql

// Strategy 3: Use migration bundles (EF Core 6+)
// dotnet ef migrations bundle -o migrate.exe
// Run in production: ./migrate.exe --connection "Server=prod;..."

// Strategy 4: Docker with migrations
// Dockerfile
/*
FROM mcr.microsoft.com/dotnet/sdk:8.0 AS migrations
WORKDIR /app
COPY . .
RUN dotnet tool install --global dotnet-ef
ENV PATH="$PATH:/root/.dotnet/tools"
RUN dotnet ef migrations bundle --self-contained -r linux-x64 -o /out/migrate

FROM mcr.microsoft.com/dotnet/aspnet:8.0
WORKDIR /app
COPY --from=migrations /out/migrate .
COPY --from=migrations /app/out .
ENTRYPOINT ["./migrate", "--connection", "Server=db;Database=Books;User=sa;Password=Pass123!"]
*/

10.5.2 Safe Migration Practices

// 1. Always backup database before migration
// 2. Test migrations on staging environment first
// 3. Use idempotent scripts (can be run multiple times safely)
// 4. Implement rollback strategy

public class SafeMigrationService
{
    private readonly AppDbContext _context;
    private readonly ILogger<SafeMigrationService> _logger;
    
    public SafeMigrationService(AppDbContext context, ILogger<SafeMigrationService> logger)
    {
        _context = context;
        _logger = logger;
    }
    
    public async Task<bool> MigrateWithRollbackAsync(CancellationToken cancellationToken = default)
    {
        using var transaction = await _context.Database.BeginTransactionAsync(cancellationToken);
        
        try
        {
            // Get pending migrations
            var pending = await _context.Database.GetPendingMigrationsAsync(cancellationToken);
            var pendingList = pending.ToList();
            
            if (!pendingList.Any())
            {
                _logger.LogInformation("No pending migrations");
                return true;
            }
            
            _logger.LogInformation($"Applying {pendingList.Count} migrations");
            
            // Apply migrations
            await _context.Database.MigrateAsync(cancellationToken);
            
            // Verify schema integrity
            if (await VerifySchemaIntegrityAsync(cancellationToken))
            {
                await transaction.CommitAsync(cancellationToken);
                _logger.LogInformation("Migrations applied successfully");
                return true;
            }
            else
            {
                _logger.LogError("Schema verification failed");
                await transaction.RollbackAsync(cancellationToken);
                return false;
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Migration failed");
            await transaction.RollbackAsync(cancellationToken);
            return false;
        }
    }
    
    private async Task<bool> VerifySchemaIntegrityAsync(CancellationToken cancellationToken)
    {
        try
        {
            // Try to query a simple count
            await _context.Books.CountAsync(cancellationToken);
            return true;
        }
        catch
        {
            return false;
        }
    }
}

10.5.3 Zero-Downtime Migration Strategies

// Strategy 1: Add columns as nullable first
public partial class AddNewColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Step 1: Add column as nullable
        migrationBuilder.AddColumn<string>(
            name: "NewColumn",
            table: "Books",
            type: "nvarchar(100)",
            nullable: true);
        
        // Step 2: Backfill data in batches
        migrationBuilder.Sql(@"
            UPDATE Books 
            SET NewColumn = 'Default Value' 
            WHERE NewColumn IS NULL;
        ");
        
        // Step 3: Make column non-nullable
        migrationBuilder.AlterColumn<string>(
            name: "NewColumn",
            table: "Books",
            type: "nvarchar(100)",
            nullable: false,
            oldClrType: typeof(string),
            oldNullable: true);
    }
}

// Strategy 2: Use online index creation (SQL Server)
public partial class AddOnlineIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE NONCLUSTERED INDEX IX_Books_NewColumn
            ON Books(NewColumn)
            WITH (ONLINE = ON);
        ");
    }
}

// Strategy 3: Use table splitting for large changes
public partial class SplitLargeTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create new table
        migrationBuilder.CreateTable(
            name: "Books_New",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false),
                NewColumn = table.Column<string>(nullable: false)
            });
        
        // Migrate data
        migrationBuilder.Sql(@"
            INSERT INTO Books_New (Id, NewColumn)
            SELECT Id, NewColumn FROM Books;
        ");
        
        // Add constraint and swap tables
        migrationBuilder.Sql(@"
            ALTER TABLE Books_New ADD CONSTRAINT PK_Books_New PRIMARY KEY (Id);
            EXEC sp_rename 'Books', 'Books_Old';
            EXEC sp_rename 'Books_New', 'Books';
        ");
    }
}

---

10.6 Testing EF Core Applications

10.6.1 In-Memory Database for Unit Tests

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

public class BookRepositoryTests
{
    private AppDbContext CreateInMemoryContext()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .ConfigureWarnings(x => x.Ignore(InMemoryEventId.TransactionIgnoredWarning))
            .Options;
        
        return new AppDbContext(options);
    }
    
    [Fact]
    public async Task AddBook_ShouldInsertBook()
    {
        // Arrange
        using var context = CreateInMemoryContext();
        var repository = new BookRepository(context);
        var book = new Book("Test Book", 29.99m);
        
        // Act
        await repository.AddAsync(book);
        await context.SaveChangesAsync();
        
        // Assert
        var saved = await context.Books.FirstOrDefaultAsync();
        Assert.NotNull(saved);
        Assert.Equal("Test Book", saved.Title);
    }
}

10.6.2 SQLite In-Memory for Integration Tests

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

public class IntegrationTestBase : IDisposable
{
    private readonly SqliteConnection _connection;
    protected readonly AppDbContext Context;
    
    public IntegrationTestBase()
    {
        _connection = new SqliteConnection("DataSource=:memory:");
        _connection.Open();
        
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(_connection)
            .Options;
        
        Context = new AppDbContext(options);
        Context.Database.EnsureCreated();
        
        SeedData();
    }
    
    private void SeedData()
    {
        Context.Books.Add(new Book("Book 1", 19.99m));
        Context.Books.Add(new Book("Book 2", 29.99m));
        Context.SaveChanges();
    }
    
    public void Dispose()
    {
        Context.Dispose();
        _connection.Dispose();
    }
}

public class BookServiceTests : IntegrationTestBase
{
    [Fact]
    public async Task GetBooks_ShouldReturnAllBooks()
    {
        var service = new BookService(Context);
        
        var books = await service.GetAllBooksAsync();
        
        Assert.Equal(2, books.Count());
    }
}

10.6.3 Testing with Real Database (Docker)

// Testcontainers for integration tests with real SQL Server
// Install: dotnet add package Testcontainers.SqlEdge

public class SqlServerTestFixture : IAsyncLifetime
{
    private readonly SqlEdgeContainer _container;
    public string ConnectionString { get; private set; }
    public AppDbContext Context { get; private set; }
    
    public SqlServerTestFixture()
    {
        _container = new SqlEdgeBuilder()
            .WithImage("mcr.microsoft.com/azure-sql-edge")
            .WithPassword("StrongPassword123!")
            .Build();
    }
    
    public async Task InitializeAsync()
    {
        await _container.StartAsync();
        ConnectionString = _container.GetConnectionString();
        
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(ConnectionString)
            .Options;
        
        Context = new AppDbContext(options);
        await Context.Database.EnsureCreatedAsync();
    }
    
    public async Task DisposeAsync()
    {
        await Context.DisposeAsync();
        await _container.DisposeAsync();
    }
}

public class IntegrationTests : IClassFixture<SqlServerTestFixture>
{
    private readonly SqlServerTestFixture _fixture;
    
    public IntegrationTests(SqlServerTestFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task ComplexQuery_ShouldWorkWithRealDatabase()
    {
        // Arrange
        var repository = new BookRepository(_fixture.Context);
        
        // Act
        var books = await repository.GetPublishedBooksAsync();
        
        // Assert
        Assert.NotNull(books);
    }
}

10.6.4 Mocking DbContext

// Use Mock for unit tests (limited use - integration tests are better)
public class BookServiceTestsWithMock
{
    [Fact]
    public async Task GetBook_ShouldReturnBook()
    {
        // Arrange
        var books = new List<Book>
        {
            new Book("Book 1", 19.99m) { Id = 1 }
        }.AsQueryable();
        
        var mockSet = new Mock<DbSet<Book>>();
        mockSet.As<IQueryable<Book>>().Setup(m => m.Provider).Returns(books.Provider);
        mockSet.As<IQueryable<Book>>().Setup(m => m.Expression).Returns(books.Expression);
        mockSet.As<IQueryable<Book>>().Setup(m => m.ElementType).Returns(books.ElementType);
        mockSet.As<IQueryable<Book>>().Setup(m => m.GetEnumerator()).Returns(books.GetEnumerator());
        
        var mockContext = new Mock<AppDbContext>();
        mockContext.Setup(c => c.Books).Returns(mockSet.Object);
        
        var service = new BookService(mockContext.Object);
        
        // Act
        var book = await service.GetBookByIdAsync(1);
        
        // Assert
        Assert.NotNull(book);
    }
}

---

10.7 Production Monitoring and Alerting

10.7.1 Health Checks

// Program.cs
builder.Services.AddHealthChecks()
    .AddDbContextCheck<AppDbContext>("database", 
        failureStatus: HealthStatus.Degraded,
        tags: new[] { "ready", "database" })
    .AddSqlServer(connectionString, 
        name: "sqlserver",
        failureStatus: HealthStatus.Unhealthy);

app.MapHealthChecks("/health/ready", new HealthCheckOptions
{
    Predicate = _ => true,
    ResponseWriter = UIResponseWriter.WriteHealthCheckUIResponse
});

app.MapHealthChecks("/health/live", new HealthCheckOptions
{
    Predicate = _ => false // Exclude all checks
});

// Custom health check for migrations
public class MigrationHealthCheck : IHealthCheck
{
    private readonly AppDbContext _context;
    
    public MigrationHealthCheck(AppDbContext context)
    {
        _context = context;
    }
    
    public async Task<HealthCheckResult> CheckHealthAsync(
        HealthCheckContext context,
        CancellationToken cancellationToken = default)
    {
        try
        {
            var pending = await _context.Database.GetPendingMigrationsAsync(cancellationToken);
            if (pending.Any())
            {
                return HealthCheckResult.Degraded(
                    $"Pending migrations: {string.Join(", ", pending)}");
            }
            
            return HealthCheckResult.Healthy();
        }
        catch (Exception ex)
        {
            return HealthCheckResult.Unhealthy("Migration check failed", ex);
        }
    }
}

10.7.2 Application Insights Integration

// Program.cs
builder.Services.AddApplicationInsightsTelemetry();

// Track SQL queries
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.EnableDetailedErrors(true);
});

// Custom telemetry for slow queries
public class EfCoreTelemetryInterceptor : SaveChangesInterceptor
{
    private readonly TelemetryClient _telemetryClient;
    private readonly Stopwatch _stopwatch = new();
    
    public EfCoreTelemetryInterceptor(TelemetryClient telemetryClient)
    {
        _telemetryClient = telemetryClient;
    }
    
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        _stopwatch.Restart();
        return result;
    }
    
    public override int SavedChanges(SaveChangesCompletedEventData eventData, int result)
    {
        _stopwatch.Stop();
        
        if (_stopwatch.ElapsedMilliseconds > 1000)
        {
            _telemetryClient.TrackTrace(
                $"Slow SaveChanges: {_stopwatch.ElapsedMilliseconds}ms",
                SeverityLevel.Warning);
        }
        
        return result;
    }
}

10.7.3 Performance Counters and Metrics

// Using OpenTelemetry
// Install: dotnet add package OpenTelemetry.Instrumentation.SqlClient

builder.Services.AddOpenTelemetry()
    .WithTracing(tracing => tracing
        .AddSqlClientInstrumentation()
        .AddEntityFrameworkCoreInstrumentation()
        .AddConsoleExporter());

// Custom metrics
public class DatabaseMetricsMiddleware
{
    private readonly RequestDelegate _next;
    private readonly ILogger _logger;
    private int _queryCount;
    private long _totalQueryTime;
    
    public DatabaseMetricsMiddleware(RequestDelegate next, ILogger<DatabaseMetricsMiddleware> logger)
    {
        _next = next;
        _logger = logger;
    }
    
    public async Task InvokeAsync(HttpContext context, AppDbContext dbContext)
    {
        dbContext.Database.CommandExecuted += OnCommandExecuted;
        
        try
        {
            await _next(context);
        }
        finally
        {
            dbContext.Database.CommandExecuted -= OnCommandExecuted;
            
            if (_queryCount > 0)
            {
                _logger.LogInformation(
                    "Request {Path}: {QueryCount} queries, Avg time: {AvgTime:F2}ms",
                    context.Request.Path,
                    _queryCount,
                    _totalQueryTime / (double)_queryCount);
                
                // Reset counters
                _queryCount = 0;
                _totalQueryTime = 0;
            }
        }
    }
    
    private void OnCommandExecuted(object sender, CommandExecutedEventArgs e)
    {
        Interlocked.Increment(ref _queryCount);
        Interlocked.Add(ref _totalQueryTime, (long)e.Duration.TotalMilliseconds);
    }
}

---

10.8 Deployment Checklist

Pre-Deployment Checklist

Category	Item	Status
Connection & Security	Connection string in secure location (Key Vault/Secrets Manager)	✓
	Managed identity or Windows Authentication configured	✓
	Firewall rules configured for database access	✓
	Transparent Data Encryption (TDE) enabled	✓
Performance	Indexes created for all foreign keys and WHERE clauses	✓
	AsNoTracking used for read-only queries	✓
	Split queries configured for multiple includes	✓
	Query plan cache analyzed for hot paths	✓
Migrations	Migrations tested on staging environment	✓
	Rollback plan documented	✓
	Database backup created before migration	✓
	Idempotent migration scripts generated	✓
Monitoring	Health checks configured and tested	✓
	Application Insights (or equivalent) configured	✓
	Alert rules for slow queries	✓
	Logging levels configured (Warning+ in production)	✓
Resiliency	Retry logic configured for transient failures	✓
	Connection pooling configured	✓
	Command timeout set appropriately	✓
	Database failover/replication configured	✓

---

Chapter Summary

Concept	Key Takeaways
Clean Architecture	Separate concerns into Domain, Application, Infrastructure, and Presentation layers. Keep EF Core in Infrastructure only.
Repository Pattern	Use repositories to abstract data access. Useful for testing, but consider EF Core is already a repository pattern.
DbContext Lifetime	Use Scoped lifetime in web apps. Use pooling for high-throughput scenarios. Dispose properly.
Security	Always use parameterized queries. Encrypt sensitive data. Use connection string secrets. Implement row-level security.
Auditing	Use SaveChanges interceptors or shadow properties for automatic audit fields (CreatedBy, ModifiedBy, etc.).
Migrations	Test migrations on staging. Use idempotent scripts. Have rollback plan. Consider zero-downtime strategies.
Testing	Use InMemory for unit tests, SQLite for integration tests, and real databases with testcontainers for confidence.
Monitoring	Implement health checks, use Application Insights, track slow queries, and set up alerting.
Production Readiness	Follow deployment checklist. Configure retry logic. Monitor performance. Have backup and recovery plans.

---

Conclusion: Your EF Core Journey

Congratulations on completing this comprehensive EF Core journey! You've mastered:

• Fundamentals: Setting up EF Core, migrations, and basic operations
• Modeling: Configuring entities, relationships, and advanced mappings
• Querying: LINQ, performance optimization, and advanced query techniques
• Data Modification: Change tracking, disconnected scenarios, and concurrency
• Advanced Features: Value conversions, owned types, inheritance, JSON, spatial data
• Performance: Indexing, caching, bulk operations, and optimization strategies
• Production: Clean architecture, security, testing, monitoring, and deployment

You're now equipped to build robust, high-performance, production-ready applications with EF Core. Remember that EF Core continues to evolve - stay updated with new releases and always measure performance before optimizing.

Happy coding, and may your queries be fast and your migrations smooth!

---