coder-csharp-efcore-queries

<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