LINQ to Entities with EF Core: the query pipeline, translation limits and client evaluation, tracking, projections, split queries, compiled queries and SQL inspection tools.
In EF Core, DbSet<T> is an IQueryable<T>, so Where, Select and so on build an expression tree, and EF Core translates it to SQL when you enumerate, caching the translation by query shape. Only what EF understands translates, like properties, operators and many string, date and EF.Functions methods. Since EF Core 3.0, client evaluation is allowed only in the final Select, so anything else untranslatable throws. For reads I use AsNoTracking or projections, AsSplitQuery when collection includes explode, and I check the SQL with ToQueryString(), logging and Query Store.
In EF Core, a LINQ query over a DbSet<T> builds an expression tree; when you execute it, EF Core's query pipeline translates the tree into SQL for the provider (SQL Server here), runs it, and materialises results — tracked or not. I'd cover translation, what can/can't be translated, client evaluation rules, tracking, split queries, compiled queries, and how I inspect and tune SQL.
var recent = await db.Orders
.Where(o => o.CustomerId == customerId && o.CreatedAt >= since)
.OrderByDescending(o => o.CreatedAt)
.Select(o => new OrderSummary(o.Id, o.Total, o.Customer.Name))
.Take(50)
.ToListAsync(ct);
Queryable methods build an expression tree (nothing executes).ToListAsync, EF Core checks its query cache (keyed by the tree shape). On a miss it runs the pipeline: normalisation → navigation expansion (o.Customer.Name becomes a JOIN) → translation to a relational model (SELECT expression) → SQL generation by the provider → compiles a shaper delegate that materialises rows.customerId, since) become SQL parameters; constants become literals.SqlCommand) and streams rows through the shaper.Translates: comparisons, boolean logic, arithmetic, most string methods (Contains, StartsWith, ToUpper, Trim), DateTime/DateOnly members, Math functions, aggregates, GroupBy with aggregates, Any/All/Contains (including list.Contains(x) → IN/OPENJSON), navigation properties, projections to DTOs/anonymous types, EF.Functions (Like, DateDiffDay, Collate, full-text), JSON columns (EF Core 7+), LeftJoin (EF Core 10).
Doesn't translate: calls to your own C# methods (o => IsVip(o)), arbitrary ToString formats, most LINQ over in-memory complex objects, Regex, some culture-sensitive string comparisons with StringComparison overloads, GroupBy returning groups of entities without aggregation (partially supported in recent versions), etc.
If a part can't be translated, EF Core throws InvalidOperationException: The LINQ expression ... could not be translated.
Select): Select(o => new { o.Id, Label = Format(o.Total) }) runs Format in memory after fetching Total.AsAsyncEnumerable(), ToListAsync()), knowing you're loading data.SaveChanges can detect changes. Costs memory and CPU.AsNoTracking(): for read-only queries — faster, less memory. Set as default for read-heavy contexts: optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking).AsNoTrackingWithIdentityResolution(): no tracking but one instance per key in the result.Loading multiple collections with Include creates JOINs that multiply rows (cartesian explosion):
db.Orders.Include(o => o.Lines).Include(o => o.Payments) // rows = lines × payments per order
AsSplitQuery() issues one SQL query per collection and stitches results — fewer duplicated rows, but multiple round trips and a risk of inconsistency between queries without a transaction. Configure globally (UseQuerySplittingBehavior) or per query. EF Core warns when a single query includes multiple collections.
private static readonly Func<AppDb, Guid, CancellationToken, Task<Order?>> FindOrder =
EF.CompileAsyncQuery((AppDb db, Guid id, CancellationToken ct) => db.Orders.FirstOrDefault(o => o.Id == id));
Skips expression tree comparison/cache lookup on each execution — worthwhile only for very hot queries (EF Core's cache is already fast). EF Core 9+ also supports precompiled queries for NativeAOT (experimental), generating query code at build time.
ToQueryString(): get SQL for a query without running it.LogTo(Console.WriteLine, LogLevel.Information) or Microsoft.EntityFrameworkCore.Database.Command category; EnableSensitiveDataLogging() only in development..TagWith("Orders dashboard") adds a SQL comment to find the query in SQL Server tools.sys.dm_exec_query_stats, missing index suggestions (evaluate before adding).Skip/Take or keyset pagination, use ExecuteUpdate/ExecuteDelete for bulk changes, consider raw SQL (FromSql) or Dapper for complex reporting queries.A dashboard endpoint took 8 seconds. TagWith + Query Store showed a query with Include of two collections returning ~400,000 rows for 200 orders (cartesian explosion), tracked by default. We replaced it with a projection to a DTO (only needed columns, aggregated counts in SQL), added a covering index for the filter, and used AsNoTracking. The endpoint dropped to ~150 ms.
A core senior .NET data-access question.
Green flags: explains the pipeline and caching; translation limits and the client-evaluation rule; tracking costs; cartesian explosion and split queries; reads SQL and plans; mentions ExecuteUpdate/Delete and parameterisation.
Red flags: fixes translation errors with ToList; does not know what SQL is generated; Include everything by default.
__customerId_0).o.Customer.Name → join), handling Include.ShapedQueryExpression (a relational SelectExpression + shaper).QuerySqlGenerator.DbDataReader rows into entities/DTOs, performing identity resolution and fix-up when tracking.C# == with nullable values differs from SQL = (where NULL = NULL is unknown). EF Core adds null compensation: o.Region == region becomes ([o].[Region] = @region OR ([o].[Region] IS NULL AND @region IS NULL)) when either can be null. This can hurt index usage; UseRelationalNulls() disables compensation (SQL semantics) when you know your data.
Parameters let SQL Server reuse a plan across values — but parameter sniffing can produce a plan good for one value and bad for others. Options: OPTION (RECOMPILE) via query hints/interceptors for skewed queries, better indexes, or splitting queries. EF Core 9 changed how collections in Contains are parameterised (JSON array parameter with OPENJSON on SQL Server, with options for inlining constants) to balance plan reuse and performance.
ToListAsync buffers results; AsAsyncEnumerable() streams rows (keeping the connection open while you iterate).AsNoTracking).| Symptom in SQL | Cause | Fix |
|---|---|---|
| Many identical SELECTs per row | N+1 (lazy loading or queries in loops) | Include, projection, batch query |
| Huge row counts with duplicated columns | Cartesian explosion from multiple Includes |
AsSplitQuery or projection |
SELECT * style wide queries |
Loading entities instead of projecting | Select DTOs |
Functions on columns in WHERE (UPPER([Name])) |
Case-insensitive comparisons in LINQ | Use collation, normalised columns, or computed indexed columns |
Large IN lists |
Contains with huge collections |
Temp tables/TVPs, batching, joins |
DbCommandInterceptor can add hints, log slow queries, or tag queries. DiagnosticListener/EventCounters expose EF metrics (queries per second, cache hit rates).
ToQueryString() on a query with a navigation projection.TagWith and find the query in Query Store.Includes with and without AsSplitQuery.Where with a custom method and read the exception; move it to the final projection.using Microsoft.EntityFrameworkCore;
namespace Linq.Entities;
public sealed class Order
{
public int Id { get; set; }
public int CustomerId { get; set; }
public DateTime CreatedUtc { get; set; }
public decimal Total { get; set; }
public List<OrderLine> Lines { get; } = [];
}
public sealed class OrderLine
{
public int Id { get; set; }
public int OrderId { get; set; }
public required string Sku { get; set; }
public int Quantity { get; set; }
}
public sealed class ShopContext(DbContextOptions<ShopContext> options) : DbContext(options)
{
public DbSet<Order> Orders => Set<Order>();
}
public sealed record OrderDto(int Id, DateTime CreatedUtc, decimal Total, int LineCount);
public sealed class OrderQueries(ShopContext db)
{
private static readonly Func<ShopContext, int, CancellationToken, Task<Order?>> ById =
EF.CompileAsyncQuery((ShopContext ctx, int id, CancellationToken ct) =>
ctx.Orders.AsNoTracking().FirstOrDefault(o => o.Id == id));
public Task<Order?> GetAsync(int id, CancellationToken ct) => ById(db, id, ct);
// Projection: only needed columns, no tracking, sargable date range
public Task<List<OrderDto>> RecentAsync(int customerId, int year, CancellationToken ct)
{
var from = new DateTime(year, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var to = from.AddYears(1);
return db.Orders
.Where(o => o.CustomerId == customerId && o.CreatedUtc >= from && o.CreatedUtc < to)
.OrderByDescending(o => o.CreatedUtc)
.Select(o => new OrderDto(o.Id, o.CreatedUtc, o.Total, o.Lines.Count))
.Take(50)
.ToListAsync(ct);
}
// Collection include with split query to avoid cartesian explosion
public Task<List<Order>> WithLinesAsync(IReadOnlyCollection<int> ids, CancellationToken ct)
=> db.Orders
.Where(o => ids.Contains(o.Id))
.Include(o => o.Lines)
.AsSplitQuery()
.AsNoTracking()
.ToListAsync(ct);
// Set-based update without loading entities (EF Core 7+)
public Task<int> ArchiveOldAsync(DateTime beforeUtc, CancellationToken ct)
=> db.Orders.Where(o => o.CreatedUtc < beforeUtc)
.ExecuteDeleteAsync(ct);
public string DebugSql(int customerId) =>
db.Orders.Where(o => o.CustomerId == customerId).ToQueryString();
}
What happens: Include + map in memory.
Why it is wrong: fetches far more data than needed and tracks it.
Do instead: project with Select into DTOs.
What happens: default tracking for reports and lists.
Why it is wrong: memory and CPU overhead.
Do instead: AsNoTracking() or no-tracking by default for read contexts.
Includes in one queryWhat happens: cartesian explosion.
Why it is wrong: massive duplicated rows and slow queries.
Do instead: AsSplitQuery() or projections.
WhereWhat happens: Where(o => IsVip(o)).
Why it is wrong: can't be translated — throws (or forces loading everything if you ToList first).
Do instead: express the logic in translatable LINQ or a database function.
What happens: db.Orders.ToList().Where(...).
Why it is wrong: loads the whole table into memory.
Do instead: keep filters on IQueryable until the end.
What happens: performance issues investigated only in C# code.
Why it is wrong: the real cost is in generated SQL and execution plans.
Do instead: log SQL, use ToQueryString, Query Store and execution plans.
What happens: every query wrapped in EF.CompileQuery.
Why it is wrong: complexity for negligible gain on most queries.
Do instead: compile only measured hot queries.
What happens: EnableSensitiveDataLogging() left on.
Why it is wrong: parameter values (personal data) end up in logs.
Do instead: enable only in development.
LINQ to Entities is a translator, and seniors manage it like one: they read the SQL it produces, keep queries translatable and sargable, project instead of loading graphs, and use set-based operations for bulk work. I treat EF Core 3.0+'s strict client-evaluation rule as a safety net and never defeat it with early AsEnumerable. For hot paths I measure translation versus execution time; usually the database plan matters far more than EF's overhead, but compiled queries help on very high request rates.
IQueryable<T> is an IEnumerable<T> that also carries an expression tree and a provider. Queryable.Where takes an Expression<Func<T,bool>> and just wraps the expression, and when you enumerate, the provider, for example EF Core, translates the whole tree, typically into SQL. IEnumerable operators take delegates and run in memory. Which one you get depends on the static type of the variable, so storing a DbSet in an IEnumerable variable quietly moves every later Where into memory. AsEnumerable() does that on purpose. I keep IQueryable inside the data layer and return materialised results.
The big problems are where the work runs and how often it runs. ToList() before Where pulls whole tables into memory, and loading related data inside the foreach is an N+1. A FirstOrDefault on a list inside a loop makes it O(n times m), and enumerating the same query several times repeats the round trips. Smaller fixes are Any() instead of Count() > 0, and MinBy or a SQL TOP 1 instead of OrderBy().First(). A 50,000-id Contains needs batching or a table-valued parameter. My rewrite filters and projects in one SQL query with AsNoTracking, builds a dictionary for lookups, materialises once, and I measure before and after.
I generate migrations locally with dotnet ef migrations add, then make deployment a separate, reviewed step. The pipeline produces an artefact: either an idempotent SQL script (dotnet ef migrations script --idempotent) for a DBA to review, or a migration bundle (dotnet ef migrations bundle), a self-contained executable that applies pending migrations. That artefact runs once, before the new app version starts, with a dedicated deployment identity. I avoid Database.Migrate() at startup because several instances can race, the app needs DDL permissions it shouldn't have, and a failed migration turns into a crash loop.
How to tell it (STAR)
**S:** Our biggest customers' order history page took about 9 seconds. **T:** I was asked to make it usable without a redesign. **A:** I read the generated SQL and plan, found cartesian explosion from two collection Includes, and replaced them with a projection and keyset pagination. **R:** p95 dropped to about [time] and database load for that endpoint fell by [percentage].
Last updated 30 Oct 2026.