Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 41 ยท Querying with LINQ

LINQ to Entities: Writing Queries

Learn how EF Core turns LINQ into SQL - IQueryable versus IEnumerable, deferred execution, expression trees, single-row operators and the mistakes that make queries slow or fail.

You rarely write SQL with EF Core. You write LINQ (Language Integrated Query) against a DbSet<T>, and EF Core translates the query into SQL for your database. If you know LINQ over lists, you already know most of the syntax. What is new is when the query runs and what can be translated.

The real EF Core cannot run on this site, so queries are shown read-only. The runnable stand-ins use IEnumerable and a small expression-tree translator to demonstrate deferred execution and translation.

A first query

C#
var cheapBooks = await db.Products
    .Where(p => p.Category == "Books" && p.Price < 20m)
    .OrderBy(p => p.Name)
    .ToListAsync();

EF Core generates one statement:

SQL
SELECT "p"."Id", "p"."Category", "p"."Name", "p"."Price"
FROM "Products" AS "p"
WHERE "p"."Category" = 'Books' AND "p"."Price" < 20.0
ORDER BY "p"."Name"

The filter and the sort happen in the database, and only matching rows travel to your program. That is the whole point.

IQueryable versus IEnumerable

db.Products is an IQueryable<Product>. Operators such as Where and OrderBy on it take an expression tree (Expression<Func<Product, bool>>), a data structure describing the lambda that EF Core can read and translate. The same method names on IEnumerable<T> take a plain delegate (Func<>) and run in memory.

flowchart LR
    Q["db.Products.Where(...).OrderBy(...)<br/>IQueryable: only a description"] -->|"ToListAsync / foreach / First / Count"| SQL["SQL sent to the database"]
    SQL --> R["Rows become objects"]

Building the query does not touch the database. A terminal operation does.

Deferred execution

A query is a recipe, not a result. Nothing runs until you enumerate it:

C#
var query = db.Products.Where(p => p.Price > 10m);   // no SQL yet
query = query.OrderBy(p => p.Name);                  // still no SQL

var list = await query.ToListAsync();                // SQL runs now
var again = await query.ToListAsync();               // SQL runs again

Terminal operations that execute the query: ToList, ToArray, ToDictionary, First, Single, Count, Any, Sum, Max, foreach, and await on them. Deferred execution lets you build queries step by step:

C#
IQueryable<Product> query = db.Products;
if (!string.IsNullOrEmpty(category)) query = query.Where(p => p.Category == category);
if (maxPrice is not null) query = query.Where(p => p.Price <= maxPrice);
var result = await query.ToListAsync();     // one combined WHERE

Executing the same variable twice runs it twice, so store the result (ToListAsync) if you need it again.

Choosing a single row

Operator No match More than one match
First throws first one
FirstOrDefault null first one
Single throws throws
SingleOrDefault null throws
Find(key) null n/a (primary key only, checks the tracker first)

Use SingleOrDefault when the data must be unique by contract (a bug should surface), FirstOrDefault when you just need one (always add an OrderBy to make "first" deterministic).

Counting and existence

C#
bool any = await db.Orders.AnyAsync(o => o.CustomerId == id);          // cheap: EXISTS
int count = await db.Orders.CountAsync(o => o.Status == "Open");       // SELECT COUNT(*)
decimal revenue = await db.Orders.SumAsync(o => o.Total);              // SELECT SUM

Do not write (await db.Orders.ToListAsync()).Count: it downloads every row just to count them.

Method syntax and query syntax

C# also offers SQL-like query syntax, which compiles to the same method calls:

C#
var query = from p in db.Products
            where p.Price > 10m
            orderby p.Name
            select p.Name;

Most EF Core code uses method syntax; query syntax shines with several joins (see Joins, Grouping and Aggregates).

What can be translated

EF Core can translate common operators, string methods (Contains, StartsWith, ToLower), DateTime members, arithmetic, Math methods and some provider functions. It cannot translate your own C# methods:

C#
// The method body is invisible to EF Core.
bool IsPopular(Product p) => p.Sales > 1000 && p.Rating > 4;

var popular = await db.Products.Where(p => IsPopular(p)).ToListAsync();
// Newer EF Core versions throw: could not be translated

Write the condition inline in the lambda, or build it as an Expression<Func<Product, bool>>. Reading the exception message and calling ToQueryString() (see Logging and Seeing the SQL) will tell you which part failed.

From LINQ to rows

Build Where, OrderBy, Select
Expression tree A readable description
Translate EF Core writes SQL
Execute ToList, First, Count

Nothing runs before Execute

1 / 2

Stand-in: deferred execution

This stand-in is not EF Core. IEnumerable with yield shows the same behavior: the query runs when you enumerate it, every time, and sees data that changed in between.

C# Stand-in: a query is a recipe

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.

Stand-in: expression trees can be read

C# Stand-in: reading an expression tree

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.

If you add a call to your own method inside the lambda, the translator hits the _ arm and throws: that is the same reason EF Core reports "could not be translated".

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When does a LINQ query against a DbSet hit the database?

    Query operators build an expression tree; a terminal operation executes it.
  2. 2. What is the difference between Single and First?

    Single asserts uniqueness (it throws on zero or many); First just takes the first row, so add an OrderBy.
  3. 3. Why is (await db.Orders.ToListAsync()).Count a bad way to count?

    CountAsync translates to SELECT COUNT(*) and transfers one number.
  4. 4. Why can a Where that calls your own C# method fail?

    Only expression trees EF Core understands can be translated; custom method bodies are opaque.

Summary

  • A DbSet<T> is an IQueryable<T>: operators build an expression tree that EF Core translates to SQL.
  • Execution is deferred until a terminal operator; running the same query twice hits the database twice.
  • Use Any, Count and Sum in SQL, never on downloaded lists.
  • Choose Single, First and Find deliberately, and keep conditions translatable.

Next: Filtering, Sorting and Paging