Entity Framework Core Complete Guide — Setup, Queries, Migrations

Entity Framework Core is the standard ORM for .NET. It maps your C# classes to database tables, handles migrations as your schema evolves, and turns LINQ expressions into SQL. This guide covers everything from a blank project to production-ready patterns.

🗄️ EF Core → SQL Translatorselect a pattern

LINQ (C#)

var users = await context.Users
    .Where(u => u.IsActive)
    .OrderBy(u => u.LastName)
    .ToListAsync();

Generated SQL

SELECT [u].[Id], [u].[Email], [u].[FirstName],
       [u].[IsActive], [u].[LastName]
FROM [Users] AS [u]
WHERE [u].[IsActive] = 1
ORDER BY [u].[LastName]

Where() → WHERE, OrderBy() → ORDER BY. EF Core translates LINQ operators 1:1.

Installation

Start with a new ASP.NET Core project and add the EF Core packages:

dotnet new webapi -n MyApp
cd MyApp
 
# Core EF packages
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer   # or Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Tools       # for migrations CLI

For SQLite (great for development and small apps):

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Defining Your Entities

Entities are plain C# classes. EF Core uses conventions to infer table names, primary keys, and column types:

// Models/Product.cs
public class Product
{
    public int Id { get; set; }               // Convention: "Id" → primary key
    public required string Name { get; set; }
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public DateTime CreatedAt { get; set; }
 
    // Navigation property (one-to-many)
    public int CategoryId { get; set; }
    public Category Category { get; set; } = null!;
}
 
// Models/Category.cs
public class Category
{
    public int Id { get; set; }
    public required string Name { get; set; }
 
    // Collection navigation property
    public List<Product> Products { get; set; } = [];
}

💡

Use required on string properties (C# 11+) to enforce non-null values at the compiler level. EF Core maps required string to a non-nullable column automatically.

Setting Up DbContext

DbContext is the central class — it holds your DbSet<T> properties and manages the database connection:

// Data/AppDbContext.cs
using Microsoft.EntityFrameworkCore;
 
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options) { }
 
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API configuration (optional but recommended)
        modelBuilder.Entity<Product>(entity =>
        {
            entity.Property(p => p.Name)
                  .HasMaxLength(200)
                  .IsRequired();
 
            entity.Property(p => p.Price)
                  .HasPrecision(18, 2);
 
            entity.HasOne(p => p.Category)
                  .WithMany(c => c.Products)
                  .HasForeignKey(p => p.CategoryId)
                  .OnDelete(DeleteBehavior.Restrict);
        });
 
        modelBuilder.Entity<Category>(entity =>
        {
            entity.Property(c => c.Name)
                  .HasMaxLength(100)
                  .IsRequired();
        });
    }
}

Registering the DbContext

In Program.cs:

using Microsoft.EntityFrameworkCore;
 
var builder = WebApplication.CreateBuilder(args);
 
// SQL Server
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
 
// Or SQLite
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=app.db"));
 
// Or PostgreSQL
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

Connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MyAppDb;Trusted_Connection=True;"
  }
}

Migrations

Migrations track schema changes as versioned files. Every time you change your entity classes, you add a migration.

# Add the EF global tool (one-time setup)
dotnet tool install --global dotnet-ef
 
# Create initial migration
dotnet ef migrations add InitialCreate
 
# Apply migrations to the database
dotnet ef database update

This generates a Migrations/ folder with files like:

// Migrations/20250116120000_InitialCreate.cs
public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Categories",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(maxLength: 100, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Categories", x => x.Id);
            });
 
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(maxLength: 200, nullable: false),
                Price = table.Column<decimal>(precision: 18, scale: 2, nullable: false),
                StockQuantity = table.Column<int>(nullable: false),
                CreatedAt = table.Column<DateTime>(nullable: false),
                CategoryId = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
                table.ForeignKey(
                    name: "FK_Products_Categories_CategoryId",
                    column: x => x.CategoryId,
                    principalTable: "Categories",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
            });
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "Products");
        migrationBuilder.DropTable(name: "Categories");
    }
}

⚠️

Never edit migration files manually after they've been applied to any database. If you need to change something, add a new migration instead.

Migration Commands Reference

# Add a new migration after changing entities
dotnet ef migrations add AddProductDescription
 
# Update database to latest migration
dotnet ef database update
 
# Roll back to a specific migration
dotnet ef database update InitialCreate
 
# Remove the last unapplied migration
dotnet ef migrations remove
 
# Generate SQL script instead of applying directly (good for production)
dotnet ef migrations script --output migration.sql

CRUD Operations

Create

// Inject AppDbContext via constructor injection
public class ProductService
{
    private readonly AppDbContext _db;
 
    public ProductService(AppDbContext db) => _db = db;
 
    public async Task<Product> CreateProductAsync(string name, decimal price, int categoryId)
    {
        var product = new Product
        {
            Name = name,
            Price = price,
            StockQuantity = 0,
            CreatedAt = DateTime.UtcNow,
            CategoryId = categoryId
        };
 
        _db.Products.Add(product);
        await _db.SaveChangesAsync();
 
        return product; // Id is populated after SaveChangesAsync
    }
}

Read — Basic Queries

// Get all products
var products = await _db.Products.ToListAsync();
 
// Get by primary key (most efficient — uses PK index)
var product = await _db.Products.FindAsync(42);
 
// Get single with condition
var product = await _db.Products
    .FirstOrDefaultAsync(p => p.Id == 42);
 
// Get with related data (eager loading)
var productsWithCategory = await _db.Products
    .Include(p => p.Category)
    .ToListAsync();

Read — LINQ Queries

// Filter
var expensiveProducts = await _db.Products
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Price)
    .ToListAsync();
 
// Project to DTO (avoid loading full entity when not needed)
var productDtos = await _db.Products
    .Where(p => p.StockQuantity > 0)
    .Select(p => new ProductDto(p.Id, p.Name, p.Price))
    .ToListAsync();
 
// Pagination
int page = 1, pageSize = 20;
var pagedProducts = await _db.Products
    .OrderBy(p => p.Name)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();
 
// Count
var inStockCount = await _db.Products
    .CountAsync(p => p.StockQuantity > 0);
 
// Any / All
bool hasExpensiveItems = await _db.Products.AnyAsync(p => p.Price > 500);

💡

Always use .Select() to project to a DTO when you only need a subset of columns. Loading full entities when you only need Name and Price wastes memory and adds unnecessary SQL columns.

Update

// Fetch-then-update pattern (safest, handles concurrency)
public async Task<bool> UpdatePriceAsync(int productId, decimal newPrice)
{
    var product = await _db.Products.FindAsync(productId);
    if (product is null) return false;
 
    product.Price = newPrice;
    await _db.SaveChangesAsync();
    return true;
}
 
// Bulk update (EF Core 7+ ExecuteUpdateAsync — no entity loading)
await _db.Products
    .Where(p => p.CategoryId == 5)
    .ExecuteUpdateAsync(p => p.SetProperty(x => x.Price, x => x.Price * 0.9m));

Delete

// Fetch-then-delete
public async Task<bool> DeleteProductAsync(int productId)
{
    var product = await _db.Products.FindAsync(productId);
    if (product is null) return false;
 
    _db.Products.Remove(product);
    await _db.SaveChangesAsync();
    return true;
}
 
// Bulk delete (EF Core 7+ ExecuteDeleteAsync — no entity loading)
await _db.Products
    .Where(p => p.StockQuantity == 0 && p.CreatedAt < DateTime.UtcNow.AddYears(-2))
    .ExecuteDeleteAsync();

Relationships

One-to-Many (configured above)

Loading related data:

// Eager loading with Include
var category = await _db.Categories
    .Include(c => c.Products)
    .FirstOrDefaultAsync(c => c.Id == categoryId);
 
// Explicit loading (load navigation property on demand)
var category = await _db.Categories.FindAsync(categoryId);
await _db.Entry(category!).Collection(c => c.Products).LoadAsync();

Many-to-Many (EF Core 5+)

public class Post
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public List<Tag> Tags { get; set; } = [];
}
 
public class Tag
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Post> Posts { get; set; } = [];
}
 
// EF Core 5+ creates the junction table automatically — no extra entity needed
modelBuilder.Entity<Post>()
    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts)
    .UsingEntity(j => j.ToTable("PostTags"));

One-to-One

public class User
{
    public int Id { get; set; }
    public required string Email { get; set; }
    public UserProfile? Profile { get; set; }
}
 
public class UserProfile
{
    public int Id { get; set; }
    public string? Bio { get; set; }
    public int UserId { get; set; }
    public User User { get; set; } = null!;
}

No-Tracking Queries

By default, EF Core tracks loaded entities for change detection. For read-only queries, disable tracking for better performance:

// No-tracking for read operations
var products = await _db.Products
    .AsNoTracking()
    .Where(p => p.Price > 50)
    .ToListAsync();
 
// Configure globally for a DbContext used only for reads
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

💡

Use .AsNoTracking() on any query where you won't be updating the returned entities. It skips the change tracker overhead and can provide 10–30% better performance for read-heavy workloads.

Transactions

using var transaction = await _db.Database.BeginTransactionAsync();
try
{
    _db.Products.Add(newProduct);
    await _db.SaveChangesAsync();
 
    _db.Orders.Add(newOrder);
    await _db.SaveChangesAsync();
 
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Common Mistakes to Avoid

⚠️

Never call .ToList() before filtering. _db.Products.ToList().Where(...) loads ALL rows into memory then filters in C#. Always filter with .Where() before .ToList() or .ToListAsync() so the filter runs in SQL.

⚠️

Don't share DbContext across threads. DbContext is not thread-safe. In ASP.NET Core, use the default scoped lifetime — one instance per HTTP request.

💡

Enable sensitive data logging only in development. Add .EnableSensitiveDataLogging() to see parameter values in query logs, but never in production.

Logging SQL Queries

To see the SQL EF Core generates (essential for debugging performance):

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
 
    if (builder.Environment.IsDevelopment())
    {
        options.LogTo(Console.WriteLine, LogLevel.Information)
               .EnableSensitiveDataLogging();
    }
});

Applying Migrations at Runtime

For containerized apps, apply migrations at startup instead of as a separate step:

// Program.cs
var app = builder.Build();
 
// Apply pending migrations automatically on startup
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();
}
 
app.Run();

⚠️

Automatic migration at startup works well for small teams. For large teams or zero-downtime deployments, run migrations as a separate step before deploying the new application version.

What's Next

With the basics solid, explore:

Performance: N+1 queries, compiled queries, split queries
Advanced relationships: Owned entities, table-per-hierarchy inheritance
Interceptors: Audit logging, soft delete automation
EF Core 8 features: JSON columns, raw SQL improvements, complex types