Expression Trees — How LINQ Queries Become SQL

Why Expression Trees Exist

When you write a LINQ query that runs against a database, your code is not executed directly. Instead, the compiler builds an expression tree — a data structure representing the code as data. EF Core walks this tree and translates it into SQL.

// Func<Patient, bool> — compiled code, runs in memory
Func<Patient, bool> funcPredicate = p => p.IsActive;

// Expression<Func<Patient, bool>> — expression tree, can be translated to SQL
Expression<Func<Patient, bool>> exprPredicate = p => p.IsActive;

// The difference:
IEnumerable<Patient> fromMemory = patients.Where(funcPredicate);   // LINQ to Objects
IQueryable<Patient>  fromDB     = db.Patients.Where(exprPredicate); // EF Core → SQL

IQueryable<T> methods accept Expression<Func<T, bool>>. IEnumerable<T> methods accept Func<T, bool>. When you call Where() on an IQueryable, you are adding an expression to a tree, not executing code.

What EF Core Can and Cannot Translate

// Translatable — EF Core knows how to express these in SQL
db.Patients.Where(p => p.Name.Contains("Smith"))          // LIKE '%Smith%'
db.Patients.Where(p => p.DateOfBirth.Year == 1985)        // YEAR(DateOfBirth) = 1985
db.Patients.Where(p => p.Department == "Cardiology")      // = 'Cardiology'
db.Patients.Where(p => EF.Functions.Like(p.Name, "Sm%"))  // LIKE 'Sm%'

// NOT translatable — throws at runtime
db.Patients.Where(p => MyCustomMethod(p.Name))            // EF cannot translate a custom method
db.Patients.Where(p => p.Name.ToTitleCase() == "Smith")   // ToTitleCase not in SQL

// Fix: call client-side evaluation explicitly
var patients = await db.Patients.ToListAsync();            // load first
var filtered = patients.Where(p => p.Name.ToTitleCase() == "Smith"); // then filter in memory

Building Dynamic Predicates

When you need to combine predicates dynamically:

// Starting point: always-true predicate
Expression<Func<Patient, bool>> predicate = p => true;

// Combine with AND using Expression.AndAlso
if (filter.IsActive.HasValue)
{
    Expression<Func<Patient, bool>> isActive = p => p.IsActive == filter.IsActive.Value;
    predicate = Expression.Lambda<Func<Patient, bool>>(
        Expression.AndAlso(predicate.Body,
            Expression.Invoke(isActive, predicate.Parameters)),
        predicate.Parameters);
}

// Or use a helper like LinqKit's PredicateBuilder
// Install: LinqKit.Microsoft.EntityFrameworkCore
var builder = PredicateBuilder.New<Patient>(true);

if (filter.IsActive.HasValue)
    builder = builder.And(p => p.IsActive == filter.IsActive.Value);

if (!string.IsNullOrEmpty(filter.Department))
    builder = builder.And(p => p.Department == filter.Department);

var results = await db.Patients
    .AsExpandable()  // LinqKit: enables expression expansion
    .Where(builder)
    .ToListAsync();

Expression Reuse with Compiled Queries

For hot code paths, EF Core re-parses the expression tree every time. Compiled queries cache the SQL:

// Compiled query — SQL generated once, reused every call
private static readonly Func<AppDbContext, Guid, Task<Patient?>> GetPatientById =
    EF.CompileAsyncQuery(
        (AppDbContext db, Guid id) =>
            db.Patients.FirstOrDefault(p => p.Id == id));

// Usage (no expression translation overhead)
var patient = await GetPatientById(db, patientId);

Production issue I've seen: A high-frequency query in a clinical alert system re-parsed its EF Core expression tree on every call — 2,000 times per minute during peak hours. The expression parsing was consuming 18% of total CPU. EF.CompileAsyncQuery brought that to near zero.

Member Access in Expressions

// Reading and building expressions manually
Expression<Func<Patient, string>> nameExpr = p => p.Name;

// The expression tree structure:
// Lambda(Parameter("p"), MemberAccess(Parameter("p"), "Name"))

// Build programmatically
var parameter = Expression.Parameter(typeof(Patient), "p");
var property  = Expression.Property(parameter, "Name");
var lambda    = Expression.Lambda<Func<Patient, string>>(property, parameter);
// Equivalent to: p => p.Name

Translating Custom Logic

When you need logic that cannot be expressed in a single lambda:

// Define a reusable expression
public static class PatientExpressions
{
    // Expression that can be composed into EF Core queries
    public static Expression<Func<Patient, bool>> IsHighRisk =>
        p => p.RiskScore > 75 && p.IsActive && p.LastReviewedAt < DateTime.UtcNow.AddMonths(-3);

    public static Expression<Func<Patient, PatientSummaryDto>> ToSummaryDto =>
        p => new PatientSummaryDto(p.Id, p.FullName, p.MRN, p.RiskScore);
}

// Usage
var highRisk = await db.Patients
    .Where(PatientExpressions.IsHighRisk)
    .Select(PatientExpressions.ToSummaryDto)
    .ToListAsync();

EF.Functions — DB-Specific Functions

// Access SQL Server functions not available in LINQ
db.Patients.Where(p => EF.Functions.Like(p.Name, "%son%"))
db.Patients.Where(p => EF.Functions.Contains(p.ClinicalNotes, "warfarin"))
// Full-text CONTAINS() — not a LIKE

db.Prescriptions.Where(rx =>
    EF.Functions.DateDiffDay(rx.IssuedAt, DateTime.UtcNow) <= 30)
// DATEDIFF(day, IssuedAt, GETUTCDATE()) <= 30

Red Flag / Green Answer

Red Flag: "We got a runtime error 'The LINQ expression could not be translated' so we added AsEnumerable() before the Where clause."

AsEnumerable() forces client-side evaluation — all rows load from the database before filtering. If the table has 500,000 rows, all 500,000 load into memory. The correct fix is to rewrite the predicate using EF-translatable methods.

Green Answer:

Identify what in the expression is not translatable (usually a custom method call or BCL method EF does not know). Replace with an EF-translatable equivalent or EF.Functions.*. If client-side evaluation is genuinely needed, load the minimal pre-filtered set from SQL first, then apply in-memory logic.

Key Takeaway

Expression trees are the mechanism that allows LINQ to be translated to SQL. Expression<Func<T, bool>> is a data structure (analyzable, translatable). Func<T, bool> is compiled code (opaque to EF Core). EF Core translates known expression patterns to SQL; unknown patterns throw at runtime. Build dynamic predicates with expression combinators or LinqKit. Use compiled queries for hot paths.