LINQ & EF Core

If async/await is the feature that made C# fast, LINQ is the feature that made it pleasant. Language Integrated Query gives you one uniform, composable vocabulary -- Where, Select, GroupBy, OrderBy, Sum -- that works identically over in-memory collections, an IAsyncEnumerable<T> stream, and a table in a SQL database. You learn the query operators once and apply them everywhere, and the compiler checks them for you. No other mainstream language has anything quite this seamless baked into its standard library.

Here is the shape of it: take a list of orders, keep the completed ones, group by customer, project a summary, filter the summaries, sort, and take the top ten.

var topCustomers = orders
    .Where(o => o.Status == OrderStatus.Completed)
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        Total = g.Sum(o => o.Amount),
        Count = g.Count()
    })
    .Where(c => c.Total > 10_000)
    .OrderByDescending(c => c.Total)
    .Take(10)
    .ToList();

The first thing to internalise is deferred execution. None of those operators do any work when you call them. Where does not iterate anything; it builds a small object that describes a filtering operation and hands it to the next operator. The entire pipeline stays dormant until something forces enumeration -- a foreach, a ToList(), a Count(), a First(). Only at that final ToList() does the machinery actually walk the data. This is lazy by design, and it is what makes LINQ composable: you can pass a half-built query around, add more operators in another method, and nothing executes until the consumer asks for results.

The second thing to internalise is the difference between the two interfaces LINQ runs over. IEnumerable<T> is the in-memory world: each lambda you write is compiled to a delegate -- real executable code -- and the pipeline runs by invoking those delegates as it iterates objects already in memory. IQueryable<T> is the remote world: each lambda is compiled instead to an expression tree -- data describing the code -- which a provider (EF Core's SQL translator, most often) inspects and converts into a query in another language. The operators look identical. What happens underneath could not be more different, and that gap is the source of the single most important trap in the whole topic.

The IQueryable Boundary

When you write a LINQ query against an EF Core DbSet<T>, you are building an expression tree. EF walks that tree and translates the whole thing into one SQL statement that runs on the database server. This is wonderful -- you express filtering, joining, grouping, and paging in C# and it executes as a single efficient round trip. But it only works for operations EF knows how to translate. The moment you call a C# method the provider cannot turn into SQL, the translation breaks.

// SomeLocalFunc is ordinary C# -- there is no SQL for it
static bool IsHighValue(Order o) => o.Amount > 10_000 && o.Region == "NZ";

var result = db.Orders
    .Where(o => IsHighValue(o))   // EF cannot translate this call
    .ToList();

In EF Core 3.0 and later this throws an InvalidOperationException at runtime telling you the expression could not be translated. That sounds hostile, but it is one of the best decisions the EF team ever made. In earlier versions EF would silently fall back to client-side evaluation: it would pull the entire Orders table across the wire into memory and then run your C# predicate on the application server. On a developer's laptop with fifty rows nobody noticed. In production with twenty million rows it was a catastrophic, hard-to-diagnose performance cliff. The 3.0 change makes the failure loud and immediate at the top-level Where, which is exactly when you want to hear about it.

The fix is to express the logic in terms EF can translate -- inline the conditions so they become an expression tree, not a method call:

var result = db.Orders
    .Where(o => o.Amount > 10_000 && o.Region == "NZ")  // translates to SQL WHERE
    .ToList();

If you genuinely need C# logic that has no SQL equivalent, the rule is: do the translatable filtering on the database first, materialise the smallest possible result with ToList() (or AsAsyncEnumerable()), and then apply the in-memory predicate against IEnumerable<T>. The danger is doing it accidentally -- composing a query across method boundaries and not noticing where IQueryable quietly became IEnumerable.

Change Tracking

EF Core is more than a query translator; it is a unit-of-work and identity-map system. When you load an entity through a tracking query, the DbContext keeps a reference to it and a snapshot of its original values. You mutate the object like any plain C# object, and when you call SaveChangesAsync() the context compares each tracked entity against its snapshot, computes the diff, and emits exactly the INSERT/UPDATE/DELETE statements needed -- in the right order, inside a transaction.

var order = await db.Orders.FirstAsync(o => o.Id == id, ct);

order.Status = OrderStatus.Completed;   // just a property assignment
order.CompletedAt = DateTimeOffset.UtcNow;

await db.SaveChangesAsync(ct);          // EF emits UPDATE Orders SET ... WHERE Id = ...

Notice there is no Update() call. You never tell EF which columns changed; it works that out from the snapshot. This is the change-tracking model doing its job, and it is genuinely productive for domain logic -- you load an aggregate, manipulate it in plain code, and persist. The other half of the same system is the identity map: within a single context, a given database row is represented by exactly one object instance. If two different queries in the same context both touch order 42, you get the same Order reference back both times -- mutate it in one place and the other sees the change. This consistency is the reason a DbContext must be short-lived.

AsNoTracking for Reads

All that snapshotting costs memory and CPU, and for a read-only query you pay it for nothing. If you are loading data to render a page or return from an API and you will never call SaveChanges on it, tell EF so with AsNoTracking():

var orders = await db.Orders
    .AsNoTracking()                      // no snapshot, no identity map
    .Where(o => o.Region == "NZ")
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .ToListAsync(ct);

For large read result sets this is a meaningful win -- EF skips building the snapshot for every entity and never adds them to the tracker. Make it the default for query endpoints and reserve tracking queries for the load-mutate-save path.

This is also the place to remember that a DbContext is not thread-safe and is not meant to be. It holds tracked state for one logical operation, so it must be scoped per request or per unit of work -- in ASP.NET Core that means AddDbContext registers it as Scoped, and a new context is created and disposed for each request. This ties directly back to the captive dependency pitfall in DI: never inject a Scoped DbContext into a Singleton service, because the singleton captures the first context forever, sharing one non-thread-safe, ever-growing tracker across every concurrent request. It will appear to work, then corrupt state or throw concurrency errors under load.

The N+1 Problem

The most common EF performance bug, by a wide margin, is N+1. You load a list of N parent rows in one query, then iterate them and touch a navigation property on each -- and each access fires its own query. One query becomes 1 + N, and the round-trip latency dominates everything.

// N+1: one query for orders, then one per order for its lines
var orders = await db.Orders.ToListAsync(ct);
foreach (var o in orders)
    Console.WriteLine(o.Lines.Count); // lazy load per order

If you have 500 orders that is 501 queries, each with its own network round trip. The fix is to tell EF up front that you need the related data, so it fetches it eagerly in the same trip:

// Fixed: eager load
var orders = await db.Orders
    .Include(o => o.Lines)
    .ToListAsync(ct);

Include solves the round-trip problem but introduces a new one when you stack several includes: EF builds one big JOIN, and joining a parent to several independent child collections produces a cartesian explosion -- the row count multiplies and the same parent data is repeated across every combination. When that bites, AsSplitQuery() tells EF to issue one query per collection instead of one giant join, trading a few extra round trips for a vastly smaller result set.

The leanest fix of all is usually to stop loading entities you do not need. Project straight into a DTO with Select, and EF generates SQL that pulls only the columns and aggregates you asked for -- no tracking, no navigation graph, no over-fetching:

var summaries = await db.Orders
    .Where(o => o.Region == "NZ")
    .Select(o => new OrderSummary(o.Id, o.CustomerId, o.Lines.Count, o.Amount))
    .ToListAsync(ct);

Reach for projection first for read endpoints, Include when you genuinely need the entities to mutate, and AsSplitQuery when multiple includes blow up the result.

Migrations

EF Core's migrations are code-first schema versioning. You change your entity classes, run dotnet ef migrations add AddCompletedAt, and EF diffs the new model against the last migration to generate a C# migration class describing the schema delta. dotnet ef database update applies pending migrations to a database. The migration files are checked into source control alongside the code that depends on them, so the schema's history travels with the application and every environment converges on the same shape.

dotnet ef migrations add AddCompletedAt
dotnet ef database update

Applying migrations directly from the application at startup is fine for small services, but in regulated, audited, or government environments -- a large slice of the NZ market -- you usually cannot let an app mutate a production schema on its own. The pattern there is to generate an idempotent SQL script and hand it to a DBA or a controlled release pipeline:

dotnet ef migrations script --idempotent --output migrate.sql

The idempotent script guards every statement so it only runs if that migration has not already been applied, making it safe to run repeatedly. Whatever path you take, read the generated SQL before it touches production. EF will happily produce destructive operations -- dropping a column, narrowing a type, renaming a table as drop-and-recreate -- and a rename that EF interprets as "delete this column and add that one" silently discards data. Migrations are convenient, not magic; review them like any other change to a production system.

When to Drop to Dapper

EF Core is excellent for CRUD and domain modelling -- you get change tracking, transactions, a typed query language, and a clean mapping from tables to aggregates. But every abstraction is a trade, and EF's trade is control over the exact SQL. For a hot read path, a gnarly reporting query with window functions and CTEs, or a bulk operation over millions of rows, the SQL EF generates may be acceptable, opaque, or occasionally awful -- and you cannot always coax it into the plan you want.

When SQL is the thing that matters, drop to Dapper (or raw ADO.NET). Dapper is a thin micro-ORM: you write the exact SQL, it runs it and maps the columns onto your type. No expression trees, no tracking, no surprises -- the query that runs is the query you wrote.

var rows = await conn.QueryAsync<OrderSummary>(
    "SELECT customer_id, SUM(amount) AS total FROM orders WHERE status = @status GROUP BY customer_id",
    new { status = "Completed" });

You give up the productivity of change tracking and typed queries, and you take on responsibility for the SQL and its parameters, but you gain predictable performance and complete control. The pragmatic, widely adopted answer is not to choose one or the other but to run both: EF for writes and domain logic, where the unit-of-work model earns its keep, and Dapper for performance-critical reads and reporting, where raw SQL wins. They share the same connection and the same database; use each where it is strong.

Sharp Edges

A handful of recurring failure modes are worth holding in your head. Client-side evaluation still surprises people -- below the top-level Where, EF can quietly evaluate some constructs in memory, so watch for queries that run far slower than the SQL suggests they should. Tracking large read result sets wastes memory and CPU for nothing; default reads to AsNoTracking(). N+1 loves to hide behind lazy loading, where an innocent property access inside a loop fires a query per row -- it never looks like a query in the code. IQueryable composed across method boundaries is treacherous: somewhere a ToList() or an untranslatable call flips you to IEnumerable and the rest of your "query" runs in memory. And because LINQ is lazily executed, forgetting to await a ToListAsync() -- or enumerating the same deferred query twice -- runs the whole thing twice against the database, often without any visible sign that you have doubled your load.