Entity Framework Core Guide - Performance & Best Practices

Entity Framework Core (EF Core) is an open-source, cross-platform object-relational mapper (ORM) for .NET from Microsoft, translating LINQ queries written in C# into SQL and mapping the results back to strongly typed entity objects. It supports SQL Server, PostgreSQL, MySQL, SQLite, and other providers, manages schema changes through code-first migrations, and tracks entity state so a single SaveChanges call persists inserts, updates, and deletes in one unit of work. Used well, EF Core removes most boilerplate data access code while still allowing raw SQL and fine-grained control where performance demands it. The most common performance mistakes, N+1 queries, unnecessary change tracking, and loading more columns than needed, are all avoidable with eager loading, AsNoTracking, and projections. This guide covers EF Core from setup through DbContext configuration, relationships, migrations, querying, performance optimization, transactions, and the repository and unit of work patterns.

Setting Up

bash

# Install packages
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools

DbContext Configuration

csharp

// Infrastructure/Data/AppDbContext.cs
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<User> Users => Set<User>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply all configurations from assembly
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);

        // Global query filter (soft delete)
        modelBuilder.Entity<User>().HasQueryFilter(u => !u.IsDeleted);

        base.OnModelCreating(modelBuilder);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Enable sensitive data logging in development
        optionsBuilder.EnableSensitiveDataLogging();
        optionsBuilder.EnableDetailedErrors();

        base.OnConfiguring(optionsBuilder);
    }

    // Override SaveChanges for audit fields
    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        foreach (var entry in ChangeTracker.Entries<BaseEntity>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.Entity.CreatedAt = DateTime.UtcNow;
                    break;
                case EntityState.Modified:
                    entry.Entity.UpdatedAt = DateTime.UtcNow;
                    break;
            }
        }

        return base.SaveChangesAsync(cancellationToken);
    }
}

Entity Configuration

Fluent API Configuration

csharp

// Domain/Entities/User.cs
public class User : BaseEntity
{
    public Guid Id { get; set; }
    public string Email { get; set; } = string.Empty;
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string PasswordHash { get; set; } = string.Empty;
    public bool IsDeleted { get; set; }

    // Navigation properties
    public ICollection<Order> Orders { get; set; } = new List<Order>();
    public UserProfile? Profile { get; set; }
}

// Infrastructure/Data/Configurations/UserConfiguration.cs
public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.ToTable("Users");

        builder.HasKey(u => u.Id);

        builder.Property(u => u.Email)
            .IsRequired()
            .HasMaxLength(255);

        builder.Property(u => u.FirstName)
            .IsRequired()
            .HasMaxLength(100);

        builder.Property(u => u.LastName)
            .IsRequired()
            .HasMaxLength(100);

        builder.Property(u => u.PasswordHash)
            .IsRequired()
            .HasMaxLength(500);

        // Indexes
        builder.HasIndex(u => u.Email)
            .IsUnique()
            .HasDatabaseName("IX_Users_Email");

        // One-to-one relationship
        builder.HasOne(u => u.Profile)
            .WithOne(p => p.User)
            .HasForeignKey<UserProfile>(p => p.UserId)
            .OnDelete(DeleteBehavior.Cascade);

        // One-to-many relationship
        builder.HasMany(u => u.Orders)
            .WithOne(o => o.User)
            .HasForeignKey(o => o.UserId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

Complex Relationships

csharp

// Many-to-Many relationship
public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public ICollection<Category> Categories { get; set; } = new List<Category>();
    public ICollection<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Product> Products { get; set; } = new List<Product>();
}

// Configuration
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.ToTable("Products");

        builder.Property(p => p.Price)
            .HasPrecision(18, 2);

        // Many-to-many with explicit join table
        builder.HasMany(p => p.Categories)
            .WithMany(c => c.Products)
            .UsingEntity<Dictionary<string, object>>(
                "ProductCategories",
                j => j.HasOne<Category>().WithMany().HasForeignKey("CategoryId"),
                j => j.HasOne<Product>().WithMany().HasForeignKey("ProductId"),
                j =>
                {
                    j.HasKey("ProductId", "CategoryId");
                    j.ToTable("ProductCategories");
                });
    }
}

How Do Migrations Work?

bash

# Create a migration
dotnet ef migrations add InitialCreate

# Apply migrations
dotnet ef database update

# Generate SQL script
dotnet ef migrations script -o ./migrations.sql

# Generate idempotent script (safe to run multiple times)
dotnet ef migrations script --idempotent -o ./migrations.sql

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

# Revert to specific migration
dotnet ef database update PreviousMigrationName

Custom Migration Operations

csharp

// Migrations/20240101_AddFullTextIndex.cs
public partial class AddFullTextIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Standard operation
        migrationBuilder.AddColumn<string>(
            name: "SearchVector",
            table: "Products",
            nullable: true);

        // Raw SQL for complex operations
        migrationBuilder.Sql(@"
            CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;
            CREATE FULLTEXT INDEX ON Products(Name, Description)
            KEY INDEX PK_Products ON ProductCatalog;
        ");

        // Seed data
        migrationBuilder.InsertData(
            table: "Categories",
            columns: new[] { "Id", "Name" },
            values: new object[] { 1, "Electronics" });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP FULLTEXT INDEX ON Products");
        migrationBuilder.DropColumn(name: "SearchVector", table: "Products");
    }
}

How Do You Query Data?

Basic Queries

csharp

// Find by ID
var user = await _context.Users.FindAsync(id);

// First or default
var user = await _context.Users
    .FirstOrDefaultAsync(u => u.Email == email);

// Where clause
var activeUsers = await _context.Users
    .Where(u => !u.IsDeleted && u.CreatedAt > DateTime.UtcNow.AddDays(-30))
    .ToListAsync();

// Select projection
var userDtos = await _context.Users
    .Select(u => new UserDto
    {
        Id = u.Id,
        FullName = u.FirstName + " " + u.LastName,
        Email = u.Email
    })
    .ToListAsync();

// Ordering
var users = await _context.Users
    .OrderByDescending(u => u.CreatedAt)
    .ThenBy(u => u.LastName)
    .ToListAsync();

// Pagination
var pagedUsers = await _context.Users
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Eager Loading (Include)

csharp

// Include related data
var userWithOrders = await _context.Users
    .Include(u => u.Orders)
    .FirstOrDefaultAsync(u => u.Id == id);

// Nested includes
var userWithOrderDetails = await _context.Users
    .Include(u => u.Orders)
        .ThenInclude(o => o.OrderItems)
            .ThenInclude(oi => oi.Product)
    .Include(u => u.Profile)
    .FirstOrDefaultAsync(u => u.Id == id);

// Filtered include (.NET 5+)
var userWithRecentOrders = await _context.Users
    .Include(u => u.Orders.Where(o => o.CreatedAt > DateTime.UtcNow.AddMonths(-1)))
    .FirstOrDefaultAsync(u => u.Id == id);

// AsSplitQuery for complex queries (avoids cartesian explosion)
var data = await _context.Users
    .Include(u => u.Orders)
    .Include(u => u.Profile)
    .AsSplitQuery()
    .ToListAsync();

Explicit Loading

csharp

// Load related data on demand
var user = await _context.Users.FindAsync(id);

// Load collection
await _context.Entry(user)
    .Collection(u => u.Orders)
    .LoadAsync();

// Load reference
await _context.Entry(user)
    .Reference(u => u.Profile)
    .LoadAsync();

// Query related data
var orderCount = await _context.Entry(user)
    .Collection(u => u.Orders)
    .Query()
    .CountAsync();

How Do You Optimize EF Core Performance?

AsNoTracking

csharp

// For read-only queries - 30-40% performance improvement
var users = await _context.Users
    .AsNoTracking()
    .ToListAsync();

// Configure at DbContext level
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

Projection Instead of Full Entity

csharp

// BAD: Loads entire entity
var users = await _context.Users.ToListAsync();
var names = users.Select(u => u.FirstName);

// GOOD: Only loads what's needed
var names = await _context.Users
    .Select(u => u.FirstName)
    .ToListAsync();

// Using DTOs
var userSummaries = await _context.Users
    .Select(u => new UserSummaryDto
    {
        Id = u.Id,
        FullName = u.FirstName + " " + u.LastName,
        OrderCount = u.Orders.Count
    })
    .ToListAsync();

Avoid N+1 Problem

csharp

// BAD: N+1 queries
var users = await _context.Users.ToListAsync();
foreach (var user in users)
{
    // This executes a query for EACH user!
    var orders = user.Orders.ToList();
}

// GOOD: Single query with Include
var users = await _context.Users
    .Include(u => u.Orders)
    .ToListAsync();

// GOOD: Two queries with explicit loading
var users = await _context.Users.ToListAsync();
var userIds = users.Select(u => u.Id).ToList();
var orders = await _context.Orders
    .Where(o => userIds.Contains(o.UserId))
    .ToListAsync();

Compiled Queries

csharp

// Compile frequently used queries for better performance
private static readonly Func<AppDbContext, string, Task<User?>> GetUserByEmailQuery =
    EF.CompileAsyncQuery((AppDbContext context, string email) =>
        context.Users.FirstOrDefault(u => u.Email == email));

// Usage
var user = await GetUserByEmailQuery(_context, email);

// With multiple parameters
private static readonly Func<AppDbContext, int, int, IAsyncEnumerable<User>> GetPagedUsersQuery =
    EF.CompileAsyncQuery((AppDbContext context, int skip, int take) =>
        context.Users.OrderBy(u => u.Id).Skip(skip).Take(take));

Raw SQL Queries

csharp

// FromSqlRaw for entity queries
var users = await _context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Email LIKE {0}", "%@gmail.com")
    .ToListAsync();

// FromSqlInterpolated (safer, auto-parameterized)
var email = "%@gmail.com";
var users = await _context.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Email LIKE {email}")
    .ToListAsync();

// ExecuteSqlRaw for non-query operations
var rowsAffected = await _context.Database
    .ExecuteSqlRawAsync("UPDATE Users SET IsActive = 0 WHERE LastLoginAt < {0}", cutoffDate);

// SqlQuery for arbitrary results (.NET 7+)
var statistics = await _context.Database
    .SqlQuery<OrderStatistics>($@"
        SELECT
            CAST(CreatedAt AS DATE) as Date,
            COUNT(*) as OrderCount,
            SUM(TotalAmount) as TotalRevenue
        FROM Orders
        WHERE CreatedAt >= {startDate}
        GROUP BY CAST(CreatedAt AS DATE)
    ")
    .ToListAsync();

Bulk Operations

csharp

// EF Core 7+ ExecuteUpdate and ExecuteDelete
// Update without loading entities
await _context.Users
    .Where(u => u.LastLoginAt < DateTime.UtcNow.AddYears(-1))
    .ExecuteUpdateAsync(s => s
        .SetProperty(u => u.IsActive, false)
        .SetProperty(u => u.UpdatedAt, DateTime.UtcNow));

// Delete without loading entities
await _context.Users
    .Where(u => u.IsDeleted && u.DeletedAt < DateTime.UtcNow.AddMonths(-6))
    .ExecuteDeleteAsync();

// Bulk insert with AddRange
var users = Enumerable.Range(1, 1000)
    .Select(i => new User { Email = $"user{i}@test.com" })
    .ToList();

await _context.Users.AddRangeAsync(users);
await _context.SaveChangesAsync();

When Should You Use Transactions?

csharp

// Implicit transaction (SaveChanges)
_context.Users.Add(user);
_context.Orders.Add(order);
await _context.SaveChangesAsync(); // All or nothing

// Explicit transaction
await using var transaction = await _context.Database.BeginTransactionAsync();
try
{
    _context.Users.Add(user);
    await _context.SaveChangesAsync();

    // Call external service
    await _paymentService.ProcessPayment(order);

    _context.Orders.Add(order);
    await _context.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

// Transaction with isolation level
await using var transaction = await _context.Database
    .BeginTransactionAsync(IsolationLevel.Serializable);

Repository Pattern

csharp

// Generic repository interface
public interface IRepository<T> where T : class
{
    Task<T?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default);
    Task<IReadOnlyList<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);
    IQueryable<T> Query();
}

// Generic repository implementation
public class Repository<T> : IRepository<T> where T : class
{
    protected readonly AppDbContext _context;
    protected readonly DbSet<T> _dbSet;

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

    public virtual async Task<T?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default)
    {
        return await _dbSet.FindAsync(new object[] { id }, cancellationToken);
    }

    public virtual async Task<IReadOnlyList<T>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        return await _dbSet.AsNoTracking().ToListAsync(cancellationToken);
    }

    public virtual async Task<T> AddAsync(T entity, CancellationToken cancellationToken = default)
    {
        await _dbSet.AddAsync(entity, cancellationToken);
        return entity;
    }

    public virtual Task UpdateAsync(T entity, CancellationToken cancellationToken = default)
    {
        _context.Entry(entity).State = EntityState.Modified;
        return Task.CompletedTask;
    }

    public virtual Task DeleteAsync(T entity, CancellationToken cancellationToken = default)
    {
        _dbSet.Remove(entity);
        return Task.CompletedTask;
    }

    public IQueryable<T> Query() => _dbSet.AsQueryable();
}

// Specialized repository
public interface IUserRepository : IRepository<User>
{
    Task<User?> GetByEmailAsync(string email, CancellationToken cancellationToken = default);
    Task<IReadOnlyList<User>> GetActiveUsersAsync(CancellationToken cancellationToken = default);
}

public class UserRepository : Repository<User>, IUserRepository
{
    public UserRepository(AppDbContext context) : base(context) { }

    public async Task<User?> GetByEmailAsync(string email, CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .AsNoTracking()
            .FirstOrDefaultAsync(u => u.Email == email, cancellationToken);
    }

    public async Task<IReadOnlyList<User>> GetActiveUsersAsync(CancellationToken cancellationToken = default)
    {
        return await _dbSet
            .AsNoTracking()
            .Where(u => !u.IsDeleted)
            .OrderBy(u => u.LastName)
            .ToListAsync(cancellationToken);
    }
}

Unit of Work

csharp

public interface IUnitOfWork : IDisposable
{
    IUserRepository Users { get; }
    IOrderRepository Orders { get; }
    IProductRepository Products { get; }
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);
}

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;

    public IUserRepository Users { get; }
    public IOrderRepository Orders { get; }
    public IProductRepository Products { get; }

    public UnitOfWork(AppDbContext context)
    {
        _context = context;
        Users = new UserRepository(context);
        Orders = new OrderRepository(context);
        Products = new ProductRepository(context);
    }

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

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

// Usage in service
public class OrderService
{
    private readonly IUnitOfWork _unitOfWork;

    public async Task CreateOrderAsync(CreateOrderRequest request)
    {
        var user = await _unitOfWork.Users.GetByIdAsync(request.UserId);
        var products = await _unitOfWork.Products.GetByIdsAsync(request.ProductIds);

        var order = new Order { /* ... */ };
        await _unitOfWork.Orders.AddAsync(order);

        await _unitOfWork.SaveChangesAsync();
    }
}

EF Core is powerful but requires understanding. Use AsNoTracking for reads, project only what you need, avoid N+1 queries with proper Include strategies, and consider raw SQL for complex operations. The best ORM query is often the one you don't make.

Master these EF Core patterns and you'll build data access layers that are both maintainable and performant. Always profile your queries in production-like environments to catch performance issues early.

Entity Framework Core: From Basics to Advanced Performance

Setting Up

DbContext Configuration

Entity Configuration

Fluent API Configuration

Complex Relationships

How Do Migrations Work?

Custom Migration Operations

How Do You Query Data?

Basic Queries

Eager Loading (Include)

Explicit Loading

How Do You Optimize EF Core Performance?

AsNoTracking

Projection Instead of Full Entity

Avoid N+1 Problem

Compiled Queries

Raw SQL Queries

Bulk Operations

When Should You Use Transactions?

Repository Pattern

Unit of Work

Share this article

Related Articles

ASP.NET Core Web API Best Practices: Building Production-Ready APIs

Clean Architecture in .NET: Building Maintainable Applications

Microservices with .NET: Building Distributed Systems