title: "C# Interview Guide: EF Core Performance — Compiled Queries, Split Queries, Raw SQL"
excerpt: "Master EF Core performance techniques: compiled queries, split queries, bulk operations, and raw SQL for efficient data access."
tags:
- ef-core
- dotnet
- performance
- data-access
- csharp
seoTitle: "EF Core Performance: Compiled Queries, Split Queries, Raw SQL Explained"
seoDescription: "Explore advanced EF Core performance strategies including compiled queries, split queries, bulk operations and raw SQL tips for efficient data access."
category: tech-stacks
---
## Q14 — EF Core Performance — Compiled Queries, Split Queries, Raw SQL
*Level: Senior | Topic: Entity Framework Core*
Entity Framework Core (EF Core) is an object-relational mapper (ORM) that reduces boilerplate data access code in .NET. However, it can introduce performance overhead if used without awareness of how queries are translated and executed. In this article, we drill deep into EF Core's runtime behavior to uncover optimization techniques that mitigate common bottlenecks: compiled queries, split queries, raw SQL, and bulk operations.
## Understanding EF Core’s Query Pipeline
Every LINQ query in EF Core undergoes several phases before your database returns data:
1. **Expression Tree Compilation** — EF Core parses the LINQ expression tree and simulates what SQL to generate.
2. **SQL Generation** — The expression tree is converted into parameterized SQL queries.
3. **Command Execution** — The query runs against the database.
4. **Materialization** — Data readers map raw database rows back to entity objects.
While this powerful abstraction simplifies development, the expression tree compilation can become expensive in hot code paths. Additionally, eagerly loading multiple collection navigations can result in Cartesian product explosions in JOINs.
### Key Performance Bottlenecks
| Scenario | Bottleneck Description | EF Core Feature to Use |
|----------------------------------|-----------------------------------------------------------------------------|-------------------------------|
| Repeated queries with same shape | LINQ-to-SQL compilation on every execution generates overhead | Compiled Queries |
| Loading multiple collections | Single query with multiple Includes causes Cartesian explosion | Split Queries (`AsSplitQuery`) |
| Complex SQL needs | LINQ cannot express all query patterns (e.g., Window functions, hints) | Raw SQL (`FromSqlRaw`) |
| Bulk updates/deletes | Loading entities for modification wastes memory and CPU | Bulk Operations (`Execute*Async`) |
This table clarifies which EF Core features map to common performance problems.
---
## Technique 1: Compiled Queries — Eliminate LINQ Compilation Overhead
EF Core compiles each LINQ expression tree to SQL on every invocation. Frequently called queries pay this cost repeatedly.
`EF.CompileQuery` and `EF.CompileAsyncQuery` lets you cache the compiled delegate once and reuse it for subsequent calls, eliminating runtime translation overhead.
### Minimal Compiled Query Example
```csharp
private static readonly Func<AppDbContext, int, Order?> GetOrderById =
EF.CompileQuery((AppDbContext db, int id) =>
db.Orders.Include(o => o.Lines).FirstOrDefault(o => o.Id == id));
public Order? GetOrderFast(int id) => GetOrderById(_db, id);
Here, the LINQ expression is translated to SQL once and cached. Each call to GetOrderFast invokes the delegate directly.
Notice the benefit when this query is invoked thousands of times per second: the compilation cost is paid once upfront.
Realistic Async Compiled Query with CancellationToken
private static readonly Func<AppDbContext, int, CancellationToken, Task<Order?>> GetOrderByIdAsync =
EF.CompileAsyncQuery((AppDbContext db, int id, CancellationToken ct) =>
db.Orders.Include(o => o.Lines).FirstOrDefault(o => o.Id == id));
public Task<Order?> GetOrderFastAsync(int id, CancellationToken ct) =>
GetOrderByIdAsync(_db, id, ct);
This pattern is optimal for high-throughput microservices requiring minimal query latency and predictable performance.
Technique 2: Split Queries — Avoid Cartesian Explosion
When multiple collection navigation properties are eagerly loaded via Include(), EF Core generates SQL with multiple JOINs. This often causes row multiplication:
- 100 orders × 50 order lines = 5,000 rows returned, even though logically 150 entities.
This slows queries and inflates memory use due to redundant data.
Use AsSplitQuery() to execute separate SQL statements for each collection navigation and merge results in memory, avoiding the Cartesian product.
Example with Split Queries
var orders = await _db.Orders
.Include(o => o.Lines)
.Include(o => o.Customer)
.AsSplitQuery()
.ToListAsync(ct);
EF Core issues separate SQL queries to fetch orders, then lines, then customers. This is often faster and more memory efficient when multiple collections are involved.
When to Use Split Queries
| Scenario | Single Query (Default) | Split Query (With AsSplitQuery) |
|---|---|---|
| Single collection include only | Best: fewer round-trips | Unnecessary overhead |
| Multiple collections includes | Risk of Cartesian explosion | Reduces row multiplication |
| Very large result sets with filtered includes | Can become inefficient | Scales better |
Technique 3: Raw SQL — Query Beyond LINQ’s Expressiveness
LINQ is powerful but cannot express every SQL feature: common table expressions (CTEs), window functions, hints, optimizer directives, etc.
EF Core allows you to inject raw SQL queries into the pipeline safely via FromSqlRaw() or FromSqlInterpolated().
Safe Parameterized Raw SQL Example
var activeOrders = await _db.Orders
.FromSqlRaw("SELECT * FROM Orders WHERE Total > {0} AND Status = {1}", threshold, "Active")
.Include(o => o.Customer)
.AsNoTracking()
.ToListAsync(ct);
Using parameterization avoids SQL injection attacks.
Complex Raw SQL Example with Joins and Ordering
var results = await _db.Set<OrderSummary>()
.FromSqlRaw(@"
SELECT o.Id, c.Name CustomerName, o.Total,
ROW_NUMBER() OVER (PARTITION BY c.Id ORDER BY o.Total DESC) RN
FROM Orders o
JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Status = {0}", "Active")
.Where(os => EF.Property<int>(os, "RN") <= 3)
.ToListAsync(ct);
Here, a Window Function is used to get the top 3 orders per customer, which LINQ can't express.
Technique 4: Bulk Operations — Efficient Updates and Deletes Without Loading Entities
EF Core 7 introduced ExecuteUpdateAsync() and ExecuteDeleteAsync() to perform server-side bulk updates and deletes without fetching entities in memory.
Bulk Update Example (Expire Draft Orders)
var updatedCount = await _db.Orders
.Where(o => o.Status == "Draft")
.ExecuteUpdateAsync(s =>
s.SetProperty(o => o.Status, "Expired"), ct);
Bulk Delete Example (Remove Cancelled Orders Older Than Cutoff)
await _db.Orders
.Where(o => o.PlacedAt < cutoff && o.Status == "Cancelled")
.ExecuteDeleteAsync(ct);
These single SQL statements significantly reduce memory consumption and improve throughput compared to load-modify-save cycles.
Architectural Overview
flowchart TD
A[Client] -->|API call request| B[EF Core Application]
B --> C["Expression Tree Compilation"]
C --> D["SQL Query Generation"]
D --> E[Database]
E --> F["Command Execution"]
F --> G["Data Reader Fetch"]
G --> H["Entity Materialization"]
H --> B
B -->|Returns| A
subgraph Optimizations
C -.-> I[Compiled Queries Cache]
D -.-> J[Split Queries or Raw SQL]
F -.-> K[Bulk Operations]
endHigh-level query flow in EF Core with optimization touchpoints.
Sequence Diagram: Multiple Collection Load with Split Queries
sequenceDiagram
participant App as Application
participant EF as EF Core
participant DB as Database
App->>EF: Query Orders with Includes + AsSplitQuery
EF->>DB: SELECT Orders
DB-->>EF: Orders result set
EF->>DB: SELECT OrderLines WHERE OrderId IN (...)
DB-->>EF: OrderLines result set
EF->>DB: SELECT Customers WHERE CustomerId IN (...)
DB-->>EF: Customers result set
EF->>App: Materialized Order aggregateThe split query strategy triggers multiple database calls executed sequentially, aggregating results afterward.
Comparison Table: Query Fetch Strategies in EF Core
| Feature | When to Use | Pros | Cons |
|---|---|---|---|
| Compiled Queries | High-frequency queries | Eliminates compilation overhead | Requires upfront compile effort |
| Split Queries | Multiple collection includes | Avoids Cartesian explosion | Additional DB round trips |
| Raw SQL | Complex, unsupported LINQ queries | Full SQL expressiveness | Less abstraction, risk of SQL mistakes |
| Bulk Operations | Bulk update/delete | Efficient, no entity loading | No change tracking, limited API |
Observability — What to Log and Monitor
Optimizing EF Core requires good telemetry to identify bottlenecks before and after changes.
What to Log
- Query execution times and SQL generated
- Cache hits/misses for compiled queries
- Query failure errors and exceptions
- Input parameters (safely masked)
- Bulk operation row counts
Structured Log Example (Serilog)
{
"Timestamp": "2024-06-01T18:42:23Z",
"Level": "Information",
"Event": "EFCore_QueryExecuted",
"QueryName": "GetOrderByIdAsync",
"Sql": "SELECT ... WHERE Id = @id",
"DurationMs": 34,
"Parameters": { "id": 12345 },
"CacheHit": true
}
Key Metrics (RED for EF Core query handling)
| Metric | Description | Objective |
|---|---|---|
| Rate | Number of queries per second | Support load smoothly |
| Errors | Number of failed database queries | Minimize errors |
| Duration | Query duration distribution | Most queries < 100ms |
Resource Usage (USE for Database and App Host)
| Metric | Description | Target |
|---|---|---|
| Utilization | CPU, memory, DB connections | Below 70% for stability |
| Saturation | DB connection pool exhaustion | Avoid connection queuing |
| Errors | Number of SQL errors | Near zero |
Distributed Tracing with OpenTelemetry
Instrument EF Core commands to trace query execution across services:
services.AddOpenTelemetryTracing(builder =>
builder
.AddAspNetCoreInstrumentation()
.AddHttpClientInstrumentation()
.AddEntityFrameworkCoreInstrumentation()
.AddJaegerExporter());
This captures spans for incoming HTTP requests, EF Core queries, and downstream database calls, providing end-to-end latency visibility.
SLI & SLO Table Example for EF Core Query Layer
| SLI | SLO |
|---|---|
| 95th percentile latency | < 100 ms query execution time |
| Successful query rate | > 99.9% queries succeed |
| Bulk operation success | 100% without error |
Final Senior Engineer Insights
- Measure before optimizing! Use profiling tools such as
dotnet-counters, Application Insights, or OpenTelemetry tracing. - Start with inexpensive wins such as
AsNoTracking()on readonly queries. - Replace eager-loading loops with
Select()into DTOs to reduce unnecessary joins and entity tracking. - Use
ExecuteUpdateAsyncandExecuteDeleteAsyncfor bulk operations to leverage database capabilities directly. - Employ compiled queries carefully on hot paths where LINQ translation overhead is measurable but avoid premature optimization.
- Consider caching common query results at the application or distributed cache layer for repeated expensive queries.
- Evaluate trade-offs of split queries to balance latency (extra round trips) vs. memory and CPU cost of Cartesian multiplication.
- Document and monitor all performance optimizations as part of your observability strategy.
Inside EF Core's Query Compilation and Execution Pipeline
To master EF Core performance engineering, understanding the intricate mechanics of how EF Core processes queries end-to-end is crucial. This internal knowledge informs when optimizations such as compiled queries or split queries yield real benefits and when they are marginal.
Step 1: Parsing and Expression Tree Translation
EF Core starts query execution by taking the LINQ expression (an abstract syntax tree representing your query) and parsing it into an Expression tree. This tree captures the query shape and intent but is not SQL yet.
During this phase:
- EF Core inspects the LINQ operators (
Where,Select,Include,GroupBy, etc.). - It binds navigation properties, applying configured conventions and Fluent API mappings.
- It attempts to identify server-side translation capabilities — e.g., can a
Wherepredicate be converted to SQL WHERE clause. - Server evaluation is planned: EF Core tries to perform all filtering and shaping in SQL, falling back to client evaluation only when necessary (and warns accordingly).
This phase is costly, especially for dynamically composed queries or those involving complex projections.
Step 2: Query Model Optimization and SQL Generation
Once parsed, EF Core produces an internal query model and applies query optimizations:
- It determines the SQL shape, including JOIN types, table aliases, and projected columns.
- Optimizes loading patterns: decides if eager loading includes should be realized as joins or separate queries.
- Applies any configured query filters (e.g., soft deletes, global filters).
- Maps LINQ constructs into SQL (e.g., translating
Containson a collection toIN). - Binds parameters for safe execution.
Then it generates the SQL command text and parameter list.
Step 3: Command Execution and Data Reader Materialization
The generated SQL command executes over an ADO.NET DbCommand, opening a connection if needed.
Data reader handling involves:
- Reading row-by-row results.
- For each row, populating entity objects according to EF Core’s change tracker rules.
- Handling identity resolution: EF Core ensures only one entity instance per key appears within the query's DbContext scope.
- Applying fix-ups for navigation properties to create the complete object graph.
This stage is CPU and memory intensive if the result set is large or contains many related entities.
Optimizer Internals Affecting Performance
- Client vs Server Evaluation: Earlier versions of EF Core allowed silent client evaluation of parts of queries EF Core could not translate, potentially causing N+1 or huge data transfer issues. Recent versions surface warnings and restrict client evaluation, promoting safer query hygiene.
- Tracking Overhead: The change tracker watches entity states for updates. Disabling tracking (
AsNoTracking) significantly reduces materialization cost for read-only queries. - Navigation Fix-up: Each related entity involved in the aggregate graph may cause EF Core to perform in-memory linking of references, which scales with graph size.
Impact of Compiled Queries on this Flow
A compiled query encapsulates steps 1 and 2 once, reusing the parsed, optimized expression tree and precompiled delegate. Only Step 3 executes on demand, with all wiring pre-established, trimming up to 50-70% of cold-cache overhead in microbenchmarked scenarios.
Real-World Scenario: Processing Large E-Commerce Orders with Multiple Navigations
To illustrate, consider a typical order processing endpoint in a high-scale e-commerce service:
- The endpoint must load orders placed within a date range.
- Each order includes multiple line items with product details.
- The customer info including preferences and address is loaded.
- The endpoint must apply business rules filtering orders by customer loyalty tier.
Naive Implementation Causes Problems
var orders = await _dbContext.Orders
.Include(o => o.Lines)
.ThenInclude(l => l.Product)
.Include(o => o.Customer)
.ThenInclude(c => c.Address)
.Where(o => o.PlacedAt >= startDate && o.PlacedAt <= endDate)
.ToListAsync(ct);
Problems:
- EF Core generates a single large SQL query that joins Orders × Lines × Products × Customers × Addresses.
- Row multiplication due to multiple collection includes causes Cartesian explosion.
- Query can return millions of duplicated rows.
- Server and client memory usage surges.
- Query execution times spike, hurting user experience.
Refined Version with Split Queries and Compiled Query
private static readonly Func<AppDbContext, DateTime, DateTime, CancellationToken, Task<List<Order>>> CompiledOrdersQuery
= EF.CompileAsyncQuery((AppDbContext db, DateTime from, DateTime to, CancellationToken token) =>
db.Orders
.Include(o => o.Lines)
.ThenInclude(l => l.Product)
.Include(o => o.Customer)
.ThenInclude(c => c.Address)
.Where(o => o.PlacedAt >= from && o.PlacedAt <= to)
.AsSplitQuery());
public Task<List<Order>> GetOrdersAsync(DateTime from, DateTime to, CancellationToken ct)
=> CompiledOrdersQuery(_dbContext, from, to, ct);
Improvements:
- The query compilation is done once, reused on each call.
AsSplitQuery()produces multiple SQL statements:- Select orders in date range.
- Select related lines and products filtered by OrderId.
- Select customers with addresses filtered by CustomerId.
- The result set is smaller per SQL call.
- EF Core merges related data in memory with fix-up.
- Memory and CPU overhead for redundant data handling is curtailed.
- Latency improves under load.
Potential Pitfalls to Watch Out For in This Scenario
| Pitfall | Description | Mitigation |
|---|---|---|
| Excess DB Round Trips | Too many queries with nested collections | Limit depth, use .AsSplitQuery() judiciously |
| Change Tracker Overhead | Costly entity tracking on large sets | Use .AsNoTracking() where no updates needed |
| Outdated Query Shape with Compiled | Using compiled query with variable includes or projections | Separate compiled queries per shape; avoid dynamic includes |
| Large Graph Materialization Slowdown | Large object graphs tax app memory | Use DTO projections if you don't need full entities |
Observability Deep Dive: Logging, Metrics, and Tracing EF Core in Production
High-scale services using EF Core must monitor data access flows tightly to detect regressions and capacity limits early.
What Exactly to Log?
Effective EF Core telemetry balances detail with performance:
- Query Execution Events:
- SQL Command Text (with parameters masked or redacted if sensitive).
- Time taken for parsing + compilation (if measurable).
- Time taken for command execution.
- Row counts returned.
- Cache Usage for Compiled Queries:
- Hits vs misses to confirm compiled queries are effective.
- Bulk Operations:
- Number of rows affected.
- Conditions filtered on.
- Exceptions:
- Include EF Core-specific exception types (such as
DbUpdateConcurrencyException).
- Include EF Core-specific exception types (such as
- Concurrency and Deadlocks:
- Log transient errors for retry logic.
Sample Structured Log Entry (Serilog / JSON)
{
"Timestamp": "2024-06-01T19:23:14Z",
"Level": "Information",
"EventId": 20401,
"EventName": "EFCore_QueryExecuted",
"QueryName": "GetOrdersAsync",
"Sql": "SELECT ... FROM Orders WHERE PlacedAt BETWEEN @start AND @end",
"DurationMs": 72,
"RowsReturned": 657,
"CacheHit": true,
"Parameters": {
"start": "2024-05-01T00:00:00Z",
"end": "2024-05-31T23:59:59Z"
}
}
Essential Metrics for the EF Core Query Layer
| Metric | Type | Purpose | Alert Threshold (Example) |
|---|---|---|---|
query_rate | Counter | Number of queries per second | Sudden drops or spikes |
query_error_count | Counter | Number of failed queries | > 0 for 5 consecutive minutes |
query_duration_ms_p95 | Gauge | 95th percentile query latency | > 100ms sustained |
rows_returned_avg | Gauge | Average rows per query | Sudden increase (indicates data explosion) |
cache_hit_ratio | Gauge | Compiled query cache hit ratio | < 80% indicates missing cache use |
Distributed Tracing Integration with OpenTelemetry
Adding EF Core instrumentation to an existing OpenTelemetry collector agent helps map database calls in the full request context:
services.AddOpenTelemetryTracing(builder =>
{
builder
.AddAspNetCoreInstrumentation()
.AddHttpClientInstrumentation()
.AddEntityFrameworkCoreInstrumentation(options =>
{
options.SetDbStatementForText = true; // To capture SQL queries
options.EnrichWithIDbCommand = (activity, command) =>
{
activity.SetTag("db.sql.parameters", command.Parameters.Count);
};
})
.AddJaegerExporter();
});
In the resulting traces:
- Each EF Core query corresponds to a child span with SQL text.
- Capture execution time and potential retries.
- Correlate with upstream HTTP or message queue spans for end-to-end visibility.
- Detect hotspots: long-running queries, implicit client-side evaluation, or frequent retries.
Sample SLI/SLO Table for Production
| SLI | SLO | Rationale |
|---|---|---|
| 99th percentile latency | < 150 ms query execution time | Interactive UI and API responsiveness |
| Successful query rate | > 99.95% | Minimize errors affecting correctness |
| Bulk operation success rate | 100% | Bulk changes must not fail silently |
| Cache hit rate (compiled queries) | > 90% | Ensure repeated queries benefit from caching |
Decision Checklist for Choosing EF Core Performance Techniques
| Factor | Compiled Query | Split Query (AsSplitQuery) | Raw SQL (FromSqlRaw) | Bulk Operations (Execute*Async) |
|---|---|---|---|---|
| Query Shape Stability | Fixed LINQ query shape | Any with multiple collection includes | Complex SQL not expressible in LINQ | Large dataset modifications |
| Query Frequency | Very high frequency (> 1000/sec) | Medium to high where navigation explosion occurs | Rare or situational | Large batch update/delete jobs |
| Performance Bottleneck Type | LINQ parsing & translation overhead | Cartesian explosion due to JOINS | LINQ translation limitations | Load-modify-save overhead |
| Need for Tracking Change | Yes, supports change tracking | Yes | Depends on SQL and query shape | Only for update/delete - no tracking |
| Complexity of Queries | Moderate | Complex with many collections | Extended SQL features (CTEs, window) | Bulk DML only |
| Risk Tolerance | Lower - Fully typed | Medium - multiple DB calls overhead | Higher - raw strings, injection risks | Medium - no EF state tracking |
This decision matrix assists in tactical optimization planning.
Expanded Code Example: Advanced Raw SQL with Mapping to Keyless Entity
EF Core supports mapping raw SQL queries to types without entity tracking or primary key requirements. Useful for reporting queries.
public class OrderSummary
{
public int Id { get; set; }
public string CustomerName { get; set; } = default!;
public decimal Total { get; set; }
public int RowNumber { get; set; }
}
// DB Context registration:
modelBuilder.Entity<OrderSummary>(eb =>
{
eb.HasNoKey();
eb.ToView(null); // Prevent association with a DB view or table
});
public async Task<List<OrderSummary>> GetTopOrdersPerCustomerAsync(string status, CancellationToken ct)
{
var sql = @"
SELECT o.Id, c.Name AS CustomerName, o.Total,
ROW_NUMBER() OVER (PARTITION BY c.Id ORDER BY o.Total DESC) AS RowNumber
FROM Orders o
JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Status = {0}
";
return await _dbContext.Set<OrderSummary>()
.FromSqlRaw(sql, status)
.Where(os => os.RowNumber <= 3)
.ToListAsync(ct);
}
This approach unlocks powerful reporting queries in EF Core while maintaining safety and composability.
Notice the
.HasNoKey()config enables mapping without identity, avoiding EF Core tracking issues with raw SQL projections.
Edge Cases and Failure Modes in EF Core Performance Optimizations
Experienced engineers must anticipate and handle subtle behaviors that occur in production:
| Edge Case / Failure Mode | Cause | Mitigation |
|---|---|---|
| Compiled Query Staleness | Query shape or schema changes invalidate cached delegates | Use distinct compiled queries per shape; version cache on migrations |
| Split Query Extra Round Trips Causing Latency | Latency sensitive apps with lots of includes | Tune split vs join based on response time SLOs; consider selective includes |
| Raw SQL Injection Risk | Using string concatenation for inputs in FromSqlRaw | Always use parameterized queries ({0}, {1} syntax) |
| Bulk Operation Not Updating Change Tracker | Bulk update bypasses change tracker | Reload entities if tracked entities are expected to be refreshed |
| Complex LINQ Queries Default to Client Evaluation | Query includes unsupported functions (local methods) | Profile with detailed logs; rewrite queries or use raw SQL |
| Out-of-Memory on Large Materialization | Very large result sets with deep object graphs | Use batching/paging, or DTO projections rather than full entities |
In-depth knowledge of EF Core internals, combined with precise observability and a rigorous decision framework, enables senior engineers to create data access layers that withstand demanding real-world workloads — offering predictable response times, scalable throughput, and maintainable codebases.
Summary
EF Core brings a convenient LINQ data access experience but understanding its internal query mechanisms unlocks much faster performance:
- Compiled queries eliminate repeated query compilation
- Split queries prevent data explosion from multiple Includes
- Raw SQL handles advanced scenarios beyond LINQ’s reach
- Bulk operations update/delete entities efficiently without loading
Applying these techniques tailored to your workload’s characteristics can transform your data access layer to be scalable, efficient, and maintainable.
Further Reading
- EF Core Compiled Queries - Microsoft Docs
- EF Core Split Queries - Microsoft Docs
- EF Core Raw SQL Queries - Microsoft Docs
- EF Core Bulk Updates/Deletes - Microsoft Docs
- OpenTelemetry .NET Instrumentation
This expanded guide elevates your EF Core query skills from basic to robust, preparing you well for senior level interview questions and real-world performance tuning.