pkey
.Net

How to Optimize LINQ Queries in C#: A Complete Guide with Diagnostics, Logging, and Real-World Performance Tuning

May 2, 2026 admin-8RoncPx4

Introduction

Optimize LINQ Queries.LINQ makes C# elegant — but also deceptively expensive.

The biggest trap isn’t syntax — it’s not knowing what actually runs in the database.

In real production systems (especially enterprise apps, CQRS, microservices), badly optimized LINQ can:

  • generate inefficient SQL
  • silently load entire tables
  • create N+1 query explosions
  • kill database performance under load

This guide goes deeper than typical tutorials.

You will learn:

  • how LINQ really executes
  • how to see the SQL generated
  • how to enable logging and diagnostics
  • how to find slow queries
  • advanced optimization patterns used in real systems

PART 1 — How LINQ Really Works (Deep Dive)

Expression Trees vs Delegates

LINQ is not one thing — it behaves differently depending on the provider.

LINQ to Objects

C#
var result = users.Where(u => u.Age > 18);

This compiles to a delegate:

C#
Func<User, bool>

Runs in memory.

LINQ to Entities (EF Core)

C#
var result = context.Users.Where(u => u.Age > 18);

This becomes an Expression Tree:

C#
Expression<Func<User, bool>>

Which EF Core translates to SQL:

SELECT * FROM Users WHERE Age > 18

Visual: LINQ Translation Pipeline

https://images.openai.com/static-rsc-4/RRMQWlu6L4FKLtrxd-vwCrTQaAxDhyUc6uDP5yox8VvPEhMyIbADV9pbKm60XlFnHLKGKIL9OyHKr7F6O101qflnEGwAhlPQ3ZRIICPHmYDKZXXPwbwBFvHtHe2vO_E1uysoup0NtI6sU_kFMrafhPg4yUIQDCHEfu3Xh4WOY7ZLJLytMNUozAajVorGZe_G?purpose=fullsize
https://images.openai.com/static-rsc-4/oOljF9IsBTSRYqX9MG34nY3aKbP9AeAn0npPu7qubk8DAozp5HPGEidULlQf0HG8tIYfSq-udujQPoE_b5Z1dWjzbqi1SkUD2IaQqJ9o-Upao_B-7PaLy8pUFDtD2MqkmetuqLHA7mIlyRRKjzEj8faDh87Iu6OEIHZm_NvOnceRINYNcBztPkSndqkqsHj4?purpose=fullsize
https://images.openai.com/static-rsc-4/DS7i7MzspCGxdYG0iv4v_L-sN60VmMIxm_UDk-4H5-WNSNx86lQ-pVto9ls1j3EbXA-iX5blUk6hO9S1a1jYioLPEJpg5DzEgtdYl_8S1qhSAXrzuMy6gp5Za0Fqo4hupVVLkbYFP5XrQBYCWgG165-jEGLoboMTz26UIhjzX_FwAfgc68cWn2b3WP5asHX1?purpose=fullsize

5

Deferred Execution (Hidden Danger)

C#
var query = context.Users.Where(u => u.IsActive);

No SQL yet.

Execution happens here:

C#
foreach (var user in query)
{
    Console.WriteLine(user.Name);
}

Multiple Enumeration Problem (Advanced)

C#
var query = context.Users.Where(u => u.IsActive);

if (query.Any())
{
    foreach (var user in query)
    {
        // SECOND execution
    }
}

✔ Generates two SQL queries

Fix

C#
var list = context.Users
    .Where(u => u.IsActive)
    .ToList();

if (list.Any())
{
    foreach (var user in list)
    {
    }
}

PART 2 — Seeing What SQL LINQ Generates

Optimize LINQ Queries.This is the most important skill.

If you don’t see SQL — you are guessing.

1. Get SQL Query with ToQueryString() (EF Core 5+)

C#
var query = context.Users
    .Where(u => u.Age > 30)
    .OrderBy(u => u.Name);

var sql = query.ToQueryString();

Console.WriteLine(sql);

✔ Output:

SQL
SELECT [u].[Id], [u].[Name], [u].[Age]
FROM [Users] AS [u]
WHERE [u].[Age] > 30
ORDER BY [u].[Name]

2. Log SQL Queries Globally

In DbContext

C#
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("your-connection")
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging();
}

3. ASP.NET Core Logging (Production Style)

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

Visual: EF Logging Flow

https://images.openai.com/static-rsc-4/N1BwMgMpVUNq1cAfi_VbFZ9SlDASOawVuD2bw-s9UHBSOqMVkyTrEO0WSWwvK-2mX1Hzc2rrQo-WexXzaS-M18G5cOyN43nLFWAldSnetxeNhc8Ap8PzdtfzebWxUgzuQ1XLSxes7PdpiOkDrXNVWuCq4tghW9RHxtDVaDD1j2COkouuyN5NRmWzSZ_ikZ60?purpose=fullsize
https://images.openai.com/static-rsc-4/o5O_L3nxuxF6vaJmNDZ6udGxqNSsRp-4HyqZCfrzVWm_gVjqtVZW4hplTdhJz_ACbkptBsAkfugPNyLJR8tS4aCodfjHsdpS5WMzWA88XEq2-hU9K--dILk4nYXgensf0laXKmo2XURZKT_EvSlUWYfMZLRewZXXRk97kOWo_DIezwPeqiXj0neHvEfpkiW4?purpose=fullsize
https://images.openai.com/static-rsc-4/zJR4YL6okxHe-47_qbEpe0vMXQrrsbeaQPoFZZcIPRN--3jDa4KX_rMKL-0xZFXqYVB75y1hBE7z1SjXaIFZltO0KJOxeVydhXlmMasNIjWueFoyWoTNgz8NwxkCY15trryqGU2rnz7WqRH1T8lbpovVVf8loXt9TZrFQRCa6KuS5DEltIE-n7PKu-015eu1?purpose=fullsize

6

4. Log Only Slow Queries

You can filter logs:

C#
optionsBuilder.LogTo(log =>
{
    if (log.Contains("Executed DbCommand"))
    {
        Console.WriteLine(log);
    }
});

Better approach: use interceptors

5. Use EF Core Interceptor to Measure Time

C#
public class QueryInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        var sw = Stopwatch.StartNew();

        var response = base.ReaderExecuting(command, eventData, result);

        sw.Stop();

        if (sw.ElapsedMilliseconds > 200)
        {
            Console.WriteLine($"SLOW QUERY ({sw.ElapsedMilliseconds} ms):");
            Console.WriteLine(command.CommandText);
        }

        return response;
    }
}

Register:

C#
optionsBuilder.AddInterceptors(new QueryInterceptor());

PART 3 — Finding Slow Queries in SQL Server

1. Use SQL Server DMVs

SQL
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count AS avg_time,
    qs.execution_count,
    qs.total_elapsed_time,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_time DESC;

2. Use Query Store

Enable:

SQL
ALTER DATABASE YourDb SET QUERY_STORE = ON;

Then analyze slow queries in SSMS. How to Optimize LINQ Queries.

Visual: Query Store

https://images.openai.com/static-rsc-4/wUMBAqe6DA_5-CR5Gqw_FAQtAJxPkmCixkv5ddmsSW_3sWJ88RuWN_pd1fuiQmoY-OngXZVJjcBIu2hW0xYYB7xP_94Pzjn6dk9ybpyj3J-BNw2r9vzOvXDdDpN0z8NtrG5Ql6C6zKzy4w9f-B5ukGORw9BTsmLj3h9Qmc3Opq_Bq5G5x9DulySohGXGBcEL?purpose=fullsize
https://images.openai.com/static-rsc-4/cYy-uAyd1dr_84mj5sozO-qoOxATDFoNN8x2Xu5vpxxab9cU6LZa_FmDtzZEdJ3g5F2HBEKGPSJaBzDp0xCO5a54_-Gb6Aof9b0kGSFw8w-wZm9zOkQSn-MPVho4FBCcViRcjBS0ilQVqg2NnbLiJxG1dxhDDqQ0AzaXpSNxxhwmQ9-yrA2jayHmA6XfdAuc?purpose=fullsize
https://images.openai.com/static-rsc-4/aXpciWseAzK_w3GcDcRfC0j27qWitafVHph9jFUlue031dQG39gEKSYnjpjyMoUkY2q_YMTC4Q467UEvwODO8nS572qQE5Zowj36K3gs2bt8WaN_26rygvT5BGYiU3faPSoFH9gRPXfdDoHlZfhfOqJ5ZZt9AA4eh5o4ZpX9o5LWaMpDhcPPAZRiIggIcP61?purpose=fullsize

7

3. Use Execution Plans

SQL
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Or use graphical plan in SSMS.

PART 4 — Core Optimization Techniques

1. Projection (Most Important)

❌ Bad:

C#
var users = context.Users.ToList();

✔ Good:

C#
var users = context.Users
    .Select(u => new UserDto
    {
        Id = u.Id,
        Name = u.Name
    })
    .ToList();

2. Avoid N+1 Queries

❌ Bad:

C#
var users = context.Users.ToList();

foreach (var user in users)
{
    var orders = context.Orders
        .Where(o => o.UserId == user.Id)
        .ToList();
}

✔ Good:

C#
var users = context.Users
    .Include(u => u.Orders)
    .ToList();

Visual: N+1 Problem

https://images.openai.com/static-rsc-4/bdhUOOy2S20ZL620Q1JMclq-zvOkiGy_gqNiqASFcUkCAA2JwvgzQld1m6k0tHhQcqys90oac-4sDxxZOfsfJ2-W_htsNyzUgVFx2yROWgY_m-ED99L_JAvMQHaOV6fSJ-RhZuHbmsyvY62vZ9uOL6FKuNuOYH_2vg-hLbPjfl7Wlp6lCvSxXw8vL8Raq0yO?purpose=fullsize
https://images.openai.com/static-rsc-4/kOobN0j4X4a64Bp-Saze6KQ4wfo2iBirtkgAnqxpHzBCZ-YRIL563AO9WDE4MxS6W1lnBXxYwzEaUiXvtATVR8OMS_B4eO7YUKfMC6fZu6sgW7pdPd2PMvzmMnzKz--Fc0Ckx5o5PAc7dKgQzGyWgbfTb4KcFTLohBoUOOeby9I_8YAT9XeO_a6aztL4ygb0?purpose=fullsize
https://images.openai.com/static-rsc-4/sApHeN12qKyWEAXIWnZfCoSe7WOeEDfM-S1hIi70Ob2OUS3NlEy1g0crVwiBfKSOfEnc1akoxJTG0HNbkRM8LhfMBlhkEsTfdSsCumkkVbw-nhXzL5c6EOzpBF-laHa0FMKi7x71Xbnwh8TT2Ugy12ZGjqaueAWM971NHJGbEYxGyS6fFYtXBEkEyJcPWEnm?purpose=fullsize

6

3. AsNoTracking for Read Models

C#
var users = context.Users
    .AsNoTracking()
    .ToList();

✔ Up to 2x faster

4. Pagination

C#
var users = context.Users
    .OrderBy(u => u.Id)
    .Skip(1000)
    .Take(50)
    .ToList();

⚠ Problem: large OFFSET = slow

✔ Better (keyset pagination):

C#
var users = context.Users
    .Where(u => u.Id > lastId)
    .OrderBy(u => u.Id)
    .Take(50)
    .ToList();

5. Use Compiled Queries

C#
static readonly Func<AppDbContext, int, List<User>> GetUsers =
    EF.CompileQuery((AppDbContext ctx, int age) =>
        ctx.Users.Where(u => u.Age > age).ToList());

6. Avoid Client-Side Evaluation

❌ Bad:

C#
var users = context.Users
    .Where(u => CustomMethod(u.Name))
    .ToList();

✔ EF cannot translate → loads ALL data

7. Use Index-Friendly Queries

❌ Bad:

C#
.Where(u => u.Name.ToLower() == "john")

✔ Good:

C#
.Where(u => u.Name == "John")

Visual: Index Usage

https://images.openai.com/static-rsc-4/pY4b4mh1dD1r7xN6suQ5tuXREr2L34O3PdFYfR_nfGC4Mlm-skpRcLAKjdqFHFaV6AvKLjWzoSvecDwhamgz1CtB_MWee6JLVeVkVdwriwvzbEuxasJ5u_xp_1Zl0eenLqgiZbghfePhc1kA3MiegNrW7BQXKaq65zn68z_SmXLHlCCy2P8THrU8QyFnXoHi?purpose=fullsize
https://images.openai.com/static-rsc-4/kKNm5dEVKUJ7sRUDH9YTeqJpTRCSlIvu8rb7ZKKmofrG-vRg3RRJlavWkvDTxhp0TRdOWrubkTIM7vvDaSWX67onYwNAtB11-xjTmYnBvB7J5kjoU1ygAOUA9KuYBjlLH9U3L_awJ0LsXTSDNJzwAXiDOm2VMQYIHCEKxUkex4tSf3OIWiJHe5lfS5n8-utS?purpose=fullsize

6

PART 5 — Advanced Patterns (Real Systems)

1. Split Queries (EF Core)

C#
var users = context.Users
    .Include(u => u.Orders)
    .AsSplitQuery()
    .ToList();

✔ Avoids huge JOIN explosion

2. Batch Queries

Instead of:

C#
foreach (var id in ids)
{
    context.Users.Find(id);
}

✔ Use:

C#
var users = context.Users
    .Where(u => ids.Contains(u.Id))
    .ToList();

3. Raw SQL for Critical Paths

C#
var users = context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Age > {0}", age)
    .ToList();

4. Caching

C#
var users = cache.GetOrCreate("users", entry =>
{
    return context.Users.AsNoTracking().ToList();
});

5. Read Model (CQRS Optimization)

Instead of complex joins:

✔ Create denormalized table:

C#
UserOrderSummary

Then query:

C#
context.UserOrderSummaries.ToList();

PART 6 — Real Production Debugging Scenario

Problem

Slow endpoint:

C#
var users = context.Users
    .Where(u => u.IsActive)
    .Select(u => new
    {
        u.Name,
        Orders = context.Orders
            .Where(o => o.UserId == u.Id)
            .ToList()
    })
    .ToList();

Step 1 — Inspect SQL

Use:

C#
query.ToQueryString();

Step 2 — Enable Logging

C#
.LogTo(Console.WriteLine)

Step 3 — Detect N+1

Logs show:

C#
var users = context.Users
    .Include(u => u.Orders)
    .Select(u => new
    {
        u.Name,
        u.Orders
    })
    .ToList();

Step 4 — Fix

C#
var users = context.Users
    .Include(u => u.Orders)
    .Select(u => new
    {
        u.Name,
        u.Orders
    })
    .ToList();

Result

  • 100 queries → 1 query
  • response time: 2.3s → 120ms

PART 7 — Checklist for LINQ Optimization

✔ Always inspect SQL
✔ Use projection
✔ Avoid ToList() early
✔ Use AsNoTracking()
✔ Avoid N+1
✔ Add indexes
✔ Use pagination
✔ log queries
✔ profile database
✔ use compiled queries

Conclusion

LINQ optimization is not about syntax — it’s about understanding the full pipeline:

C# → Expression Tree → SQL → Execution Plan → Indexes

Once you master:

  • how to see SQL
  • how to measure performance
  • how to rewrite queries

you move from “developer” to performance engineer.

Optimize LINQ Queries… more LINQ Optimization Cases: IQueryable vs IEnumerable, Hidden Memory Problems and Real EF Core Pitfalls

One of the most important topics in LINQ performance is understanding the difference between IQueryable and IEnumerable.

Many slow LINQ queries are not slow because LINQ is bad. They are slow because the query accidentally moves from the database to application memory.

IQueryable vs IEnumerable

This difference is critical.

IQueryable

IQueryable represents a query that can still be translated by a provider, for example Entity Framework Core.

C#
IQueryable<User> query = context.Users
    .Where(u => u.IsActive);

At this point, no SQL has been executed yet.

EF Core can still translate this into SQL:

C#
SELECT *<br>FROM Users<br>WHERE IsActive = 1

This means filtering happens inside the database.

That is usually what you want.

IEnumerable

IEnumerable represents an in-memory sequence. How to Optimize LINQ Queries.

SQL
SELECT *
FROM Users
WHERE IsActive = 1

This looks similar, but it can become dangerous depending on where the conversion happens.

Once data is materialized with ToList(), AsEnumerable(), or similar methods, the rest of the query is executed in memory.

Example:

C#
var users = context.Users
    .AsEnumerable()
    .Where(u => u.Age > 30)
    .ToList();

This may load many rows from the database first, then filter them in C#.

Bad.

Bad Example: Accidental In-Memory Filtering

C#
var users = context.Users
    .AsEnumerable()
    .Where(u => u.Age > 30)
    .ToList();

The problem is AsEnumerable().

How to Optimize LINQ Queries, after that line, EF Core stops translating the query to SQL. The Where runs in memory.

Better:

C#
var users = context.Users
    .Where(u => u.Age > 30)
    .ToList();

Now the database does the filtering.

Rule

Use IQueryable when building database queries.

Use IEnumerable only after the data is already intentionally loaded into memory.

Example: Repository Returning IEnumerable — Hidden Performance Bug

This is a very common enterprise mistake.

Bad repository:

C#
public IEnumerable<User> GetUsers()
{
    return _context.Users;
}

Optimize LINQ Queries, usage:

var users = repository.GetUsers()
    .Where(u => u.IsActive)
    .Take(50)
    .ToList();

This looks fine, but depending on implementation, the filtering may happen in memory.

Better:

C#
public IQueryable<User> QueryUsers()
{
    return _context.Users;
}

Usage:

C#
var users = repository.QueryUsers()
    .Where(u => u.IsActive)
    .Take(50)
    .ToList();

Now EF Core can translate the full query.

However, exposing IQueryable from repositories is also controversial because it leaks database query logic outside the repository.

A better compromise is often a specific query method, this is how to optimize LINQ queries:

C#
public Task<List<UserDto>> GetActiveUsersAsync(int page, int pageSize)
{
    return _context.Users
        .AsNoTracking()
        .Where(u => u.IsActive)
        .OrderBy(u => u.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(u => new UserDto
        {
            Id = u.Id,
            Name = u.Name,
            Email = u.Email
        })
        .ToListAsync();
}

This keeps performance and encapsulation.

Case: AsEnumerable Should Be Used Only Intentionally

Sometimes AsEnumerable() is useful.

For example, when part of the logic cannot be translated to SQL:

C#
var users = await context.Users
    .Where(u => u.IsActive)
    .Select(u => new
    {
        u.Id,
        u.Name
    })
    .ToListAsync();

var result = users
    .Where(u => MyCustomCSharpMethod(u.Name))
    .ToList();

This is acceptable because the database query is already limited.

Bad:

C#
var result = context.Users
    .AsEnumerable()
    .Where(u => MyCustomCSharpMethod(u.Name))
    .ToList();

This can load the entire Users table.

Case: Count() vs Any()

Bad:

C#
if (context.Users.Count(u => u.IsActive) > 0)
{    
  // users exist<br>
}

Better:

C#
if (await context.Users.AnyAsync(u => u.IsActive))
{
    // users exist
}

Any() can stop after finding the first matching row. Count() has to count all matching rows.

Case: FirstOrDefault vs SingleOrDefault

Use FirstOrDefault() when you only need the first matching record.

C#
var user = await context.Users
    .FirstOrDefaultAsync(u => u.Email == email);

Use SingleOrDefault() only when you want to enforce that there must be zero or one matching row.

C#
var user = await context.Users
    .SingleOrDefaultAsync(u => u.Email == email);

SingleOrDefault() may require extra work because it needs to check whether more than one row exists.

For unique columns like Email, prefer a unique index in the database.

Case: Contains and Large IN Lists

This is common:

C#
var users = await context.Users
    .Where(u => ids.Contains(u.Id))
    .ToListAsync();

EF Core usually translates this to SQL IN.

SQL
WHERE Id IN (1, 2, 3, 4)

This is fine for small and medium lists.

But for thousands of IDs, it can become slow.

Better options:

  • use a temporary table
  • use table-valued parameters in SQL Server
  • batch the query
  • redesign the flow

Example batching:

C#
var ids = new List<int> { 1, 2, 3, 4 };
var result = new List<User>();

foreach (var batch in ids.Chunk(500))
{
    var users = await context.Users
        .Where(u => batch.Contains(u.Id))
        .ToListAsync();

    result.AddRange(users);
}

Case: StartsWith vs Contains

This can matter for indexes.

Better:

C#
var users = await context.Users
    .Where(u => u.Name.StartsWith("John"))
    .ToListAsync();

Potentially worse:

C#
var users = await context.Users
    .Where(u => u.Name.Contains("John"))
    .ToListAsync();

StartsWith("John") can often use an index.

Contains("John") usually becomes:

SQL
LIKE '%John%'

That pattern often cannot use a normal index efficiently.

Case: Avoid Functions on Indexed Columns

Bad:

C#
var users = await context.Users
    .Where(u => u.Email.ToLower() == email.ToLower())
    .ToListAsync();

This may prevent efficient index usage.

Better:

C#
var normalizedEmail = email.ToUpper();

var users = await context.Users
    .Where(u => u.NormalizedEmail == normalizedEmail)
    .ToListAsync();

Store normalized values in separate indexed columns when search performance matters.

Case: OrderBy Before Where

Bad:

C#
var users = await context.Users
    .OrderBy(u => u.Name)
    .Where(u => u.IsActive)
    .Take(50)
    .ToListAsync();

Better:

C#
var users = await context.Users
    .Where(u => u.IsActive)
    .OrderBy(u => u.Name)
    .Take(50)
    .ToListAsync();

Filter first. Sort later.

Sorting fewer rows is cheaper.

Case: Select Before Include

This is an important EF Core case.

Bad:

C#
var users = await context.Users
    .Include(u => u.Orders)
    .Select(u => new UserDto
    {
        Id = u.Id,
        Name = u.Name
    })
    .ToListAsync();

If you project only Id and Name, you probably do not need Include.

Better:

C#
var users = await context.Users
    .Select(u => new UserDto
    {
        Id = u.Id,
        Name = u.Name
    })
    .ToListAsync();

Use Include when loading entities with navigation properties.

Use Select when creating DTOs.

Case: Include Explosion

Bad:

C#
var users = await context.Users
    .Include(u => u.Orders)
    .Include(u => u.Invoices)
    .Include(u => u.SupportTickets)
    .ToListAsync();

This can produce a huge JOIN result.

Better:

C#
var users = await context.Users
    .Include(u => u.Orders)
    .Include(u => u.Invoices)
    .Include(u => u.SupportTickets)
    .AsSplitQuery()
    .ToListAsync();

AsSplitQuery() can reduce Cartesian explosion by executing multiple simpler SQL queries.

Case: Projection Instead of Include

Often this is even better:

C#
var users = await context.Users
    .AsNoTracking()
    .Select(u => new UserSummaryDto
    {
        Id = u.Id,
        Name = u.Name,
        OrdersCount = u.Orders.Count,
        LastOrderDate = u.Orders
            .OrderByDescending(o => o.CreatedAt)
            .Select(o => o.CreatedAt)
            .FirstOrDefault()
    })
    .ToListAsync();

This avoids loading full child collections.

You get only the data needed by the screen or API.

Case: Bad Select with Full Entity

Bad:

C#
var users = await context.Users
    .Select(u => new
    {
        User = u,
        OrderCount = u.Orders.Count
    })
    .ToListAsync();

This may still load too much user data.

Better:

C#
var users = await context.Users
    .Select(u => new UserSummaryDto
    {
        Id = u.Id,
        Name = u.Name,
        OrderCount = u.Orders.Count
    })
    .ToListAsync();

Never project full entities unless you really need them.

Case: Tracking vs No Tracking

Bad for read-only endpoints:

C#
var users = await context.Users
    .Where(u => u.IsActive)
    .ToListAsync();

Better:

C#
var users = await context.Users
    .AsNoTracking()
    .Where(u => u.IsActive)
    .ToListAsync();

EF Core tracking is useful when you want to update entities.

For read-only APIs, dashboards, reports, and CQRS read models, use AsNoTracking().

Case: AsNoTrackingWithIdentityResolution

Sometimes you want no tracking but still want EF Core to avoid duplicate entity instances.

C#
var users = await context.Users
    .AsNoTrackingWithIdentityResolution()
    .Include(u => u.Orders)
    .ToListAsync();

Use it when:

  • you read complex object graphs
  • you do not want change tracking
  • but you want identity resolution

For simple DTO queries, prefer normal AsNoTracking().

Case: Pagination with Skip/Take

Basic pagination:

C#
var page = await context.Users
    .OrderBy(u => u.Id)
    .Skip(1000)
    .Take(50)
    .ToListAsync();

This is okay for small offsets.

For large tables, this becomes slower because SQL Server still has to walk through skipped rows.

Better: keyset pagination.

C#
var page = await context.Users
    .Where(u => u.Id > lastSeenId)
    .OrderBy(u => u.Id)
    .Take(50)
    .ToListAsync();

This is often much faster for infinite scroll, APIs, exports, and background jobs.

Case: Do Not Return IQueryable from API Controllers

Bad:

C#
[HttpGet]
public IQueryable<User> GetUsers()
{
    return _context.Users;
}

This leaks query logic and can create unpredictable behavior.

How to Optimize LINQ Queries:

C#
[HttpGet]
public async Task<List<UserDto>> GetUsers()
{
    return await _context.Users
        .AsNoTracking()
        .Where(u => u.IsActive)
        .OrderBy(u => u.Name)
        .Select(u => new UserDto
        {
            Id = u.Id,
            Name = u.Name
        })
        .ToListAsync();
}

API endpoints should return controlled DTOs, not open query objects.

Case: Dynamic Filters with IQueryable

This is one of the best uses of IQueryable.

C#
IQueryable<User> query = context.Users.AsNoTracking();

if (!string.IsNullOrWhiteSpace(search))
{
    query = query.Where(u => u.Name.Contains(search));
}

if (isActive.HasValue)
{
    query = query.Where(u => u.IsActive == isActive.Value);
}

if (minAge.HasValue)
{
    query = query.Where(u => u.Age >= minAge.Value);
}

var result = await query
    .OrderBy(u => u.Name)
    .Take(100)
    .Select(u => new UserDto
    {
        Id = u.Id,
        Name = u.Name,
        Age = u.Age
    })
    .ToListAsync();

This is good because the query is built step by step, but executed only once at the end.

Case: Wrong Dynamic Filtering with IEnumerable

Bad:

C#
IEnumerable<User> users = await context.Users.ToListAsync();

if (!string.IsNullOrWhiteSpace(search))
{
    users = users.Where(u => u.Name.Contains(search));
}

if (isActive.HasValue)
{
    users = users.Where(u => u.IsActive == isActive.Value);
}

var result = users.ToList();

This loads all users first.

For large tables, this is a serious performance bug.

Case: GroupBy in Database vs Memory

Bad:

C#
var result = context.Orders
    .ToList()
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        Count = g.Count()
    })
    .ToList();

How to Optimize LINQ Queries:

C#
var result = await context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        Count = g.Count()
    })
    .ToListAsync();

The second version lets SQL Server perform grouping.

Case: Sum, Min, Max, Average

Bad:

C#
var orders = await context.Orders.ToListAsync();

var total = orders.Sum(o => o.TotalValue);

Better:

C#
var total = await context.Orders.SumAsync(o => o.TotalValue);

Let the database aggregate.

Case: Date Filtering

Bad:

C#
var orders = await context.Orders
    .Where(o => o.CreatedAt.Date == selectedDate.Date)
    .ToListAsync();

Calling .Date on the column may hurt index usage.

Better:

C#
var start = selectedDate.Date;
var end = start.AddDays(1);

var orders = await context.Orders
    .Where(o => o.CreatedAt >= start && o.CreatedAt < end)
    .ToListAsync();

This is much more index-friendly.

Case: Avoid Lazy Loading in APIs

Lazy loading can cause hidden queries.

Bad:

C#
var users = await context.Users.ToListAsync();

foreach (var user in users)
{
    Console.WriteLine(user.Orders.Count);
}

This can trigger one query per user.

Better:

C#
var users = await context.Users
    .Select(u => new UserSummaryDto
    {
        Id = u.Id,
        Name = u.Name,
        OrdersCount = u.Orders.Count
    })
    .ToListAsync();

This creates one controlled SQL query.

Case: Use TagWith to Identify LINQ Queries in SQL Logs

Very useful in production diagnostics.

C#
var users = await context.Users
    .TagWith("UsersController.GetActiveUsers")
    .Where(u => u.IsActive)
    .ToListAsync();

Generated SQL contains a comment:

BAT (Batchfile)
-- UsersController.GetActiveUsers
SELECT ...FROM Users WHERE IsActive = 1

This helps you connect slow SQL queries back to C# code.

Case: Get Generated SQL with ToQueryString

C#
var query = context.Users.Where(u => u.IsActive);

var sql = query.ToQueryString();

var users = await query.ToListAsync();

Important: ToQueryString() works before execution.

Good:

C#
var query = context.Users.Where(u => u.IsActive);

var sql = query.ToQueryString();

var users = await query.ToListAsync();

Bad:

C#
var users = await context.Users
    .Where(u => u.IsActive)
    .ToListAsync();

// Too late — this is already executed.

Case: Add EF Core SQL Logging

In Program.cs:

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

For development only:

C#
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors()
        .LogTo(Console.WriteLine, LogLevel.Information);
});

EnableSensitiveDataLogging() can log parameter values, so do not enable it blindly in production.

Case: Log Only Database Commands

C#
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
        .LogTo(
            Console.WriteLine,
            new[] { DbLoggerCategory.Database.Command.Name },
            LogLevel.Information);
});

This keeps logs cleaner.

Case: Detect Slow Queries with Interceptor

C#
public sealed class SlowQueryInterceptor : DbCommandInterceptor
{
    private readonly ILogger<SlowQueryInterceptor> _logger;

    public SlowQueryInterceptor(ILogger<SlowQueryInterceptor> logger)
    {
        _logger = logger;
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();

        var response = await base.ReaderExecutingAsync(
            command,
            eventData,
            result,
            cancellationToken);

        stopwatch.Stop();

        if (stopwatch.ElapsedMilliseconds > 500)
        {
            _logger.LogWarning(
                "Slow SQL query detected: {ElapsedMilliseconds} ms. SQL: {Sql}",
                stopwatch.ElapsedMilliseconds,
                command.CommandText);
        }

        return response;
    }
}

Register it:

C#
builder.Services.AddScoped<SlowQueryInterceptor>();

builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    var interceptor = sp.GetRequiredService<SlowQueryInterceptor>();

    options.UseSqlServer(connectionString)
        .AddInterceptors(interceptor);
});

Important Note About Interceptor Timing

The example above is simplified.

For precise timing, you usually need to start measuring before execution and stop after execution. In production, you can use EF Core diagnostics, logging, MiniProfiler, Application Insights, OpenTelemetry, or SQL Server Query Store.

Case: Use SQL Server Query Store

For production systems, EF logging is not enough.

Use SQL Server Query Store to find:

  • most expensive queries
  • queries with high CPU
  • queries with high duration
  • regressed execution plans
  • queries that became slower after deployment

Basic enable command:

SQL
ALTER DATABASE YourDatabase<br>SET QUERY_STORE = ON;

Then in SQL Server Management Studio:

Database → Query Store → Top Resource Consuming Queries

This is one of the best tools for finding real slow queries.

Case: Use SET STATISTICS IO and TIME

When investigating one SQL query:

SQL
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Then run the SQL generated by EF Core.

Look for:

  • logical reads
  • CPU time
  • elapsed time
  • scans
  • missing indexes

High logical reads usually mean SQL Server is reading too much data.

Case: Do Not Optimize LINQ Without Checking SQL

This is the most important rule.

Bad workflow:

LINQ looks nice → assume it is fast

Good workflow:

LINQ query
↓
ToQueryString()
↓
SQL logs
↓
Execution plan
↓
Indexes
↓
Rewrite query
↓
Measure again

LINQ optimization without looking at SQL is guessing.

Practical Checklist

Before accepting a LINQ query in production, ask:

  1. Does it execute in the database or memory?
  2. Is it still IQueryable before ToListAsync()?
  3. Is there any accidental AsEnumerable()?
  4. Does it use Select projection?
  5. Does it avoid unnecessary Include?
  6. Does it use AsNoTracking() for read-only data?
  7. Does it avoid N+1 queries?
  8. Can I see generated SQL with ToQueryString()?
  9. Is the query visible in logs?
  10. Is it covered by proper indexes?
  11. Does pagination use Skip/Take or keyset pagination?
  12. Are string/date filters index-friendly?
  13. Is the query measured in SQL Server Query Store?

Short Summary

IQueryable means:

Query is still being built.
Database provider can translate it.
Filtering can happen in SQL.

IEnumerable means:

Data is being processed as a .NET sequence.
Filtering may happen in memory.
Dangerous before reducing data size.

The golden rule:

Keep the query as IQueryable as long as possible.
Materialize with ToListAsync only at the end.

This one rule prevents a huge number of LINQ performance problems.

Source

Example project with no dependencies (SQLite) :
https://github.com/rafalkukuczka/LinqOptimizationDemo
https://github.com/rafalkukuczka/LinqOptimizationDemoSqlServer

References

https://pl.wikipedia.org/wiki/LINQ

https://sd.blackball.lv/en/articles/read/19561-optimizing-linq-queries-for-performance-and-readability-in-csharp

https://blog.elmah.io/visualizing-linq-queries-with-linqpad-boost-your-ef-core-debugging

https://www.linqpad.net/linqpad8.aspx

More Info

We implement this in real production systems.
If you need help → contact us

Contact