The same LINQ you already know doesn't run in your process against EF Core — it gets read, translated into SQL, and shipped off to the database. That translation step is powerful, and it has real edges.
You already know LINQ — .Where(...), .OrderBy(...), .Select(...) — from querying in-memory lists. Here's the thing that makes EF Core feel almost magical the first time you see it: you write the exact same LINQ against context.Products, and instead of filtering a list in memory, EF Core reads your LINQ expression and generates a SELECT ... WHERE ... SQL statement — filtering happens on the database server, not in your application. Same syntax, completely different execution model underneath. Understanding that difference is the key to writing EF Core queries that are both correct and fast.
In this lesson, you'll learn how DbSet<T>'s IQueryable<T> nature drives LINQ-to-SQL translation, what can and can't be translated, and the classic client-vs-server evaluation gotcha that trips up even experienced developers.
When you write LINQ against a DbSet<T>, you're not writing instructions that run immediately — you're building a description of a query, piece by piece, that EF Core reads and converts into SQL only when you finally ask for results. The C# compiler and EF Core work together to make this translation possible.
The Simple Example lesson mentioned that DbSet<T> implements IQueryable<T> — this is where that fact matters most. IQueryable<T> is different from the IEnumerable<T> you've used for in-memory LINQ: instead of holding delegates (compiled C# code), it builds an expression tree — a data structure representing your query as data, still inspectable, not yet executed. EF Core's LINQ provider walks that expression tree and generates the equivalent SQL for whatever database provider you're using (SQL Server, PostgreSQL, SQLite, and so on).
IQueryable<Product> query = context.Products
.Where(p => p.Price > 20)
.OrderBy(p => p.Name);
// No SQL has run yet — 'query' is an expression tree describing what you want.
List<Product> products = await query.ToListAsync();
// NOW EF Core translates the tree to SQL and executes it.Imagine EF Core had no translation ability at all, and context.Products behaved like a regular IEnumerable<Product>. To find products over $20, you'd have to load every single row in the Products table into memory first, and only then filter with LINQ in your application process. For a table with 50 rows, that's wasteful. For a table with 50 million rows, it's not just slow — it's often simply impossible, and it throws away one of the main reasons to use a database at all: letting the database engine, which is built and indexed for exactly this, do the filtering.
IQueryable<T> and expression trees let EF Core see your query's intent before any data moves, and rewrite it as SQL that runs where the data already lives. The database applies the WHERE, the ORDER BY, even the pagination, and only the rows you actually asked for travel back across the network. You keep writing familiar C# LINQ; EF Core does the (considerable) work of making sure it runs efficiently, server-side.
IQueryable<Product> q = context.Products.Where(p => p.Price > 20);List<Product> products = await q.ToListAsync();A filter, a sort, and a projection — all translated into one SQL statement:
List<string> names = await context.Products
.Where(p => p.Price > 20 && p.Stock > 0)
.OrderBy(p => p.Name)
.Select(p => p.Name)
.ToListAsync();This becomes roughly:
SELECT p.Name
FROM Products AS p
WHERE p.Price > @p0 AND p.Stock > 0
ORDER BY p.NameNotice .Select(p => p.Name) even changed what column the SQL asks for — EF Core doesn't fetch every column and then discard most of them in C#; the projection is translated too, so only Name is ever selected from the database.
A product search endpoint needs to filter by a normalized, business-specific comparison — say, matching a search term against a product's name using a custom C# helper method that trims and normalizes text a particular way:
public static class SearchHelpers
{
public static bool MatchesSearch(string productName, string term)
=> productName.Trim().Contains(term.Trim(), StringComparison.OrdinalIgnoreCase);
}
// This throws InvalidOperationException at runtime:
List<Product> results = await context.Products
.Where(p => SearchHelpers.MatchesSearch(p.Name, searchTerm))
.ToListAsync();EF Core's SQL translator has no idea what SearchHelpers.MatchesSearch does — it's arbitrary C# code, not an expression it recognizes a SQL equivalent for. Since EF Core 3.0, it does not silently fall back to pulling every product into memory and running your method there — it throws, loudly, at query execution time, specifically so this mistake doesn't quietly turn into "load the entire table on every search."
The fix is to either express the same logic using translatable EF Core / LINQ methods directly:
// Translatable — EF Core maps .Contains() to SQL LIKE
List<Product> results = await context.Products
.Where(p => p.Name.Contains(searchTerm))
.ToListAsync();...or, when the custom logic genuinely can't be expressed in SQL, deliberately switch to client-side evaluation by materializing first, then filtering in memory — accepting the cost of pulling more rows across the wire:
// Deliberate, visible client-side filtering — you chose this, EF Core didn't hide it from you
List<Product> candidates = await context.Products
.Where(p => p.Category == "Electronics") // narrow down server-side first!
.ToListAsync();
List<Product> results = candidates
.Where(p => SearchHelpers.MatchesSearch(p.Name, searchTerm))
.ToList();The key discipline in that last version: narrow the result set as much as possible with translatable filters before materializing, so the untranslatable logic only runs against a small, already-filtered set of rows — not the entire table.
Writing LINQ against a DbSet<T> is like speaking to a chef through a translator who only knows certain kitchen terms. Say "no onions, extra spicy, medium rare" — the translator knows exactly how to relay that, and the chef prepares precisely that dish. Say something the translator has no vocabulary for — an idiom, a joke, a reference only you'd understand — and the translator can't just guess. A good translator stops and says "I can't convey that" rather than passing along something wrong or, worse, just making up a dish and hoping it's close enough.
That's exactly EF Core's LINQ provider: it translates what it recognizes, faithfully, into SQL — and rather than silently mistranslating (or, worse, secretly running the whole request themselves after asking the chef for everything in the kitchen), it tells you plainly when something can't be translated, so you can rephrase it in terms it understands.
They share a lot of the same LINQ method names, which makes this easy to assume — but they behave completely differently underneath. Chain LINQ onto an IQueryable<T> and it stays translatable, potentially all the way to SQL. Call .AsEnumerable() or .ToList() partway through, and everything after that point switches to plain in-memory LINQ over objects already pulled into your process — no further translation happens, for better (you can now call any C# method) or worse (you've likely already paid for pulling more data across the wire than you needed).
This is the sharpest edge of LINQ-to-SQL translation: context.Products.Where(p => SearchHelpers.MatchesSearch(p.Name, term)) compiles perfectly fine — the C# compiler has no idea EF Core can't translate that method call. The failure only shows up at runtime, when the query actually executes and the provider tries (and fails) to translate it. This is exactly why testing real query execution, not just compilation, matters for EF Core code.
context.Products.ToList().Where(p => p.Price > 20) — this pulls the entire Products table into memory first, then filters it in your process. The .Where(...) never gets a chance to be translated to SQL at all, because it's running against an in-memory List<Product>, not the IQueryable<Product> anymore. Chain .Where(...) before .ToListAsync(), so the filter is translated and applied on the database.
Calling a custom static helper or a computed property inside .Where(...), as shown in the real-world example, and being surprised by a runtime InvalidOperationException. Either rewrite the logic using translatable expressions and built-in methods EF Core recognizes, or deliberately narrow the query first and finish the untranslatable part after materializing — the second real-world example above shows both.
Building a query into a variable, then reusing that same IQueryable<T> variable multiple times expecting it to run once and cache results — each materialization (.ToListAsync(), a foreach, etc.) re-executes the query against the database again. Materialize once into a concrete list if you need to reuse the results without hitting the database again — exactly the deferred-execution discipline from the earlier LINQ lessons, still fully in effect here.
| Situation | Approach |
|---|---|
| Filtering, sorting, projecting, paging on columns/expressions EF Core recognizes | Keep it all in the IQueryable<T> chain — let it translate to SQL |
| Logic that genuinely can't be expressed in SQL (complex custom formatting, calling an external library) | Narrow with translatable filters first, materialize, then finish with plain LINQ over the (small) in-memory result |
| Unsure whether something translates | Try it and check — a runtime InvalidOperationException tells you immediately, rather than silently misbehaving |
You've seen how LINQ against EF Core gets translated to SQL, and where that translation breaks down. Let's confirm the reasoning stuck.
1. Why does context.Products.Where(p => p.Price > 20) get translated to SQL, while a regular List<Product>.Where(p => p.Price > 20) doesn't?
Correct: B
Why B is correct: The distinction is IQueryable<T> vs IEnumerable<T>. Against IQueryable<T>, the compiler builds an Expression object (data describing the query) that EF Core's provider can inspect and translate to SQL. Against IEnumerable<T>, the same lambda compiles directly to executable IL (a Func delegate) that just runs immediately in memory — there's nothing to translate.
Why A is incorrect: List<T> fully supports Where — it's a standard LINQ-to-Objects operator; the difference is what happens underneath, not whether it's supported.
Why C is incorrect: Translation happens for Where alone — OrderBy isn't required to trigger it.
Why D is incorrect: EF Core has no such mechanism — an in-memory List<Product> stays entirely in memory and is never converted into anything database-related.
Reinforcement: The IQueryable<T> vs IEnumerable<T> distinction is exactly what makes LINQ-to-SQL translation possible in the first place.
2. A developer writes context.Products.Where(p => SearchHelpers.MatchesSearch(p.Name, term)).ToListAsync(), where MatchesSearch is a custom static C# method. What happens?
Correct: B
Why B is correct: Since EF Core 3.0, a query that cannot be translated (such as one calling an unrecognized custom method inside a filter) throws an InvalidOperationException at runtime rather than silently falling back to loading everything into memory.
Why A is incorrect: This describes the older, pre-3.0 EF Core behavior, which current EF Core deliberately no longer does — precisely because it caused accidental full-table loads that were hard to notice.
Why C is incorrect: Translatability can only be determined by actually walking the expression tree at runtime against the specific provider — the C# compiler has no way to know this ahead of time, so the code compiles fine and fails only when run.
Why D is incorrect: EF Core has no general mechanism to inline arbitrary C# method bodies into SQL — only a known, recognized set of expressions and methods are translatable.
Reinforcement: A query can compile perfectly and still fail at runtime if it contains logic EF Core's SQL translator doesn't recognize — always verify real query execution, not just compilation.
3. Which rewrite correctly fixes the untranslatable query from the previous question, while still minimizing the amount of data pulled from the database?
Correct: B
Why B is correct: This narrows the result set as much as possible with a translatable filter (Category == "Electronics") that runs on the database, then finishes the untranslatable logic in memory — but only against the small, already-filtered set of rows, not the entire table.
Why A is incorrect: This works without throwing, but defeats the purpose — it pulls every row in Products across the network before filtering anything, exactly the inefficiency this lesson is about avoiding.
Why C is incorrect: Swallowing the exception doesn't fix the translation problem — it just hides a real error and leaves the query broken.
Why D is incorrect: Renaming a method has no effect on translatability — EF Core recognizes specific well-known expressions and methods, not names.
Reinforcement: When something can't be translated, narrow with server-side filters first, then apply the untranslatable logic to the smallest possible in-memory set — never to the whole table.
You now understand how EF Core turns LINQ into SQL, and where that translation has real edges. Next up: what happens to the entities your queries return — tracking vs no-tracking.
dotnetmadeeasy.com — Learn C# and .NET, the right way.