EntityFrameworkCore.Projectables

EF Core Projectables is a library that allows you to define reusable business logic (properties, methods, or constructors) directly on your entities using the [Projectable] attribute. These members are automatically translated into efficient SQL by a source generator, allowing you to use them in LINQ queries (Where, OrderBy, Select, etc.) without manual expression tree boilerplate or client-side evaluation.

Key benefits include:

• Write Once, Use Anywhere: Define a complex calculation once and use it in any LINQ query.
• Composable Logic: Projectable members can call other projectable members.
• SQL Translation: Supports pattern matching (switch expressions), null-conditional operators (?.), and enum method expansion, all rewritten into SQL CASE expressions.
• Provider Agnostic: Works with any EF Core provider (SQL Server, PostgreSQL, SQLite, Cosmos DB, etc.).

class Order
{
    public decimal TaxRate { get; set; }
    public ICollection<OrderLine> Lines { get; set; }

    [Projectable]
    public decimal Subtotal => Lines.Sum(l => l.Quantity * l.UnitPrice);

    [Projectable]
    public decimal Total => Subtotal * (1 + TaxRate);

    [Projectable]
    public string Tier => Subtotal switch
    {
        > 1000 => "Premium",
        > 250  => "Standard",
        _      => "Basic"
    };
}

// Usage in LINQ
var orders = dbContext.Orders
    .Where(o => o.Subtotal > 500)
    .OrderByDescending(o => o.Total)
    .Select(o => new { o.Total, o.Tier })
    .ToList();

In Limited mode, expansion happens inside EF Core's query translation preprocessor. The expanded query is then stored in EF Core's query cache, allowing subsequent executions with the same query shape to skip the expansion step entirely.

Workflow:

1. LINQ query is created.
2. EF Core query preprocessor receives the query.
3. Projectables expands member calls inside the preprocessor.
4. Expanded query is compiled and stored in the query cache.
5. SQL is generated and executed.

Key Characteristics:

• Performance: Significantly better performance for repeated queries as cached queries bypass expansion.
• Dynamic Parameters: Caution is required; dynamic parameters captured as closures may not work correctly because the expanded query is cached with the parameter values from the first execution.
• Runtime State: If a projectable member uses external runtime state (not part of EF Core query parameters), the cached expansion may become stale.

options.UseProjectables(p => p.CompatibilityMode(CompatibilityMode.Limited));

In Full mode (the default), the expression tree is expanded on every individual query invocation before being passed to EF Core. This ensures maximum compatibility and correctness for dynamic scenarios.

Workflow:

1. LINQ query is created.
2. Projectables expands all member calls.
3. Expanded query is sent to the EF Core compiler.
4. SQL is generated and executed.

Key Characteristics:

• Dynamic Parameters: Works correctly with dynamic parameters as it captures fresh values on every execution.
• Compatibility: Highest compatibility; works in all EF Core scenarios.
• Overhead: There is a slight overhead per query due to expression tree walking and expansion.
• Caching: EF Core's query cache key changes with expanded expressions, which may make the compiled query cache less effective.

// Full is the default
options.UseProjectables(); 

// Or explicitly:
options.UseProjectables(p => p.CompatibilityMode(CompatibilityMode.Full));

In Full Compatibility Mode, Projectables expands the LINQ expression before it reaches the EF Core query compiler. This is achieved by wrapping the default QueryCompiler with CustomQueryCompiler.

Workflow

1. The raw LINQ expression is passed to CustomQueryCompiler.
2. CustomQueryCompiler calls Expand() using ProjectableExpressionReplacer.Replace().
3. The resulting expanded expression is then passed to the standard EF Core pipeline.

Query Cache Implications

• Cache Keys: EF Core's query cache is based on the expanded expression. Two queries that look identical in LINQ but call different projectable members will result in different cache keys.
• Performance: Every unique LINQ query shape undergoes expansion on every execution. The expansion step itself is not cached by EF Core.

// Internal implementation detail of CustomQueryCompiler
public override TResult Execute<TResult>(Expression query)
    => _decoratedQueryCompiler.Execute<TResult>(Expand(query));

public override TResult ExecuteAsync<TResult>(Expression query, CancellationToken cancellationToken)
    => _decoratedQueryCompiler.ExecuteAsync<TResult>(Expand(query), cancellationToken);

public override Func<QueryContext, TResult> CreateCompiledQuery<TResult>(Expression query)
    => _decoratedQueryCompiler.CreateCompiledQuery<TResult>(Expand(query));

The core logic for transforming LINQ expressions into their database-compatible forms resides in ProjectableExpressionReplacer. The expansion process follows these steps:

1. Tree Traversal: An ExpressionVisitor recursively visits every node in the expression tree.
2. Detection: It identifies MemberExpression (property access) or MethodCallExpression nodes decorated with the [ProjectableAttribute].
3. Resolution: It uses ProjectionExpressionResolver to find the auto-generated companion class and invokes its Expression() factory method via reflection.
4. Caching: Resolved LambdaExpression objects are cached in a per-replacer dictionary to minimize reflection overhead during a single expansion.
5. Substitution: ExpressionArgumentReplacer replaces the lambda's parameters with the actual arguments from the original call site.
6. Recursion: The process recurses into the newly substituted expression body to handle nested projectable calls.

When using [Projectable(AllowBlockBody = true)], the following C# constructs are supported and converted into expression trees for SQL generation:

If-Else and Early Returns

• If-Else chains: Converted to ternary (? :) expressions.
• If without Else: Supported if followed by a fallback return (e.g., if (condition) return A; return B;).
• Multiple Early Returns: Independent if statements are converted into a nested ternary chain.

Switch Statements

• Switch: Converted to nested ternary expressions or CASE WHEN ... IN (...) SQL patterns.
• Case Collapsing: Multiple cases mapping to the same result (e.g., case 1: case 2: return "Low";) are optimized into IN clauses in SQL.

Local Variables

• Inlining: Local variables declared at the method body level are inlined at each usage point.
• Transitive Inlining: Supported (e.g., var a = x; var b = a; becomes x).
• Warning (Variable Duplication): If a local variable is referenced multiple times, its initializer is duplicated at each point. This can impact performance or semantics if the initializer has side effects.
• Scope Limitation: Local variables are only supported at the method body level, not inside nested blocks like if or switch statements.

[Projectable(AllowBlockBody = true)]
public string GetValueLabel()
{
    switch (Value)
    {
        case 1: case 2: return "Low";
        case 3: case 4: case 5: return "Medium";
        default: return "High";
    }
}