LINQ Queries with EF Core: SQL Translation, Client Evaluation and the IQueryable Trap

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: How Many Queries Does This Code Run?

var query = db.Books.Where(b => b.PublishedYear < 1970);
Console.WriteLine("query defined");

var count = query.Count();
var list = query.ToList();

Three lines of LINQ. Does the first line go to the database? How many SQL commands run in total?

The answer is two, and the first line runs none of them. The console shows "query defined" first, then two separate commands follow:

query defined
info: 4.11.2026 00:59:21.068 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM "Books" AS "b"
      WHERE "b"."PublishedYear" < 1970
info: 4.11.2026 00:59:21.076 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "b"."Id", "b"."AuthorId", "b"."Pages", "b"."Price", "b"."PublishedYear", "b"."Title"
      FROM "Books" AS "b"
      WHERE "b"."PublishedYear" < 1970

The query variable is not a result, it is a recipe. Think of an order ticket in a restaurant: writing the ticket does not start the kitchen; the kitchen only starts when the ticket goes in. Every call that actually asks for the result, such as Count(), ToList(), First() or foreach, sends the ticket to the kitchen. Use the same recipe twice and it gets cooked twice. In this post we will see how that recipe turns into SQL, which part of it runs in the database and which part in memory, and where that distinction gets expensive.

How Does a LINQ Query Become SQL?

The model in this post is the Bookstore model from the earlier posts, shaped for querying. We seed the data with HasData from the previous post, and to see the SQL we use LogTo to log only the executed commands:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace Bookstore;

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string? Country { get; set; }
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = null!;
    public decimal Price { get; set; }
    public int Pages { get; set; }
    public int PublishedYear { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class BookstoreContext : DbContext
{
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .UseSqlite("Data Source=bookstore.db")
            .LogTo(Console.WriteLine, new[] { RelationalEventId.CommandExecuted, CoreEventId.RowLimitingOperationWithoutOrderByWarning });

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>().HasData(
            new Author { Id = 1, Name = "Ursula K. Le Guin", Country = "USA" },
            new Author { Id = 2, Name = "Isaac Asimov", Country = "USA" },
            new Author { Id = 3, Name = "Stanislaw Lem", Country = "Poland" });

        modelBuilder.Entity<Book>().HasData(
            new Book { Id = 1, Title = "A Wizard of Earthsea", Price = 12.50m, Pages = 183, PublishedYear = 1968, AuthorId = 1 },
            new Book { Id = 2, Title = "The Left Hand of Darkness", Price = 14.00m, Pages = 304, PublishedYear = 1969, AuthorId = 1 },
            new Book { Id = 3, Title = "The Dispossessed", Price = 15.00m, Pages = 387, PublishedYear = 1974, AuthorId = 1 },
            new Book { Id = 4, Title = "Foundation", Price = 11.00m, Pages = 255, PublishedYear = 1951, AuthorId = 2 },
            new Book { Id = 5, Title = "I, Robot", Price = 10.00m, Pages = 253, PublishedYear = 1950, AuthorId = 2 },
            new Book { Id = 6, Title = "Solaris", Price = 13.00m, Pages = 204, PublishedYear = 1961, AuthorId = 3 });
    }
}

db.Books is a DbSet<Book>, which is an IQueryable<Book>. A Where(b => b.PublishedYear < 1970) written on an IQueryable is not compiled into a delegate; the C# compiler turns it into an expression tree, a data structure that says "is the PublishedYear property less than 1970". When the query is about to run, EF Core walks this tree, translates every node into the provider's SQL dialect and sends the command.

You do not have to read logs every time. ToQueryString(), introduced in EF Core 5, gives you the SQL a query would produce without running it:

var minYear = 1960;
var query = db.Books
    .Where(b => b.PublishedYear >= minYear && b.Title.Contains("The"))
    .OrderBy(b => b.Title);

Console.WriteLine(query.ToQueryString());

.param set @__minYear_0 1960

SELECT "b"."Id", "b"."AuthorId", "b"."Pages", "b"."Price", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE ("b"."PublishedYear" >= @__minYear_0) AND (('The' = '') OR (instr("b"."Title", 'The') > 0))
ORDER BY "b"."Title"

Notice two things. The "The" you wrote as a constant was embedded in the SQL as a value, but minYear, a local variable, became a parameter. EF Core turns every value coming from outside the lambda into a parameter; this protects against SQL injection and lets the same query plan be reused for different minYear values. Second, string.Contains was translated to the instr function on SQLite. EF Core 10 names the parameter @minYear in the same query and skips the ('The' = '') check; the logic is the same.

Filtering: SQL Equivalents of Where

Most of the expressions you are used to in C# have a direct SQL equivalent:

var ids = new[] { 1, 4, 6 };
var q1 = db.Books.Where(b => ids.Contains(b.Id)).Select(b => b.Title);
var q2 = db.Books.Where(b => b.Title.StartsWith("The") || b.Pages > 300).Select(b => b.Title);
var q3 = db.Authors.Where(a => a.Country == null).Select(a => a.Name);

The SQL generated by EF Core 6:

SELECT "b"."Title"
FROM "Books" AS "b"
WHERE "b"."Id" IN (1, 4, 6)

SELECT "b"."Title"
FROM "Books" AS "b"
WHERE ("b"."Title" LIKE 'The%') OR ("b"."Pages" > 300)

SELECT "a"."Name"
FROM "Authors" AS "a"
WHERE "a"."Country" IS NULL

A collection's Contains became IN, StartsWith became LIKE 'The%', and the == null comparison was correctly translated to IS NULL. In SQL, Country = NULL is never true; EF Core thinks about it for you to preserve C# null semantics. On EF Core 10 the first query looks different: instead of embedding the array values in the SQL, it turns them into separate parameters, IN (@ids1, @ids2, @ids3). We will talk about this in the EF Core 10 post.

Sorting and Paging

Paging is done with Skip and Take. Let us ask for the third page with two books per page:

var page = 3;
var size = 2;
var titles = db.Books
    .OrderBy(b => b.PublishedYear)
    .ThenBy(b => b.Id)
    .Skip((page - 1) * size)
    .Take(size)
    .Select(b => b.Title)
    .ToList();

SELECT "b"."Title"
FROM "Books" AS "b"
ORDER BY "b"."PublishedYear", "b"."Id"
LIMIT @__p_1 OFFSET @__p_0

ThenBy(b => b.Id) may look unnecessary, but it is not. If two books had been published in the same year, the database would not be obliged to return them in the same order every time, and a book could show up on two pages or on none. Always end the ordering with a unique column.

If you forget OrderBy altogether, EF Core warns you:

warn: 4.11.2026 00:59:21.107 CoreEventId.RowLimitingOperationWithoutOrderByWarning[10102] (Microsoft.EntityFrameworkCore.Query) 
      The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results. If the 'Distinct' operator is used after 'OrderBy', then make sure to use the 'OrderBy' operator after 'Distinct' as the ordering would otherwise get erased.

You see this warning at the default log level too; if you see it in an application's logs, there is paging with an undefined order somewhere.

Projection: Fetch Only What You Need

Most queries so far fetched every column. If a list screen only needs the book's title, the author's name and the year, ask for just those:

var rows = db.Books
    .Where(b => b.Author.Country == "USA")
    .OrderBy(b => b.PublishedYear)
    .Select(b => new { b.Title, AuthorName = b.Author.Name, b.PublishedYear })
    .ToList();

SELECT "b"."Title", "a"."Name" AS "AuthorName", "b"."PublishedYear"
FROM "Books" AS "b"
INNER JOIN "Authors" AS "a" ON "b"."AuthorId" = "a"."Id"
WHERE "a"."Country" = 'USA'
ORDER BY "b"."PublishedYear"

1950 I, Robot - Isaac Asimov
1951 Foundation - Isaac Asimov
1968 A Wizard of Earthsea - Ursula K. Le Guin
1969 The Left Hand of Darkness - Ursula K. Le Guin
1974 The Dispossessed - Ursula K. Le Guin

We wrote b.Author.Country and b.Author.Name without calling Include; EF Core saw the navigation and added the JOIN itself. The selected columns dropped to three. You can use a DTO class instead of an anonymous type; in the ASP.NET Core Web API series we will produce the models returned from endpoints exactly this way. Objects returned as anonymous types or DTOs are not tracked by the change tracker, which makes them cheap for read-only screens. We will get to tracking two posts from now.

A Single Record: First, Single and Find

There are three common ways to ask for one record, and each produces different SQL:

var first = db.Books.Where(b => b.AuthorId == 2).OrderBy(b => b.Id).First();
var single = db.Books.Single(b => b.Id == 4);
var found = db.Books.Find(4);

Console.WriteLine($"{first.Title} {single.Title} {ReferenceEquals(single, found)}");

SELECT "b"."Id", "b"."AuthorId", "b"."Pages", "b"."Price", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."AuthorId" = 2
ORDER BY "b"."Id"
LIMIT 1

SELECT "b"."Id", "b"."AuthorId", "b"."Pages", "b"."Price", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."Id" = 4
LIMIT 2

Foundation Foundation True

First asks for LIMIT 1. Single asks for LIMIT 2, because its job includes answering "is there more than one record"; if a second row comes back, it throws. There is no SQL at all for the third call: Find looks at the change tracker first, and since book 4 was already loaded by Single, it returns the same object. The True from ReferenceEquals proves it. When no record is found, First and Single throw, while FirstOrDefault, SingleOrDefault and Find return null.

The Client Evaluation Rule

Not every C# expression has a SQL equivalent. Think of a method of your own:

public static class Formatting
{
    public static string Label(string title, int year) => $"{title} ({year})";
}

Using this method in the final Select of a query is allowed:

var labels = db.Books
    .OrderBy(b => b.Id)
    .Select(b => Formatting.Label(b.Title, b.PublishedYear))
    .Take(2)
    .ToList();

SELECT "b"."Title", "b"."PublishedYear"
FROM "Books" AS "b"
ORDER BY "b"."Id"
LIMIT @__p_0

A Wizard of Earthsea (1968) | The Left Hand of Darkness (1969)

Knowing it cannot translate the method, EF Core fetched the two columns the method needs and called Label in memory as each row arrived. This is called client evaluation, and since EF Core 3.0 it only happens automatically in the top-level projection. Put the same method into Where and things change:

var matches = db.Books.Where(b => Formatting.Label(b.Title, b.PublishedYear).Length > 20).ToList();

System.InvalidOperationException: The LINQ expression 'DbSet<Book>()
    .Where(b => Formatting.Label(
        title: b.Title, 
        year: b.PublishedYear).Length > 20)' could not be translated. Additional information: Translation of method 'Formatting.Label' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'Formatting.Label' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

To apply this filter in memory, EF Core would have to fetch the whole table. EF Core 2.x did that while only logging a warning, and queries nobody noticed on small test databases pulled millions of rows in production. With 3.0 the rule changed: a filter is either translated to SQL or you get an exception. We will tell the story of that change in the post on migrating old projects.

The error message also shows the way out. If you really need to filter in memory, do it on purpose, and narrow the data in the database as much as you can first:

var titles = db.Books
    .Where(b => b.PublishedYear < 1970)
    .AsEnumerable()
    .Where(b => Formatting.Label(b.Title, b.PublishedYear).Length > 20)
    .Select(b => b.Title)
    .ToList();

SELECT "b"."Id", "b"."AuthorId", "b"."Pages", "b"."Price", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."PublishedYear" < 1970

Everything above the AsEnumerable() line belongs to SQL, everything below it to C#. You drew the line, and you know how much data crosses it.

IQueryable or IEnumerable?

The same line sometimes gets drawn without you noticing. Look at these two helper methods:

public static class BookQueries
{
    public static IEnumerable<Book> All(BookstoreContext db) => db.Books;
    public static IQueryable<Book> AllQueryable(BookstoreContext db) => db.Books;
}

Both return db.Books; the only difference is the return type. Let us apply the same filter to both:

IEnumerable<Book> books = BookQueries.All(db);
var old = books.Where(b => b.PublishedYear < 1955).Select(b => b.Title).ToList();

SELECT "b"."Id", "b"."AuthorId", "b"."Pages", "b"."Price", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"

IQueryable<Book> books = BookQueries.AllQueryable(db);
var old = books.Where(b => b.PublishedYear < 1955).Select(b => b.Title).ToList();

SELECT "b"."Title"
FROM "Books" AS "b"
WHERE "b"."PublishedYear" < 1955

Both return "Foundation, I, Robot". But the first has no WHERE; the whole table came over with every column and the filter ran in memory. The reason is C# method resolution. When the variable's type is IEnumerable<Book>, the compiler picks Enumerable.Where, and the lambda becomes an ordinary delegate instead of an expression tree. EF Core never sees the filter. No error, no warning; with six books nobody notices, with six million books the server goes down. That is why you should watch the return type of methods that return queries in a repository or service layer. We will come back to this debate in the architecture post.

Common Pitfalls

Sorting decimal on SQLite. SQLite has no real decimal type; EF Core stores Price as TEXT. For comparisons, EF Core 6 uses its own ef_compare function (WHERE ef_compare("b"."Price", '12.0') < 0), but sorting does not work:

var ordered = db.Books.OrderBy(b => b.Price).Select(b => b.Title).ToList();

System.NotSupportedException: SQLite does not support expressions of type 'decimal' in ORDER BY clauses. Convert the values to a supported type, or use LINQ to Objects to order the results on the client side.

On EF Core 6, writing OrderBy(b => (double)b.Price) goes to SQL as ORDER BY CAST("b"."Price" AS REAL) and works. On EF Core 10, the same OrderBy(b => b.Price) query works with COLLATE EF_DECIMAL; support for Sum and Average had arrived in EF Core 9. SQL Server has none of these problems.

Case sensitivity. On SQLite, b.Title.Contains("the") is translated with instr, so it is case-sensitive and does not find "The Left Hand of Darkness". b.Title == "foundation" does not match on SQLite either. The same queries give different results on SQL Server, whose default collation is case-insensitive. Even when the LINQ expression is the same, the database's collation decides the comparison rules. If you want a case-insensitive search on SQLite, you can use EF.Functions.Like(b.Title, "%the%"); that query found 2 books.

Checking existence with Count() > 0. db.Books.Count(b => b.Pages > 300) > 0 counts every matching row. db.Books.Any(b => b.Pages > 300) produces SELECT EXISTS (...) and stops at the first match. On large tables the difference is serious.

An early ToList(). Writing db.Books.ToList().Where(...) is the explicit form of the IEnumerable pitfall. Always put the materializing call at the very end of the query.

A Small Experiment

★☆☆ Write a GetPage(int page, int size) method that takes the page number and page size as parameters. Print the SQL with ToQueryString(), then remove the OrderBy and see the warning in the console.

★★☆ Write a projection that returns the author's name, the number of books and the year of the latest book: db.Authors.Select(a => new { a.Name, Count = a.Books.Count, Latest = a.Books.Max(b => b.PublishedYear) }). Examine the subqueries in the generated SQL and count how many commands run.

★★★ Leave BookQueries.All with its IEnumerable<Book> return type, but prove from the logs on the calling side that the whole table is fetched. Then add a few thousand rows to the Books table with a seed and measure both versions with Stopwatch.

With projection we got part of the related data, but sometimes you want the object itself together with its relationships: an author with their books, an order with its items. In the next post we cover Include and ThenInclude, filtered include, explicit and lazy loading, and the choice between a single query and a split query.