Using EF Core Like a Pro: Performance, Patterns, and Modern Features in .NET 8–10

Posted by:

Rob Hutton

|

On:

|

, , ,

Master modern EF Core features — compiled queries, split queries, JSON columns, EF.Functions, and global filters — to write clean, efficient, and scalable data access code.

Read article on Medium.

Entity Framework Core has evolved into a powerful, full-featured ORM that lets .NET developers work efficiently with relational databases while keeping code clean and maintainable.

With EF Core 8+ and beyond, new features like compiled queries, advanced aggregations, split queries, JSON column support, EF.Functions, and global query filters make it easier than ever to write high-performance, expressive, and maintainable data access code.

In this article, we’ll explore practical, real-world examples that demonstrate how to leverage these tools to optimize queries, simplify complex data operations, and avoid common pitfalls — all without sacrificing readability or domain modeling.

1. Compiled Queries: Hot Path Optimization

In EF Core, every LINQ query is translated into SQL each time it runs. For most applications, this is fine . However, in high-traffic APIs or performance-critical paths, this translation can become a bottleneck. That’s where compiled queries shine.

A compiled query is essentially a preprocessed version of a LINQ query that EF Core stores in memory so it doesn’t have to translate it into SQL every time it executes. By caching the query plan, EF Core can skip the parsing and translation steps, which reduces CPU overhead and speeds up repeated executions, especially for queries that run hundreds or thousands of times per second.

That said, compiled queries aren’t a silver bullet. They add some upfront complexity, and creating them for queries that are executed only occasionally provides little benefit. Overusing them everywhere can make your code harder to maintain without noticeable performance gains. The best practice is to reserve compiled queries for “hot paths”, those queries that are executed very frequently or are part of latency-sensitive operations.

✨Example: fetching a user by ID frequently in a Blazor app or API endpoint.

✅Reduces the overhead of repeated LINQ-to-SQL translation.

✅Especially beneficial for hot paths like dashboards, lookups, or frequently accessed entities.

✅Can be combined with AsNoTracking for even better read performance.

// Standard LINQ query
var user = await db.Users
    .AsNoTracking()
    .FirstOrDefaultAsync(u => u.Id == userId);

// Compiled query version
private static readonly Func<AppDbContext, Guid, User?> _getUserById =
    EF.CompileQuery(
        (AppDbContext db, Guid id) =>
            db.Users
              .AsNoTracking()
              .FirstOrDefault(u => u.Id == id));

// Usage
var userCompiled = _getUserById(db, userId);

💡Don’t use compiled queries everywhere.
Reserve them for queries executed frequently.

2. Tracking vs No-Tracking and Identity Resolution

EF Core allows entities to be tracked or untracked. Tracking is convenient for updates, but comes with a cost in memory and change tracking.

  • Tracking: EF Core remembers every entity you load. If two queries return the same entity (same primary key), EF Core gives you the same object instance. This is called identity resolution, and it happens automatically for tracked queries.
  • No-Tracking (AsNoTracking()): EF Core doesn’t track entities. Every time you query the same entity, EF Core creates a new object instance, even if it represents the same database row. This saves memory and CPU, but sometimes leads to duplicate objects.

Why identity resolution matters

Imagine you load orders with their products:

var orders = db.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .ToList();
  • Without identity resolution (AsNoTracking()), if multiple orders include the same product, EF Core will create separate Product objects for each occurrence.
  • This is inefficient and can cause problems if you:
  • Compare object references (product1 == product2 is false even if it’s the same product)
  • Serialize objects to JSON and end up with duplicates
  • Rely on reference equality in your code

Why combine no-tracking with identity resolution

  • Memory-efficient: You skip full change tracking for all entities.
  • Safe object graph: Navigation properties referencing the same entity will now share the same object, which avoids duplication.
  • Perfect for read-heavy queries: Dashboards, reporting, or API endpoints where you load entities for read-only purposes but still want a consistent object graph.

✨Example: no-tracking with identity resolution

  • Multiple orders containing the same product will all point to the same Product instance.
  • Still no change tracking overhead, so it’s fast.
var orders = await db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .Where(o => o.OrderDate >= DateTime.UtcNow.AddDays(-30))
    .ToListAsync();

💡No-tracking with Identity Resolution is the best of both worlds for read-heavy queries: low memory + consistent object references.

3. Bulk Operations: ExecuteUpdate & ExecuteDelete

Older EF Core versions required you to load entities into memory, modify them, and then call SaveChanges(). For bulk updates or deletes, this is inefficient and slow.

EF Core 8+ introduces ExecuteUpdate and ExecuteDelete, allowing changes directly in the database.

✨Example: delete orders older than a year

This example deletes all orders older than a year directly in the database without loading them into memory, which improves performance, reduces memory usage, and avoids unnecessary change tracking.

await db.Orders
    .Where(o => o.OrderDate < DateTime.UtcNow.AddYears(-1))
    .ExecuteDeleteAsync();

✨Example: bulk update order statuses

This example updates all pending orders older than a week directly in the database to mark them as “Expired”, avoiding loading entities into memory and improving performance for bulk operations.

await db.Orders
    .Where(o => o.Status == "Pending" && o.OrderDate < DateTime.UtcNow.AddDays(-7))
    .ExecuteUpdateAsync(o => o.SetProperty(order => order.Status, "Expired"));

✅Eliminates the overhead of fetching entities into memory.

✅Reduces database round-trips and memory usage.

✅Perfect for maintenance tasks, scheduled jobs, or background services.

💡Use these methods for hot paths and bulk operations, but remember that they bypass EF Core change tracking. This means that EF Core doesn’t know what changed, so you can’t rely on any automatic updates to tracked entities.

4. Modern Aggregates & GroupBy

EF Core 8+ now translates Count, Sum, Min/Max, Average, and GroupBy directly into SQL, eliminating the need to pull data into memory for aggregation. This makes reporting, dashboards, and analytics much faster and more efficient.

✨Example: Group By

This query calculates the number of orders and total sales per customer for the last month directly in the database, which is efficient because it avoids loading all orders into memory and performs aggregation on the server.

var orderStats = await db.Orders
    .Where(o => o.OrderDate >= DateTime.UtcNow.AddMonths(-1))
    .GroupBy(o => o.CustomerId)
    .Select(g => new 
    {
        CustomerId = g.Key,
        OrdersCount = g.Count(),
        TotalAmount = g.Sum(o => o.Total)
    })
    .ToListAsync();

✅The aggregation is performed on the server, not in memory.

✅Reduces the amount of data transferred and memory usage.

✅Ideal for dashboards, analytics, or reporting APIs.

💡Combine with compiled queries if this is a hot path that runs frequently.

✨Example: Count

This query counts the number of orders each customer made in the last month directly in the database, which is beneficial because it efficiently aggregates data on the server without loading all orders into memory.

// Count the number of orders per customer in the last month
var ordersPerCustomer = await db.Orders
    .Where(o => o.OrderDate >= DateTime.UtcNow.AddMonths(-1))
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        OrdersCount = g.Count()
    })
    .ToListAsync();

✅Quickly see how many orders each customer made without loading all order details.

✨Example: Sum

This query calculates the total sales per customer directly in the database, which is beneficial because it performs the aggregation server-side, avoiding the overhead of loading all orders into memory.

// Total sales per customer
var totalSales = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        TotalAmount = g.Sum(o => o.Total)
    })
    .ToListAsync();

✅Calculate totals for reporting or dashboards efficiently on the server side.

✨Example: Min / Max

This query retrieves the earliest and latest order dates for each customer directly in the database, which is beneficial because it provides key timeline insights efficiently without loading all order records into memory.

// Find the earliest and latest order for each customer
var orderDates = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        FirstOrder = g.Min(o => o.OrderDate),
        LastOrder = g.Max(o => o.OrderDate)
    })
    .ToListAsync();

✅ Great for metrics like first purchase or most recent purchase without extra queries.

✨Example: Average

This query calculates the average order value per customer directly in the database, which is beneficial because it provides insights into customer spending efficiently without loading all orders into memory.

// Average order value per customer
var averageOrder = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        AverageOrderValue = g.Average(o => o.Total)
    })
    .ToListAsync();

✅Useful for analyzing spending behavior or segmenting customers by average order size.

✨Example: Multiple Aggregates Together

This query computes multiple aggregates per customer — including count, total, minimum, maximum, and average order values — directly in the database, which is beneficial because it provides a complete snapshot of customer activity efficiently without loading all orders into memory.

// Combine Count, Sum, Min, Max, Average
var customerStats = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        OrdersCount = g.Count(),
        TotalAmount = g.Sum(o => o.Total),
        MinOrder = g.Min(o => o.Total),
        MaxOrder = g.Max(o => o.Total),
        AverageOrder = g.Average(o => o.Total)
    })
    .ToListAsync();

✅ One query gives you a full snapshot of customer order activity, all in a single server-side operation.

5. Split Queries & Include Optimization

When querying entities with multiple related tables (Include / ThenInclude), EF Core can generate huge SQL joins that create duplicate rows and slow queries, a problem known as a cartesian explosion.

EF Core 8+ introduces split queries (AsSplitQuery()) that execute multiple smaller queries instead of one giant join.

Split Query Benefits

  • Split queries are most helpful when including multiple collections, like Include(a => a.Items).ThenInclude(i => i.Product) or Include on multiple navigation properties.
  • For single navigation properties, a regular join is usually fine, so it’s worth mentioning don’t overuse split queries unnecessarily.

Split Query Trade Offs

  • Each split query sends more round-trips to the database, so there’s a small overhead if you’re including many large collections.
  • The performance gain comes from avoiding huge Cartesian products in memory, but in some cases, you might measure a tiny latency increase in SQL round-trips.

✨Example: Load orders with items and products safely

This query loads active customers along with their orders, order items, products, and addresses using split queries without tracking, which is beneficial because it avoids huge joins, prevents duplicate entities in memory, reduces the risk of cartesian explosion, and keeps queries fast and memory-efficient for deeply nested relationships.

var customers = await db.Customers
    .AsNoTracking()
    .AsSplitQuery()
    .Include(c => c.Orders)
        .ThenInclude(o => o.Items)
            .ThenInclude(i => i.Product)
    .Include(c => c.Addresses)
    .Where(c => c.IsActive)
    .ToListAsync();

✅Reduces SQL query size and complexity.

✅Prevents loading duplicate entities in memory.

✅Keeps queries fast and memory-efficient, especially for deeply nested relationships.

💡Combine split queries with logging or interceptors to see query execution times for each part. This helps decide whether split queries actually improve performance in your scenario.

6. Interceptors & Logging

EF Core lets you intercept queries, commands, and save operations, giving you deep insight into your database activity. This is invaluable for performance monitoring, detecting N+1 queries, or automatically setting audit fields.

✨Example: Logging long queries

This interceptor logs long-running SQL queries before they execute, which is beneficial because it helps detect performance issues, identify inefficient queries, and monitor database activity without changing application logic.

public class QueryInterceptor : DbCommandInterceptor
{
    public override async Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        if (command.CommandText.Length > 1000)
        {
            Console.WriteLine($"Long query detected: {command.CommandText}");
        }

        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
}

✨Example: Query tagging for easier profiling

This query fetches active customers while tagging the SQL for easier identification in logs or profiling, which is beneficial because it helps track and debug queries in complex applications without affecting performance.

var activeCustomers = await db.Customers
    .TagWith("Fetching active customers for dashboard")
    .Where(c => c.IsActive)
    .ToListAsync();

✅Identify slow or problematic queries in production.

✅Automatically enforce auditing or monitoring policies.

✅Gives visibility into EF Core behavior without changing your business logic.

7. JSON Columns & Flexible Data

EF Core 8+ lets you map JSON columns to properties, so you can store complex, semi-structured data while still being able to query it efficiently. This is perfect for dynamic attributes, settings, or metadata.

✨Example: Filter products by JSON metadata

This query fetches all products whose Metadata JSON column has a Color property equal to “Red”, which is beneficial because it allows filtering on structured JSON data directly in the database without loading all product records into memory.

var redProducts = await db.Products
    .Where(p => p.Metadata["Color"].ToString() == "Red")
    .ToListAsync();

✅Flexible schema without changing database structure.

✅Queryable directly in SQL without the need to pull all JSON into memory.

✅Ideal for dashboards, product catalogs, or optional entity attributes.

💡Combine JSON columns with AsNoTracking() for read-heavy reporting scenarios.

8. Raw SQL Queries & Mapping to DTOs

Sometimes LINQ isn’t enough, especially for complex joins, grouping, or database-specific functions. EF Core allows raw SQL queries that map directly to DTOs, bypassing unnecessary tracking.

✨Example: Top customers by total spending

This query retrieves top customers whose total spending exceeds a given threshold using raw SQL mapped to a DTO, which is beneficial because it allows complex aggregations and filtering that would be cumbersome or less efficient in LINQ, while avoiding loading unnecessary entity data into memory.

var topCustomers = await db.TopCustomers
    .FromSqlInterpolated($@"
        SELECT c.Id, c.Name, SUM(o.Total) as TotalSpent
        FROM Customers c
        JOIN Orders o ON c.Id = o.CustomerId
        GROUP BY c.Id, c.Name
        HAVING SUM(o.Total) > {minSpend}
    ")
    .ToListAsync();

✅Access complex SQL features not yet fully supported in LINQ.

✅Map directly to DTOs for APIs or reports.

✅Avoids the overhead of loading full entities into memory.

💡Always parameterize your raw SQL (or use FromSqlInterpolated) to prevent SQL injection.

9. EF.Functions & SQL Helpers

EF Core exposes database functions directly in LINQ, allowing you to perform SQL-level operations efficiently without writing raw SQL. These functions make queries cleaner, composable, and highly performant.

✨Example: Date Difference Calculations

This query fetches orders from the last 30 days using SQL’s DateDiffDay function, which is beneficial because it filters efficiently on the server without loading all orders into memory.

// Orders in the last 30 days
var recentOrders = await db.Orders
    .Where(o => EF.Functions.DateDiffDay(o.OrderDate, DateTime.UtcNow) < 30)
    .ToListAsync();

✨Example: Pattern Matching with LIKE

This query retrieves products whose names contain “Red” using SQL LIKE, which is beneficial because it performs efficient pattern matching directly in the database without extra client-side filtering.

var redProducts = await db.Products
    .Where(p => EF.Functions.Like(p.Name, "%Red%"))
    .ToListAsync();

✨Example: Case-Insensitive Pattern Matching (PostgreSQL)

This query fetches products with “red” in their name, ignoring case using ILike, which is beneficial for databases like PostgreSQL that require case-insensitive searches without additional string manipulation in memory.

var redProductsIgnoreCase = await db.Products
    .Where(p => EF.Functions.ILike(p.Name, "%red%"))
    .ToListAsync();

✨Example: Case-Insensitive Pattern Matching (SQL)

This query retrieves products whose names contain “red” using SQL Server’s LIKE function, which is beneficial because it performs efficient pattern matching directly in the database without extra client-side filtering.

var redProducts = await db.Products
    .Where(p => EF.Functions.Like(p.Name, "%red%"))
    .ToListAsync();

✅ILike → PostgreSQL only.

✅Like → SQL Server (case-insensitive by default).

✅If you want cross-database case-insensitive pattern matching, use LIKE with an appropriate collation for SQL Server or ILIKE for PostgreSQL.

✨Example: Collation-Based Sorting

This query orders customers using a specific SQL collation, which is beneficial because it ensures consistent sorting and comparison rules without altering the database schema.

var sortedCustomers = await db.Customers
    .OrderBy(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CI_AS"))
    .ToListAsync();

✨Example: Spatial / Geographic Queries

This query finds stores within 1000 meters of a user’s location using STDistance, which is beneficial because it performs spatial calculations efficiently in the database instead of filtering in memory.

var nearbyStores = await db.Stores
    .Where(s => EF.Functions.STDistance(s.Location, userLocation) < 1000)
    .ToListAsync();

✅Leverages database functions directly without writing raw SQL.

✅Cleaner LINQ syntax that still executes efficiently in SQL.

✅Works seamlessly with aggregates, compiled queries, and projections.

💡 Combining JSON columns, raw SQL, and EF.Functions lets you handle dynamic, complex, or performance-critical queries in modern applications efficiently.

10. Global Query Filters

Global query filters allow you to automatically filter entities at the DbContext level, so you don’t have to remember to add .Where() everywhere. Common use cases: soft deletes, multi-tenancy, or archiving.

✨Example: Soft delete filter

This configures a global query filter to automatically exclude deleted users, which is beneficial because it ensures consistent soft-delete behavior across all queries without needing to manually add .Where(u => !u.IsDeleted) every time.

modelBuilder.Entity<User>()
    .HasQueryFilter(u => !u.IsDeleted);

Querying:

var activeUsers = await db.Users.ToListAsync(); // automatically excludes deleted users

✅Ensures consistent filtering across all queries.

✅Reduces repetitive .Where clauses in your code.

✅Works with AsNoTracking or AsNoTrackingWithIdentityResolution for reporting scenarios.

💡 Combine with interceptors or audit fields for multi-tenant apps.

11. Enum & Value Conversions

EF Core allows mapping enums or custom value objects to database columns with conversions. This makes your domain model expressive while keeping the database normalized.

✨Example: Enum mapping

This maps the OrderStatus enum to a string column in the database, which is beneficial because it keeps the domain model type-safe while storing readable values in the database.

public enum OrderStatus { Pending, Shipped, Delivered }

modelBuilder.Entity<Order>()
    .Property(o => o.Status)
    .HasConversion<string>();

✅Store enums as strings or integers for readability and compatibility.

✅Enables type-safe domain models without sacrificing database simplicity.

✨Example: Custom value object

This maps the Email value object to a string column in the database, which is beneficial because it allows you to use rich, type-safe domain objects while persisting them as simple database types.

modelBuilder.Entity<User>()
    .Property(u => u.Email)
    .HasConversion(
        v => v.Value,   // store as string
        v => new Email(v) // retrieve as Email object
    );

✅Keeps rich domain objects while persisting as simple database types.

12. Common Pitfalls & Gotchas

Even experienced EF Core developers can run into subtle traps that silently affect performance, correctness, or maintainability. Being aware of these helps you write robust, efficient data access.

  • N+1 Queries: If you forget to Include() related entities, EF Core may issue multiple queries—one per parent row—resulting in the classic N+1 problem. Use Include() wisely, or AsSplitQuery() for multiple collections to avoid performance disasters.
  • Client-Side Evaluation: EF Core tries to translate LINQ to SQL, but sometimes it can’t. When that happens, it falls back to client-side evaluation, pulling all data into memory. Always check warnings and avoid this in hot paths to prevent memory bloat and slow queries.
  • Tracking Overhead: By default, EF Core tracks entities, which is convenient for updates but adds memory and CPU overhead. For read-heavy operations, use AsNoTracking() or AsNoTrackingWithIdentityResolution() to improve performance while still maintaining object identity.
  • Raw SQL Risks: Raw SQL gives power but also danger. Always parameterize queries using FromSqlInterpolated() to prevent SQL injection, and carefully map results to DTOs or entities to avoid unexpected behavior.
  • Version Changes: EF Core 8+ introduces new translation rules for aggregates, GroupBy, and other queries. Code that worked in EF Core 7 may behave differently, so always test and review SQL generated after upgrades.

💡 Use logging, interceptors, and query tagging to catch these pitfalls early. Seeing the exact SQL your LINQ generates makes debugging performance issues or unexpected results much easier.

Modern EF Core gives developers the tools to handle everything from complex aggregates and nested relationships to performance-critical queries and semi-structured JSON data — all while keeping code clean, expressive, and maintainable.

By combining features like compiled queries, split queries, EF.Functions, global query filters, and value conversions, you can build robust, high-performance applications that scale gracefully and minimize memory overhead.

Happy coding!

Posted by

Rob Hutton

in