LINQ Optimization Patterns

Optimize LINQ queries and EF Core data access for high-performance ABP applications.

When to Use

• Fixing N+1 query problems
• Optimizing slow repository queries
• Implementing efficient pagination
• Reducing database round trips
• Improving API response times

N+1 Query Prevention

Problem: N+1 Queries

// BAD: N+1 queries - executes 1 + N queries
public async Task<List<DoctorDto>> GetDoctorsWithAppointmentsAsync()
{
    var doctors = await _doctorRepository.GetListAsync();

    var dtos = new List<DoctorDto>();
    foreach (var doctor in doctors)
    {
        // Each iteration executes a query!
        var appointments = await _appointmentRepository
            .GetListAsync(a => a.DoctorId == doctor.Id);

        dtos.Add(new DoctorDto
        {
            Id = doctor.Id,
            Name = doctor.FullName,
            AppointmentCount = appointments.Count
        });
    }
    return dtos;
}

Solution 1: Eager Loading with Include

// GOOD: Single query with Include
public async Task<List<DoctorDto>> GetDoctorsWithAppointmentsAsync()
{
    var query = await _doctorRepository.GetQueryableAsync();

    var doctors = await query
        .Include(d => d.Appointments)
        .ToListAsync();

    return doctors.Select(d => new DoctorDto
    {
        Id = d.Id,
        Name = d.FullName,
        AppointmentCount = d.Appointments.Count
    }).ToList();
}

Solution 2: Projection (Best for DTOs)

// BETTER: Project to DTO directly - only selects needed columns
public async Task<List<DoctorDto>> GetDoctorsWithAppointmentsAsync()
{
    var query = await _doctorRepository.GetQueryableAsync();

    return await query
        .Select(d => new DoctorDto
        {
            Id = d.Id,
            Name = d.FullName,
            AppointmentCount = d.Appointments.Count
        })
        .ToListAsync();
}

Solution 3: Batch Loading

// GOOD: Two queries instead of N+1
public async Task<List<DoctorDto>> GetDoctorsWithAppointmentsAsync()
{
    var doctors = await _doctorRepository.GetListAsync();
    var doctorIds = doctors.Select(d => d.Id).ToList();

    var appointmentCounts = await (await _appointmentRepository.GetQueryableAsync())
        .Where(a => doctorIds.Contains(a.DoctorId))
        .GroupBy(a => a.DoctorId)
        .Select(g => new { DoctorId = g.Key, Count = g.Count() })
        .ToDictionaryAsync(x => x.DoctorId, x => x.Count);

    return doctors.Select(d => new DoctorDto
    {
        Id = d.Id,
        Name = d.FullName,
        AppointmentCount = appointmentCounts.GetValueOrDefault(d.Id, 0)
    }).ToList();
}

Eager Loading Patterns

Include and ThenInclude

var query = await _appointmentRepository.GetQueryableAsync();

// Single level include
var appointments = await query
    .Include(a => a.Patient)
    .Include(a => a.Doctor)
    .ToListAsync();

// Nested include
var doctors = await (await _doctorRepository.GetQueryableAsync())
    .Include(d => d.Appointments)
        .ThenInclude(a => a.Patient)
    .Include(d => d.Specializations)
        .ThenInclude(s => s.Specialization)
    .ToListAsync();

WithDetails (ABP Extension)

// ABP provides WithDetails for common includes
public class DoctorRepository : EfCoreRepository<ClinicDbContext, Doctor, Guid>, IDoctorRepository
{
    public async Task<Doctor> GetWithAppointmentsAsync(Guid id)
    {
        var query = await GetQueryableAsync();

        return await query
            .IncludeDetails() // Uses default includes from repository
            .FirstOrDefaultAsync(d => d.Id == id);
    }

    public override async Task<IQueryable<Doctor>> WithDetailsAsync()
    {
        return (await GetQueryableAsync())
            .Include(d => d.Appointments)
            .Include(d => d.Specializations);
    }
}

Split Queries (Multiple Collections)

// Avoid Cartesian explosion with multiple collections
var doctors = await (await _doctorRepository.GetQueryableAsync())
    .Include(d => d.Appointments)
    .Include(d => d.Schedules)
    .Include(d => d.Specializations)
    .AsSplitQuery() // Executes 4 queries instead of 1 with Cartesian product
    .ToListAsync();

AsNoTracking for Read-Only

// GOOD: Read-only queries should use AsNoTracking
public async Task<List<PatientDto>> GetPatientsAsync()
{
    var query = await _patientRepository.GetQueryableAsync();

    return await query
        .AsNoTracking() // Skip change tracking - faster
        .Select(p => new PatientDto
        {
            Id = p.Id,
            FullName = $"{p.FirstName} {p.LastName}",
            Email = p.Email
        })
        .ToListAsync();
}

// Note: Projections with Select() are automatically NoTracking
// AsNoTracking is implicit when projecting to non-entity types

Projection Patterns

Select Only Needed Columns

// BAD: Loads entire entity
var patients = await query.ToListAsync();
var emails = patients.Select(p => p.Email);

// GOOD: Only loads Email column from database
var emails = await query
    .Select(p => p.Email)
    .ToListAsync();

Project to DTO

// GOOD: Project to DTO in query
public async Task<PagedResultDto<PatientListDto>> GetListAsync(GetPatientListInput input)
{
    var query = await _patientRepository.GetQueryableAsync();

    var totalCount = await query.CountAsync();

    var patients = await query
        .WhereIf(!input.Filter.IsNullOrWhiteSpace(),
            p => p.FirstName.Contains(input.Filter) ||
                 p.LastName.Contains(input.Filter) ||
                 p.Email.Contains(input.Filter))
        .OrderBy(input.Sorting ?? nameof(Patient.LastName))
        .PageBy(input)
        .Select(p => new PatientListDto
        {
            Id = p.Id,
            FullName = $"{p.FirstName} {p.LastName}",
            Email = p.Email,
            DateOfBirth = p.DateOfBirth,
            AppointmentCount = p.Appointments.Count
        })
        .ToListAsync();

    return new PagedResultDto<PatientListDto>(totalCount, patients);
}

Conditional Projection

// Include related data only when needed
public async Task<PatientDto> GetAsync(Guid id, bool includeAppointments = false)
{
    var query = await _patientRepository.GetQueryableAsync();

    if (includeAppointments)
    {
        return await query
            .Where(p => p.Id == id)
            .Select(p => new PatientDto
            {
                Id = p.Id,
                FullName = $"{p.FirstName} {p.LastName}",
                Appointments = p.Appointments.Select(a => new AppointmentDto
                {
                    Id = a.Id,
                    Date = a.AppointmentDate,
                    Status = a.Status
                }).ToList()
            })
            .FirstOrDefaultAsync();
    }

    return await query
        .Where(p => p.Id == id)
        .Select(p => new PatientDto
        {
            Id = p.Id,
            FullName = $"{p.FirstName} {p.LastName}"
        })
        .FirstOrDefaultAsync();
}

Efficient Pagination

Cursor-Based Pagination (Best for Large Data)

// Better than Skip/Take for large datasets
public async Task<List<PatientDto>> GetPatientsAfterAsync(
    DateTime? lastCreatedAt,
    Guid? lastId,
    int take = 20)
{
    var query = await _patientRepository.GetQueryableAsync();

    if (lastCreatedAt.HasValue && lastId.HasValue)
    {
        query = query.Where(p =>
            p.CreationTime < lastCreatedAt.Value ||
            (p.CreationTime == lastCreatedAt.Value && p.Id.CompareTo(lastId.Value) < 0));
    }

    return await query
        .OrderByDescending(p => p.CreationTime)
        .ThenByDescending(p => p.Id)
        .Take(take)
        .Select(p => new PatientDto { /* ... */ })
        .ToListAsync();
}

Offset Pagination with Count Optimization

// Only count when needed (first page or explicitly requested)
public async Task<PagedResultDto<PatientDto>> GetListAsync(
    GetPatientListInput input,
    bool includeCount = true)
{
    var query = await _patientRepository.GetQueryableAsync();
    query = ApplyFilters(query, input);

    long totalCount = 0;
    if (includeCount)
    {
        totalCount = await query.LongCountAsync();
    }

    var patients = await query
        .OrderBy(input.Sorting ?? "LastName")
        .PageBy(input)
        .Select(p => new PatientDto { /* ... */ })
        .ToListAsync();

    return new PagedResultDto<PatientDto>(totalCount, patients);
}

Filtering Patterns

ABP WhereIf Extension

public async Task<List<AppointmentDto>> GetListAsync(GetAppointmentListInput input)
{
    var query = await _appointmentRepository.GetQueryableAsync();

    return await query
        .WhereIf(input.DoctorId.HasValue, a => a.DoctorId == input.DoctorId)
        .WhereIf(input.PatientId.HasValue, a => a.PatientId == input.PatientId)
        .WhereIf(input.Status.HasValue, a => a.Status == input.Status)
        .WhereIf(input.FromDate.HasValue, a => a.AppointmentDate >= input.FromDate)
        .WhereIf(input.ToDate.HasValue, a => a.AppointmentDate <= input.ToDate)
        .WhereIf(!input.Filter.IsNullOrWhiteSpace(),
            a => a.Patient.FirstName.Contains(input.Filter) ||
                 a.Patient.LastName.Contains(input.Filter))
        .OrderByDescending(a => a.AppointmentDate)
        .Select(a => new AppointmentDto { /* ... */ })
        .ToListAsync();
}

Search with Full-Text (PostgreSQL)

// Use EF.Functions for database-specific operations
var patients = await query
    .Where(p => EF.Functions.ILike(p.FirstName, $"%{searchTerm}%") ||
                EF.Functions.ILike(p.LastName, $"%{searchTerm}%"))
    .ToListAsync();

Aggregation Patterns

Efficient Counting

// BAD: Loads all entities to count
var count = (await _patientRepository.GetListAsync()).Count;

// GOOD: Count at database level
var query = await _patientRepository.GetQueryableAsync();
var count = await query.CountAsync();

// GOOD: Count with filter
var activeCount = await query
    .Where(p => p.Status == PatientStatus.Active)
    .CountAsync();

Grouped Aggregations

// Get appointment counts by status
var query = await _appointmentRepository.GetQueryableAsync();

var statusCounts = await query
    .GroupBy(a => a.Status)
    .Select(g => new
    {
        Status = g.Key,
        Count = g.Count()
    })
    .ToDictionaryAsync(x => x.Status, x => x.Count);

Existence Checks

// BAD: Loads entity to check existence
var patient = await _patientRepository.FirstOrDefaultAsync(p => p.Email == email);
var exists = patient != null;

// GOOD: Use Any() - stops at first match
var query = await _patientRepository.GetQueryableAsync();
var exists = await query.AnyAsync(p => p.Email == email);

Raw SQL (When Necessary)

// For complex queries that can't be expressed in LINQ
public async Task<List<DoctorAvailabilityDto>> GetDoctorAvailabilityAsync(DateTime date)
{
    var context = await _dbContextProvider.GetDbContextAsync();

    return await context.Database
        .SqlQuery<DoctorAvailabilityDto>($@"
            SELECT d.Id, d.FullName, COUNT(a.Id) as AppointmentCount
            FROM Doctors d
            LEFT JOIN Appointments a ON d.Id = a.DoctorId
                AND a.AppointmentDate::date = {date:yyyy-MM-dd}
                AND a.IsDeleted = false
            WHERE d.IsDeleted = false
            GROUP BY d.Id, d.FullName
            ORDER BY AppointmentCount ASC")
        .ToListAsync();
}

Anti-Patterns to Avoid

| Anti-Pattern | Problem | Solution | |--------------|---------|----------| | N+1 queries | Multiple round trips | Use Include or projection | | ToList() then Where() | Loads all data to memory | Filter in query | | Select * equivalent | Loads unnecessary columns | Project to DTO | | Tracking read-only data | Overhead for change tracking | Use AsNoTracking | | Large OFFSET pagination | Slow for large offsets | Use cursor-based pagination | | Count() after ToList() | Loads all data | Use CountAsync() | | Multiple SaveChangesAsync() | Multiple transactions | Batch changes | | Count() after pagination | Double query execution | Count BEFORE ToListAsync() | | GetListAsync() for validation | Loads entire table | Use filtered AnyAsync() or GetQueryableAsync() | | In-memory joins for bulk ops | Memory explosion | Use IQueryable joins, filter server-side |

Critical: Count After Pagination (Double Query)

// ❌ BAD: Executes query TWICE (one for data, one for count)
var dtos = await AsyncExecuter.ToListAsync(
    queryable
    .OrderBy(input.Sorting)
    .Skip(input.SkipCount)
    .Take(input.MaxResultCount)
);
var totalCount = queryable.Count(); // Second execution on same queryable!

// ✅ GOOD: Count FIRST, then paginate
var totalCount = await AsyncExecuter.CountAsync(queryable);
var dtos = await AsyncExecuter.ToListAsync(
    queryable
    .OrderBy(input.Sorting)
    .Skip(input.SkipCount)
    .Take(input.MaxResultCount)
);

Critical: Loading Full Tables for Validation

// ❌ BAD: Loads ALL records to memory for validation
var _projects = await _projectRepository.GetListAsync();
var _customers = await _customerRepository.GetListAsync();
var _licensePlates = await _licensePlateRepository.GetListAsync();

// Then validates with in-memory LINQ
foreach (var item in input)
{
    var project = _projects.FirstOrDefault(p => p.Code == item.ProjectCode);
    // ...
}

// ✅ GOOD: Only load what you need based on input
var projectCodes = input.Select(x => x.ProjectCode).Distinct().ToList();
var customerNames = input.Select(x => x.CustomerName).Distinct().ToList();

var projects = await (await _projectRepository.GetQueryableAsync())
    .Where(p => projectCodes.Contains(p.ProjectCode))
    .ToDictionaryAsync(p => p.ProjectCode, p => p);

var customers = await (await _customerRepository.GetQueryableAsync())
    .Where(c => customerNames.Contains(c.CustomerName))
    .ToDictionaryAsync(c => c.CustomerName, c => c);

// Validate using dictionaries (O(1) lookup)
foreach (var item in input)
{
    if (!projects.TryGetValue(item.ProjectCode, out var project))
    {
        validations.Add($"Invalid project code: {item.ProjectCode}");
    }
}

Performance Checklist

• [ ] No N+1 queries (check with logging)
• [ ] Projections used for DTOs
• [ ] AsNoTracking for read-only queries
• [ ] Appropriate indexes exist for filters
• [ ] Pagination uses cursor or optimized offset
• [ ] Counts done at database level
• [ ] Include only loads needed relations
• [ ] Split queries for multiple collections

Debugging Queries

// Enable EF Core logging in appsettings.Development.json
{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

// Or use ToQueryString() to see generated SQL
var query = await _patientRepository.GetQueryableAsync();
var sql = query
    .Where(p => p.Status == PatientStatus.Active)
    .ToQueryString();

_logger.LogInformation("Generated SQL: {Sql}", sql);

Integration Points

This skill is used by:

• abp-developer: Efficient data access implementation
• abp-code-reviewer: Query performance validation
• debugger: Performance issue diagnosis