Mastering Entity Framework Core: From Basics to Advanced Patterns

Entity Framework Core is the go-to object-relational mapper (ORM) for .NET applications. It lets you work with databases using C# objects instead of writing raw SQL queries. Whether you are building a small application or a large enterprise system, understanding EF Core deeply will make you a more effective developer.

This guide covers everything from setting up your first DbContext to implementing advanced patterns used in production applications.

Setting Up Entity Framework Core

First, install the required NuGet packages for your database provider. For SQL Server:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

For SQLite:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

For PostgreSQL:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Creating Your First DbContext

The DbContext is the main class that coordinates Entity Framework functionality for your data model:

using Microsoft.EntityFrameworkCore;

namespace MyApp.Data;

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Configure Customer entity
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).HasMaxLength(100).IsRequired();
            entity.Property(e => e.Email).HasMaxLength(255).IsRequired();
            entity.HasIndex(e => e.Email).IsUnique();
        });

        // Configure Order entity
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.OrderDate).IsRequired();
            entity.Property(e => e.TotalAmount).HasPrecision(18, 2);
            
            entity.HasOne(e => e.Customer)
                .WithMany(c => c.Orders)
                .HasForeignKey(e => e.CustomerId)
                .OnDelete(DeleteBehavior.Cascade);
        });

        // Configure Product entity
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).HasMaxLength(200).IsRequired();
            entity.Property(e => e.Price).HasPrecision(18, 2);
            entity.Property(e => e.Sku).HasMaxLength(50);
            entity.HasIndex(e => e.Sku).IsUnique();
        });

        // Configure OrderItem entity
        modelBuilder.Entity<OrderItem>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Quantity).IsRequired();
            entity.Property(e => e.UnitPrice).HasPrecision(18, 2);

            entity.HasOne(e => e.Order)
                .WithMany(o => o.Items)
                .HasForeignKey(e => e.OrderId)
                .OnDelete(DeleteBehavior.Cascade);

            entity.HasOne(e => e.Product)
                .WithMany()
                .HasForeignKey(e => e.ProductId)
                .OnDelete(DeleteBehavior.Restrict);
        });
    }
}

Defining Entity Classes

Here are the entity classes that match our DbContext configuration:

namespace MyApp.Data.Entities;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string? Phone { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public bool IsActive { get; set; } = true;

    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; } = DateTime.UtcNow;
    public decimal TotalAmount { get; set; }
    public OrderStatus Status { get; set; } = OrderStatus.Pending;

    public Customer Customer { get; set; } = null!;
    public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Description { get; set; }
    public decimal Price { get; set; }
    public string? Sku { get; set; }
    public int StockQuantity { get; set; }
    public bool IsAvailable { get; set; } = true;
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }

    public Order Order { get; set; } = null!;
    public Product Product { get; set; } = null!;
}

public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

Registering DbContext in ASP.NET Core

In your Program.cs file, register the DbContext with the dependency injection container:

var builder = WebApplication.CreateBuilder(args);

// SQL Server
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// Or SQLite
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));

// Or PostgreSQL
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

Working with Migrations

Migrations let you evolve your database schema over time. Create your first migration:

dotnet ef migrations add InitialCreate
dotnet ef database update

When you change your entities, create a new migration:

dotnet ef migrations add AddCustomerPhone
dotnet ef database update

To revert a migration:

dotnet ef database update PreviousMigrationName
dotnet ef migrations remove

Basic CRUD Operations

Let us create a service that handles common database operations:

using Microsoft.EntityFrameworkCore;

namespace MyApp.Services;

public class CustomerService
{
    private readonly ApplicationDbContext _context;

    public CustomerService(ApplicationDbContext context)
    {
        _context = context;
    }

    // Create
    public async Task<Customer> CreateCustomerAsync(string name, string email, string? phone = null)
    {
        var customer = new Customer
        {
            Name = name,
            Email = email,
            Phone = phone
        };

        _context.Customers.Add(customer);
        await _context.SaveChangesAsync();

        return customer;
    }

    // Read - Single
    public async Task<Customer?> GetCustomerByIdAsync(int id)
    {
        return await _context.Customers
            .Include(c => c.Orders)
            .FirstOrDefaultAsync(c => c.Id == id);
    }

    // Read - List with filtering and pagination
    public async Task<List<Customer>> GetCustomersAsync(
        string? searchTerm = null,
        bool? isActive = null,
        int page = 1,
        int pageSize = 10)
    {
        var query = _context.Customers.AsQueryable();

        if (!string.IsNullOrEmpty(searchTerm))
        {
            query = query.Where(c => 
                c.Name.Contains(searchTerm) || 
                c.Email.Contains(searchTerm));
        }

        if (isActive.HasValue)
        {
            query = query.Where(c => c.IsActive == isActive.Value);
        }

        return await query
            .OrderBy(c => c.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();
    }

    // Update
    public async Task<Customer?> UpdateCustomerAsync(int id, string name, string email, string? phone)
    {
        var customer = await _context.Customers.FindAsync(id);

        if (customer is null)
        {
            return null;
        }

        customer.Name = name;
        customer.Email = email;
        customer.Phone = phone;

        await _context.SaveChangesAsync();

        return customer;
    }

    // Delete
    public async Task<bool> DeleteCustomerAsync(int id)
    {
        var customer = await _context.Customers.FindAsync(id);

        if (customer is null)
        {
            return false;
        }

        _context.Customers.Remove(customer);
        await _context.SaveChangesAsync();

        return true;
    }
}

Advanced Querying Techniques

EF Core provides powerful querying capabilities. Here are some advanced patterns:

public class OrderService
{
    private readonly ApplicationDbContext _context;

    public OrderService(ApplicationDbContext context)
    {
        _context = context;
    }

    // Eager loading with Include and ThenInclude
    public async Task<Order?> GetOrderWithDetailsAsync(int orderId)
    {
        return await _context.Orders
            .Include(o => o.Customer)
            .Include(o => o.Items)
                .ThenInclude(i => i.Product)
            .FirstOrDefaultAsync(o => o.Id == orderId);
    }

    // Projection with Select
    public async Task<List<OrderSummaryDto>> GetOrderSummariesAsync(int customerId)
    {
        return await _context.Orders
            .Where(o => o.CustomerId == customerId)
            .Select(o => new OrderSummaryDto
            {
                OrderId = o.Id,
                OrderDate = o.OrderDate,
                TotalAmount = o.TotalAmount,
                Status = o.Status.ToString(),
                ItemCount = o.Items.Count
            })
            .ToListAsync();
    }

    // Aggregation queries
    public async Task<CustomerStatsDto> GetCustomerStatsAsync(int customerId)
    {
        return await _context.Orders
            .Where(o => o.CustomerId == customerId)
            .GroupBy(o => o.CustomerId)
            .Select(g => new CustomerStatsDto
            {
                CustomerId = g.Key,
                TotalOrders = g.Count(),
                TotalSpent = g.Sum(o => o.TotalAmount),
                AverageOrderValue = g.Average(o => o.TotalAmount),
                FirstOrderDate = g.Min(o => o.OrderDate),
                LastOrderDate = g.Max(o => o.OrderDate)
            })
            .FirstOrDefaultAsync() ?? new CustomerStatsDto { CustomerId = customerId };
    }

    // Complex filtering with expression trees
    public async Task<List<Order>> SearchOrdersAsync(OrderSearchCriteria criteria)
    {
        var query = _context.Orders
            .Include(o => o.Customer)
            .Include(o => o.Items)
            .AsQueryable();

        if (criteria.CustomerId.HasValue)
        {
            query = query.Where(o => o.CustomerId == criteria.CustomerId.Value);
        }

        if (criteria.MinAmount.HasValue)
        {
            query = query.Where(o => o.TotalAmount >= criteria.MinAmount.Value);
        }

        if (criteria.MaxAmount.HasValue)
        {
            query = query.Where(o => o.TotalAmount <= criteria.MaxAmount.Value);
        }

        if (criteria.StartDate.HasValue)
        {
            query = query.Where(o => o.OrderDate >= criteria.StartDate.Value);
        }

        if (criteria.EndDate.HasValue)
        {
            query = query.Where(o => o.OrderDate <= criteria.EndDate.Value);
        }

        if (criteria.Statuses?.Any() == true)
        {
            query = query.Where(o => criteria.Statuses.Contains(o.Status));
        }

        return await query
            .OrderByDescending(o => o.OrderDate)
            .Take(criteria.MaxResults)
            .ToListAsync();
    }

    // Raw SQL when needed
    public async Task<List<TopProductDto>> GetTopSellingProductsAsync(int count = 10)
    {
        return await _context.Database
            .SqlQuery<TopProductDto>($"""
                SELECT 
                    p.Id as ProductId,
                    p.Name as ProductName,
                    SUM(oi.Quantity) as TotalQuantitySold,
                    SUM(oi.Quantity * oi.UnitPrice) as TotalRevenue
                FROM Products p
                INNER JOIN OrderItems oi ON p.Id = oi.ProductId
                INNER JOIN Orders o ON oi.OrderId = o.Id
                WHERE o.Status != {(int)OrderStatus.Cancelled}
                GROUP BY p.Id, p.Name
                ORDER BY TotalQuantitySold DESC
                LIMIT {count}
                """)
            .ToListAsync();
    }
}

public record OrderSummaryDto
{
    public int OrderId { get; init; }
    public DateTime OrderDate { get; init; }
    public decimal TotalAmount { get; init; }
    public string Status { get; init; } = string.Empty;
    public int ItemCount { get; init; }
}

public record CustomerStatsDto
{
    public int CustomerId { get; init; }
    public int TotalOrders { get; init; }
    public decimal TotalSpent { get; init; }
    public decimal AverageOrderValue { get; init; }
    public DateTime? FirstOrderDate { get; init; }
    public DateTime? LastOrderDate { get; init; }
}

public record OrderSearchCriteria
{
    public int? CustomerId { get; init; }
    public decimal? MinAmount { get; init; }
    public decimal? MaxAmount { get; init; }
    public DateTime? StartDate { get; init; }
    public DateTime? EndDate { get; init; }
    public List<OrderStatus>? Statuses { get; init; }
    public int MaxResults { get; init; } = 100;
}

public record TopProductDto
{
    public int ProductId { get; init; }
    public string ProductName { get; init; } = string.Empty;
    public int TotalQuantitySold { get; init; }
    public decimal TotalRevenue { get; init; }
}

Implementing the Repository Pattern

The repository pattern abstracts the data access layer and makes your code more testable:

namespace MyApp.Data.Repositories;

public interface IRepository<T> where T : class
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate);
    Task AddAsync(T entity);
    Task AddRangeAsync(IEnumerable<T> entities);
    void Update(T entity);
    void Remove(T entity);
    void RemoveRange(IEnumerable<T> entities);
}

public class Repository<T> : IRepository<T> where T : class
{
    protected readonly ApplicationDbContext _context;
    protected readonly DbSet<T> _dbSet;

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

    public virtual async Task<T?> GetByIdAsync(int id)
    {
        return await _dbSet.FindAsync(id);
    }

    public virtual async Task<IEnumerable<T>> GetAllAsync()
    {
        return await _dbSet.ToListAsync();
    }

    public virtual async Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate)
    {
        return await _dbSet.Where(predicate).ToListAsync();
    }

    public virtual async Task AddAsync(T entity)
    {
        await _dbSet.AddAsync(entity);
    }

    public virtual async Task AddRangeAsync(IEnumerable<T> entities)
    {
        await _dbSet.AddRangeAsync(entities);
    }

    public virtual void Update(T entity)
    {
        _dbSet.Update(entity);
    }

    public virtual void Remove(T entity)
    {
        _dbSet.Remove(entity);
    }

    public virtual void RemoveRange(IEnumerable<T> entities)
    {
        _dbSet.RemoveRange(entities);
    }
}

Unit of Work Pattern

The Unit of Work pattern coordinates multiple repositories and manages transactions:

namespace MyApp.Data;

public interface IUnitOfWork : IDisposable
{
    IRepository<Customer> Customers { get; }
    IRepository<Order> Orders { get; }
    IRepository<Product> Products { get; }
    IRepository<OrderItem> OrderItems { get; }
    Task<int> SaveChangesAsync();
    Task BeginTransactionAsync();
    Task CommitTransactionAsync();
    Task RollbackTransactionAsync();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly ApplicationDbContext _context;
    private IDbContextTransaction? _transaction;

    private IRepository<Customer>? _customers;
    private IRepository<Order>? _orders;
    private IRepository<Product>? _products;
    private IRepository<OrderItem>? _orderItems;

    public UnitOfWork(ApplicationDbContext context)
    {
        _context = context;
    }

    public IRepository<Customer> Customers =>
        _customers ??= new Repository<Customer>(_context);

    public IRepository<Order> Orders =>
        _orders ??= new Repository<Order>(_context);

    public IRepository<Product> Products =>
        _products ??= new Repository<Product>(_context);

    public IRepository<OrderItem> OrderItems =>
        _orderItems ??= new Repository<OrderItem>(_context);

    public async Task<int> SaveChangesAsync()
    {
        return await _context.SaveChangesAsync();
    }

    public async Task BeginTransactionAsync()
    {
        _transaction = await _context.Database.BeginTransactionAsync();
    }

    public async Task CommitTransactionAsync()
    {
        if (_transaction is not null)
        {
            await _transaction.CommitAsync();
            await _transaction.DisposeAsync();
            _transaction = null;
        }
    }

    public async Task RollbackTransactionAsync()
    {
        if (_transaction is not null)
        {
            await _transaction.RollbackAsync();
            await _transaction.DisposeAsync();
            _transaction = null;
        }
    }

    public void Dispose()
    {
        _transaction?.Dispose();
        _context.Dispose();
    }
}

Query Performance Optimization

Optimizing EF Core queries is critical for application performance:

public class OptimizedQueryExamples
{
    private readonly ApplicationDbContext _context;

    public OptimizedQueryExamples(ApplicationDbContext context)
    {
        _context = context;
    }

    // Use AsNoTracking for read-only queries
    public async Task<List<Customer>> GetCustomersReadOnlyAsync()
    {
        return await _context.Customers
            .AsNoTracking()
            .ToListAsync();
    }

    // Use AsSplitQuery for complex includes to avoid cartesian explosion
    public async Task<List<Order>> GetOrdersWithSplitQueryAsync()
    {
        return await _context.Orders
            .Include(o => o.Customer)
            .Include(o => o.Items)
                .ThenInclude(i => i.Product)
            .AsSplitQuery()
            .ToListAsync();
    }

    // Project only needed fields
    public async Task<List<CustomerListItemDto>> GetCustomerListAsync()
    {
        return await _context.Customers
            .AsNoTracking()
            .Select(c => new CustomerListItemDto
            {
                Id = c.Id,
                Name = c.Name,
                Email = c.Email,
                OrderCount = c.Orders.Count
            })
            .ToListAsync();
    }

    // Batch operations for better performance
    public async Task UpdateProductPricesAsync(decimal percentageIncrease)
    {
        await _context.Products
            .ExecuteUpdateAsync(setters => setters
                .SetProperty(p => p.Price, p => p.Price * (1 + percentageIncrease / 100)));
    }

    // Bulk delete
    public async Task DeleteInactiveCustomersAsync()
    {
        await _context.Customers
            .Where(c => !c.IsActive && c.Orders.Count == 0)
            .ExecuteDeleteAsync();
    }
}

public record CustomerListItemDto
{
    public int Id { get; init; }
    public string Name { get; init; } = string.Empty;
    public string Email { get; init; } = string.Empty;
    public int OrderCount { get; init; }
}

Handling Concurrency

EF Core provides built-in support for optimistic concurrency:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = Array.Empty<byte>();
}

public class ProductService
{
    private readonly ApplicationDbContext _context;

    public ProductService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<bool> UpdateProductAsync(int id, decimal newPrice)
    {
        var product = await _context.Products.FindAsync(id);

        if (product is null)
        {
            return false;
        }

        product.Price = newPrice;

        try
        {
            await _context.SaveChangesAsync();
            return true;
        }
        catch (DbUpdateConcurrencyException)
        {
            // Handle the concurrency conflict
            return false;
        }
    }
}

Conclusion

Entity Framework Core is a powerful tool that can handle everything from simple CRUD operations to complex enterprise scenarios. The key to using it effectively is understanding when to use each feature and how to optimize your queries for performance.

Remember these principles:

• Use AsNoTracking for read-only queries
• Project only the fields you need with Select
• Use AsSplitQuery for complex includes
• Implement the repository and unit of work patterns for better testability
• Handle concurrency appropriately for your use case
• Use ExecuteUpdate and ExecuteDelete for bulk operations

With these patterns and techniques, you can build efficient, maintainable data access layers in your ASP.NET Core applications.