Skip to content
Tutorials Center
My learning

Topics

Lesson 150 of 182 ยท Data Access

Entity Framework Core

Learn EF Core: DbContext, entities, migrations, how LINQ becomes SQL, change tracking, relationships, Include and N+1, with runnable stand-ins that show how it works.

Entity Framework Core (EF Core) is the object-relational mapper that most .NET teams reach for first. You describe your data as ordinary C# classes, query it with LINQ, change the objects, and call SaveChanges(). EF Core turns the LINQ into SQL, notices which objects you modified, and writes the INSERT, UPDATE and DELETE statements inside a transaction.

That convenience is only a good deal if you understand what happens underneath. Otherwise a harmless-looking loop sends a thousand queries, or a query silently loads a whole table. This lesson explains the moving parts and, since the runner has no EF Core package, rebuilds the key mechanisms in miniature so you can watch them work: a LINQ-to-SQL translator, a change tracker and the N+1 problem. Real EF Core code is shown read-only and labelled.

EF Core is a NuGet package (Microsoft.EntityFrameworkCore plus a provider such as Microsoft.EntityFrameworkCore.SqlServer, .Sqlite or Npgsql.EntityFrameworkCore.PostgreSQL). It is not part of the ASP.NET Core shared framework, so it is not available in our in-browser runner. Install it in your own project with dotnet add package. For the bigger picture see Data Access in .NET and ADO.NET.

Entities and the DbContext

An entity is a plain class that maps to a table. A DbContext is your session with the database: it exposes DbSet<T> properties (tables), tracks the objects it loaded, and saves changes.

C#
// Read-only: needs the EF Core packages.
using Microsoft.EntityFrameworkCore;

public class Author
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Book> Books { get; set; } = [];
}

public class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public decimal Price { get; set; }
    public int AuthorId { get; set; }          // foreign key
    public Author Author { get; set; } = null!; // navigation
}

public class ShopContext(DbContextOptions<ShopContext> options) : DbContext(options)
{
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>(book =>
        {
            book.Property(b => b.Title).HasMaxLength(200);
            book.Property(b => b.Price).HasPrecision(10, 2);
            book.HasIndex(b => b.Title);
        });
    }
}

EF Core uses conventions to guess the model: a property named Id (or BookId) is the primary key, AuthorId plus an Author navigation is a foreign key, and string? versus string decides whether a column allows NULL because nullable reference types are honored. When conventions are not enough you use the Fluent API (OnModelCreating) or attributes.

You register the context with dependency injection (DI); the default lifetime is scoped, so each web request gets its own context:

C#
// Read-only: Program.cs of an ASP.NET Core app.
builder.Services.AddDbContext<ShopContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

A DbContext is not thread-safe and is meant to be short-lived: create it, do one unit of work, dispose it. Do not store one in a static field or share it between concurrent tasks.

How a model becomes a schema

A model class implies a table definition. EF Core's dotnet ef migrations tooling compares your model with the last snapshot and generates a migration, a C# file with Up and Down methods that you commit to source control.

Terminal
# Read-only: needs the dotnet CLI and the dotnet-ef tool.
dotnet ef migrations add InitialCreate
dotnet ef database update

The idea is simple enough to model with reflection. This program reads entity classes and prints the CREATE TABLE they imply, including NOT NULL from the nullable annotations and a foreign key from the XId naming convention:

C# From entity classes to CREATE TABLE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Website and PublishedOn are nullable, so they get no NOT NULL. Change string? to string and the schema changes with it. That is exactly why EF Core cares about your nullable annotations.

From LINQ to SQL

A query against a DbSet is built from IQueryable<T>, not IEnumerable<T>. The difference is the key to EF Core. When you write .Where(b => b.Price < 20) on an IQueryable, the lambda is captured as an expression tree (data describing the code, see expression trees) instead of a compiled delegate. EF Core walks that tree and generates SQL when you enumerate the query (ToListAsync, FirstAsync, foreach...), a form of deferred execution.

C#
// Read-only: a query and the SQL EF Core 10 might send (SQL Server flavor).
var cheap = await db.Books
    .Where(b => b.Price < 20m && b.Title.StartsWith("L"))
    .OrderBy(b => b.Title)
    .Select(b => new { b.Title, b.Price })
    .ToListAsync();
SQL
SELECT [b].[Title], [b].[Price]
FROM [Books] AS [b]
WHERE [b].[Price] < 20.0 AND [b].[Title] LIKE N'L%'
ORDER BY [b].[Title]

Notice that the Select produced a column list of just two columns. Projecting to what you need is one of the cheapest performance wins in EF Core. You can see the real SQL by enabling logging (optionsBuilder.LogTo(Console.WriteLine)) or calling query.ToQueryString().

The following program is a miniature provider. An ExpressionVisitor walks a predicate and writes a WHERE clause, turning captured variables and constants into parameters, which is how EF Core avoids SQL injection while still letting you write C#:

C# A mini LINQ to SQL translator

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Look at the first result: the variable limit was evaluated into @p0 = 20, and the string literal into @p1 = L%. The values never appear in the SQL text. Look also at NotSupportedException: this is precisely what EF Core throws (or, in older versions, silently evaluated in memory) when you call your own C# method inside a query. A method the translator does not know cannot become SQL.

IQueryable versus IEnumerable: after you call ToList() or AsEnumerable(), later Where calls run in C# memory on everything already loaded. Put filters, sorting and projection before you materialize, or you will download a whole table to keep ten rows. Beware of methods that return IEnumerable<T> from a repository: they quietly turn the rest of your query into in-memory LINQ.

Change tracking and SaveChanges

When you load an entity with a normal query, the context keeps the object plus a snapshot of its original values. At SaveChanges() it compares each tracked entity with its snapshot (this is DetectChanges) and decides a state: Unchanged, Added, Modified or Deleted. It then generates one SQL statement per change, all inside a single transaction.

C#
// Read-only: EF Core in action.
var book = await db.Books.SingleAsync(b => b.Id == 7);
book.Price = 24.99m;                 // only the context's snapshot knows the old value
db.Books.Add(new Book { Title = "New Title", AuthorId = 1 });
await db.SaveChangesAsync();         // UPDATE ... SET Price = @p0 WHERE Id = @p1; INSERT ...

The next program is a hand-rolled tracker with the same logic. It compares snapshots by reflection, emits only the changed columns for an update, and reports the states before saving:

C# A tiny change tracker

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Only City appears in the UPDATE, because only City changed. Sam receives a generated key. After saving, every entry is Unchanged again with a fresh snapshot.

Practical consequences of tracking:

  • Tracking costs memory and time. For read-only queries (a list page, an API GET), add .AsNoTracking() so EF Core skips the snapshots.
  • One context, one identity per row. Asking the same context for the same primary key twice returns the same object instance.
  • Bulk changes: ExecuteUpdateAsync and ExecuteDeleteAsync (EF Core 7+) run a single SQL statement without loading entities:
C#
// Read-only. One UPDATE statement, nothing loaded into memory.
int changed = await db.Books
    .Where(b => b.Price < 10m)
    .ExecuteUpdateAsync(s => s.SetProperty(b => b.Price, b => b.Price * 1.1m));

Relationships and loading strategies

Relationships come from the foreign key and navigation properties above: one-to-many (Author has many Book), one-to-one, and many-to-many (EF Core creates the join table for you). Related data does not arrive automatically. You choose how to load it:

Strategy Code What happens
Eager .Include(a => a.Books) One query with a JOIN (or a second query with split queries)
Explicit db.Entry(author).Collection(a => a.Books).LoadAsync() You load it on demand
Lazy proxies package, virtual navigations Loaded silently on first access (often a trap)
Projection .Select(a => new { a.Name, Count = a.Books.Count }) Only the columns you need; usually best

The classic mistake is N+1: one query for the parents, then one more for each parent's children. EF Core's lazy loading makes it easy to cause by accident, and a loop that touches a navigation property is the usual trigger. We can count queries with a fake database:

C# The N+1 problem and eager loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

With six authors, N+1 sends 7 queries; the eager version sends 2. With 1,000 authors the gap is 1,001 versus 2, and every extra query pays a network round trip.

Eager loading is not free either. A single Include of a collection repeats the parent columns in every joined row (a "cartesian explosion" when you include several collections). AsSplitQuery() sends one query per collection instead, trading round trips for smaller result sets. When you only need a few fields, a projection with Select avoids both problems.

Pitfalls professionals avoid

  • Long-lived contexts. The tracker grows, results go stale. Use one context per unit of work.
  • Calling SaveChanges in a loop. Each call is a round trip and a transaction. Add all changes, then save once.
  • Client evaluation surprises. Untranslatable code in the final Select runs in C# after the rows arrive; anywhere else in the query it throws. Keep database logic translatable or move it after AsEnumerable() deliberately.
  • Exposing entities in APIs. Return DTOs (records are ideal), not tracked entities with cycles and private columns.
  • Ignoring concurrency. Two users editing the same row overwrite each other unless you add a concurrency token (a [Timestamp] row version). More in SQL and NoSQL from C#.
  • Not looking at the SQL. Turn on query logging in development and read what is sent.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does a lambda in db.Books.Where(...) get translated to SQL, while the same lambda on a List<Book> does not?

    IQueryable<T> methods take Expression<Func<...>>, which is data describing the code. The provider walks that tree to generate SQL. IEnumerable methods take compiled delegates.
  2. 2. A page loads 50 authors and prints author.Books.Count for each with lazy loading enabled. How many queries are likely sent?

    One query for the authors plus one per author when the Books navigation is first touched: the N+1 problem. Include or a projection reduces it to one or two.
  3. 3. What does AsNoTracking() do?

    Without tracking, changes to the returned objects are not detected, so use it for queries whose results you will not save.
  4. 4. Where does an EF Core UPDATE statement get the list of columns to set?

    The change tracker compares current values with the original snapshot (DetectChanges) and writes only the modified columns.
  5. 5. What is the lifetime you normally register a DbContext with in ASP.NET Core?

    A DbContext is not thread-safe and is designed for one unit of work, so AddDbContext registers it as scoped. A singleton context would be shared by concurrent requests.

Summary

  • EF Core maps entity classes to tables through conventions, the Fluent API and your nullable annotations; migrations turn model changes into versioned schema scripts.
  • IQueryable queries are expression trees translated to SQL when enumerated; code the provider cannot translate throws.
  • The change tracker snapshots loaded entities and, at SaveChanges, writes minimal INSERT/UPDATE/DELETE statements in one transaction.
  • Use AsNoTracking for read-only queries and ExecuteUpdate/ExecuteDelete for set-based changes.
  • Choose loading deliberately: projection first, Include when you need the entities, and avoid lazy loading to dodge N+1.
  • Keep contexts short-lived and scoped, and read the generated SQL.

Next: Repositories, Unit of Work and Clean Architecture