LINQ to Entities is the dialect of LINQ that Entity Framework Core uses to turn C# query expressions into SQL. The C# you write looks like ordinary LINQ over an in-memory collection, but every operator gets parsed into an expression tree, walked by EF's query provider, and translated into a single SQL statement that the database actually runs. This lesson covers what makes that translation possible, where it breaks down, and how to write LINQ queries that produce the SQL you intended.

When you call Where on a List<Customer>, the runtime calls the Where extension method defined on IEnumerable<T>. The lambda you pass becomes a compiled C# delegate, and Where invokes it once per element in memory. That flavor of LINQ is LINQ to Objects: everything runs in the .NET process, against objects already loaded into memory.

When you call Where on DbContext.Customers, you're calling a different Where method, the one defined on IQueryable<T>. The lambda doesn't get compiled to a delegate. Instead, the compiler builds an expression tree: a data structure that describes the lambda's body as nodes (a MemberAccess on Customer.City, an Equal operator, a Constant of "Seattle", and so on). EF Core's query provider walks that tree, generates SQL, and ships it to the database. The database does the filtering. Only the results that match come back over the wire.

The shape of the C# looks almost identical, which is the whole point of LINQ: one mental model for querying any data source. The difference is invisible until you write something that translates poorly, at which point EF either throws or silently degrades performance. Knowing which provider you're talking to is the foundation of writing efficient EF queries.

The path from C# to results is one-way. Once you cross from IQueryable<T> into IEnumerable<T> (by calling AsEnumerable(), ToList(), ToArray(), or foreach), the rest of the operators run in memory.

IQueryable<T> extends IEnumerable<T> with two extra pieces: an Expression property holding the expression tree built so far, and a Provider reference that knows how to execute it. Chaining Where or Select on an IQueryable<T> returns a new IQueryable<T> with a bigger expression tree. Nothing has run yet.

IEnumerable<T> carries no expression tree. Chaining Where on an IEnumerable<T> returns an iterator that, when enumerated, walks the source one element at a time and applies the delegate in process.

The switching point is AsEnumerable(). The moment you call it (or any materializing operator), the query splits in two: everything before it goes to the database, everything after it runs in memory on the returned rows.

The first query pulls back only orders where Total > 100 AND Status = 'Shipped'. The second query pulls back every shipped order, then filters in memory. If the table has half a million shipped orders and only a handful exceed $100, the second version moves half a million rows over the network for no reason.

The rule of thumb: keep your queries on IQueryable<T> for as long as possible, and only cross over to IEnumerable<T> when you've narrowed the data down to what you actually need.

Building a query doesn't run it. Returning an IQueryable<T> from a method, assigning it to a variable, even calling Where, Select, and OrderBy on it, all of that is just adding nodes to the expression tree. The actual SQL doesn't get sent until you do one of these:

Until then, the query is just a description. This is called deferred execution, and it's why you can build queries in pieces:

Each query = query.Where(...) line builds a longer expression tree. The database sees one combined SQL statement when ToListAsync finally pulls the trigger. There's no risk of "running the same query three times" because the query isn't running at all until enumeration.

A side effect: enumerating the same IQueryable<T> twice runs the SQL twice. If you need the results more than once, materialize once and reuse the list.

Each call against an unmaterialized IQueryable<T> is a fresh round trip to the database. Call ToListAsync once, use the list as many times as needed.

Where is the main operator. It takes a lambda returning bool and translates the body into a SQL WHERE clause. Chaining multiple Where calls combines the predicates with AND. This is often clearer than building a giant boolean expression with &&.

Generated SQL (SQL Server, EF Core 8):

Three chained predicates, one SQL WHERE clause, one round trip. EF Core normalizes whatever you wrote into a single statement.

Most C# operators inside Where translate cleanly: ==, !=, <, <=, >, >=, &&, ||, !. String methods like Contains, StartsWith, EndsWith, ToLower, and ToUpper translate to LIKE patterns or LOWER/UPPER calls. DateTime arithmetic via AddDays, AddHours, and similar methods translates to DATEADD (or the equivalent on other providers).

What doesn't translate: arbitrary C# methods. If you call your own IsValidCoupon(o) inside a Where, EF can't see inside the method body. In EF Core 3.x and later, the provider throws an InvalidOperationException at runtime rather than silently pulling every row into memory and filtering there. That behavior change (called the "client evaluation restriction") was deliberate: EF used to fall back to in-memory evaluation, which often turned a 5 ms query into a 30-second one without any warning.

The fix is either to rewrite the predicate in terms EF can translate, or to pull the data down first and apply the C# method in memory (which you should do only after narrowing the rows as much as you can in SQL).

Select projects each row into a new shape. With EF, the shape determines what columns get selected from the database. If you project to a DTO with three string properties, the SQL pulls back three columns. If you select the entity itself, every mapped column comes back, including ones you don't use.

Generated SQL for the second query:

Anonymous types are the everyday projection target. They're cheap, they describe themselves, and you can read them right where you're consuming the data. For projections you'll pass around or return from a service layer, use a named DTO so the type has a meaningful name:

The c.Orders.Count inside the projection translates to a SQL COUNT subquery, not a separate round trip. The database does the counting and ships back one number per row.

Selecting whole entities when only a few columns are needed is a common EF performance mistake. A Customer with 30 columns moves 30 columns over the wire and into change tracking. Project to a DTO or anonymous type for read paths and only materialize entities for updates.

Sorting is OrderBy for ascending and OrderByDescending for descending. To break ties on a second column, chain ThenBy or ThenByDescending. EF translates these to a single ORDER BY clause with comma-separated columns.

Generated SQL:

Paging is Skip(n) followed by Take(m). EF translates the pair to OFFSET / FETCH on SQL Server and PostgreSQL, or LIMIT / OFFSET on MySQL and SQLite. The exact syntax varies by provider, but the result is the same: only the requested page of rows comes back.

Generated SQL (SQL Server):

Skip without an OrderBy is technically allowed but produces an undefined row order. Always pair Skip/Take with OrderBy so the page boundaries are deterministic. Without a stable order, the same Skip(20).Take(20) call can return different rows on consecutive runs.

OFFSET n makes the database read and discard n rows on every page. For deep pagination (page 1000 of a million-row table), this gets expensive. Keyset pagination (filtering on the last seen sort key) avoids the cost. The _Performance Optimization_ lesson covers it.

Count, Sum, Average, Min, and Max all translate to their SQL equivalents and return a single scalar value. They materialize immediately, so they're also the trigger for query execution.

Generated SQL for the `SumAsync` call:

EF wraps the SUM in COALESCE so that an empty set returns 0 instead of NULL. That matches the C# return type of SumAsync on decimal, which is non-nullable. For Max over a possibly-empty set, you'll often see the pattern MaxAsync(o => (DateTime?)o.PlacedAt) to make the column nullable, so an empty result returns null instead of throwing.

You can combine an aggregate with Where either as a predicate to CountAsync (shown above) or as a separate Where followed by CountAsync(). EF generates the same SQL either way:

A more interesting query: "top 5 customers by lifetime spend in the last year."

Generated SQL:

The Sum subquery shows up twice (once for the projection, once for the order-by), which is fine for a top-5 query but would be a problem at scale. Performance tuning lives in the _Performance Optimization_ lesson.

By default, EF loads only the entity you queried. Related collections and references stay null (or empty) until you ask for them explicitly. That's not a bug, it's the design: loading every navigation property automatically would pull half the database every time.

Include tells EF to eagerly join in a related entity. For a deeper path, chain ThenInclude. For independent navigation properties, chain multiple Include calls.

That single query brings back the order, its customer, every order item, every product on those items, and the category for each product. EF translates it into a LEFT JOIN chain and reassembles the object graph from the flat result set.

Generated SQL (abbreviated):

This works beautifully for a single order. It scales poorly when you Include multiple collections on the same query, because every combination of rows from each collection appears in the result set. An order with 10 items and 5 reviews returns 50 rows, with the order and customer columns repeated on every row. EF Core 5+ defaults to a "split query" mode for this case in some scenarios. The term for this problem is Cartesian explosion. When the join fan-out gets bad, the network and memory cost of the repeated columns dwarfs the savings of doing one round trip. The _Performance Optimization_ lesson covers when to switch to AsSplitQuery and how to spot the problem.

For multiple independent includes (sibling navigation properties), repeated Include calls work fine:

Each Include on a collection navigation property widens the result set by a factor of that collection's size. Two collection includes multiply. Three collection includes can cube. Be deliberate about what's needed on the screen, and project to a DTO when an entity tree isn't the right shape.

When EF materializes entities from a query, it stores them in the DbContext's change tracker so that calling SaveChanges later can detect modifications. That tracking has a cost: a snapshot of each entity gets stored, equality comparisons run on every property, and the context's memory grows with every loaded row.

For queries where you know you won't mutate the results (showing data on a page, generating a report, exporting to JSON), call AsNoTracking to skip the tracker entirely.

AsNoTracking returns the same shape of results but doesn't add them to the change tracker. For read-heavy paths, the speedup is meaningful: less memory pressure, fewer snapshot allocations, and a slightly faster materialization step.

The _Performance Optimization_ lesson covers tracking versus no-tracking in depth, including the related AsNoTrackingWithIdentityResolution and when each is the right pick. For now, the rule: if the data is read-only, call AsNoTracking and move on.

LINQ to Entities covers maybe 90% of what you need. The remaining 10% (full-text search, vendor-specific operators, queries the translator can't express, hand-tuned SQL for a hot path) is where FromSql and FromSqlInterpolated come in.

FromSqlInterpolated accepts an interpolated string and parameterizes the values automatically. Use it whenever a parameter is involved:

The {status} and {minTotal} placeholders become SQL parameters, not string concatenations, so SQL injection is impossible. EF treats the result as the starting point of a regular LINQ query, which is why the trailing Where, OrderByDescending, and ToListAsync chain works. The translator wraps your raw SQL as a subquery and adds the LINQ operators on top.

FromSql is the lower-level cousin. It takes a FormattableString and treats every interpolated value as a parameter. The behavior is identical to FromSqlInterpolated; the difference is mostly historical. Use FromSqlInterpolated for new code.

A common danger: building a raw SQL string with C# string concatenation and passing it to FromSqlRaw. That bypasses parameterization and opens up SQL injection. Don't do this:

FromSql queries must return all the columns required to materialize the target entity. If your SELECT * is missing a tracked column, EF throws. For projections to arbitrary shapes (not entity types), use a keyless entity type or switch to ADO.NET / Dapper.

Most translation failures fall into a handful of categories. Knowing the patterns helps you spot them before they ship.

Custom C# methods. As mentioned earlier, EF can't see inside your own methods. db.Orders.Where(o => MyRules.IsValid(o)) throws at query time. Either inline the logic into expressions EF can translate, or move the filter to in-memory evaluation after a narrower SQL query has run.

`DateTime.Now` versus `DateTime.UtcNow`. Both translate, but DateTime.UtcNow is usually what you want for queries against UTC-stored timestamps. Confusing the two leads to off-by-timezone bugs that are easy to ship and hard to diagnose.

String formatting and parsing. int.Parse(o.OrderNumber) doesn't translate. Neither does o.PlacedAt.ToString("yyyy-MM"). Either store the data in the form you want to query, or pull it down and format in memory.

Comparing nullable values. o.Customer.MiddleName == "X" does what you'd expect in SQL, but o.Customer.MiddleName.Length == 0 throws if MiddleName is null. Use string.IsNullOrEmpty(o.Customer.MiddleName), which translates to MiddleName IS NULL OR MiddleName = ''.

Group by with complex projections. Simple GroupBy(o => o.Status).Select(g => new { g.Key, Count = g.Count() }) translates fine. More elaborate projections (custom aggregates, accessing items inside the group) hit untranslatable patterns. When a GroupBy won't translate, the error message is usually clear about which part failed.

Method calls on collections of values. o.Items.Where(i => i.Quantity > 1).Sum(i => i.UnitPrice * i.Quantity) translates as a correlated subquery. Method calls inside that sub-expression follow the same translation rules: keep them to operators EF knows about.

The fix for almost all of these is see the generated SQL. EF Core makes that easy with LogTo:

LogTo(Console.WriteLine, LogLevel.Information) prints every SQL statement EF generates, plus the parameters, plus timing. EnableSensitiveDataLogging() adds the actual parameter values to the log (otherwise they're masked as @p0, @p1). In development, both together give you a live view of what's hitting the database. Don't ship sensitive data logging to production; the parameter values often include user data you don't want in your log aggregator.

Sample output for a typical query:

Turning this on for the first time reveals queries that were running silently. Nested includes that fan out, second round trips from calling ToList too early, queries that pull every column when only three are used. Reading the SQL is the fastest way to learn LINQ to Entities.