EF Core Query Performance: Projection, AsNoTracking and AsSplitQuery

Writing queries with EF Core is easy: a few lines of LINQ and EF Core does the rest. The price of that convenience is not seeing which SQL actually runs. A query nobody notices on a hundred-row development table can keep a page waiting for seconds against hundreds of thousands of rows in production. In this article I cover the three basic tools for faster EF Core queries, Select projection, AsNoTracking and AsSplitQuery, by looking at the SQL each one produces.

The examples use the shop model from the EF Core series: the Musteri (customer), Siparis (order), Urun (product) and Fatura (invoice) entities and MarketDbContext. A customer has many orders; an order has many products (many-to-many, through the SiparisUrun join table) and one invoice. You can follow along without having read the series.

See the SQL first

The first step in improving a query is seeing what SQL EF Core turns it into. The easiest way is ToQueryString(), which returns the SQL without running the query:

using var context = new MarketDbContext();

var query = context.Siparisler
    .Where(s => s.SiparisTarihi >= new DateTime(2026, 1, 1))
    .OrderBy(s => s.Id);

Console.WriteLine(query.ToQueryString());

To see every query the application runs, turn on logging. In a console app LogTo is enough; in ASP.NET Core, setting the log level of the Microsoft.EntityFrameworkCore.Database.Command category to Information does the same:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information);
}

One important detail: the query variable above is not a query that has run, it is a description of one (an IQueryable). The database is only hit when something asks for results: ToList(), First(), Count(), Any() or a foreach loop. Every Where, OrderBy and Select added before that point is translated to SQL. Filters written after AsEnumerable() or ToList(), on the other hand, run in memory, not in the database. context.Siparisler.AsEnumerable().Where(...) pulls the whole table into the application and filters it there.

Select projection instead of Include

Say we are building an order list. Each row shows the order date, the customer's name, the number of products and the total. The first idea is to load the related data with Include:

var orders = context.Siparisler
    .Include(s => s.Musteri)
    .Include(s => s.Urunler)
    .ToList();

EF Core produces SQL similar to the following (slightly simplified for readability):

SELECT [s].[Id], [s].[CreatedDate], [s].[MusteriId], [s].[SiparisTarihi],
       [m].[Id], [m].[CreatedDate], [m].[Email], [m].[MusteriAdi],
       [s1].[SiparislerId], [s1].[UrunlerId], [s1].[Id], [s1].[CreatedDate], [s1].[Fiyat], [s1].[UrunAdi]
FROM [Siparisler] AS [s]
INNER JOIN [Musteriler] AS [m] ON [s].[MusteriId] = [m].[Id]
LEFT JOIN (
    SELECT [s0].[SiparislerId], [s0].[UrunlerId], [u].[Id], [u].[CreatedDate], [u].[Fiyat], [u].[UrunAdi]
    FROM [SiparisUrun] AS [s0]
    INNER JOIN [Urunler] AS [u] ON [s0].[UrunlerId] = [u].[Id]
) AS [s1] ON [s].[Id] = [s1].[SiparislerId]
ORDER BY [s].[Id], [m].[Id], [s1].[SiparislerId], [s1].[UrunlerId]

This query has three problems. It reads columns the screen never uses (CreatedDate, Email, product names). An order with five products comes back as five rows, with the order and customer columns repeated on each. And every object is put under change tracking, although we only want to display them.

Let's write the same screen with Select, picking only the fields we need:

var orders = context.Siparisler
    .Select(s => new OrderSummaryDto(
        s.Id,
        s.SiparisTarihi,
        s.Musteri.MusteriAdi,
        s.Urunler.Count,
        s.Urunler.Sum(u => u.Fiyat)))
    .ToList();

public sealed record OrderSummaryDto(
    int Id,
    DateTime OrderDate,
    string CustomerName,
    int ProductCount,
    decimal Total);

SELECT [s].[Id], [s].[SiparisTarihi], [m].[MusteriAdi], (
    SELECT COUNT(*)
    FROM [SiparisUrun] AS [s0]
    INNER JOIN [Urunler] AS [u] ON [s0].[UrunlerId] = [u].[Id]
    WHERE [s].[Id] = [s0].[SiparislerId]), (
    SELECT COALESCE(SUM([u0].[Fiyat]), 0.0)
    FROM [SiparisUrun] AS [s1]
    INNER JOIN [Urunler] AS [u0] ON [s1].[UrunlerId] = [u0].[Id]
    WHERE [s].[Id] = [s1].[SiparislerId])
FROM [Siparisler] AS [s]
INNER JOIN [Musteriler] AS [m] ON [s].[MusteriId] = [m].[Id]

Each order now comes back as one row, the database does the counting and summing, and only the five values the screen shows travel to the application. Notice that there is no Include: when you reach a relationship inside Select, such as s.Musteri.MusteriAdi, EF Core adds the JOIN itself. And because the result is a DTO, not an entity, it is not tracked either.

Include belongs where you read entities in order to change them. If you load an order with its products, remove one and call SaveChanges, you need the full entities. For data you only display, a projection is almost always lighter.

AsNoTracking: turning off change tracking

When a query returns entities, EF Core by default registers each one with the change tracker: it keeps a snapshot of the object's current values and, when SaveChanges is called, finds what changed by comparing against that snapshot. If the same row is read again, it returns the tracked object instead of creating a new one (identity resolution).

That is very useful for data you read and modify. For data you only display, it is wasted memory and CPU time. AsNoTracking() switches it off:

var orders = context.Siparisler
    .AsNoTracking()
    .Include(s => s.Musteri)
    .Where(s => s.SiparisTarihi >= new DateTime(2026, 1, 1))
    .ToList();

A few details worth knowing:

• Untracked objects are not saved. If you read an order with AsNoTracking, change its date and call SaveChanges, nothing happens; EF Core does not know about that object. Leave tracking on for queries whose results you update.
• Same row, separate objects. Without tracking there is no identity resolution either. Reading 100 orders of the same customer with Include(s => s.Musteri) creates 100 separate Musteri objects in memory. If that is a problem, use AsNoTrackingWithIdentityResolution(): it does not track, but it returns the same object for the same row.
• Not needed with projections. When you Select into a DTO, nothing is tracked anyway; there is no need to add AsNoTracking.
• You can change the default. In a read-heavy application you can turn tracking off for the whole context and turn it back on with AsTracking() only where needed:

optionsBuilder
    .UseSqlServer(connectionString)
    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

Duplicated rows and AsSplitQuery

Let's load a customer together with their orders and the products in those orders:

var customer = context.Musteriler
    .Include(m => m.Siparisler)
        .ThenInclude(s => s.Urunler)
    .Single(m => m.Id == customerId);

By default EF Core turns this into a single query with JOINs. If the customer has 50 orders with 4 products each on average, about 200 rows come back from the database; every customer column is repeated on all 200 rows, and every order's columns are repeated once per product. It gets worse when you load two collections at the same level: had the customer 50 orders and 10 addresses, and had we included both, the result would be 50 × 10 = 500 rows. This is known as cartesian explosion. EF Core also logs a warning about this risk for queries that load more than one collection.

AsSplitQuery() loads the same data with a separate SQL query per collection:

var customer = context.Musteriler
    .Include(m => m.Siparisler)
        .ThenInclude(s => s.Urunler)
    .AsSplitQuery()
    .Single(m => m.Id == customerId);

-- 1. The customer
SELECT [m].[Id], [m].[CreatedDate], [m].[Email], [m].[MusteriAdi]
FROM [Musteriler] AS [m]
WHERE [m].[Id] = @customerId

-- 2. The customer's orders
SELECT [s].[Id], [s].[CreatedDate], [s].[MusteriId], [s].[SiparisTarihi]
FROM [Siparisler] AS [s]
WHERE [s].[MusteriId] = @customerId
ORDER BY [s].[Id]

-- 3. The products in those orders
SELECT [su].[SiparislerId], [u].[Id], [u].[CreatedDate], [u].[Fiyat], [u].[UrunAdi]
FROM [Siparisler] AS [s]
INNER JOIN [SiparisUrun] AS [su] ON [s].[Id] = [su].[SiparislerId]
INNER JOIN [Urunler] AS [u] ON [su].[UrunlerId] = [u].[Id]
WHERE [s].[MusteriId] = @customerId
ORDER BY [s].[Id]

The SQL above is simplified to show the idea; the real queries EF Core generates contain extra subqueries and ordering columns depending on the version. Together the three queries return 1 + 50 + 200 rows with no needless repetition, and EF Core stitches the results back into the same object graph in memory.

AsSplitQuery is not better in every case, though:

• Every query is a separate round trip. If the database is across a network, three queries cost more latency than one. When the collections are small, a single query is often faster. That is why split queries can cost more than they save on small projects or relationships with few rows.
• There is no consistency guarantee between the queries. If another operation changes the data between the first query and the second, the results may not match. If that matters, run them inside a transaction (for example with snapshot isolation).
• Ordering must be unique when paging. With Skip/Take, the OrderBy has to rely on a unique column (such as Id); otherwise the separate queries can return different rows.

You can make split queries the default for the whole context and pick a single query with AsSingleQuery() where needed:

optionsBuilder.UseSqlServer(connectionString,
    o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Decide by measuring, not guessing: running the same query both ways and comparing the time and the number of rows returned takes a few minutes.

Other small but effective rules

• Filter in the database. Where has to come before ToList(). Fetching the list and filtering afterwards moves the whole table.
• Use Any() for existence checks. Count() > 0 counts every matching row; Any() stops at the first.
• Page your lists. Take only what you show with OrderBy + Skip + Take. Skip slows down on very deep pages; continuing from the last seen Id (keyset pagination) is faster.
• Avoid N+1 queries. With lazy loading on, reading siparis.Musteri.MusteriAdi inside a loop runs a separate query on every iteration. Get what you need in one go, with a projection or Include.
• Don't load entities for bulk operations. From EF Core 7 on, ExecuteUpdate and ExecuteDelete update or delete rows with a single SQL statement, without loading them into memory.
• Use async methods in web applications. ToListAsync() doesn't make the query faster, but it frees the thread while waiting, so the server can answer more requests at once.
• Don't forget the right indexes. Even with the best SQL from EF Core, the database scans the table if there is no suitable index. See SQL Server indexes for that.

Which one, when?

Conclusion

The secret to faster EF Core queries is seeing the SQL behind the LINQ. For data you only display, take just the fields you need with Select; when you read entities you won't change, use AsNoTracking; when loading several collections together, watch for duplicated rows and try AsSplitQuery with measurements. This article follows on from the EF Core series; for the wider picture of site speed, see Website Speed and Core Web Vitals.

For further reading, I recommend the "Efficient Querying", "Tracking vs. No-Tracking Queries" and "Single vs. Split Queries" pages in Microsoft's EF Core documentation.