EF Core Quick Reference

DbContext Configuration

// Dependency Injection
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

// With Pooling (20-30% faster)
services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connectionString), poolSize: 128);

// DbContextFactory (for long-lived services)
services.AddDbContextFactory<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

Querying

// Basic query
var products = await context.Products.ToListAsync();

// With filtering
var active = await context.Products
    .Where(p => p.IsActive)
    .ToListAsync();

// With ordering
var sorted = await context.Products
    .OrderBy(p => p.Name)
    .ToListAsync();

// Single result
var product = await context.Products.FindAsync(id);
var product = await context.Products.FirstAsync(p => p.Id == id);
var product = await context.Products.SingleAsync(p => p.Id == id);

// AsNoTracking (read-only, 20-30% faster)
var products = await context.Products
    .AsNoTracking()
    .ToListAsync();

Loading Strategies

// Eager Loading
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .ThenInclude(p => p.Author)
    .ToListAsync();

// Filtered Include
var blogs = await context.Blogs
    .Include(b => b.Posts.Where(p => p.IsPublished))
    .ToListAsync();

// Split Query (avoid cartesian explosion)
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Authors)
    .AsSplitQuery()
    .ToListAsync();

// Projection (best performance)
var products = await context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        CategoryName = p.Category.Name
    })
    .ToListAsync();

// Explicit Loading
await context.Entry(blog)
    .Collection(b => b.Posts)
    .LoadAsync();

CRUD Operations

// Create
var product = new Product { Name = "Laptop", Price = 999 };
context.Products.Add(product);
await context.SaveChangesAsync();

// Read
var product = await context.Products.FindAsync(id);

// Update
product.Price = 899;
await context.SaveChangesAsync();

// Update (disconnected)
context.Products.Update(product);
await context.SaveChangesAsync();

// Delete
context.Products.Remove(product);
await context.SaveChangesAsync();

// Batch operations
context.Products.AddRange(products);
context.Products.UpdateRange(products);
context.Products.RemoveRange(products);
await context.SaveChangesAsync();

Relationships

// One-to-Many
modelBuilder.Entity<Post>()
    .HasOne(p => p.Blog)
    .WithMany(b => b.Posts)
    .HasForeignKey(p => p.BlogId);

// One-to-One
modelBuilder.Entity<User>()
    .HasOne(u => u.Profile)
    .WithOne(p => p.User)
    .HasForeignKey<UserProfile>(p => p.UserId);

// Many-to-Many (simple)
modelBuilder.Entity<Post>()
    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts);

// Many-to-Many (with join entity)
modelBuilder.Entity<StudentCourse>()
    .HasKey(sc => new { sc.StudentId, sc.CourseId });

Migrations

# Create migration
dotnet ef migrations add MigrationName

# Apply migrations
dotnet ef database update

# Rollback
dotnet ef database update PreviousMigration

# Generate script
dotnet ef migrations script --idempotent

# Remove last migration
dotnet ef migrations remove

Performance Optimization

// AsNoTracking for read-only
var products = await context.Products
    .AsNoTracking()
    .ToListAsync();

// Compiled Queries
private static readonly Func<AppDbContext, int, Task<Product>> _getProduct =
    EF.CompileAsyncQuery((AppDbContext context, int id) =>
        context.Products.FirstOrDefault(p => p.Id == id));

// Projection over full entity
var dtos = await context.Products
    .Select(p => new { p.Id, p.Name })
    .ToListAsync();

// Split queries
var result = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .AsSplitQuery()
    .ToListAsync();

Advanced Patterns

// Global Query Filter
modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted);

// Ignore filter
var all = await context.Products
    .IgnoreQueryFilters()
    .ToListAsync();

// Optimistic Concurrency
[Timestamp]
public byte[] RowVersion { get; set; }

// Handle conflict
try {
    await context.SaveChangesAsync();
} catch (DbUpdateConcurrencyException ex) {
    // Resolve conflict
}

// Raw SQL
var products = await context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .ToListAsync();

// Execute SQL
await context.Database
    .ExecuteSqlRawAsync("UPDATE Products SET Price = Price * 1.1");

Common Patterns

// Repository Pattern
public interface IRepository\<T\> where T : class
{
    Task\<T\> GetByIdAsync(int id);
    Task<IEnumerable\<T\>> GetAllAsync();
    Task AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(int id);
}

// Unit of Work
public interface IUnitOfWork
{
    IRepository<Product> Products { get; }
    IRepository<Order> Orders { get; }
    Task<int> CommitAsync();
}

Indexing

// Single column
modelBuilder.Entity<Product>()
    .HasIndex(p => p.Name);

// Composite index
modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.OrderDate });

// Unique index
modelBuilder.Entity<User>()
    .HasIndex(u => u.Email)
    .IsUnique();

// Filtered index
modelBuilder.Entity<Product>()
    .HasIndex(p => p.Price)
    .HasFilter("[IsDeleted] = 0");

Performance Checklist

• Use AsNoTracking for read-only queries
• Enable DbContext pooling
• Avoid N+1 queries (use Include or projection)
• Use projection instead of full entities
• Implement proper indexes
• Use compiled queries for hot paths
• Use AsSplitQuery for multiple includes
• Batch operations instead of individual SaveChanges
• Use raw SQL for complex queries
• Enable connection resiliency

Anti-Patterns to Avoid

// ❌ N+1 Query Problem
foreach (var order in orders) {
    var customer = await context.Customers.FindAsync(order.CustomerId);
}

// ❌ Tracking for read-only
var products = await context.Products.ToListAsync(); // Slow!

// ❌ Loading everything
var allOrders = await context.Orders.ToListAsync(); // Millions of rows!

// ❌ Cartesian explosion
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Authors)
    .ToListAsync(); // Huge result set!

// ❌ SQL Injection
var sql = $"SELECT * FROM Products WHERE Name = '{userInput}'";

Connection Strings

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyDb;Trusted_Connection=True;",
    "PostgreSQL": "Host=localhost;Database=mydb;Username=postgres;Password=pass",
    "MySQL": "Server=localhost;Database=mydb;User=root;Password=pass;",
    "SQLite": "Data Source=app.db"
  }
}

Testing

// In-Memory Database
services.AddDbContext<AppDbContext>(options =>
    options.UseInMemoryDatabase("TestDb"));

// SQLite (better)
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=:memory:"));

// TestContainers (production-like)
await using var container = new MsSqlBuilder()
    .WithPassword("Pass123!")
    .Build();
await container.StartAsync();