Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 41 ยท Querying with LINQ

Client vs Server Evaluation, AsNoTracking and Global Filters

Learn which part of an EF Core query runs in SQL and which in C#, how AsEnumerable shifts the boundary, when to use AsNoTracking, and how global query filters implement soft delete.

Every EF Core query is split in two: a part that is translated to SQL and runs in the database (server evaluation) and a part that runs in C# on the rows that came back (client evaluation). Where the line falls decides whether your query downloads ten rows or ten million. This lesson teaches how to see the line, move it on purpose and tune the results with AsNoTracking, plus how global query filters add rules to every query.

The real EF Core cannot run on this site, so queries are read-only and the runnable stand-ins model the boundary with LINQ over lists.

The boundary

C#
var names = await db.Products
    .Where(p => p.Price > 10m)                // server: becomes WHERE
    .OrderBy(p => p.Name)                     // server: ORDER BY
    .Select(p => new { p.Name, p.Price })     // server: column list
    .ToListAsync();                           // <- the boundary: rows arrive here

var labels = names.Select(n => Format(n.Name, n.Price));   // client: plain C#

Everything before the terminal operator is described to the database. Everything after it runs in memory, with the full power of C#. EF Core 3.0 and later enforce one safety rule: filters (Where) and other operators that would change which rows return must be translatable; EF Core throws instead of silently downloading the whole table and filtering in memory (older versions did, a famous performance trap).

Only the final projection may contain untranslatable C#:

C#
// Allowed: the last Select runs on the client after the rows arrive.
var rows = await db.Products.Select(p => new { p.Name, Text = Format(p.Price) }).ToListAsync();

// Throws: a Where cannot call Format.
var bad = await db.Products.Where(p => Format(p.Price) == "$10.00").ToListAsync();

AsEnumerable and ToList move the boundary

AsEnumerable() ends the SQL part without materializing yet. After it, LINQ runs in memory:

C#
var result = db.Products
    .Where(p => p.Price > 10m)            // SQL
    .AsEnumerable()                        // boundary
    .Where(p => IsPopular(p))              // C#: on rows already filtered by price
    .ToList();

This is correct when the SQL part already cut the data down. Put AsEnumerable() too early (on the whole table) and you download everything. Prefer to make the condition translatable, and fall back to client evaluation only after a selective server-side filter.

AsNoTracking

By default, entities are tracked: the context keeps each one and a snapshot of its values so SaveChanges can find changes (see Change Tracking Basics). For read-only queries that is wasted memory and time:

C#
var catalog = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

AsNoTracking returns entities that the context does not track: changes to them are never saved, and the same row loaded twice gives two different objects. Projections (Select into DTOs) are never tracked anyway. Related variants:

  • AsNoTrackingWithIdentityResolution() returns one object per row within one query, useful with Include on graphs with shared references.
  • ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking makes it the context default; opt into tracking with AsTracking().

Rule of thumb: tracking for write paths, no tracking for read paths.

Global query filters

A global query filter is a Where that EF Core adds to every query for an entity type. It is the standard way to implement soft delete and multi-tenancy:

C#
public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool IsDeleted { get; set; }
    public int TenantId { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasQueryFilter(p => !p.IsDeleted && p.TenantId == _tenantId);
}

Now db.Posts.ToListAsync() always appends WHERE NOT IsDeleted AND TenantId = @tenant, including through navigations and Include. The filter can refer to a context field such as _tenantId, which EF Core re-evaluates for each context instance.

To see everything (an admin screen, a restore tool), opt out per query:

C#
var all = await db.Posts.IgnoreQueryFilters().ToListAsync();

EF Core 10 supports named query filters, so you can disable only one of several (IgnoreQueryFilters(["SoftDelete"])) and keep tenant isolation.

Cautions:

  • Required navigations to a filtered entity can hide parents, because a deleted Category may remove the products that reference it. Make the navigation optional or filter both sides.
  • Filters make queries less obvious. Document them, and test with IgnoreQueryFilters.
  • Soft delete means rows stay forever; plan purging and uniqueness (a unique index on Email must be filtered to non-deleted rows).

Where does it run?

Server Where, OrderBy, Select, Count
Boundary ToList or AsEnumerable
Client Any C# afterwards

Filter on the server

1 / 2

Stand-in: how much data crosses the boundary

This stand-in is not EF Core. It counts the rows pulled into "client memory" when the filter runs on the server side versus after AsEnumerable.

C# Stand-in: filter before or after the boundary

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The same 2,000 products match, but one version sent only those, and the other sent a hundred thousand rows to throw most of them away.

Stand-in: a soft-delete filter on every query

C# Stand-in: a global filter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which part of a query may contain C# that EF Core cannot translate?

    EF Core evaluates the top-level projection on the client; filters must be translatable so it never silently downloads everything.
  2. 2. When is AsNoTracking appropriate?

    No-tracking queries skip the snapshot and identity bookkeeping, so reads are cheaper, but changes are not saved.
  3. 3. What does a global query filter do?

    It is the basis for soft delete and multi-tenancy; IgnoreQueryFilters opts out.
  4. 4. Why is calling AsEnumerable() right after db.Products risky?

    Use AsEnumerable only after a selective server-side filter has cut down the rows.

Summary

  • Operators before the terminal call are translated to SQL; code after it runs in C# on the returned rows.
  • Only the final projection may be untranslatable; use AsEnumerable after a selective Where, never on a whole table.
  • AsNoTracking makes read-only queries lighter; tracking belongs on write paths.
  • Global query filters (soft delete, tenants) are added to every query; IgnoreQueryFilters opts out, and navigations need care.

Next: One-to-Many Relationships