C# & .NET (C# 14, .NET 10 LTS) Module 14 – LINQ

IQueryable and expression trees

How C# IQueryable carries an expression tree to a query provider while IEnumerable runs delegates in memory, and how to build, translate and compile trees.

Advanced
20 minutes
Examples run with .NET 10.0.401
By MySmartCoPilot

Free: A lambda becomes a delegate or an expression tree, depending on its type · IQueryable sends the tree to a provider, and AsEnumerable switches to memory. With a pass: 3 in-depth parts and 3 interview answers, about 12 minutes.

What you will learn

Explain where an IQueryable query runs, where an IEnumerable one runs, and what AsEnumerable changes
Read the nodes of an expression tree and compile it into a delegate
Build a predicate with the factory methods of Expression, checked against an allowlist
Write predicates that a query provider can translate, and spot the ones it cannot
Measure what Compile costs, and what the interpreter that Native AOT uses changes

Before you start

LINQ joins: Join, GroupJoin, LeftJoin and RightJoin

What you’ll learn · Before you start

What you will learn

Explain where an IQueryable query runs, where an IEnumerable one runs, and what AsEnumerable changes
Read the nodes of an expression tree and compile it into a delegate
Build a predicate with the factory methods of Expression, checked against an allowlist
Write predicates that a query provider can translate, and spot the ones it cannot
Measure what Compile costs, and what the interpreter that Native AOT uses changes

Before you start

LINQ joins: Join, GroupJoin, LeftJoin and RightJoin

On this page

In this lesson’s runs, a filter for 3A trains under 1,500 made a store send 3 of its 12 rows, and one AsEnumerable() before the same Where made it send all 12. The same Where runs in two very different places: on a list it calls a C# delegate for every item, and on an IQueryable it never runs at all, because it is read as data and turned into the data source’s own query.

You will read and build expression trees, see how a provider translates one, and what it refuses. The runs end with what Compile() costs and what changes when a tree is interpreted, as it always is under Native AOT.

A lambda becomes a delegate or an expression tree, depending on its type

C# compiles one lambda two ways. Assigned to Func<Train, bool>, it becomes a method you can call. Assigned to Expression<Func<Train, bool>>, it becomes an expression tree: A data structure that describes code: each node is an expression, such as a member access, a constant or a comparison. A lambda assigned to Expression<Func<…>> becomes one, which a program can read, change, compile or translate.: objects that describe the code, which a program can read, change and send elsewhere. tree.cs writes the same test both ways:

tree.cs: one lambda as a delegate and as an expression tree C# · tree/tree.cs

using System.Linq.Expressions;

// One lambda written twice: once as a delegate, once as an expression tree. The trains are made up for this lesson.
Train[] trains =
[
    new(12951, "NDLS", "3A", 2450), new(12627, "SBC", "SL", 480), new(22691, "SBC", "3A", 1890),
    new(12839, "HWH", "3A", 450), new(12028, "MAS", "CC", 960), new(12245, "HWH", "3A", 2620),
];

Func<Train, bool> code = t => t.Fare > 500 && t.Class == "3A";
Expression<Func<Train, bool>> data = t => t.Fare > 500 && t.Class == "3A";

Console.WriteLine($"the delegate says {code(trains[0])} for train {trains[0].Number}: it can be called, and nothing more");
Console.WriteLine($"the tree is a node of type {data.NodeType} with a body of type {data.Body.NodeType}, and prints as {data}");

// The tree is objects you can read: each node has a NodeType, and an operator node has a Left and a Right.
Console.WriteLine("the nodes of its body:");
Print(data.Body, 1);

// Compile turns the tree into a delegate that runs the same test.
Func<Train, bool> compiled = data.Compile();
Console.WriteLine($"the delegate keeps {trains.Count(code)} trains; the compiled tree keeps {trains.Count(compiled)}");

static void Print(Expression node, int depth)
{
    string pad = new(' ', depth * 2);
    switch (node)
    {
        case BinaryExpression b:
            Console.WriteLine($"{pad}{b.NodeType}");
            Print(b.Left, depth + 1);
            Print(b.Right, depth + 1);
            break;
        case MemberExpression m:
            Console.WriteLine($"{pad}MemberAccess {m.Member.Name} of the parameter {m.Expression}");
            break;
        case ConstantExpression c:
            Console.WriteLine($"{pad}Constant {c.Value} of type {c.Type.Name}");
            break;
        default:
            Console.WriteLine($"{pad}{node.NodeType} {node}");
            break;
    }
}

record Train(int Number, string From, string Class, int Fare);

Output

the delegate says True for train 12951: it can be called, and nothing more
the tree is a node of type Lambda with a body of type AndAlso, and prints as t => ((t.Fare > 500) AndAlso (t.Class == "3A"))
the nodes of its body:
  AndAlso
    GreaterThan
      MemberAccess Fare of the parameter t
      Constant 500 of type Int32
    Equal
      MemberAccess Class of the parameter t
      Constant 3A of type String
the delegate keeps 3 trains; the compiled tree keeps 3

Recorded with .NET SDK 10.0.401 (runtime 10.0.12) on macOS 26 arm64. To run it yourself: dotnet run tree.cs

The delegate can only be called: it said True for train 12951. The tree is a node of type Lambda with a body of type AndAlso, and it printed as t => ((t.Fare > 500) AndAlso (t.Class == "3A")). Under the body sit the nodes the run listed: GreaterThan with the member Fare and the constant 500, Equal with the member Class and the constant "3A". Compile() turned the tree back into a delegate, and both kept the same 3 of the 6 trains.

Only lambdas with an expression body become trees. The C# guide to expression trees lists what they cannot hold, among them statement bodies with braces, assignments, async and await, the null-conditional ?., interpolated strings and references to local functions; such a lambda still compiles as a delegate.

Key idea

The same lambda is code when its type is a delegate and data when its type is Expression<…>: a tree of nodes that can be read, changed, sent or compiled.

Checkpoint

Check: A lambda as a delegate or a tree

Question 1 of 2 Expression<Func<Train, bool>> data = t => t.Fare > 500; What does data hold?
Choose one answer.

The text of the lambda, as a string A compiled method, the same as a Func<Train, bool> The result of the test for every train Objects that describe the lambda: a Lambda node whose body is a GreaterThan node

Show the answer: Question 1 of 2

Answer: Objects that describe the lambda: a Lambda node whose body is a GreaterThan node

A lambda assigned to Expression<…> becomes a tree of nodes. tree.cs printed its body as AndAlso, GreaterThan and Equal nodes, and Compile() turned it into a delegate.
Question 2 of 2 Print in tree.cs walks t => t.Fare < 400 || t.Class == "SL". What does it print first, on the first line?
Choose one answer.

LessThan OrElse AndAlso Lambda

Show the answer: Question 2 of 2

Answer: OrElse

The body of a lambda is its top operator: for a || b it is OrElse, with a on the left and b on the right, as AndAlso was for the && of tree.cs.

IQueryable sends the tree to a provider, and AsEnumerable switches to memory

IQueryable<T> is the interface of data sources that take trees. Its documentation says it holds an expression tree and a query provider: The IQueryProvider behind an IQueryable, which runs the query's expression tree when the query is read. A database provider, such as EF Core's, translates the tree into SQL; others compile it and run it in memory., and that reading it makes the provider execute the tree, which may mean translating it into another query language. store.cs has a made-up store of 12 trains and a small provider that turns Where into the store’s own request:

store.cs: the same Where, run by the store and in memory C# · store/store.cs

using System.Collections;
using System.Linq.Expressions;

// A made-up store of 12 trains that stands in for a database: it understands only its own request language, and it
// counts the rows it sends back. TrainStore.Trains is an IQueryable<Train> whose provider translates Where.
var store = new TrainStore(
[
    new(12951, "NDLS", "3A", 2450), new(12627, "SBC", "SL", 480), new(22691, "SBC", "3A", 1890),
    new(12839, "HWH", "3A", 450), new(12028, "MAS", "CC", 960), new(12245, "HWH", "3A", 2620),
    new(12622, "MAS", "SL", 720), new(12296, "SBC", "3A", 1340), new(12424, "NDLS", "2A", 3880),
    new(12860, "HWH", "SL", 610), new(16525, "SBC", "3A", 1210), new(12002, "NDLS", "CC", 1015),
]);

// IQueryable: Where adds a node to the query's expression tree, and the provider translates the whole tree.
IQueryable<Train> cheap3A = store.Trains.Where(t => t.Fare < 1500 && t.Class == "3A");
Console.WriteLine($"the query's tree:  {cheap3A.Expression}");
List<Train> kept = cheap3A.ToList();
Console.WriteLine($"IQueryable:        the store ran \"{store.LastRequest}\", sent {store.RowsSent} rows, {kept.Count} kept");

// AsEnumerable: the same Where, after it, is Enumerable.Where with a delegate, so the store sends every row.
IEnumerable<Train> inMemory = store.Trains.AsEnumerable().Where(t => t.Fare < 1500 && t.Class == "3A");
kept = inMemory.ToList();
Console.WriteLine($"AsEnumerable:      the store ran \"{store.LastRequest}\", sent {store.RowsSent} rows, {kept.Count} kept");
Console.WriteLine($"the trains:        {string.Join(" ", kept.Select(t => t.Number))}");

record Train(int Number, string From, string Class, int Fare);

// The store: rows, and a request it can run (one condition per column comparison, joined with AND and OR).
sealed class TrainStore(List<Train> rows)
{
    public IQueryable<Train> Trains => new Query(new Provider(this), null);

    public string LastRequest { get; private set; } = "";

    public int RowsSent { get; private set; }

    public IEnumerable<Train> Run(Condition? where)
    {
        LastRequest = where is null ? "SELECT * FROM Trains" : $"SELECT * FROM Trains WHERE {where}";
        List<Train> sent = [.. rows.Where(r => where?.Holds(r) ?? true)];
        RowsSent = sent.Count;
        return sent;
    }
}

// The request language: comparisons of a column with a value, combined with AND and OR.
abstract record Condition
{
    public abstract bool Holds(Train row);
}

sealed record Compare(string Column, string Op, IComparable Value) : Condition
{
    public override bool Holds(Train row)
    {
        IComparable cell = Column switch
        {
            "Number" => row.Number,
            "From" => row.From,
            "Class" => row.Class,
            "Fare" => row.Fare,
            _ => throw new NotSupportedException($"no column {Column}"),
        };
        int c = cell.CompareTo(Value);
        return Op switch { "=" => c == 0, "<>" => c != 0, "<" => c < 0, "<=" => c <= 0, ">" => c > 0, _ => c >= 0 };
    }

    public override string ToString() => $"{Column} {Op} {(Value is string s ? $"'{s}'" : Value)}";
}

sealed record Join(Condition Left, string Word, Condition Right) : Condition
{
    public override bool Holds(Train row) => Word == "AND" ? Left.Holds(row) && Right.Holds(row) : Left.Holds(row) || Right.Holds(row);

    public override string ToString() => $"{Left} {Word} {Right}";
}

// The IQueryable: an expression tree and the provider that runs it. The tree starts as this query itself.
sealed class Query : IQueryable<Train>
{
    private readonly Provider _provider;

    public Query(Provider provider, Expression? expression)
    {
        _provider = provider;
        Expression = expression ?? Expression.Constant(this);
    }

    public Type ElementType => typeof(Train);

    public Expression Expression { get; }

    public IQueryProvider Provider => _provider;

    public IEnumerator<Train> GetEnumerator() => _provider.Execute<IEnumerable<Train>>(Expression).GetEnumerator();

    IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();

    public override string ToString() => "Trains";
}

// The provider: Queryable.Where calls CreateQuery with a bigger tree; reading the query calls Execute with it.
sealed class Provider(TrainStore store) : IQueryProvider
{
    public IQueryable CreateQuery(Expression expression) => CreateQuery<Train>(expression);

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression) =>
        (IQueryable<TElement>)(object)new Query(this, expression);

    public object? Execute(Expression expression) => Execute<IEnumerable<Train>>(expression);

    public TResult Execute<TResult>(Expression expression)
    {
        Condition? where = null;
        for (Expression node = expression; node is MethodCallExpression call; node = call.Arguments[0])
        {
            if (call.Method.Name != "Where")
            {
                throw new NotSupportedException($"the store cannot run {call.Method.Name}");
            }

            var lambda = (LambdaExpression)((UnaryExpression)call.Arguments[1]).Operand;
            Condition condition = Translate(lambda.Body, lambda.Parameters[0]);
            where = where is null ? condition : new Join(condition, "AND", where);
        }

        return (TResult)(object)store.Run(where);
    }

    static Condition Translate(Expression node, ParameterExpression row) => node switch
    {
        BinaryExpression { NodeType: ExpressionType.AndAlso } b => new Join(Translate(b.Left, row), "AND", Translate(b.Right, row)),
        BinaryExpression { NodeType: ExpressionType.OrElse } b => new Join(Translate(b.Left, row), "OR", Translate(b.Right, row)),
        BinaryExpression b when Operators.TryGetValue(b.NodeType, out string? op) => new Compare(Column(b.Left, row), op, Value(b.Right)),
        _ => throw new NotSupportedException($"the store cannot translate {node}"),
    };

    static readonly Dictionary<ExpressionType, string> Operators = new()
    {
        [ExpressionType.Equal] = "=", [ExpressionType.NotEqual] = "<>", [ExpressionType.LessThan] = "<",
        [ExpressionType.LessThanOrEqual] = "<=", [ExpressionType.GreaterThan] = ">", [ExpressionType.GreaterThanOrEqual] = ">=",
    };

    // A column is a property of the row parameter: t.Fare.
    static string Column(Expression node, ParameterExpression row) =>
        node is MemberExpression m && m.Expression == row ? m.Member.Name : throw new NotSupportedException($"not a column: {node}");

    // A value is a constant: 1500, "3A".
    static IComparable Value(Expression node) =>
        node is ConstantExpression { Value: IComparable v } ? v : throw new NotSupportedException($"not a value: {node}");
}

Output

the query's tree:  Trains.Where(t => ((t.Fare < 1500) AndAlso (t.Class == "3A")))
IQueryable:        the store ran "SELECT * FROM Trains WHERE Fare < 1500 AND Class = '3A'", sent 3 rows, 3 kept
AsEnumerable:      the store ran "SELECT * FROM Trains", sent 12 rows, 3 kept
the trains:        12839 12296 16525

Recorded with .NET SDK 10.0.401 (runtime 10.0.12) on macOS 26 arm64. To run it yourself: dotnet run store.cs

Queryable.Where added a node to the query’s tree, Trains.Where(t => …). When ToList() read the query, the provider walked that tree and asked the store for Fare < 1500 AND Class = '3A': 3 rows were sent, 3 kept. After AsEnumerable(), Where was Enumerable.Where, a delegate run in this process, so the store sent all 12 rows and the program threw 9 away. So an IQueryable filter ran in the store, and the same filter on an IEnumerable ran in the program. Step through the translation:

Interactive · step by step A provider reads the tree of Where and writes its request Step through the translation. Each box is a node of the tree; the provider visits the operators on the left and the columns and constants on the right.

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text

The tree arrives. Reading the query calls the provider with the whole tree: a call of Where whose argument is this lambda, as data. No C# code of the lambda runs at any point.
The body is AndAlso. The lambda's body is an AndAlso node, the && of the source. Its two sides must both be translated before the provider can join them.
The left side. LessThan compares a member of the parameter t, the column Fare, with the constant 1500. The provider writes Fare < 1500.
The right side. Equal compares the column Class with the constant "3A". The provider writes Class = '3A', with the quotes of the store's language.
AndAlso joins them. With both sides written, AndAlso becomes AND between them: Fare < 1500 AND Class = '3A'.
The store runs it. The store ran SELECT * FROM Trains WHERE Fare < 1500 AND Class = '3A' and sent 3 of its 12 rows. The filter ran in the store, and the 9 other rows never left it.

	`IEnumerable<T>`	`IQueryable<T>`
`Where` takes	a delegate, `Func<T, bool>`	a tree, `Expression<Func<T, bool>>`
The filter runs	in your process, item by item	where the provider decides; in the store here
In store.cs	12 rows sent, 3 kept	3 rows sent, 3 kept
Usual sources	arrays, lists, iterators	a database through EF Core
Switch with	`AsQueryable()`	`AsEnumerable()`

The rest of the lesson goes into what makes this work, and what breaks it:

The factory methods of Expression build a tree in code, node by node, for filters chosen at run time.
An ExpressionVisitor walks a tree and copies it, or rewrites the parts it is given.
A provider translates only what it knows, so some predicates fail when the query is read.
Compile() turns a tree into a delegate, and costs far more than a call of the delegate.

Key idea

An IQueryable query is a tree that its provider runs; once AsEnumerable() switches to IEnumerable, every operator after it runs in your process on everything the source sends.

Checkpoint

Check: Where IQueryable and IEnumerable run

Question 1 of 2 store.cs holds 12 trains, 4 of them from SBC. How many rows does the store send for store.Trains.Where(t => t.From == "SBC").ToList()?
Type a number.
Your answer rows

A hint

The provider translates the Where, and the store filters before it sends.

Show the answer: Question 1 of 2

Answer: 4 rows

The provider turns the lambda into From = 'SBC', and the store sends only the 4 rows that pass. After AsEnumerable() it would send all 12 and the filter would run in memory.
Question 2 of 2 What does the store of store.cs run for store.Trains.Where(t => t.Class == "3A").AsEnumerable().Where(t => t.Fare < 1500).ToList()?
Choose one answer.

Class = '3A' AND Fare < 1500, sending 3 rows No condition at all: AsEnumerable() makes the store send all 12 rows Class = '3A' only: it sends the 6 trains of class 3A, and the fare filter runs in memory Fare < 1500 only

Show the answer: Question 2 of 2

Answer: Class = '3A' only: it sends the 6 trains of class 3A, and the fare filter runs in memory

AsEnumerable() splits a query: what comes before it is translated and runs in the store, what comes after runs in memory on what the store sent.

Building expression trees by hand: a filter from a search form, checked against an allowlist Pro: opens with a Pro, Premium or Ultimate pass

When the shape of a filter is chosen at run time, from the boxes a user ticks, the tree is built in code. The run below builds the compiler’s tree node by node, turns form criteria into a checked filter, and joins two lambdas without breaking their parameters.

In this part:

The factory methods build the tree the compiler builds
A filter from form criteria, checked when it is built
Two lambdas joined need one parameter: ExpressionVisitor

1 runnable example (C#) · about 4 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a pass I have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

What a provider can translate, and what fails only when the query runs Pro: opens with a Pro, Premium or Ultimate pass

A provider translates the trees it understands and refuses the rest, and it learns which is which only when the query is read. The run below puts a fuller version of the store’s provider through the cases that trip real ones: captured variables, methods of your own, and the Contains that C# 14 chooses for an array.

In this part:

A captured variable becomes a parameter, read on every run
A method of your own fails when the query is read
C# 14 picks the span Contains, and the provider has to know it
The translator is an ExpressionVisitor that knows a few node types

1 runnable example (C#) · about 4 minutes

Opening this part with your pass…Pro part. It opens with the same pass as the part above.

What Compile costs, and the interpreter that Native AOT always uses, measured Pro: opens with a Pro, Premium or Ultimate pass

A tree can always run in memory by compiling it into a delegate. The run below measures what the compilation costs against the calls it makes, and shows a tree that the compiled form runs and the interpreter refuses.

In this part:

Compile() costs microseconds, a call of its delegate nanoseconds
Keep the compiled delegate, not the tree
Native AOT always interprets, and the interpreter cannot run every tree

1 runnable example (C#) · about 4 minutes

Opening this part with your pass…Pro part. It opens with the same pass as the part above.

Practise: queries the store can run

search.cs holds the store of store.cs and three questions to write as queries over its IQueryable<Train>. Each check passes only if the result is right and the store sent no more rows than the query kept, so every filter has to stay in the tree. Until you write them, all 3 checks fail:

search.cs: three queries the store must run itself C# · practice/search.cs

// Practice: write the three queries at the bottom so that every check passes, then run this file again. Each one
// gets the store's IQueryable<Train> and must stay an IQueryable, so that the store runs the filter itself: a check
// fails when the store sends more rows than the query keeps. The store and its trains are those of store.cs.
using System.Collections;
using System.Linq.Expressions;

List<Train> rows =
[
    new(12951, "NDLS", "3A", 2450), new(12627, "SBC", "SL", 480), new(22691, "SBC", "3A", 1890),
    new(12839, "HWH", "3A", 450), new(12028, "MAS", "CC", 960), new(12245, "HWH", "3A", 2620),
    new(12622, "MAS", "SL", 720), new(12296, "SBC", "3A", 1340), new(12424, "NDLS", "2A", 3880),
    new(12860, "HWH", "SL", 610), new(16525, "SBC", "3A", 1210), new(12002, "NDLS", "CC", 1015),
];

var checks = new List<(string Name, Func<string> Run, string Expected)>
{
    ("3A trains from SBC under 1500", () => Run(Sbc3AUnder1500), "12296 16525, 2 rows sent"),
    ("SL trains from HWH or MAS", () => Run(SleeperFromHwhOrMas), "12622 12860, 2 rows sent"),
    ("at most 1000, any class but SL", () => Run(UpTo1000NotSleeper), "12839 12028, 2 rows sent"),
};

int passed = 0;
foreach (var (name, run, expected) in checks)
{
    string got;
    try
    {
        got = run();
    }
    catch (Exception e)
    {
        got = e.GetType().Name;
    }
    string verdict = got == expected ? "ok" : $"FAIL, got \"{got}\"";
    if (verdict == "ok")
    {
        passed++;
    }
    Console.WriteLine($"{name}: {verdict}");
}
Console.WriteLine($"{passed} of {checks.Count} checks pass");
return passed == checks.Count ? 0 : 1;

// Runs a query on a fresh store and says what it kept and how many rows the store sent.
string Run(Func<IQueryable<Train>, IQueryable<Train>> query)
{
    var store = new TrainStore(rows);
    List<Train> kept = query(store.Trains).ToList();
    return $"{string.Join(" ", kept.Select(t => t.Number))}, {store.RowsSent} rows sent";
}

// The 3A trains that leave from SBC and cost less than 1500.
static IQueryable<Train> Sbc3AUnder1500(IQueryable<Train> trains) =>
    throw new NotImplementedException("One Where whose lambda compares Class, From and Fare with constants.");

// The SL trains that leave from HWH or from MAS.
static IQueryable<Train> SleeperFromHwhOrMas(IQueryable<Train> trains) =>
    throw new NotImplementedException("Combine the two stations with || inside the comparison of Class.");

// The trains that cost at most 1000, in any class except SL.
static IQueryable<Train> UpTo1000NotSleeper(IQueryable<Train> trains) =>
    throw new NotImplementedException("Use <= for the fare and != for the class.");

record Train(int Number, string From, string Class, int Fare);

// The store: rows, and a request it can run (one condition per column comparison, joined with AND and OR).
sealed class TrainStore(List<Train> rows)
{
    public IQueryable<Train> Trains => new Query(new Provider(this), null);

    public string LastRequest { get; private set; } = "";

    public int RowsSent { get; private set; }

    public IEnumerable<Train> Run(Condition? where)
    {
        LastRequest = where is null ? "SELECT * FROM Trains" : $"SELECT * FROM Trains WHERE {where}";
        List<Train> sent = [.. rows.Where(r => where?.Holds(r) ?? true)];
        RowsSent = sent.Count;
        return sent;
    }
}

// The request language: comparisons of a column with a value, combined with AND and OR.
abstract record Condition
{
    public abstract bool Holds(Train row);
}

sealed record Compare(string Column, string Op, IComparable Value) : Condition
{
    public override bool Holds(Train row)
    {
        IComparable cell = Column switch
        {
            "Number" => row.Number,
            "From" => row.From,
            "Class" => row.Class,
            "Fare" => row.Fare,
            _ => throw new NotSupportedException($"no column {Column}"),
        };
        int c = cell.CompareTo(Value);
        return Op switch { "=" => c == 0, "<>" => c != 0, "<" => c < 0, "<=" => c <= 0, ">" => c > 0, _ => c >= 0 };
    }

    public override string ToString() => $"{Column} {Op} {(Value is string s ? $"'{s}'" : Value)}";
}

sealed record Join(Condition Left, string Word, Condition Right) : Condition
{
    public override bool Holds(Train row) => Word == "AND" ? Left.Holds(row) && Right.Holds(row) : Left.Holds(row) || Right.Holds(row);

    public override string ToString() => $"{Left} {Word} {Right}";
}

// The IQueryable: an expression tree and the provider that runs it. The tree starts as this query itself.
sealed class Query : IQueryable<Train>
{
    private readonly Provider _provider;

    public Query(Provider provider, Expression? expression)
    {
        _provider = provider;
        Expression = expression ?? Expression.Constant(this);
    }

    public Type ElementType => typeof(Train);

    public Expression Expression { get; }

    public IQueryProvider Provider => _provider;

    public IEnumerator<Train> GetEnumerator() => _provider.Execute<IEnumerable<Train>>(Expression).GetEnumerator();

    IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();

    public override string ToString() => "Trains";
}

// The provider: Queryable.Where calls CreateQuery with a bigger tree; reading the query calls Execute with it.
sealed class Provider(TrainStore store) : IQueryProvider
{
    public IQueryable CreateQuery(Expression expression) => CreateQuery<Train>(expression);

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression) =>
        (IQueryable<TElement>)(object)new Query(this, expression);

    public object? Execute(Expression expression) => Execute<IEnumerable<Train>>(expression);

    public TResult Execute<TResult>(Expression expression)
    {
        Condition? where = null;
        for (Expression node = expression; node is MethodCallExpression call; node = call.Arguments[0])
        {
            if (call.Method.Name != "Where")
            {
                throw new NotSupportedException($"the store cannot run {call.Method.Name}");
            }

            var lambda = (LambdaExpression)((UnaryExpression)call.Arguments[1]).Operand;
            Condition condition = Translate(lambda.Body, lambda.Parameters[0]);
            where = where is null ? condition : new Join(condition, "AND", where);
        }

        return (TResult)(object)store.Run(where);
    }

    static Condition Translate(Expression node, ParameterExpression row) => node switch
    {
        BinaryExpression { NodeType: ExpressionType.AndAlso } b => new Join(Translate(b.Left, row), "AND", Translate(b.Right, row)),
        BinaryExpression { NodeType: ExpressionType.OrElse } b => new Join(Translate(b.Left, row), "OR", Translate(b.Right, row)),
        BinaryExpression b when Operators.TryGetValue(b.NodeType, out string? op) => new Compare(Column(b.Left, row), op, Value(b.Right)),
        _ => throw new NotSupportedException($"the store cannot translate {node}"),
    };

    static readonly Dictionary<ExpressionType, string> Operators = new()
    {
        [ExpressionType.Equal] = "=", [ExpressionType.NotEqual] = "<>", [ExpressionType.LessThan] = "<",
        [ExpressionType.LessThanOrEqual] = "<=", [ExpressionType.GreaterThan] = ">", [ExpressionType.GreaterThanOrEqual] = ">=",
    };

    // A column is a property of the row parameter: t.Fare.
    static string Column(Expression node, ParameterExpression row) =>
        node is MemberExpression m && m.Expression == row ? m.Member.Name : throw new NotSupportedException($"not a column: {node}");

    // A value is a constant: 1500, "3A".
    static IComparable Value(Expression node) =>
        node is ConstantExpression { Value: IComparable v } ? v : throw new NotSupportedException($"not a value: {node}");
}

Output (exit status 1)

3A trains from SBC under 1500: FAIL, got "NotImplementedException"
SL trains from HWH or MAS: FAIL, got "NotImplementedException"
at most 1000, any class but SL: FAIL, got "NotImplementedException"
0 of 3 checks pass

Recorded with .NET SDK 10.0.401 (runtime 10.0.12) on macOS 26 arm64. To run it yourself: dotnet run search.cs

Write the three queries

Copy search.cs. Write each method as one Where on trains whose lambda compares Class, From and Fare with constants, using &&, ||, <, <= and !=. Run dotnet run search.cs until it prints 3 of 3 checks pass, then put .AsEnumerable() before one Where and .AsQueryable() after it, and watch its check fail.

The questions below check the first two sections, and each begins where your answers so far are weakest:

Interview questions

Fresher · What is the difference between IEnumerable and IQueryable in C#?

Both describe a sequence you can query with LINQ, and IQueryable<T> extends IEnumerable<T>. The operators of IEnumerable<T> take delegates and run them in your process, item by item. The operators of IQueryable<T> take expression trees and add them to the query’s tree, which its provider translates when the query is read, for example into SQL that runs in the database. So a filter on an IQueryable from EF Core runs in the database and returns only the matching rows, while the same filter after AsEnumerable() runs in memory on every row the database sends.

Fresher to mid

An endpoint returns db.Customers.AsEnumerable().Where(c => c.City == city).Take(20).ToList() through EF Core. It was fast in testing; in production each request takes seconds, and the database sends every customer. Explain what runs where, and fix it without changing the result.

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Mid to senior

An admin search page builds an Expression<Func<Order, bool>> from query-string fields such as field=Total, op=gt and value=500, passing the field name straight to Expression.Property. Review the design: what can go wrong, how do you build the tree safely, and how do you test it?

Model answer, Pro. First step and follow-ups
Mid to senior

A repository's unit tests pass against a list wrapped with AsQueryable(), but in production the same query throws when it is read, because the database provider cannot translate one predicate. Why do the tests miss it, and how do you test queries so they would not?

Model answer, Pro. First step and follow-ups

Key takeaways

A lambda is a delegate or an expression tree depending on the type it is given; a tree is data that can be read, changed and compiled.
An IQueryable query holds a tree and a provider, which translates the tree when the query is read; AsEnumerable() makes everything after it run in memory, on every item the source sends.
An IQueryable filter in store.cs made the store send 3 rows of 12, and the same filter on an IEnumerable, after AsEnumerable(), made it send all 12.

Terms in this lesson

expression tree: A data structure that describes code: each node is an expression, such as a member access, a constant or a comparison. A lambda assigned to Expression<Func<…>> becomes one, which a program can read, change, compile or translate.
ExpressionVisitor: A class that walks an expression tree node by node and returns a copy, in which the Visit methods you override replace the nodes you choose. Expression trees are immutable, so this is how a changed tree is made.
query provider: The IQueryProvider behind an IQueryable, which runs the query's expression tree when the query is read. A database provider, such as EF Core's, translates the tree into SQL; others compile it and run it in memory.

References

Expression trees (C#) (Microsoft Learn)
Expression.AndAlso method (Microsoft Learn)
Building expression trees (Microsoft Learn)
Translating expression trees (Microsoft Learn)
Executing expression trees (Microsoft Learn)
IQueryable<T> interface (Microsoft Learn)
How to build LINQ queries based on run-time state (Microsoft Learn)
LambdaExpression.Compile method (Microsoft Learn)
Native AOT deployment overview (Microsoft Learn)
Client vs. server evaluation (EF Core) (Microsoft Learn)
Breaking changes in EF Core 10 (Microsoft Learn)
C# compiler breaking changes since C# 13: span overloads (Microsoft Learn)
OWASP Input Validation Cheat Sheet (OWASP Foundation)

Report a problem with this lesson

In this module: LINQ

LINQ: your first queries Free lesson, Pro answers: 15 minutes
LINQ projection, filtering, sorting and paging Free core, Pro depth: 5 of 15 minutes free
LINQ grouping and aggregation Free core, Pro depth: 6 of 15 minutes free
LINQ joins: Join, GroupJoin, LeftJoin and RightJoin Free core, Pro depth: 5 of 15 minutes free
IQueryable and expression trees Free core, Pro depth: 8 of 20 minutes free

What you will learn

Before you start

What you will learn

Before you start

A lambda becomes a delegate or an expression tree, depending on its type

IQueryable sends the tree to a provider, and AsEnumerable switches to memory

Building expression trees by hand: a filter from a search form, checked against an allowlist Pro: opens with a Pro, Premium or Ultimate pass

What a provider can translate, and what fails only when the query runs Pro: opens with a Pro, Premium or Ultimate pass

What Compile costs, and the interpreter that Native AOT always uses, measured Pro: opens with a Pro, Premium or Ultimate pass

Practise: queries the store can run

Interview questions

An endpoint returns db.Customers.AsEnumerable().Where(c => c.City == city).Take(20).ToList() through EF Core. It was fast in testing; in production each request takes seconds, and the database sends every customer. Explain what runs where, and fix it without changing the result.

An admin search page builds an Expression<Func<Order, bool>> from query-string fields such as field=Total, op=gt and value=500, passing the field name straight to Expression.Property. Review the design: what can go wrong, how do you build the tree safely, and how do you test it?

A repository's unit tests pass against a list wrapped with AsQueryable(), but in production the same query throws when it is read, because the database provider cannot translate one predicate. Why do the tests miss it, and how do you test queries so they would not?

Key takeaways

References