Topic 15418 min readStructured Tutorial

Entity Framework Core Query Optimization

Quick Answer

Entity Framework Core query optimization involves techniques such as using eager loading, filtering data early, avoiding unnecessary data retrieval, and leveraging compiled queries to improve performance and reduce database load in C# applications.

Learning Objectives

Explain the purpose of Query Optimization in a practical learning context.
Identify the main ideas, terms, and decisions involved in Query Optimization.
Apply Query Optimization in a simple real-world scenario or practice task.

Introduction to EF Core Query Optimization

Entity Framework Core (EF Core) is a popular Object-Relational Mapper (ORM) for .NET developers. It simplifies database access by allowing developers to write C# code instead of raw SQL.

However, inefficient queries can lead to performance bottlenecks. Optimizing EF Core queries is essential to ensure your applications run smoothly and scale well.

Optimize queries early to avoid costly database operations later.

Understanding EF Core Query Execution

EF Core translates LINQ queries into SQL commands executed against the database. Understanding when queries are executed helps optimize performance.

Queries in EF Core are executed when the data is enumerated, such as when calling methods like ToList(), FirstOrDefault(), or when iterating over results.

Deferred execution delays query until data is needed.
Immediate execution occurs when methods like ToList() are called.
Tracking queries monitor changes for updates; no-tracking queries improve read performance.

Techniques for Query Optimization

Several techniques can improve EF Core query performance by reducing unnecessary data retrieval and database round-trips.

Use eager loading with Include() to load related data in a single query.
Apply filtering and projection early to limit data fetched.
Use AsNoTracking() for read-only queries to reduce tracking overhead.
Avoid loading unnecessary columns by selecting only required fields.
Leverage compiled queries for frequently executed queries.

Eager Loading with Include()

Eager loading fetches related entities as part of the initial query, reducing the number of database calls.

Use Include() to specify related data to load.
Chain multiple Include() calls for nested related data.

Filtering and Projection

Filtering limits the data returned by applying conditions early in the query.

Projection selects only the necessary columns or transforms data into DTOs.

Use Where() to filter data before retrieval.
Use Select() to project data into lightweight objects.

No-Tracking Queries

Common Pitfalls to Avoid

Avoiding common mistakes helps maintain efficient EF Core queries.

Loading entire tables without filters causes unnecessary data transfer.
Using lazy loading excessively can cause N+1 query problems.
Ignoring database indexes can slow down query execution.
Fetching more data than needed increases memory usage.

Practical Example

This query loads orders along with their related customer data in a single database call.

This query retrieves only active customers and selects only their Id and Name properties.

This query fetches products without tracking them, improving performance for read-only scenarios.

This example demonstrates a compiled query to efficiently retrieve a customer by ID.

Examples

Using Include() for Eager Loading

var orders = context.Orders.Include(o => o.Customer).ToList();

This query loads orders along with their related customer data in a single database call.

Filtering and Projection Example

var customerNames = context.Customers
    .Where(c => c.IsActive)
    .Select(c => new { c.Id, c.Name })
    .ToList();

This query retrieves only active customers and selects only their Id and Name properties.

Using AsNoTracking() for Read-Only Query

var products = context.Products.AsNoTracking().ToList();

This query fetches products without tracking them, improving performance for read-only scenarios.

Compiled Query Example

static readonly Func<AppDbContext, int, Customer> getCustomerById =
    EF.CompileQuery((AppDbContext ctx, int id) =>
        ctx.Customers.FirstOrDefault(c => c.Id == id));

var customer = getCustomerById(context, 5);

This example demonstrates a compiled query to efficiently retrieve a customer by ID.

Best Practices

Always filter data as early as possible in the query.
Use eager loading to reduce multiple database round-trips.
Apply AsNoTracking() for queries that do not require entity updates.
Select only the columns you need to minimize data transfer.
Consider compiled queries for frequently executed queries.
Monitor generated SQL queries to understand performance impact.

Common Mistakes

Loading entire tables without filters causing slow queries.
Overusing lazy loading leading to N+1 query problems.
Not using AsNoTracking() for read-only queries.
Selecting entire entities when only a few fields are needed.
Ignoring database indexes and query plans.

Hands-on Exercise

Optimize a Slow Query

Given a LINQ query that loads all orders and customers separately, refactor it to use eager loading and filtering to improve performance.

Expected output: A single optimized query that loads filtered orders with related customers efficiently.

Hint: Use Include() and Where() to limit data and reduce database calls.

Implement a Compiled Query

Create a compiled query to retrieve products by category ID and test its performance compared to a regular query.

Expected output: A compiled query function that returns products by category with improved performance.

Hint: Use EF.CompileQuery() and compare execution times.

Interview Questions

What is eager loading in Entity Framework Core?

Interview

Eager loading is a technique where related data is loaded as part of the initial query using the Include() method, reducing the number of database calls.

How does AsNoTracking() improve query performance?

Interview

AsNoTracking() disables change tracking for entities returned by a query, reducing overhead and improving performance for read-only operations.

What is a compiled query in EF Core?

Interview

A compiled query caches the query plan to reduce the cost of query compilation on repeated executions, improving performance for frequently run queries.