Querying

LINQ queries become SQL: what gets translated, what change tracking costs, and how to see the SQL EF Core sends.

Syntaxawait context.Blogs.AsNoTracking().Where(b => b.Rating >= 3).ToListAsync();

A LINQ query over a DbSet is not a result. It is a description of a query, held in an IQueryable<T>. Nothing reaches the database until something asks for the data: ToListAsync, FirstOrDefaultAsync, SingleAsync, CountAsync, AnyAsync. Until then you can keep adding Where and OrderBy clauses, and they all end up in one SQL statement.

Tracking

By default a query that returns entities tracks them: the context keeps a reference to each object and remembers the values it read, so it can work out later what you changed. That costs memory and time. For data you only display, add AsNoTracking() and EF Core hands the objects over without remembering them. A query whose Select builds something other than an entity is never tracked, so AsNoTracking adds nothing there.

Seeing the SQL

ToQueryString() on any IQueryable returns the SQL as text without running it. Read it. EF Core 10 names parameters after your variables, so @rating rather than the older @__rating_0.

What trips people up

EF Core can only translate what the provider understands. Call your own method inside a Where and the query cannot become SQL, so EF Core throws InvalidOperationException saying the LINQ expression "could not be translated". That exception is protection: the alternative is fetching the whole table and filtering in memory. Rewrite the filter so the database can do it, or decide out loud to pull the rows first with AsAsyncEnumerable.

Example

Example · csharp
// dotnet add package Microsoft.EntityFrameworkCore.Sqlite
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();

using var context = new BlogContext(
    new DbContextOptionsBuilder<BlogContext>().UseSqlite(connection).Options);
await context.Database.EnsureCreatedAsync();

context.Blogs.AddRange(
    new Blog { Name = "Ops diary", Rating = 4 },
    new Blog { Name = "SQL notes", Rating = 2 },
    new Blog { Name = "C# notes", Rating = 5 });
await context.SaveChangesAsync();

// Forget the three objects the insert left behind, so the counts below are clear.
context.ChangeTracker.Clear();

List<Blog> tracked = await context.Blogs.Where(b => b.Rating >= 3).ToListAsync();
Console.WriteLine($"rows: {tracked.Count}, tracked: {context.ChangeTracker.Entries().Count()}");

context.ChangeTracker.Clear();

List<Blog> free = await context.Blogs.AsNoTracking().Where(b => b.Rating >= 3).ToListAsync();
Console.WriteLine($"rows: {free.Count}, tracked: {context.ChangeTracker.Entries().Count()}");

public class Blog
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public int Rating { get; set; }
}

public class BlogContext(DbContextOptions<BlogContext> options) : DbContext(options)
{
    public DbSet<Blog> Blogs => Set<Blog>();
}

// Output:
// rows: 2, tracked: 2
// rows: 2, tracked: 0

When to use it

  • Return a filtered, sorted list of rows from an HTTP endpoint without loading the whole table.
  • Cut the memory a read-only report uses by turning off change tracking.
  • Work out why one endpoint is slow by printing the SQL it generates.

More examples

Reading the SQL before you ship the query

Use this whenever a query is slower than expected or returns something surprising. Seeing one SELECT where you expected one SELECT is the quickest way to catch an accidental extra round trip.

Example · csharp
using Microsoft.EntityFrameworkCore;

int minimum = 3;

IQueryable<Blog> query = context.Blogs
    .AsNoTracking()
    .Where(b => b.Rating >= minimum)
    .OrderByDescending(b => b.Rating);

// ToQueryString returns the SQL as text. It does not run the query.
Console.WriteLine(query.ToQueryString());

// On SQLite that prints a statement of this shape:
//
//   SELECT "b"."Id", "b"."Name", "b"."Rating"
//   FROM "Blogs" AS "b"
//   WHERE "b"."Rating" >= @minimum
//   ORDER BY "b"."Rating" DESC
//
// The value of minimum is sent as the parameter @minimum, not pasted into the
// text, so the database can reuse its plan for the next call.

// Only now does anything reach the database.
List<Blog> results = await query.ToListAsync();

A query that cannot be translated, and the fix

This exception is the most common EF Core surprise. The useful question is not how to silence it but which of the two fixes you want: push the work into SQL, or accept the cost of pulling rows and say so in the code.

Example · csharp
using Microsoft.EntityFrameworkCore;

static string Normalise(string url) => url.Trim().ToLowerInvariant();

// This throws InvalidOperationException at run time. The message says the LINQ
// expression could not be translated, because the database has never heard of
// Normalise and EF Core refuses to download the table to apply it.
List<Blog> broken = await context.Blogs
    .Where(b => Normalise(b.Url).Contains("soundscode"))
    .ToListAsync();

// Fix one: express the filter in operations the provider knows.
List<Blog> fixedInSql = await context.Blogs
    .Where(b => b.Url.ToLower().Contains("soundscode"))
    .ToListAsync();

// Fix two: decide deliberately to filter in memory. Only do this where the
// row count is small and bounded, because every row crosses the network.
List<Blog> inMemory = await context.Blogs
    .Where(b => b.Rating >= 4)        // narrow it in SQL first
    .AsAsyncEnumerable()              // from here on, this is plain LINQ in C#
    .Where(b => Normalise(b.Url).Contains("soundscode"))
    .ToListAsync();

One page of results plus a total

This is the shape of almost every list endpoint. The projection keeps unused columns out of the result set, and the tie-break column is what stops rows drifting between pages when ratings are equal.

Example · csharp
using Microsoft.EntityFrameworkCore;

int page = 2;
int pageSize = 20;

// Count runs its own SELECT COUNT(*), which is why paging costs two queries.
int total = await context.Blogs.CountAsync(b => b.Rating >= 3);

// Skip and Take become OFFSET and LIMIT (or FETCH NEXT on SQL Server).
// OrderBy is not optional: without it the database may return any rows at all
// for page 2, and the same row can appear on two pages.
var items = await context.Blogs
    .AsNoTracking()
    .Where(b => b.Rating >= 3)
    .OrderByDescending(b => b.Rating)
    .ThenBy(b => b.Id)                       // tie-break, so the order is total
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(b => new { b.Id, b.Name, b.Rating })   // only the columns needed
    .ToListAsync();

Console.WriteLine($"page {page} of {(total + pageSize - 1) / pageSize}");

Related

dotnet linq filter projectdotnet linq deferreddotnet efcore relationsdotnet paginationdotnet efcore intro

Discussion

  • Be the first to comment on this lesson.