EF Core Complex Query Optimization Guide: Include, Dynamic Filtering, and Performance Tuning - Devuly | Smart Analytics for Developers & Projects

This article focuses on the most common pain points in EF Core complex queries: multi-table relationships are hard to write, dynamic conditions become messy, and query performance can easily collapse. The core solutions include Include/ThenInclude, expression tree-based filtering, and performance tuning with AsNoTracking and projections. Keywords: EF Core, complex queries, performance optimization.

Table of Contents

Technical Specifications Snapshot

Parameter	Description
Technical Topic	EF Core complex query practices
Language	C# / SQL
Runtime	.NET
Core Protocols	LINQ to Entities / SQL
Stars	Not provided in the original article
Core Dependencies	Microsoft.EntityFrameworkCore, LINQ, Expression Trees
Typical Scenarios	Multi-table joins, dynamic filtering, pagination and sorting, aggregate statistics

The key to EF Core complex queries is not syntax, but SQL awareness

When many developers write EF Core, their first instinct is, “How do I express this in LINQ?” But what actually determines quality is the SQL that LINQ ultimately translates into. Once a complex query loses the SQL perspective, it becomes easy to create slow queries, redundant loading, and memory bloat.

It is more accurate to think of EF Core as a type-safe SQL generator. You are not writing magic—you are describing the upstream intent of a database execution plan. Once you adopt this mindset, designing complex queries becomes much clearer.

The three capabilities worth mastering first

Use Include and ThenInclude to handle relationship loading.
Use expression trees or PredicateBuilder for dynamic filtering.
Use projections, no-tracking queries, and split queries to control performance costs.

var blogs = await context.Blogs
    .Include(b => b.Posts) // Load the blog's post collection
    .ThenInclude(p => p.Author) // Continue loading each post's author
    .ToListAsync(); // Execute the query

This code loads three levels of related data—Blog, Post, and Author—in a single query flow.

Relationship queries should prioritize the correct data shape

In a typical one-to-many plus many-to-one structure such as Blog, Post, and Author, Include is the most direct way to eagerly load related data. It fits scenarios where you need an entity object graph, not scenarios where you only want a minimal result set.

If you only need display fields, blindly using Include often loads far more data than necessary. In that case, prefer Select projections instead of stacking more navigation properties.

Filtered Include can reduce unnecessary data loading

var blogs = await context.Blogs
    .Include(b => b.Posts.Where(p => p.PublishDate.Year == DateTime.Now.Year)) // Load only posts from the current year
    .ToListAsync();

This code eagerly loads only the related collection entries that match the condition, reducing meaningless data transfer.

The deeper the Include chain, the more you should watch for Cartesian explosion

When a query includes multiple collection navigation properties at the same time, a single SQL statement can easily produce duplicated rows because of JOIN expansion. It may look like one query, but in practice it can multiply the result set many times over.

var blogs = await context.Blogs
    .Include(b => b.Posts) // Collection one
    .Include(b => b.Comments) // Collection two
    .AsSplitQuery() // Split into multiple SQL queries to avoid result set explosion
    .ToListAsync();

This code executes multiple collection relationships as split queries to reduce the risk of Cartesian explosion.

The essence of dynamic filtering is safely composing translatable expressions

Business filtering conditions are often optional: name, price range, category, and status may all be empty. If you write each condition as a separate if, the code quickly grows out of control and becomes hard to reuse.

The core of dynamic querying is not “building SQL with dynamic strings,” but dynamically composing expression trees. This preserves type safety while still allowing EF Core to translate the query to database-side execution.

Simple condition composition works well when you start from IQueryable

public async Task<List<Product>> SearchProductsAsync(string? name, decimal? minPrice, decimal? maxPrice)
{
    var query = context.Products.AsQueryable(); // Preserve deferred execution

    if (!string.IsNullOrWhiteSpace(name))
        query = query.Where(p => p.Name.Contains(name)); // Filter by name

    if (minPrice.HasValue)
        query = query.Where(p => p.Price >= minPrice.Value); // Filter by minimum price

    if (maxPrice.HasValue)
        query = query.Where(p => p.Price <= maxPrice.Value); // Filter by maximum price

    return await query.ToListAsync(); // Execute once in a unified way
}

This code appends Where clauses on demand to build a translatable dynamic query.

PredicateBuilder is a better fit for complex boolean combinations

var predicate = PredicateBuilder.True
<Product>(); // Initial condition is always true

if (!string.IsNullOrWhiteSpace(name))
    predicate = predicate.And(p => p.Name.Contains(name)); // Append name condition

if (minPrice.HasValue)
    predicate = predicate.And(p => p.Price >= minPrice.Value); // Append lower-bound condition

var products = await context.Products
    .Where(predicate) // Combined expression tree
    .ToListAsync();

This code combines multiple optional filters into one unified expression.

Custom methods that cannot be translated will destroy performance directly

Do not put local methods such as MyUtil.IsHotProduct(p) inside expressions. If EF Core cannot translate them, it may either throw an exception or degrade into client-side evaluation—pulling the entire table into memory and filtering afterward, which is extremely expensive.

Pagination queries must be built on top of stable ordering

Pagination is a high-frequency requirement in admin dashboards, management systems, and search pages. The most common mistake is not writing Skip and Take incorrectly, but forgetting to sort first, which leads to unstable results or even translation errors.

Pagination, sorting, and filtering should be composed in a fixed order

var query = context.Products.AsQueryable();

query = query.Where(p => p.Name.Contains(searchTerm)); // Filter first
query = query.OrderBy(p => p.Id); // Then sort to guarantee stable pagination

var items = await query
    .Skip((pageIndex - 1) * pageSize) // Skip previous pages
    .Take(pageSize) // Take current page data
    .ToListAsync();

This code generates a stable result set using the sequence: filter → sort → paginate.

Aggregate queries should be pushed to the database whenever possible

Operations such as GroupBy, Count, Sum, and Average are exactly what databases do best. If you fetch the data into memory first and then aggregate, the query becomes slower and amplifies both network and memory costs.

var categoryStats = await context.Products
    .GroupBy(p => p.CategoryId) // Group by category
    .Select(g => new
    {
        CategoryId = g.Key,
        ProductCount = g.Count(), // Count items
        AvgPrice = g.Average(p => p.Price), // Calculate average price
        TotalRevenue = g.Sum(p => p.Price * p.SalesCount) // Sum total revenue
    })
    .ToListAsync();

This code pushes grouped statistics directly down to the database for execution.

Five performance optimization rules cover most slow-query scenarios

Read-only queries should use AsNoTracking by default

var products = await context.Products
    .AsNoTracking() // Disable change tracking to reduce memory and CPU overhead
    .Where(p => p.Price > 100)
    .ToListAsync();

This code removes entity tracking overhead in read-only scenarios.

Projection is more efficient than loading full entities

var productDtos = await context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price // Select only the required fields
    })
    .ToListAsync();

This code fetches only the fields the business logic actually needs, reducing transfer and materialization costs.

Logging is the lowest-cost way to diagnose EF Core query issues

optionsBuilder
    .LogTo(Console.WriteLine, LogLevel.Information) // Output the generated SQL
    .EnableSensitiveDataLogging(); // Show parameter values during debugging

This code prints the SQL generated by EF Core, making it easier to diagnose N+1 issues, table scans, and incorrect translations.

A decision table for complex queries can significantly reduce common mistakes

Scenario	Recommended Approach	Risk Warning
Multi-table entity loading	`Include` + `ThenInclude`	Too many JOINs can inflate the result set
Dynamic multi-condition filtering	`IQueryable` + expression trees	Avoid non-translatable methods
Read-only list pages	`AsNoTracking` + `Select`	Do not load full entities by default
Multiple collection navigation properties	`AsSplitQuery`	Prevent Cartesian explosion
Pagination over large datasets	`Skip/Take` after sorting	Pagination is unstable without ordering
Aggregate statistics	`GroupBy` + aggregate functions	Prefer database-side execution

FAQ

When should you use Include in EF Core, and when should you use Select?

If your goal is to get a complete entity object graph, prefer Include. If your goal is an API response or UI display fields, prefer Select projections because they are lighter and easier to control.

Why must you sort before pagination?

Because database result sets do not have a stable default order. Using Skip/Take without OrderBy produces non-deterministic results, and in some scenarios EF Core will refuse to translate the query.

How can you quickly tell whether a query has performance problems?

Start by inspecting the generated SQL, then check for table scans, repeated JOINs, N+1 queries, and overly wide column sets. Enable LogTo during debugging, and in most cases you can identify the main issue within minutes.

Core summary: This article reconstructs the core methods for EF Core complex queries, covering relationship loading with Include/ThenInclude, dynamic filtering with expression trees, pagination and sorting, aggregate statistics, and five performance optimization rules. It helps .NET developers avoid N+1 issues, Cartesian explosion, and client-side evaluation.