Out-of-Process Queries Study Portal

A: Querying data that is not in your process's memory, held by another program, usually on another machine: a relational database, a document store, a remote API. Every query crosses a process and usually a network boundary, which has two consequences: (1) your C# code cannot run where the data is, so the query must be translated into the other system's language; (2) each round trip costs milliseconds, not nanoseconds, so how many queries you send and how much data they return matters far more than CPU work in C#.

A:

The same query syntax compiles to either; the static type of the source decides which operators the compiler binds to.

A: Three things: an Expression (the tree describing the query so far), an ElementType, and a Provider (IQueryProvider). Each Queryable operator calls source.Provider.CreateQuery<T>(newExpression), where the new expression is a MethodCallExpression wrapping the previous one: for example Queryable.Where(<previous query>, o => o.Total > 100). No database call happens. When you enumerate (or call ToList, First, Count…), the provider's Execute walks the whole tree, translates it, runs it, and turns the results into objects.

IQueryable<Order> q = db.Orders;                 // Expression: the DbSet itself
q = q.Where(o => o.Total > 100);                 // Expression: Where(DbSet, o => o.Total > 100)
q = q.OrderByDescending(o => o.CreatedAt);       // Expression: OrderByDescending(Where(...), ...)
var page = q.Select(o => new { o.Id, o.Total })  // Expression: Select(OrderByDescending(...), ...)
            .Take(20)
            .ToList();                            // ONE SQL statement executed here

// SELECT TOP(20) o.Id, o.Total FROM Orders o
// WHERE o.Total > 100.0 ORDER BY o.CreatedAt DESC

A:

A: In an expression tree a captured local appears as a member access on the closure object (value(DisplayClass).minTotal), not as a constant. EF Core recognizes that pattern and turns it into a DbParameter (@__minTotal_0). That is why changing minTotal does not produce a new SQL string or a new query plan, and why this is safe from SQL injection. A literal written in the lambda (o.Total > 100) is inlined as a constant.

A:

// All in SQL
var a = db.Orders.Where(o => o.Total > 100).Select(o => o.Id).ToList();

// Filter in SQL, formatting in C# (deliberate and fine)
var b = db.Orders.Where(o => o.Total > 100)
                 .Select(o => new { o.Id, o.CreatedAt })
                 .AsEnumerable()
                 .Select(o => $"{o.Id} on {o.CreatedAt:d}")
                 .ToList();

// Whole table loaded, then filtered in memory
IEnumerable<Order> all = db.Orders;
var c = all.Where(o => o.Total > 100).ToList();

A: Since EF Core 3.0 it throws InvalidOperationException: The LINQ expression … could not be translated instead of silently pulling the data into memory (EF Core 1-2 did "client evaluation", which caused hidden full-table loads). Typical causes: calling your own C# method inside Where (o => IsVip(o)), string or date functions the provider does not map, or complex logic on navigation collections. Fix it by expressing the logic with translatable operations, mapping a database function ([DbFunction]), or moving that part after AsEnumerable() once the result set is small. EF Core still allows client evaluation in the final Select projection, because that does not change which rows are fetched.

A: Loading a list with one query and then running one more query per row to get related data: 1 + N round trips. It happens with lazy loading (touching order.Customer in a loop) or with a query inside a foreach. With 500 orders and 2 ms per round trip that is a full second.

// N+1
foreach (var o in db.Orders.ToList())
    Console.WriteLine(db.Customers.Single(c => c.Id == o.CustomerId).Name);

// One query with a JOIN
var rows = db.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToList();

Fixes: project the related fields in Select, use Include for entity graphs, or load the related set in one query (WHERE Id IN (...)) and join in memory.

A: A projection fetches only the columns you name, avoids change-tracking overhead, and lets the database use covering indexes. Loading full entities to show three fields transfers every column, allocates full objects and, if tracked, snapshots them. For read-only queries use projections, or at least AsNoTracking().

A: Each enumeration re-executes the query: two round trips, and possibly different results if the data changed in between. var q = db.Orders.Where(…); if (q.Any()) foreach (var o in q) … runs two SQL statements. Materialize once with ToList() when you need the results more than once.

A: Any(). It translates to EXISTS (…), which can stop at the first matching row; Count() makes the database count every match.

A: Include-ing two collections (Include(o => o.Lines).Include(o => o.Payments)) produces one SQL JOIN whose row count is lines × payments per order, duplicating data massively. AsSplitQuery() makes EF Core send one query per collection instead, trading one round trip for several smaller result sets.

A: db.Orders.Where(o => ids.Contains(o.Id)) becomes WHERE Id IN (…). Older EF Core versions inlined every value, producing a new SQL string (and plan) for every list size. EF Core 8 sends the list as a single JSON parameter (OPENJSON on SQL Server), so the SQL stays stable. Very large lists are still better handled with a temporary table or a join.

A: It depends on how you consume it. foreach over the IQueryable (or AsAsyncEnumerable()) streams: rows are read from the DbDataReader one at a time and each object can be collected once you are done with it (unless the change tracker keeps it). ToList() buffers: every row is materialized and held in a list at once. For exports of millions of rows use streaming plus AsNoTracking(), or you will hold the whole result set, and a tracking snapshot of each entity, in memory.

A: The expression tree nodes (one heap object per node, rebuilt every time the query code runs), the closure object for captured variables, EF Core's parameter values, the SQL command, and the materialized result objects. After the first run, translation is skipped thanks to the query cache. For very hot queries EF.CompileQuery / EF.CompileAsyncQuery pre-compile the query into a delegate so even the tree-building and cache lookup are skipped.

A: Call query.ToQueryString() (EF Core 5+), enable logging (optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information) or the Microsoft.EntityFrameworkCore.Database.Command log category), or use a database profiler. Reading the SQL is the only reliable way to know whether a filter ran in the database or in memory.

A: EF Core (SQL Server, PostgreSQL, SQLite, Cosmos DB), the MongoDB C# driver (AsQueryable() → aggregation pipeline), OData clients (→ URL query options), Azure Table/Cosmos SDKs, and legacy LINQ to SQL. All follow the same pattern: expression tree in, native query out, and each supports a different subset of C#.

A: It is a trade-off. IQueryable<T> lets callers compose filters and paging that still run in SQL, but it leaks persistence concerns and allows untranslatable or expensive queries to be built anywhere. IEnumerable<T> (or IReadOnlyList<T>) returned from a materialized query is predictable, but any extra filtering by callers happens in memory. Common practice: repositories or query handlers take the criteria as parameters (or specification objects built from expressions) and return materialized lists or DTOs.

A: ToList() executes SELECT * FROM Orders and loads every row; the Where then runs in memory. Put Where before ToList().

A: You can: EF Core evaluates expressions that do not depend on the row (such as DateTime.Now.AddDays(-7)) and sends the result as a parameter, or translates it to the database's own function (for example GETDATE()). What you cannot do is call arbitrary C# on row values, such as o => MyHelper.Score(o) > 5.