EF Core: The .NET Object-Relational Mapper

A practical guide to Entity Framework Core — the ORM that lets .NET developers work with databases using C# objects and LINQ instead of hand-written SQL, covering DbContext, migrations, querying, change tracking, relationships, performance tuning, and when to reach for something else.

---

Table of Contents

1. Introduction
2. DbContext and DbSet
3. Modeling: Conventions, Data Annotations, and Fluent API
4. Migrations
5. Querying with LINQ
6. Change Tracking and SaveChanges
7. Relationships and Loading Strategies
8. Concurrency Control
9. Transactions
10. Performance Tuning
11. Raw SQL and Escape Hatches
12. Testing with EF Core
13. When to Reach for Something Else
14. Quick Reference Table
15. Conclusion

---

Introduction

Entity Framework Core (EF Core) is Microsoft's modern, cross-platform Object-Relational Mapper for .NET — it lets you define your data model as plain C# classes, query it with LINQ, and let EF Core translate that into SQL for whichever database provider you're targeting (SQL Server, PostgreSQL, SQLite, MySQL, Cosmos DB, and others).

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

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseSqlServer(connectionString);
}

var expensiveProducts = await dbContext.Products
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.Price)
    .ToListAsync();

That LINQ query gets translated into a parameterized SQL SELECT statement, and the results are automatically mapped back into Product objects — no manual SqlCommand, no manual row-to-object mapping. This guide covers the concepts that matter most for using EF Core correctly and efficiently: how it tracks changes, how relationships and loading work, where performance problems typically hide, and when a different tool (Dapper, raw SQL) is the better choice instead.

---

1. DbContext and DbSet

DbContext: the unit of work

DbContext represents a session with the database — it tracks entities you've loaded or added, translates LINQ queries into SQL, and coordinates saving changes back as a single unit of work.

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

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

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Lifetime matters

AddDbContext registers AppDbContext with a scoped lifetime by default — one instance per HTTP request in a typical ASP.NET Core app. This is deliberate: DbContext is not thread-safe, and isn't meant to be shared across concurrent operations or held for longer than a single logical unit of work (see the Background Services guide in this series for the specific pitfall of capturing a scoped DbContext inside a singleton).

DbSet as a queryable entry point

DbSet<Product> Products => Set<Product>();

A DbSet<T> represents a table (or, for more complex mappings, a query) and implements IQueryable<T> — every LINQ query against it is translated to SQL lazily, only executing when the query is actually enumerated (via ToListAsync(), FirstOrDefaultAsync(), a foreach, etc.), not when the LINQ expression itself is built.

---

2. Modeling: Conventions, Data Annotations, and Fluent API

EF Core determines your database schema from your C# classes through three complementary mechanisms, layered in increasing precedence.

Conventions (the defaults)

public class Product
{
    public int Id { get; set; }              // convention: "Id" becomes the primary key automatically
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public int CategoryId { get; set; }       // convention: recognized as a foreign key to Category
    public Category Category { get; set; } = null!;
}

EF Core's conventions handle a surprising amount automatically — a property named Id (or {ClassName}Id) becomes the primary key, a property matching another entity's name plus Id is recognized as a foreign key, and so on.

Data annotations

public class Product
{
    public int Id { get; set; }

    [Required, MaxLength(200)]
    public string Name { get; set; } = "";

    [Column(TypeName = "decimal(10,2)")]
    public decimal Price { get; set; }

    [NotMapped]
    public string DisplayName => $"{Name} (${Price})"; // computed property, not persisted
}

Attributes directly on the model classes are convenient for simple, per-property configuration, but they mix persistence concerns into your domain model — many teams prefer keeping model classes clean and pushing this configuration into the Fluent API instead.

Fluent API

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(entity =>
    {
        entity.Property(p => p.Name).IsRequired().HasMaxLength(200);
        entity.Property(p => p.Price).HasColumnType("decimal(10,2)");
        entity.HasIndex(p => p.CategoryId);
        entity.HasOne(p => p.Category)
              .WithMany(c => c.Products)
              .HasForeignKey(p => p.CategoryId)
              .OnDelete(DeleteBehavior.Restrict);
    });
}

The Fluent API is the most powerful and complete configuration mechanism — anything data annotations can express, Fluent API can too, plus configuration (like the delete behavior above) that annotations can't express at all. For larger models, splitting configuration into separate IEntityTypeConfiguration<T> classes keeps OnModelCreating from becoming an unmanageable wall of code:

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.Property(p => p.Name).IsRequired().HasMaxLength(200);
        builder.HasIndex(p => p.CategoryId);
    }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
}

---

3. Migrations

Migrations are EF Core's mechanism for evolving your database schema alongside your C# model, keeping both under version control together.

Creating and applying a migration

dotnet ef migrations add AddProductDiscountField
dotnet ef database update

// Generated migration file (excerpt)
public partial class AddProductDiscountField : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<decimal>(
            name: "DiscountPercent",
            table: "Products",
            type: "decimal(5,2)",
            nullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "DiscountPercent", table: "Products");
    }
}

Every migration has an Up (apply the change) and Down (revert it) method — dotnet ef database update applies any pending migrations in order, and can also target a specific prior migration to roll back to.

Reviewing generated migrations before applying them

EF Core's migration generation is good but not infallible — it's worth actually reading a generated migration before running it against production, especially for anything beyond a simple additive column change (renaming a column, changing a type, adding a non-nullable column to a table with existing rows all deserve a second look, since the "obvious" auto-generated approach isn't always the safest one for existing data).

Applying migrations in production

// Programmatic approach — useful for automated deployment pipelines
using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
await db.Database.MigrateAsync();

Running MigrateAsync() automatically at application startup is convenient for small projects but risky at scale — with multiple instances starting simultaneously (a rolling deployment), you can end up with concurrent migration attempts. A more robust pattern for production systems is running migrations as a distinct, single deployment step (a CI/CD pipeline stage, a one-off job) before the new application version starts serving traffic, rather than baking it into every instance's startup path.

Generating SQL scripts for review/DBA approval

dotnet ef migrations script --idempotent -o migration.sql

For environments where a DBA needs to review and manually apply schema changes (common in regulated or highly cautious production environments), generating an idempotent SQL script rather than relying on EF Core's runtime migration application gives a reviewable artifact that can go through the same change-approval process as any other production database change.

---

4. Querying with LINQ

Basic queries

var products = await dbContext.Products
    .Where(p => p.CategoryId == categoryId && p.Price < 100)
    .OrderBy(p => p.Name)
    .ToListAsync();

Projections (selecting only what you need)

var productSummaries = await dbContext.Products
    .Where(p => p.CategoryId == categoryId)
    .Select(p => new ProductSummaryDto { Name = p.Name, Price = p.Price })
    .ToListAsync();

Projecting into a DTO (rather than loading full entities and mapping afterward) lets EF Core generate a SELECT that only pulls the columns you actually need — a meaningful performance win for wide tables when you only need a couple of fields, and it also means the result isn't tracked by the change tracker at all (see Section 5), since it's not a mapped entity.

AsNoTracking for read-only queries

var products = await dbContext.Products
    .AsNoTracking()
    .Where(p => p.CategoryId == categoryId)
    .ToListAsync();

For any query whose results you won't modify and save back, AsNoTracking() skips the overhead of change tracking (Section 5) entirely — a simple, low-risk performance improvement that's easy to forget to apply consistently across read-heavy endpoints, and one of the first things worth checking when a read-only query path is slower than expected.

Aggregate and grouping queries

var revenueByCategory = await dbContext.Orders
    .SelectMany(o => o.Items)
    .GroupBy(i => i.Product.CategoryId)
    .Select(g => new { CategoryId = g.Key, TotalRevenue = g.Sum(i => i.Price * i.Quantity) })
    .ToListAsync();

EF Core translates GroupBy, aggregate functions, and most standard LINQ operators into equivalent SQL — but not every LINQ construct has a clean SQL translation; anything EF Core can't translate either throws at query time (in modern EF Core versions) or, in older/misconfigured setups, silently falls back to evaluating part of the query in memory (client evaluation), which can be a serious, easy-to-miss performance trap on large tables.

Split queries for multiple included collections

var orders = await dbContext.Orders
    .Include(o => o.Items)
    .Include(o => o.StatusHistory)
    .AsSplitQuery()
    .ToListAsync();

Including multiple collection navigations in a single query by default produces one large SQL query with multiple joins, which can multiply row counts (a "cartesian explosion") when more than one collection is included at once. AsSplitQuery() instead issues separate SQL queries per included collection — often meaningfully faster for this specific shape of query, at the cost of losing single-query atomicity (the split queries aren't guaranteed to reflect a perfectly consistent snapshot if data changes between them, though this is rarely a practical concern for typical read scenarios).

---

5. Change Tracking and SaveChanges

How change tracking works

var product = await dbContext.Products.FirstAsync(p => p.Id == 42);
product.Price = 24.99m; // no database call happens here
await dbContext.SaveChangesAsync(); // EF Core detects the change and issues an UPDATE

When you query an entity without AsNoTracking(), EF Core keeps a snapshot of its original values in the DbContext's change tracker. When SaveChangesAsync() is called, EF Core compares the entity's current values against that snapshot, and generates INSERT/UPDATE/DELETE statements only for what actually changed — you never write SQL for the update itself, just modify the object's properties.

Entity states

dbContext.Entry(product).State = EntityState.Modified; // rarely needed directly, but useful to understand

Every tracked entity has a state: Added, Unchanged, Modified, Deleted, or Detached — this state is what SaveChangesAsync() actually reads to decide what SQL to generate, and it's normally managed automatically by EF Core's own change detection rather than something you set by hand.

Batching changes into one SaveChangesAsync() call

dbContext.Products.Add(newProduct);
existingProduct.Price = 19.99m;
dbContext.Categories.Remove(oldCategory);

await dbContext.SaveChangesAsync(); // all three changes committed together, in one transaction

SaveChangesAsync() wraps everything tracked as changed since the last save into a single database transaction — all changes across multiple entities succeed or fail together, which is one of EF Core's most valuable, easy-to-take-for-granted correctness guarantees.

---

6. Relationships and Loading Strategies

Defining relationships

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public List<OrderItem> Items { get; set; } = [];
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;
    public int ProductId { get; set; }
    public Product Product { get; set; } = null!;
    public int Quantity { get; set; }
}

Eager loading with Include

var order = await dbContext.Orders
    .Include(o => o.Items).ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

Include (and ThenInclude for nested navigations) loads related data as part of the same query, up front — the right choice when you know you'll need the related data, avoiding the N+1 problem described next.

The N+1 problem (and lazy loading's role in it)

// Without Include, accessing a navigation property triggers a separate query per entity if lazy loading is enabled
var orders = await dbContext.Orders.ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // one additional query PER order, if lazy loading is on
}

This is the same N+1 problem covered in this series' GraphQL guide, manifesting in EF Core specifically through lazy loading — a feature that must be explicitly opted into (via the Microsoft.EntityFrameworkCore.Proxies package and UseLazyLoadingProxies()) and is generally considered something to use cautiously, since it makes this exact performance trap easy to hit accidentally: a loop that looks perfectly innocent in code silently issues dozens or hundreds of extra round trips to the database.

Explicit loading

var order = await dbContext.Orders.FirstAsync(o => o.Id == orderId);
await dbContext.Entry(order).Collection(o => o.Items).LoadAsync();

A middle ground between eager and lazy loading — you load the main entity first, then explicitly (and visibly, in code) load specific related data only when you actually need it, avoiding both the upfront cost of always eager-loading everything and the hidden-cost surprise of implicit lazy loading.

Recommended default

For most application code, eager loading (Include) combined with AsNoTracking() for read-only queries is the safest, most predictable default — it makes data access patterns explicit and visible in the query itself, rather than relying on lazy loading's implicit, easy-to-overlook extra round trips.

---

7. Concurrency Control

Optimistic concurrency with a row version/concurrency token

public class Product
{
    public int Id { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;
}

try
{
    await dbContext.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Another transaction modified this row between when we loaded it and when we tried to save
    var databaseValues = await ex.Entries.Single().GetDatabaseValuesAsync();
    // reload, merge, or surface a conflict to the user, depending on the application's needs
}

EF Core includes the RowVersion (or another designated concurrency token column) in the WHERE clause of the generated UPDATE statement — if the row has changed since it was loaded, zero rows match the WHERE clause, EF Core detects this as a concurrency conflict, and throws DbUpdateConcurrencyException rather than silently overwriting someone else's concurrent change.

When to care about this

Optimistic concurrency tokens matter most for entities that are both frequently read-then-updated and genuinely subject to concurrent edits (a shared inventory count, a document multiple users might edit) — for data that's effectively only ever modified by one process/user at a time, the added complexity of concurrency-conflict handling usually isn't worth introducing.

---

8. Transactions

Implicit transactions via SaveChangesAsync

As covered in Section 5, a single SaveChangesAsync() call is already wrapped in an implicit transaction — for the common case of "make several related changes, then save them together," you don't need to manage a transaction explicitly at all.

Explicit transactions across multiple SaveChanges calls

using var transaction = await dbContext.Database.BeginTransactionAsync();
try
{
    order.Status = OrderStatus.Confirmed;
    await dbContext.SaveChangesAsync();

    await _inventoryService.ReserveStockAsync(order); // might call SaveChangesAsync() internally too

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

An explicit transaction is needed when a logical operation spans multiple separate SaveChangesAsync() calls (often because it also involves calling into other services/repositories) and all of them need to succeed or fail together as one atomic unit.

---

9. Performance Tuning

Watch the generated SQL

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString).LogTo(Console.WriteLine, LogLevel.Information));

Logging the actual SQL EF Core generates (during development, not typically in production due to overhead and log volume) is often the fastest way to spot an unexpectedly expensive query — an accidental N+1 pattern, an unnecessary SELECT * where a projection would do, or a query that isn't translating as cleanly to SQL as you'd assume.

Compiled queries for extremely hot paths

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

var product = await GetProductById(dbContext, 42);

Compiled queries skip LINQ expression tree translation on every call (EF Core normally caches this internally too, so the benefit here is narrower than it might sound) — worth reaching for only in genuinely extreme-throughput, latency-sensitive paths after profiling shows query translation overhead actually matters, not as a default optimization applied everywhere.

Bulk operations: ExecuteUpdate and ExecuteDelete

await dbContext.Products
    .Where(p => p.CategoryId == discontinuedCategoryId)
    .ExecuteUpdateAsync(setters => setters.SetProperty(p => p.IsActive, false));

await dbContext.Products
    .Where(p => p.CategoryId == discontinuedCategoryId)
    .ExecuteDeleteAsync();

Introduced in EF Core 7, these translate directly into a single UPDATE/DELETE SQL statement affecting potentially many rows at once — dramatically more efficient than the traditional pattern of loading every matching entity into memory, modifying each one, and calling SaveChangesAsync(), especially for bulk operations affecting thousands of rows.

Avoid SELECT * by default: use projections

As covered in Section 4, projecting into a DTO rather than always loading full entities is one of the most consistently effective, low-effort performance improvements available — especially valuable for list/summary views that don't need every column of a wide entity.

Indexes still matter

EF Core generates the SQL, but it doesn't design your indexes for you — the indexing guidance covered in this series' SQL Server and PostgreSQL guides applies exactly the same way regardless of whether the SQL hitting the database came from hand-written T-SQL or EF Core's translation of a LINQ query.

---

10. Raw SQL and Escape Hatches

FromSqlInterpolated for read queries

var products = await dbContext.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE CategoryId = {categoryId} AND Price > {minPrice}")
    .ToListAsync();

FromSqlInterpolated (note: interpolated, not raw string concatenation) safely parameterizes the interpolated values, avoiding SQL injection while still letting you drop down to raw SQL for a query LINQ can't express cleanly, or where a hand-tuned query outperforms what EF Core's translator would generate.

ExecuteSqlInterpolated for commands

await dbContext.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {categoryId}");

The equivalent escape hatch for non-query commands — again, safely parameterized despite the string-interpolation-like syntax.

Calling stored procedures

var results = await dbContext.Products
    .FromSqlInterpolated($"EXEC GetDiscountedProducts @CategoryId = {categoryId}")
    .ToListAsync();

For applications that lean on stored procedures for specific, performance-critical, or business-logic-encapsulating operations (as discussed in this series' SQL Server guide), EF Core can still map the results back into entities/DTOs cleanly.

---

11. Testing with EF Core

The in-memory provider: convenient, but not a substitute for the real thing

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
    .Options;
using var context = new AppDbContext(options);

EF Core's in-memory provider is fast and easy to set up for unit tests, but it's not a real relational database — it doesn't enforce foreign key constraints the same way, doesn't translate LINQ to SQL (so a query that would fail to translate against a real provider might silently "work" against the in-memory provider by evaluating in memory instead), and can behave differently around concurrency and transactions. Tests that pass against the in-memory provider are not a reliable guarantee the same code works against SQL Server/PostgreSQL.

SQLite as a more faithful lightweight alternative

var connection = new SqliteConnection("Filename=:memory:");
connection.Open();
var options = new DbContextOptionsBuilder<AppDbContext>().UseSqlite(connection).Options;

An in-memory SQLite database is a genuine relational database, enforcing real constraints and actually translating/executing SQL — a meaningfully more faithful substitute than the in-memory provider for integration-style tests, though SQLite's own SQL dialect and behavior still differ from SQL Server/PostgreSQL in some respects, so it's a step closer to production fidelity, not a perfect stand-in.

Testing against a real database (via containers)

For the highest-fidelity tests, spinning up the actual target database (SQL Server, PostgreSQL) in a Docker container as part of the test run — often via a library like Testcontainers — is increasingly the preferred approach for integration tests where correctness against the real provider's actual behavior matters, accepting the added test-run time and infrastructure as a reasonable tradeoff for that fidelity.

---

12. When to Reach for Something Else

EF Core is an excellent default for the majority of application data access, but it's not universally the right tool for every query.

Scenario	Better fit
Simple, well-understood CRUD across a moderate-complexity schema	EF Core — the productivity and maintainability wins are substantial
A small number of extremely performance-critical, high-volume queries	Dapper (or raw ADO.NET) for just those specific queries, EF Core for everything else
Complex reporting queries with heavy aggregation across many tables	Often better as hand-written SQL (via FromSqlInterpolated or a dedicated reporting query layer) than fighting LINQ's translation of a very complex query
Bulk data operations affecting millions of rows	ExecuteUpdate/ExecuteDelete where applicable, or a dedicated bulk-copy/ETL tool for very large-scale operations
A team that already deeply understands SQL and wants maximum control	Dapper — thinner abstraction, less "magic," fuller visibility into exactly what SQL runs

A common, pragmatic pattern (also mentioned in this series' SQL Server guide): EF Core for the majority of an application's data access, with Dapper reserved for a small, deliberately chosen set of hot-path or complex-reporting queries where the fine control and reduced overhead genuinely matter — not an all-or-nothing choice between the two.

---

Quick Reference Table

Concept	Purpose
DbContext	Unit of work — tracks changes, translates queries, coordinates saves
DbSet<T>	Queryable entry point representing a table
Migrations	Version-controlled, incremental schema evolution alongside the C# model
AsNoTracking()	Skips change tracking overhead for read-only queries
Include/ThenInclude	Eager-load related data in the same (or a split) query
Lazy loading	Implicit per-access loading — convenient but an easy source of N+1 queries
DbUpdateConcurrencyException	Signals an optimistic concurrency conflict on save
ExecuteUpdate/ExecuteDelete	Single-statement bulk operations without loading entities into memory
FromSqlInterpolated/ExecuteSqlInterpolated	Safe escape hatches to raw SQL when LINQ isn't the right fit
AsSplitQuery()	Avoids cartesian-explosion row multiplication with multiple included collections
In-memory provider vs. SQLite vs. containers	Increasing levels of test fidelity to the real target database

---

Conclusion

EF Core's core value proposition — write C# and LINQ, get correct, parameterized SQL and automatic change tracking in return — holds up well for the large majority of application data access, and it removes a huge amount of repetitive, error-prone boilerplate (manual mapping, hand-written INSERT/UPDATE statements, connection management) that raw ADO.NET requires. The places it demands real attention are consistent and well-understood: watch for N+1 queries around navigation properties, use AsNoTracking() deliberately for read-only paths, project into DTOs rather than always loading full entities, and don't assume LINQ has a clean, efficient SQL translation for every possible query shape without checking the generated SQL.

None of this makes EF Core the wrong choice — it makes it a tool whose sharp edges are well-documented and predictable once you know where to look, which is exactly what you want from something that sits directly between your application code and the database powering it.

---

Found this useful? Feel free to star the repo, open an issue with corrections, or share the N+1 query that taught you to always check the generated SQL.