Entity Framework Core Quick Reference Cheatsheet

Loading Strategies

Comparison Table

Eager Loading

// Include related data
var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

// Split query (avoid cartesian explosion)
var orders = await _context.Orders
    .Include(o => o.Items)
    .AsSplitQuery()  // Separate query per include
    .ToListAsync();

Explicit Loading

var order = await _context.Orders.FindAsync(id);

// Load collection
await _context.Entry(order)
    .Collection(o => o.Items)
    .LoadAsync();

// Load reference
await _context.Entry(order)
    .Reference(o => o.Customer)
    .LoadAsync();

// With filter
await _context.Entry(order)
    .Collection(o => o.Items)
    .Query()
    .Where(i => i.Price > 100)
    .LoadAsync();

Query Optimization

No Tracking

// Single query
var products = await _context.Products
    .AsNoTracking()
    .ToListAsync();

// Context-wide
_context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

// With identity resolution (avoids duplicates)
var orders = await _context.Orders
    .Include(o => o.Customer)
    .AsNoTrackingWithIdentityResolution()
    .ToListAsync();

Compiled Queries

private static readonly Func<AppDbContext, int, Task<Product?>> _getProductById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Products.FirstOrDefault(p => p.Id == id));

// Usage
var product = await _getProductById(_context, productId);

Projections

// Only select needed columns
var dtos = await _context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        CategoryName = p.Category.Name
    })
    .ToListAsync();

Global Query Filters

Setup

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Soft delete filter
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);

    // Multi-tenancy filter
    modelBuilder.Entity<Order>()
        .HasQueryFilter(o => o.TenantId == _tenantId);
}

// Bypass filter
var allProducts = await _context.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Concurrency

Optimistic Concurrency

// In entity
public byte[] RowVersion { get; set; }

// Configuration
modelBuilder.Entity<Product>()
    .Property(p => p.RowVersion)
    .IsRowVersion();

// Handling conflicts
try
{
    await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var dbValues = await entry.GetDatabaseValuesAsync();

    // Client wins
    entry.OriginalValues.SetValues(dbValues);
    await _context.SaveChangesAsync();

    // Database wins
    entry.Reload();
}

Advanced Modeling

Shadow Properties

// Configuration
modelBuilder.Entity<Product>()
    .Property<DateTime>("CreatedAt");

// Access
var createdAt = _context.Entry(product)
    .Property<DateTime>("CreatedAt").CurrentValue;

// In queries
var recent = await _context.Products
    .Where(p => EF.Property<DateTime>(p, "CreatedAt") > DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

Owned Types

// Value object
public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
}

// Configuration
modelBuilder.Entity<Customer>()
    .OwnsOne(c => c.Address);

// JSON column (.NET 7+)
modelBuilder.Entity<Customer>()
    .OwnsOne(c => c.Address, a => a.ToJson());

Inheritance (TPH/TPT/TPC)

// TPH (Table Per Hierarchy) - Default, single table
modelBuilder.Entity<Animal>()
    .HasDiscriminator<string>("AnimalType")
    .HasValue<Dog>("dog")
    .HasValue<Cat>("cat");

// TPT (Table Per Type) - Separate tables, JOINs
modelBuilder.Entity<Dog>().ToTable("Dogs");
modelBuilder.Entity<Cat>().ToTable("Cats");

// TPC (Table Per Concrete) - .NET 7+
modelBuilder.Entity<Animal>().UseTpcMappingStrategy();

Raw SQL

Safe Execution

// Parameterized query (safe!)
var products = await _context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .ToListAsync();

// With LINQ composition
var expensive = await _context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .ToListAsync();

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

Cascade Delete

modelBuilder.Entity<Order>()
    .HasMany(o => o.Items)
    .WithOne(i => i.Order)
    .OnDelete(DeleteBehavior.Cascade);

Soft Delete Pattern

// Entity
public interface ISoftDelete
{
    bool IsDeleted { get; set; }
    DateTime? DeletedAt { get; set; }
}

// Global filter
foreach (var entityType in modelBuilder.Model.GetEntityTypes()
    .Where(e => typeof(ISoftDelete).IsAssignableFrom(e.ClrType)))
{
    var parameter = Expression.Parameter(entityType.ClrType, "e");
    var filter = Expression.Lambda(
        Expression.Equal(
            Expression.Property(parameter, nameof(ISoftDelete.IsDeleted)),
            Expression.Constant(false)),
        parameter);

    modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);
}

// Soft delete method
public async Task SoftDeleteAsync<T>(T entity) where T : class, ISoftDelete
{
    entity.IsDeleted = true;
    entity.DeletedAt = DateTime.UtcNow;
    await _context.SaveChangesAsync();
}

Migrations

Commands

# Add migration
dotnet ef migrations add MigrationName

# Apply migrations
dotnet ef database update

# Generate SQL script
dotnet ef migrations script --idempotent -o migrate.sql

# Remove last migration
dotnet ef migrations remove

Data Migration

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("UPDATE Products SET Price = Price * 1.1");
}

Common Interview Answers

Q: Eager vs Lazy vs Explicit loading?

• Eager: Load with main query (Include)
• Lazy: Load on property access (N+1 problem!)
• Explicit: Load on demand with Entry().Load()

Q: What is AsSplitQuery?

• Splits single query with multiple includes into separate queries
• Avoids cartesian explosion with large result sets
• Trade-off: Multiple roundtrips vs single large query

Q: How to handle concurrency?

• Use RowVersion/Timestamp column
• Catch DbUpdateConcurrencyException
• Decide: Client wins, DB wins, or merge

Q: TPH vs TPT vs TPC?

• TPH: Single table, discriminator column (fastest queries)
• TPT: Separate tables, JOINs (normalized, slower)
• TPC: Concrete tables only (no base table, .NET 7+)

Q: How to optimize read-heavy workloads?

• AsNoTracking() for read-only queries
• Projections (Select only needed columns)
• Compiled queries for frequently called queries
• Split queries for complex includes

Performance Tips

// 1. Use projections
.Select(p => new { p.Id, p.Name })

// 2. No tracking for reads
.AsNoTracking()

// 3. Batch operations
_context.BulkInsert(entities);  // Use EFCore.BulkExtensions

// 4. Index frequently queried columns
modelBuilder.Entity<Product>()
    .HasIndex(p => p.CategoryId);

// 5. Avoid N+1 with Include
.Include(o => o.Items)

// 6. Use FindAsync for single entity by PK
var product = await _context.Products.FindAsync(id);