<skill_overview> Write efficient EF Core queries and avoid performance pitfalls
Writing LINQ queries with EF Core Optimizing slow database queries Debugging N+1 or performance issues Bulk data operations Deciding between query approaches
EF Core Performance Querying Data
</skill_overview> <query_rules>
Use AsNoTracking() for ALL read-only queries Reduces memory, skips change detection, significantly faster
var users = await context.Users .AsNoTracking() .Where(u => u.IsActive) .ToListAsync();
// Projections are automatically no-tracking var userDtos = await context.Users .Where(u => u.IsActive) .Select(u => new UserDto(u.Id, u.Name, u.Email)) .ToListAsync();
// BAD: Tracking enabled for read-only data var users = await context.Users .Where(u => u.IsActive) .ToListAsync();
When you need to update entities after loading
<execute_update> Update multiple rows without loading entities
// Deactivate inactive users await context.Users .Where(u => u.LastLoginAt < DateTime.UtcNow.AddYears(-1)) .ExecuteUpdateAsync(u => u .SetProperty(x => x.IsActive, false) .SetProperty(x => x.DeactivatedAt, DateTime.UtcNow));
// Give 10% raise to all employees in department await context.Employees .Where(e => e.DepartmentId == deptId) .ExecuteUpdateAsync(e => e .SetProperty(x => x.Salary, x => x.Salary * 1.10m));
</execute_update>
<execute_delete> Delete multiple rows without loading entities
// Delete old logs await context.Logs .Where(l => l.CreatedAt < DateTime.UtcNow.AddMonths(-6)) .ExecuteDeleteAsync();
// Permanently delete soft-deleted records older than 30 days await context.Users .IgnoreQueryFilters() .Where(u => u.IsDeleted) .Where(u => u.DeletedAt < DateTime.UtcNow.AddDays(-30)) .ExecuteDeleteAsync();
</execute_delete>
private static readonly Func<AppDbContext, string, IAsyncEnumerable<User>> SearchByEmailQuery = EF.CompileAsyncQuery((AppDbContext db, string email) => db.Users.Where(u => u.Email.Contains(email)));
private readonly AppDbContext _context;
public async Task<User?> GetByIdAsync(int id) { return await GetByIdQuery(_context, id); }
public IAsyncEnumerable<User> SearchByEmailAsync(string email) { return SearchByEmailQuery(_context, email); }
}
</compiled_queries>
Always paginate large result sets
public async Task<PagedResult<UserDto>> GetUsersAsync(int page, int pageSize) { var query = context.Users .AsNoTracking() .Where(u => u.IsActive) .OrderBy(u => u.CreatedAt);
var totalCount = await query.CountAsync();
var items = await query .Skip((page - 1) * pageSize) .Take(pageSize) .Select(u => new UserDto(u.Id, u.Name, u.Email)) .ToListAsync();
return new PagedResult<UserDto>(items, totalCount, page, pageSize);
}
Always include OrderBy before Skip/Take for consistent results
Query returns many more rows than expected, slow performance Multiple Include() on collections without AsSplitQuery() Use AsSplitQuery() or separate queries