Entity Framework Core.
Cheat sheet.

Entity Framework Core is Microsoft's modern object-relational mapper (ORM) for .NET applications. It enables developers to work with databases using .NET objects, eliminating the need for most data-access code. This cheatsheet covers core concepts, DbContext configuration, entity relationships, CRUD operations, LINQ querying, migrations, performance optimization, and advanced features like transactions and concurrency control.

What is EF Core?

• ORM (Object-Relational Mapper) for .NET
• Maps C# objects to database tables
• Supports multiple database providers (SQL Server, PostgreSQL, MySQL, SQLite)
• Code-First and Database-First approaches

Key Components

// Entity (POCO class)
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

// DbContext
public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
}

Basic Setup

// Program.cs or Startup.cs
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

// Connection string in appsettings.json
"ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=MyDb;Trusted_Connection=true;"
}

DbContext Lifetime

• Scoped (default): One instance per request
• Transient: New instance each time
• Singleton: Single instance (avoid for web apps)

Override Methods

public class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure if not done in DI
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure entities, relationships, seed data
    }
}

Data Annotations

public class User
{
    [Key]
    public int Id { get; set; }
    
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }
    
    [Column("EmailAddress")]
    public string Email { get; set; }
    
    [NotMapped]
    public string FullName { get; set; }
}

Fluent API

modelBuilder.Entity<User>(entity =>
{
    entity.HasKey(e => e.Id);
    entity.Property(e => e.Name)
          .IsRequired()
          .HasMaxLength(100);
    entity.HasIndex(e => e.Email)
          .IsUnique();
    entity.ToTable("Users", "dbo");
});

Create

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

// Multiple entities
context.Products.AddRange(product1, product2);

Read

// Get all
var products = await context.Products.ToListAsync();

// Find by primary key
var product = await context.Products.FindAsync(id);

// First or default
var product = await context.Products
    .FirstOrDefaultAsync(p => p.Name == "Laptop");

Update

// Retrieve and modify
var product = await context.Products.FindAsync(id);
product.Price = 1099;
await context.SaveChangesAsync();

// Update without loading
context.Products.Update(product);
await context.SaveChangesAsync();

Delete

// Delete by entity
context.Products.Remove(product);

// Delete by id
var product = new Product { Id = id };
context.Entry(product).State = EntityState.Deleted;
await context.SaveChangesAsync();

LINQ Methods

// Where
var expensive = await context.Products
    .Where(p => p.Price > 500)
    .ToListAsync();

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

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

// GroupBy
var grouped = await context.Products
    .GroupBy(p => p.Category)
    .Select(g => new { Category = g.Key, Count = g.Count() })
    .ToListAsync();

Include (Eager Loading)

// Single navigation property
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

// Multiple levels
var orders = await context.Orders
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .ToListAsync();

Raw SQL

// Raw SQL query
var products = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 500)
    .ToListAsync();

// Stored procedure
var products = await context.Products
    .FromSqlRaw("EXEC GetExpensiveProducts @MinPrice", 
        new SqlParameter("@MinPrice", 500))
    .ToListAsync();

One-to-Many

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Order> Orders { get; set; } // Navigation property
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; } // Foreign key
    public Customer Customer { get; set; } // Navigation property
}

// Fluent API
modelBuilder.Entity<Order>()
    .HasOne(o => o.Customer)
    .WithMany(c => c.Orders)
    .HasForeignKey(o => o.CustomerId);

One-to-One

public class User
{
    public int Id { get; set; }
    public UserProfile Profile { get; set; }
}

public class UserProfile
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
}

// Fluent API
modelBuilder.Entity<User>()
    .HasOne(u => u.Profile)
    .WithOne(p => p.User)
    .HasForeignKey<UserProfile>(p => p.UserId);

Many-to-Many

// EF Core 5.0+
public class Student
{
    public int Id { get; set; }
    public List<Course> Courses { get; set; }
}

public class Course
{
    public int Id { get; set; }
    public List<Student> Students { get; set; }
}

// With join entity
public class StudentCourse
{
    public int StudentId { get; set; }
    public Student Student { get; set; }
    public int CourseId { get; set; }
    public Course Course { get; set; }
    public DateTime EnrollmentDate { get; set; }
}

Basic Commands

# Add migration
dotnet ef migrations add InitialCreate

# Update database
dotnet ef database update

# Remove last migration
dotnet ef migrations remove

# Generate SQL script
dotnet ef migrations script

# Revert to specific migration
dotnet ef database update PreviousMigration

Code Example

// Apply migrations programmatically
using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    context.Database.Migrate();
}

1. Use AsNoTracking

// For read-only queries
var products = await context.Products
    .AsNoTracking()
    .Where(p => p.Price > 100)
    .ToListAsync();

2. Projection

// Select only needed columns
var productNames = await context.Products
    .Select(p => new { p.Id, p.Name })
    .ToListAsync();

3. Pagination

var pagedProducts = await context.Products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

4. Split Queries

// Avoid cartesian explosion
var orders = await context.Orders
    .AsSplitQuery()
    .Include(o => o.OrderItems)
    .Include(o => o.Customer)
    .ToListAsync();

5. Compiled Queries

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

// Usage
var product = await GetProductById(context, productId);

6. Batch Operations

// Use AddRange instead of multiple Add
context.Products.AddRange(productList);

// ExecuteUpdate (EF Core 7+)
await context.Products
    .Where(p => p.Category == "Electronics")
    .ExecuteUpdateAsync(p => p.SetProperty(x => x.Price, x => x.Price * 1.1));

Global Query Filters

// Soft delete implementation
modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted);

// Ignore filter when needed
var allProducts = await context.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Value Converters

// Enum to string
modelBuilder.Entity<Order>()
    .Property(o => o.Status)
    .HasConversion<string>();

// Custom converter
var converter = new ValueConverter<List<string>, string>(
    v => string.Join(',', v),
    v => v.Split(',', StringSplitOptions.RemoveEmptyEntries).ToList());

Shadow Properties

// Define shadow property
modelBuilder.Entity<Product>()
    .Property<DateTime>("LastModified");

// Set value
context.Entry(product).Property("LastModified").CurrentValue = DateTime.Now;

Transactions

using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // Multiple operations
    await context.SaveChangesAsync();
    await context.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Concurrency Control

public class Product
{
    public int Id { get; set; }
    
    [ConcurrencyCheck]
    public string Name { get; set; }
    
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

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

Repository Pattern

public interface IRepository<T> where T : class
{
    Task<T> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task AddAsync(T entity);
    void Update(T entity);
    void Delete(T entity);
}

public class Repository<T> : IRepository<T> where T : class
{
    private readonly AppDbContext _context;
    private readonly DbSet<T> _dbSet;

    public Repository(AppDbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public async Task<T> GetByIdAsync(int id) => await _dbSet.FindAsync(id);
    public async Task<IEnumerable<T>> GetAllAsync() => await _dbSet.ToListAsync();
    public async Task AddAsync(T entity) => await _dbSet.AddAsync(entity);
    public void Update(T entity) => _dbSet.Update(entity);
    public void Delete(T entity) => _dbSet.Remove(entity);
}

Unit of Work Pattern

public interface IUnitOfWork : IDisposable
{
    IRepository<Product> Products { get; }
    IRepository<Order> Orders { get; }
    Task<int> CompleteAsync();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
    
    public IRepository<Product> Products { get; }
    public IRepository<Order> Orders { get; }

    public UnitOfWork(AppDbContext context)
    {
        _context = context;
        Products = new Repository<Product>(_context);
        Orders = new Repository<Order>(_context);
    }

    public async Task<int> CompleteAsync() => await _context.SaveChangesAsync();
    public void Dispose() => _context.Dispose();
}

Key Concepts & Comparisons

Entity State Management

Query Execution Comparison

Loading Strategies

N+1 Query Solutions

// Problem: N+1 queries
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // N queries
}

// Solution 1: Eager loading
var orders = context.Orders.Include(o => o.Customer).ToList();

// Solution 2: Split query (multiple includes)
var orders = context.Orders
    .AsSplitQuery()
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .ToList();

// Solution 3: Projection
var orderData = context.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToList();

Development Best Practices

✅ Async Operations: Always use async/await for database operations
✅ Resource Management: Use scoped DbContext lifetime in DI container
✅ Data Transfer: Use DTOs for API responses, not direct entity exposure
✅ Testing: Test with realistic data volumes and enable SQL logging
✅ Architecture: Keep DbContext focused, implement proper layering

Performance Optimization Strategies

Soft Delete Implementation

// 1. Add property to entity
public bool IsDeleted { get; set; }

// 2. Configure global filter
modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted);

// 3. Override SaveChanges
protected override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    var deletedEntries = ChangeTracker.Entries<ISoftDelete>()
        .Where(e => e.State == EntityState.Deleted);
    
    foreach (var entry in deletedEntries)
    {
        entry.State = EntityState.Modified;
        entry.Entity.IsDeleted = true;
    }
    
    return base.SaveChangesAsync(cancellationToken);
}

Entity States

Database Providers Setup

// SQL Server
options.UseSqlServer(connectionString);

// PostgreSQL  
options.UseNpgsql(connectionString);

// MySQL
options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));

// SQLite
options.UseSqlite(connectionString);

// In-Memory (testing)
options.UseInMemoryDatabase("TestDb");

Common Migration Commands

# Create migration
dotnet ef migrations add MigrationName

# Apply migrations
dotnet ef database update

# Revert to specific migration
dotnet ef database update PreviousMigrationName

# Remove last migration (if not applied)
dotnet ef migrations remove

# Generate SQL script
dotnet ef migrations script

# List migrations
dotnet ef migrations list

Configuration Approaches

Transaction Patterns

// Simple transaction
using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // Multiple operations
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    
    context.OrderItems.AddRange(orderItems);
    await context.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

// Transaction scope (alternative)
using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
try
{
    await context.SaveChangesAsync();
    await anotherContext.SaveChangesAsync();
    scope.Complete();
}
catch
{
    // Automatic rollback
    throw;
}

Bulk Operations (EF Core 7+)

// Bulk update
await context.Products
    .Where(p => p.Category == "Electronics")
    .ExecuteUpdateAsync(p => p.SetProperty(x => x.Price, x => x.Price * 1.1));

// Bulk delete
await context.Products
    .Where(p => p.IsDiscontinued)
    .ExecuteDeleteAsync();

// Traditional bulk insert (pre EF Core 7)
context.Products.AddRange(productList);
await context.SaveChangesAsync();