EF Core Advanced Querying Patterns: Master Complex Queries in C#

Entity Framework Core (EF Core) is a powerful ORM that simplifies data access in .NET applications. However, as your application grows, you'll encounter scenarios where basic LINQ queries fall short. Performance bottlenecks, complex joins, multi-tenancy, and soft delete requirements demand advanced querying patterns. In this tutorial, we'll dive deep into EF Core's advanced querying capabilities: compiled queries for high-performance repeated execution, raw SQL for complex operations, global query filters for automatic data scoping, and more. You'll learn how to optimize your queries, avoid common pitfalls like the N+1 problem, and handle real-world production scenarios. We'll also walk through a production incident where a missing global filter caused a data leak, and provide a debugging guide to quickly diagnose query issues. By the end, you'll be equipped to write efficient, maintainable, and secure data access code in C#.

1. Compiled Queries for High Performance

Compiled queries allow EF Core to cache the query plan, reducing overhead for frequently executed queries. This is especially useful for queries that are executed many times with different parameters. Use EF.CompileQuery or EF.CompileAsyncQuery to create a delegate that can be reused. The delegate takes a DbContext and parameters, returning the result. Example: a query to fetch orders by customer ID. The compiled query is thread-safe and can be stored as a static field. Note that compiled queries cannot use Include with ThenInclude in some versions; test thoroughly.

private static readonly Func<AppDbContext, int, IQueryable<Order>> GetOrdersByCustomer = 
    EF.CompileQuery((AppDbContext context, int customerId) =>
        context.Orders.Where(o => o.CustomerId == customerId).OrderBy(o => o.OrderDate));

public IQueryable<Order> GetOrders(int customerId)
{
    using var context = new AppDbContext();
    return GetOrdersByCustomer(context, customerId);
}

2. Raw SQL Queries with FromSqlRaw and FromSqlInterpolated

Sometimes LINQ cannot express the query efficiently, or you need to use database-specific features. EF Core provides FromSqlRaw and FromSqlInterpolated to execute raw SQL. FromSqlRaw is for parameterized queries (use with caution to avoid SQL injection). FromSqlInterpolated handles interpolation safely. These methods return IQueryable<T> so you can compose further LINQ operators. Example: a full-text search query. Note that raw SQL queries bypass global query filters unless you include them manually. Also, the result set must match the entity shape.

var searchTerm = "EF Core";
var blogs = await context.Blogs
    .FromSqlInterpolated($@"SELECT * FROM Blogs 
        WHERE CONTAINS(Title, {searchTerm})")
    .Where(b => b.IsActive)
    .ToListAsync();

3. Global Query Filters for Multi-Tenancy and Soft Delete

Global query filters are LINQ predicates applied automatically to all queries for an entity. They are defined in OnModelCreating using HasQueryFilter. Common use cases: multi-tenancy (filter by TenantId), soft delete (IsDeleted == false), and active records. Filters are applied to all queries including Include and navigation properties. To bypass a filter, use IgnoreQueryFilters. Example: a multi-tenant system where each user sees only their organization's data. The filter is applied at the DbContext level, typically using a scoped service for the tenant ID.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>().HasQueryFilter(o => o.TenantId == _tenantId);
    modelBuilder.Entity<Customer>().HasQueryFilter(c => !c.IsDeleted);
}

// To bypass:
var allOrders = await context.Orders.IgnoreQueryFilters().ToListAsync();

4. Eager Loading vs. Explicit Loading vs. Lazy Loading

EF Core supports three loading patterns for related data. Eager loading uses Include/ThenInclude to load related data in a single query. Explicit loading loads related data on demand using Load/Query. Lazy loading (requires proxy) loads navigation properties automatically when accessed. Each has trade-offs: eager loading can cause large joins, lazy loading leads to N+1 queries, explicit loading gives control. Best practice: use eager loading for known relationships, explicit loading for optional data, and avoid lazy loading in production. Example: loading orders with items and product details.

// Eager loading
var orders = await context.Orders
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .ToListAsync();

// Explicit loading
var order = await context.Orders.FindAsync(1);
await context.Entry(order).Collection(o => o.OrderItems).LoadAsync();

// Lazy loading (requires proxy)
public class Order
{
    public virtual ICollection<OrderItem> OrderItems { get; set; }
}

5. Split Queries to Avoid Cartesian Explosion

When including multiple collections, EF Core generates a single query with joins that can cause a Cartesian explosion (rows multiplied). Split queries execute multiple queries, one per collection, reducing data duplication. Use AsSplitQuery() to enable. However, split queries increase round trips and may cause inconsistent data if not wrapped in a transaction. Example: loading orders with items and shipments. For large datasets, split queries are often faster. Note: split queries are not supported for some providers.

var orders = await context.Orders
    .Include(o => o.OrderItems)
    .Include(o => o.Shipments)
    .AsSplitQuery()
    .ToListAsync();

6. Query Tags for Debugging and Monitoring

Query tags allow you to annotate generated SQL with a custom string, making it easier to identify queries in logs and database traces. Use TagWith() to add a tag. Tags appear as comments in the SQL. This is invaluable for debugging performance issues in production. Example: tagging a query with the calling method name. You can also use multiple tags. Tags are not SQL-injectable.

var orders = await context.Orders
    .Where(o => o.CustomerId == customerId)
    .TagWith("GetOrdersByCustomer")
    .ToListAsync();
// Generated SQL: -- GetOrdersByCustomer
// SELECT ... FROM Orders WHERE CustomerId = @__customerId_0