LINQ isn't one language
The same C# syntax — .Where(x => x.IsActive) — means something completely different depending on what type it's called on. Against a List<T>, that lambda compiles to a real delegate and runs as compiled IL, one item at a time, in your process. Against an EF Core DbSet<T> (an IQueryable<T>), the same lambda compiles to an expression tree — a data structure describing the lambda, not executable code — that EF Core's query provider walks, translates to SQL, and sends to the database. Same keyword, same syntax highlighting, entirely different execution model. Most LINQ bugs I've debugged in production traced back to a developer — often me — forgetting which side of that line a particular query was on.
Expression trees: the mechanism that makes translation possible
This is the part that explains everything else in this post. When a method takes Func<T,bool>, the compiler emits a delegate — compiled code you can call directly. When a method takes Expression<Func<T,bool>> instead — which is exactly what IQueryable<T>'s Where overload takes — the compiler emits a tree of objects representing the lambda's structure: a BinaryExpression for ==, a MemberExpression for x.IsActive, and so on. EF Core's LINQ provider inspects that tree, matches recognizable shapes against its own SQL-generation rules, and produces a SQL command string. It is not a compiler or a general-purpose interpreter — it recognizes patterns it knows how to translate and has explicit rules for each one.
This is exactly the design of the LINQ to SQL Converter on this site, just simplified: it tokenizes and parses the LINQ text itself (no expression trees involved, since it isn't running against a real compiled query), matches recognized method calls, and emits SQL for what it recognizes — while explicitly listing anything it doesn't. EF Core's real translation pipeline is a vastly more complete version of the same idea, built over years against a live schema with full type information.
The N+1 query: lazy loading's oldest trick
Here's a fact that surprises developers coming from EF6 or other ORMs: EF Core does not lazy-load navigation properties by default. Calling order.Customer.Name after loading an Order without eager-loading throws a NullReferenceException on Customer, not a silent extra query — unless you've explicitly opted into lazy loading with the Microsoft.EntityFrameworkCore.Proxies package, called UseLazyLoadingProxies() on your DbContextOptionsBuilder, and marked every navigation property virtual.
If your team did opt in — often to make an existing EF6 codebase "just work" during a migration — the N+1 problem shows up exactly where you'd expect: a loop over 500 orders, each access to order.Customer silently issuing its own round-trip, turning one query into 501. The fix isn't clever code, it's .Include(o => o.Customer) in the original query, or projecting only the columns you need with .Select(...) so there's no navigation property to lazily touch in the first place.
Client evaluation: the trap that used to be silent
Before EF Core 3.0, if part of your query couldn't be translated to SQL — a call to a custom C# method, a regex match, string formatting the provider didn't recognize — EF Core would quietly pull the untranslatable part into memory and finish evaluating it there. It worked, until the untranslated part sat before a filter, meaning the whole table came back over the wire before being filtered in your process. Nobody was warned; the query just got slower as the table grew.
Since EF Core 3.0, that silent fallback is mostly gone. A query with an untranslatable expression anywhere except the final top-level projection throws an InvalidOperationException at runtime, naming the exact sub-expression it couldn't translate. It's a better failure mode — you find out at test time, not six months later when the table crosses a size threshold — but it does mean queries that used to "just work" on an older EF Core mental model now need rewriting, usually by moving the non-translatable logic into .AsEnumerable() explicitly after the parts that can run in SQL.
Contains() is not one thing
This is the one that catches people most often, because the method name is identical and the intent looks identical: filtering "where something is in a set." But which set determines the SQL:
// A local collection on the left of Contains → SQL IN
var activeIds = new[] { 4, 7, 12 };
var orders = db.Orders.Where(o => activeIds.Contains(o.Id));
// SQL: WHERE [Id] IN (4, 7, 12)
// A string property's Contains → SQL LIKE
var orders2 = db.Orders.Where(o => o.CustomerName.Contains("Acme"));
// SQL: WHERE [CustomerName] LIKE '%Acme%'The first form — collection.Contains(entityProperty) — becomes a SQL IN list, because EF Core recognizes "is this column's value one of these known values." The second — stringProperty.Contains(literal) — becomes LIKE '%...%', a substring match, because that's what string.Contains means in C#. Try the LINQ to SQL Converter against both forms side by side; watching the same method name produce two structurally different WHERE clauses is the fastest way to make the distinction stick.
Where EF Core still has surprises
EF Core has steadily improved how it handles the IN-list case above. In earlier versions, a collection passed to Contains got inlined as a literal value list, which meant the generated SQL string was different for every distinct collection size — a query with 3 IDs and one with 4 IDs produced two different SQL strings, defeating SQL Server's plan cache under load.
More recent EF Core versions changed this so the whole collection can be passed as a single parameter instead of a growing list of literals, avoiding a new query plan per distinct collection size. If you're seeing plan cache bloat correlated with Contains queries against variable-length collections, it's worth checking your specific EF Core version's release notes for this exact improvement before assuming it's unavoidable.
Read the SQL. Always.
Every trap above has the same fix: look at the actual SQL EF Core generated, don't assume from the LINQ. Two ways to do that in a real project — call optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information) when configuring your DbContext to print every generated command to your console during development, or add .EnableSensitiveDataLogging() temporarily to see parameter values inline (never leave that on in production — it logs literal data values). Once you've got the raw SQL out of the log, running it through the SQL Formatter turns a single unreadable line into something you can actually scan for a missing WHERE clause or an unexpected join. For a quick sanity check on an isolated query shape without spinning up a whole DbContext, paste the LINQ into the LINQ to SQL Converter — it won't replace EF Core's real provider, but it's fast enough to check "does this look like what I expect" before running the real thing against a database.
The team behind the SQL Formatter incident found a missing WHERE clause specifically because they were looking at generated SQL before deploying, not trusting that the LINQ read correctly meant the SQL would too. Once you've scaffolded entities from a real schema — see CREATE TABLE to EF Core Entity: Scaffolding Done Right, which also covers how the SQL Table to C# Entity Generator maps columns to C# types — this is the habit that catches the gap between what you meant and what the provider actually sent.