Entity Framework Core

What is Entity Framework Core?

Entity Framework Core (EF Core) is Microsoft’s modern object-relational mapper (ORM) for .NET. In simple terms, it’s a library that lets you work with databases using C# objects instead of writing raw SQL queries. You define your data models as classes, and EF Core handles the translation between your objects and database tables.

Here’s the mental shift: instead of thinking in terms of SQL statements, you work with objects:

// Without EF Core - raw SQL
var sql = "SELECT * FROM Products WHERE Price > 100";
// Execute SQL, map results manually...

// With EF Core - objects and LINQ
var expensiveProducts = context.Products
    .Where(p => p.Price > 100)
    .ToList();

EF Core handles the heavy lifting of connecting to databases, translating LINQ queries to SQL, tracking changes, and persisting data back to the database.

Why Use EF Core?

The Traditional Problem

Working directly with databases in code is tedious and error-prone:

// Raw ADO.NET approach - lots of boilerplate
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

var command = new SqlCommand("SELECT * FROM Products WHERE Id = @Id", connection);
command.Parameters.AddWithValue("@Id", productId);

using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
    var product = new Product
    {
        Id = reader.GetGuid(0),
        Name = reader.GetString(1),
        Price = reader.GetDecimal(2)
        // Map every field manually...
    };
}

This code is:

Verbose and repetitive
Error-prone (typos in SQL, wrong column indices)
Hard to maintain (schema changes break everything)
Not strongly-typed (no compile-time safety)

The EF Core Solution

EF Core simplifies this dramatically:

var product = await context.Products
    .FirstOrDefaultAsync(p => p.Id == productId);

That’s it! EF Core handles connection management, query generation, and object mapping automatically.

Core Concepts

DbContext

The DbContext is your gateway to the database. It represents a session with the database and manages entity tracking and queries:

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

    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<Customer> Customers { get; set; }
}

Each DbSet<T> property represents a table in your database.

Entities

Entities are simple C# classes (POCOs - Plain Old CLR Objects) that represent your data:

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public DateTime CreatedAt { get; set; }
    
    // Navigation property for relationships
    public List<OrderItem> OrderItems { get; set; } = new();
}

EF Core uses conventions to map these classes to database tables. By default:

Class name → Table name
Property name → Column name
Property named Id or {ClassName}Id → Primary key

Setting Up EF Core

1. Install Packages

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL  # For PostgreSQL
# Or use Microsoft.EntityFrameworkCore.SqlServer for SQL Server

2. Configure in Program.cs

var builder = WebApplication.CreateBuilder(args);

// Register DbContext with dependency injection
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

3. Connection String in appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=myapp;Username=postgres;Password=password"
  }
}

CRUD Operations

Create

public class ProductService
{
    private readonly AppDbContext _context;

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

    public async Task<Product> CreateProductAsync(CreateProductRequest request)
    {
        var product = new Product
        {
            Id = Guid.NewGuid(),
            Name = request.Name,
            Description = request.Description,
            Price = request.Price,
            StockQuantity = request.StockQuantity,
            CreatedAt = DateTime.UtcNow
        };

        _context.Products.Add(product);
        await _context.SaveChangesAsync();

        return product;
    }
}

Read

// Get single record
public async Task<Product?> GetProductByIdAsync(Guid id)
{
    return await _context.Products
        .FirstOrDefaultAsync(p => p.Id == id);
}

// Get multiple records with filtering
public async Task<List<Product>> GetProductsInStockAsync()
{
    return await _context.Products
        .Where(p => p.StockQuantity > 0)
        .OrderBy(p => p.Name)
        .ToListAsync();
}

// Get with paging
public async Task<List<Product>> GetProductsPagedAsync(int page, int pageSize)
{
    return await _context.Products
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();
}

Update

public async Task UpdateProductAsync(Guid id, UpdateProductRequest request)
{
    var product = await _context.Products.FindAsync(id);
    
    if (product == null)
        throw new NotFoundException($"Product {id} not found");

    product.Name = request.Name;
    product.Description = request.Description;
    product.Price = request.Price;
    product.StockQuantity = request.StockQuantity;

    await _context.SaveChangesAsync(); // EF Core tracks changes automatically
}

Delete

public async Task DeleteProductAsync(Guid id)
{
    var product = await _context.Products.FindAsync(id);
    
    if (product == null)
        throw new NotFoundException($"Product {id} not found");

    _context.Products.Remove(product);
    await _context.SaveChangesAsync();
}

Relationships

EF Core handles relationships between entities elegantly:

One-to-Many

public class Customer
{
    public Guid Id { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    
    // Navigation property - one customer has many orders
    public List<Order> Orders { get; set; } = new();
}

public class Order
{
    public Guid Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    
    // Foreign key
    public Guid CustomerId { get; set; }
    
    // Navigation property - one order belongs to one customer
    public Customer Customer { get; set; }
}

Loading related data:

// Eager loading - load customer with their orders
var customer = await _context.Customers
    .Include(c => c.Orders)
    .FirstOrDefaultAsync(c => c.Id == customerId);

// Multiple levels of includes
var order = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

Many-to-Many

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    
    // Navigation property
    public List<Category> Categories { get; set; } = new();
}

public class Category
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    
    // Navigation property
    public List<Product> Products { get; set; } = new();
}

// In DbContext - EF Core 5+ handles many-to-many automatically
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasMany(p => p.Categories)
        .WithMany(c => c.Products);
}

Migrations

Migrations let you evolve your database schema over time while preserving data:

Create Initial Migration

dotnet ef migrations add InitialCreate

This generates migration files that describe how to create your database schema.

Apply Migrations

dotnet ef database update

This executes the migration, creating or updating your database.

Adding New Fields

When you modify your entities:

public class Product
{
    // ... existing properties
    
    // New property added
    public string Sku { get; set; }
}

Create a new migration:

dotnet ef migrations add AddSkuToProduct
dotnet ef database update

EF Core generates the SQL to add the column without losing existing data.

Configuration with Fluent API

For more control, use the Fluent API in OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(entity =>
    {
        // Table name
        entity.ToTable("Products");
        
        // Primary key
        entity.HasKey(e => e.Id);
        
        // Property configurations
        entity.Property(e => e.Name)
            .IsRequired()
            .HasMaxLength(200);
        
        entity.Property(e => e.Description)
            .HasMaxLength(1000);
        
        entity.Property(e => e.Price)
            .HasColumnType("decimal(18,2)");
        
        // Index
        entity.HasIndex(e => e.Name);
        
        // Default value
        entity.Property(e => e.CreatedAt)
            .HasDefaultValueSql("CURRENT_TIMESTAMP");
    });
}

Querying Best Practices

Use AsNoTracking for Read-Only Queries

When you don’t need to update entities, disable change tracking for better performance:

public async Task<List<ProductDto>> GetAllProductsAsync()
{
    return await _context.Products
        .AsNoTracking() // Better performance for read-only scenarios
        .Select(p => new ProductDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price
        })
        .ToListAsync();
}

Project to DTOs Early

Don’t load full entities if you only need a few fields:

// Bad - loads entire entity
var products = await _context.Products.ToListAsync();
var names = products.Select(p => p.Name).ToList();

// Good - only queries needed columns
var names = await _context.Products
    .Select(p => p.Name)
    .ToListAsync();

Avoid N+1 Query Problems

// Bad - N+1 queries
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
    // This executes a separate query for each order!
    var customer = await _context.Customers.FindAsync(order.CustomerId);
}

// Good - single query with join
var orders = await _context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

Common Patterns in APIs

Repository Pattern

public interface IProductRepository
{
    Task<Product?> GetByIdAsync(Guid id);
    Task<IEnumerable<Product>> GetAllAsync();
    Task<Product> AddAsync(Product product);
    Task UpdateAsync(Product product);
    Task DeleteAsync(Guid id);
}

public class ProductRepository : IProductRepository
{
    private readonly AppDbContext _context;

    public ProductRepository(AppDbContext context)
    {
        _context = context;
    }

    public async Task<Product?> GetByIdAsync(Guid id)
    {
        return await _context.Products.FindAsync(id);
    }

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        return await _context.Products.ToListAsync();
    }

    public async Task<Product> AddAsync(Product product)
    {
        _context.Products.Add(product);
        await _context.SaveChangesAsync();
        return product;
    }

    // ... other methods
}

Unit of Work Pattern

The DbContext itself implements the Unit of Work pattern - it tracks changes and commits them together:

public async Task ProcessOrderAsync(CreateOrderRequest request)
{
    // All operations happen in one transaction
    var order = new Order
    {
        Id = Guid.NewGuid(),
        CustomerId = request.CustomerId,
        OrderDate = DateTime.UtcNow
    };
    
    _context.Orders.Add(order);
    
    foreach (var item in request.Items)
    {
        var orderItem = new OrderItem
        {
            OrderId = order.Id,
            ProductId = item.ProductId,
            Quantity = item.Quantity
        };
        _context.OrderItems.Add(orderItem);
    }
    
    // Single save commits everything or nothing
    await _context.SaveChangesAsync();
}

Common Pitfalls

1. Not Using Async

// Bad - blocks thread
var products = _context.Products.ToList();

// Good - async all the way
var products = await _context.Products.ToListAsync();

2. Disposing DbContext Too Early

// Bad - context disposed before accessing navigation property
var product = await _context.Products.FindAsync(id);
_context.Dispose();
var orders = product.OrderItems; // Exception! Navigation property not loaded

// Good - load everything before disposing (or use dependency injection)
var product = await _context.Products
    .Include(p => p.OrderItems)
    .FirstOrDefaultAsync(p => p.Id == id);

3. Tracking Too Many Entities

// Bad - loads and tracks thousands of entities
var allProducts = await _context.Products.ToListAsync();

// Good - use AsNoTracking for read-only or paginate
var products = await _context.Products
    .AsNoTracking()
    .Take(100)
    .ToListAsync();

Wrap Up

Entity Framework Core is the de facto ORM for .NET applications, and for good reason. It eliminates most of the tedious database code you’d otherwise write, provides type safety, and makes complex queries surprisingly simple.

The key concepts to remember:

DbContext is your database session
Entities are your data models
LINQ translates to SQL automatically
Migrations manage schema changes
Always use async methods in APIs
Include related data explicitly to avoid N+1 queries

Once you get comfortable with EF Core, you’ll find that most database operations become straightforward. The learning curve is worth it - you’ll spend far less time fighting with database code and more time building features that matter.

For most .NET APIs, EF Core is the right choice. It’s mature, well-documented, and handles the vast majority of data access scenarios you’ll encounter.