Improving the performance of a slow-running LINQ query—especially when using Entity Framework or LINQ to SQL—often requires identifying bottlenecks and applying optimizations both in code and at the database level.

1. Use AsNoTracking() for Read-Only Queries

Entity Framework by default tracks entities, which adds overhead. Use this for read-only queries:

var users = context.Users.AsNoTracking().Where(u => u.IsActive).ToList();

2. Filter Early (Apply Where as Soon as Possible)

Move .Where() clauses before any .Select(), .ToList(), .Include(), etc., to limit the dataset early:

// BAD: selects all before filtering
var data = db.Users.Select(u => new { u.Id, u.Name }).Where(u => u.Id > 100).ToList();

// GOOD: filters first
var data = db.Users.Where(u => u.Id > 100).Select(u => new { u.Id, u.Name }).ToList();

3. Avoid N+1 Problems with .Include()

Use .Include() to eagerly load related data and avoid multiple queries:

// BAD: causes N+1 query problem
var users = context.Users.ToList();
foreach (var user in users) {
    var orders = context.Orders.Where(o => o.UserId == user.Id).ToList();
}

// GOOD: loads related data in one query
var users = context.Users.Include(u => u.Orders).ToList();

4. Project Early with .Select()

Avoid loading entire entities when you only need a few fields:

// BAD: loads all columns
var users = context.Users.ToList();

// GOOD: projects to lightweight objects
var users = context.Users.Select(u => new { u.Id, u.Name }).ToList();

5. Limit Results with .Take() / .Skip()

Only retrieve what you need:

var recentUsers = context.Users.OrderByDescending(u => u.CreatedDate).Take(50).ToList();

6. Use Indexes in the Database

Ensure SQL Server (or other DB) has proper indexes on columns used in:

• Where
• OrderBy
• Join conditions

You can use .ToQueryString() in EF Core to inspect the SQL and identify performance bottlenecks.

7. Batch Queries (Avoid Large ToList() Calls)

If you’re processing a large dataset, use batching to reduce memory pressure:

var pageSize = 100;
for (int i = 0; i < totalCount; i += pageSize)
{
    var batch = context.Users.Skip(i).Take(pageSize).ToList();
    Process(batch);
}

8. Avoid Client-Side Evaluation

Make sure your LINQ expressions are fully translatable to SQL. Otherwise, EF will bring data into memory and evaluate it on the client (which is slow).

// BAD: Contains on a list of objects not supported in SQL
var ids = someComplexObjectList.Select(x => x.Id);
var data = context.Users.Where(u => ids.Contains(u.Id)).ToList(); // may run in memory

// GOOD: use a plain list of IDs
var idList = new List<int> { 1, 2, 3 };
var data = context.Users.Where(u => idList.Contains(u.Id)).ToList();

9. Profile the Generated SQL

Use:

• .ToQueryString() in EF Core
• SQL Profiler or logs To inspect the actual SQL and optimize it.

var query = context.Users.Where(x => x.IsActive);
Console.WriteLine(query.ToQueryString());

10. Split Complex LINQ into Steps

Break large LINQ queries into smaller parts to help EF translate them better and isolate bottlenecks.

Summary Table