🚀 EF Core Query Optimization for Beginners

When developers first start using Entity Framework Core, it's easy to write queries that work—but not all queries perform well.

The good news? A few simple practices can make your queries much faster, use less memory, and generate better SQL.

We'll use this real-world example:

var offerDtos = await db.Offers
    .AsNoTracking()
    .Where(o => o.DeliveryDate >= date &&
                o.DeliveryDate < nextDay &&
                (o.IsCompleted == null || o.IsCompleted == 0))
    .OrderBy(o => o.Id)
    .Select(o => new OfferForWarehouseDto
    {
        OfferId = o.Id,
        CustomerName = o.Customer.Name,
        DeliveryDate = o.DeliveryDate,
        OfferDate = o.OfferDate,
        Status = o.Status,
        Comments = o.Comments,
        WarehouseStatus = "Pending",
        Lines = o.OfferLines
            .Select(l => new WarehouseLineDto
            {
                OfferLineId = l.Id,
                ItemCode = l.ItemCode,
                ItemDescription = l.ItemDescription,
                Color = l.Color,
                Unit = l.Unit,
                OrderedQuantity = l.Quantity,
                CurrentStock = l.Product.Stock
            })
            .ToList()
    })
    .ToListAsync(cancellationToken);

Let's break it down step by step.

---

1. Use AsNoTracking() for Read-Only Queries ⭐

.AsNoTracking()

One of EF Core's most useful features is the Change Tracker.

Whenever you load entities, EF Core tracks them in memory so it can detect changes and generate the appropriate INSERT, UPDATE, or DELETE statements when you call SaveChanges().

For example:

var offer = await db.Offers.FirstAsync();

offer.Status = "Completed";

await db.SaveChangesAsync();

Even though you never tell EF Core that Status changed, it knows because the Change Tracker has been monitoring the entity.

This is extremely convenient when you're editing data.

However, tracking every entity comes with a cost.

For each tracked entity, EF Core stores:

• The original values
• The current values
• The entity state (Added, Modified, Deleted, Unchanged)
• Relationship information

If you're only displaying data in a grid or returning it from an API, all of this tracking is unnecessary.

That's why read-only queries should use:

.AsNoTracking()

This tells EF Core:

"I'm only reading this data. Don't track it."

Benefits

• ✅ Lower memory usage
• ✅ Faster query execution
• ✅ Less CPU overhead
• ✅ Better performance for APIs, dashboards, and reports

Learn more

• Change Tracking: https://learn.microsoft.com/en-us/ef/core/change-tracking/
• Tracking vs. No-Tracking Queries: https://learn.microsoft.com/en-us/ef/core/querying/tracking

---

2. Filter Data Before Loading It

.Where(o =>
    o.DeliveryDate >= date &&
    o.DeliveryDate < nextDay &&
    (o.IsCompleted == null || o.IsCompleted == 0))

Always filter data in the database.

Good:

db.Offers.Where(...)

Bad:

db.Offers.ToList().Where(...)

The first generates a SQL WHERE clause.

The second loads the entire table into memory before filtering.

The database is optimized for filtering large datasets, so let it do the work.

---

3. Project Only What You Need

Notice that we're not returning Offer entities.

Instead, we're creating DTOs.

.Select(o => new OfferForWarehouseDto
{
    OfferId = o.Id,
    CustomerName = o.Customer.Name
})

This tells EF Core exactly which columns to retrieve.

Instead of:

SELECT *

EF Core generates SQL that selects only the properties used in the DTO.

Benefits include:

• Smaller SQL results
• Less network traffic
• Lower memory usage
• Faster execution

Learn more

https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#project-only-properties-you-need

---

4. Don't Use Include() When Projecting

Many developers write:

.Include(o => o.Customer)
.Include(o => o.OfferLines)
.ThenInclude(l => l.Product)
.Select(...)

This is unnecessary.

When projecting into a DTO, EF Core automatically generates the required SQL joins.

Simply write:

.Select(...)

The generated SQL is usually smaller, cleaner, and more efficient.

---

5. Sort in SQL

Sorting should happen before materializing the results.

Good:

.OrderBy(o => o.Id)
.ToListAsync();

Bad:

.ToList()
.OrderBy(o => o.Id);

SQL Server is optimized for sorting data.

Don't move that work into your application.

---

6. Project Child Collections

EF Core can also project related collections.

Lines = o.OfferLines
    .Select(l => new WarehouseLineDto
    {
        ItemCode = l.ItemCode,
        CurrentStock = l.Product.Stock
    })
    .ToList()

Notice that we never load the entire OfferLine or Product entities.

Only the required columns are selected.

This keeps queries efficient while still returning nested objects.

---

7. Use Async Queries

Always prefer:

await ToListAsync();

instead of:

ToList();

Async queries don't make the database faster, but they prevent your application thread from blocking while waiting for SQL Server.

This improves scalability in ASP.NET Core applications.

---

8. Create Proper Database Indexes

Even the best LINQ query can be slow if the database has no indexes.

This query filters by:

• DeliveryDate
• IsCompleted

A composite index can dramatically improve performance.

CREATE INDEX IX_Offers_DeliveryDate_IsCompleted
ON Offers (DeliveryDate, IsCompleted);

Remember:

EF Core generates SQL.
SQL Server executes SQL.
Indexes make SQL fast.

---

Final Optimized Query

var offerDtos = await db.Offers
    .AsNoTracking()
    .Where(o => o.DeliveryDate >= date &&
                o.DeliveryDate < nextDay &&
                (o.IsCompleted == null || o.IsCompleted == 0))
    .OrderBy(o => o.Id)
    .Select(o => new OfferForWarehouseDto
    {
        OfferId = o.Id,
        CustomerName = o.Customer.Name,
        DeliveryDate = o.DeliveryDate,
        OfferDate = o.OfferDate,
        Status = o.Status,
        Comments = o.Comments,
        WarehouseStatus = "Pending",
        Lines = o.OfferLines
            .Select(l => new WarehouseLineDto
            {
                OfferLineId = l.Id,
                ItemCode = l.ItemCode,
                ItemDescription = l.ItemDescription,
                Color = l.Color,
                Unit = l.Unit,
                OrderedQuantity = l.Quantity,
                CurrentStock = l.Product.Stock
            })
            .ToList()
    })
    .ToListAsync(cancellationToken);

🎯 Key Takeaways

Whenever you write an EF Core query, ask yourself:

• ✅ Is this a read-only query? → Use AsNoTracking().
• ✅ Am I letting the database filter my data?
• ✅ Am I selecting only the columns I need?
• ✅ Am I avoiding unnecessary Include() calls?
• ✅ Am I using async methods like ToListAsync()?
• ✅ Does my database have the right indexes?

Following these simple habits will help you write faster, cleaner, and more scalable EF Core applications.

Additional Resources

• EF Core Performance: https://learn.microsoft.com/en-us/ef/core/performance/
• Efficient Querying: https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying
• Change Tracking: https://learn.microsoft.com/en-us/ef/core/change-tracking/
• Tracking vs. No-Tracking Queries: https://learn.microsoft.com/en-us/ef/core/querying/tracking