Optimizing EF Core Queries for Large Data Applications | ASP.NET CORE

Introduction

As your application grows, so does your data — and that’s when Entity Framework Core (EF Core) performance can become a serious concern.
Large datasets, complex joins, and frequent API calls can slow down your system if queries aren’t optimized properly.

This article provides a step-by-step guide to optimizing EF Core queries in ASP.NET Core applications, focusing on real-world techniques, query tuning strategies, and best practices for handling millions of records efficiently.

Why EF Core Query Optimization Matters

EF Core is powerful and easy to use, but its simplicity can sometimes hide performance costs.
Poorly designed queries can lead to:

• Excessive database round trips

• High memory usage

• Unnecessary data loading

• Long response times

The goal is to write efficient, predictable, and scalable queries without losing the benefits of EF Core’s abstraction.

Technical Workflow (Flowchart)

flowchart TD
A[Client Sends API Request] --> B[Controller Executes EF Core Query]
B --> C[Query Translation to SQL]
C --> D[Database Execution Plan]
D --> E[Database Returns Data]
E --> F[EF Core Materializes Entities]
F --> G[Optimized Data Returned to Client]

Step 1: Use AsNoTracking() for Read-Only Queries

By default, EF Core tracks every entity it retrieves — this tracking consumes memory and slows down queries.

When you don’t need to update the data, always use AsNoTracking().

var products = _context.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToList();

When to use

• Dashboard data

• Reports

• GET APIs

Performance gain: Up to 30–40% faster for large datasets.

Step 2: Load Only Required Columns (Projection)

Fetching all columns wastes bandwidth and memory.
Use projection with Select to retrieve only what you need.

var productList = _context.Products
    .Where(p => p.IsActive)
    .Select(p => new 
    {
        p.Id,
        p.Name,
        p.Price
    })
    .ToList();

Best practice: Always project to a lightweight DTO (Data Transfer Object).

Step 3: Use Pagination for Large Data

Never load thousands of records in one go.
Implement server-side pagination with Skip() and Take().

int page = 1;
int pageSize = 100;

var pagedProducts = _context.Products
    .AsNoTracking()
    .OrderBy(p => p.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToList();

Why: Reduces memory footprint and improves response time.

Step 4: Avoid N+1 Query Problems (Use Include and ThenInclude)

The N+1 problem happens when EF Core loads related data separately for each entity.
Use eager loading to fetch related data in one query.

var orders = _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .ThenInclude(i => i.Product)
    .AsNoTracking()
    .ToList();

Alternative
If related data is optional or large, consider explicit loading instead of eager loading.

Step 5: Use Compiled Queries for Frequent Executions

If you execute the same query repeatedly, EF Core can compile it once and reuse it.

private static readonly Func<AppDbContext, decimal, IEnumerable<Product>> _compiledQuery =
    EF.CompileQuery((AppDbContext context, decimal price) =>
        context.Products.Where(p => p.Price > price));

var result = _compiledQuery(_context, 100);

Use case: High-frequency queries (e.g., dropdowns, master data).

Step 6: Filter on the Server, Not in Memory

A common mistake is bringing data into memory before filtering.
Always ensure filtering happens before ToList().

❌ Bad

var data = _context.Products.ToList()
             .Where(p => p.Price > 500);

✅ Good

var data = _context.Products
             .Where(p => p.Price > 500)
             .ToList();

Step 7: Use Raw SQL for Complex Queries

For extremely large or complex data operations, EF Core LINQ might not generate optimal SQL.
Use FromSqlRaw when you need full SQL control.

var result = _context.Products
    .FromSqlRaw("SELECT Id, Name, Price FROM Products WHERE Price > 1000")
    .AsNoTracking()
    .ToList();

Best for: Reports, analytics, and performance-critical modules.

Step 8: Indexing and Database Optimization

No matter how good your C# code is — database indexing matters the most.

Tips

• Add indexes on frequently filtered or joined columns.

• Use SQL Server Profiler to analyze slow queries.

• Keep indexes updated with maintenance jobs.

Example:

CREATE INDEX IX_Products_IsActive ON Products(IsActive);
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

Step 9: Avoid Lazy Loading in Large Systems

Lazy loading fetches data automatically when navigation properties are accessed — but it can lead to multiple database hits.

Disable it unless absolutely necessary:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseLazyLoadingProxies(false));

Use Include() or explicit loading instead.

Step 10: Optimize Tracking and Change Detection

When performing bulk inserts/updates, disable automatic change detection:

_context.ChangeTracker.AutoDetectChangesEnabled = false;

foreach (var item in bulkItems)
{
    _context.Add(item);
}

_context.SaveChanges();
_context.ChangeTracker.AutoDetectChangesEnabled = true;

This speeds up bulk operations significantly.

Step 11: Caching Frequent Data

Combine EF Core with MemoryCache or Redis to reduce database hits.

var cacheKey = "ActiveProducts";
if (!_cache.TryGetValue(cacheKey, out List<Product> products))
{
    products = _context.Products
        .AsNoTracking()
        .Where(p => p.IsActive)
        .ToList();

    _cache.Set(cacheKey, products, TimeSpan.FromMinutes(10));
}
return products;

Tip: Cache small, rarely changing data such as configuration or master lists.

Step 12: Analyze Queries Using Logging

Enable EF Core logging to monitor query execution time and SQL translation:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));

This helps identify inefficient joins or missing indexes.

Common EF Core Performance Checklist

Area	Practice	Example
Tracking	Disable for read-only queries	.AsNoTracking()
Projection	Use DTOs	.Select(x => new DTO { ... })
Pagination	Always use Skip/Take	.Skip().Take()
N+1 Queries	Use Include or projection	.Include()
Lazy Loading	Disable by default	.UseLazyLoadingProxies(false)
Compiled Queries	For repetitive logic	EF.CompileQuery()
Bulk Ops	Disable AutoDetectChanges	ChangeTracker.AutoDetectChangesEnabled = false
Caching	Memory/Redis	_cache.TryGetValue()

Real-World Example: Large Product Catalog API

[HttpGet("catalog")]
public IActionResult GetCatalog(int page = 1, int pageSize = 50)
{
    var data = _context.Products
        .AsNoTracking()
        .Where(p => p.IsActive)
        .OrderBy(p => p.Name)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(p => new ProductDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price
        })
        .ToList();

    return Ok(data);
}

This API efficiently handles hundreds of thousands of records with pagination, projection, and no tracking.

Conclusion

Optimizing EF Core queries is all about understanding how EF Core interacts with SQL Server and minimizing unnecessary data movement.

When building large-scale applications:

• Always measure query performance.

• Use lightweight DTOs.

• Cache frequently accessed data.

• Profile and tune regularly.