LINQ Query Patterns

This skill enforces specific opinions on how LINQ queries should be defined and executed within the application.

Core Principles

1. Separation of Concerns: The definition of a query (specification) must be separate from its execution.
2. Query Syntax: All queries must use LINQ Query Syntax (from ... where ... select), not Method Syntax (.Where(...).Select(...)).
3. Async Execution: All database materialization must be asynchronous (await ...ToListAsync(), await ...FirstOrDefaultAsync()).
4. Projection: Read operations must project directly into DTOs or result types. Entities should only be selected when the intent is modification or deletion.
5. Intermediate Variables: Use let clauses for intermediate calculations or sub-queries instead of nested logic.

Examples

Read Operations (Projections)

Bad:

// ❌ Bad: Method syntax, direct return without separation
return await context.Acts
    .Where(a => a.Id == id)
    .Select(a => new ActDto { ... })
    .FirstOrDefaultAsync();

// ❌ Bad: Selecting entity then mapping in memory
var act = await context.Acts.FirstOrDefaultAsync(a => a.Id == id);
return MapToDto(act);

Good:

// ✅ Good: Separate spec, query syntax, direct projection
var actSpec =
    from act in context.Acts.AsNoTracking()
    where act.Id == id
    select new ActDto
    {
        Id = act.Id,
        Name = act.Name,
        // ...
    };

return await actSpec.FirstOrDefaultAsync(cancellationToken);

Modification Operations (Entity Selection)

Bad:

// ❌ Bad: Method syntax
var act = await context.Acts.FirstOrDefaultAsync(a => a.Id == id);

Good:

// ✅ Good: Separate spec, query syntax selecting entity
var actSpec =
    from a in context.Acts
    where a.Id == id
    select a;

var act = await actSpec.FirstOrDefaultAsync(cancellationToken);

if (act != null)
{
    act.Name = dto.Name;
    await context.SaveChangesAsync(cancellationToken);
}

Complex Queries

Good:

var billingItemsSpec =
    // Find the current billing schedule for the selected service
    from contractBillingSchedule in context.ContractBillingSchedule
    where contractBillingSchedule.Contract.Client.TenantGUID == request.TenantGuid
    && billingServiceIDs.Contains(contractBillingSchedule.BillingSchedule.BillingServiceID)
    
    // Use 'let' for intermediate logic
    let billingScheduleHistory = contractBillingSchedule.BillingSchedule.BillingScheduleHistories
        .OrderByDescending(x => x.CreatedAt)
        .FirstOrDefault()
        
    // Joins
    join pricingCalculation in context.PricingCalculation 
        on billingScheduleHistory.BillingScheduleHistoryID equals pricingCalculation.BillingScheduleHistoryID
        
    select new
    {
        pricingCalculation.BillingItem.ResourceName,
        pricingCalculation.Description,
        // ...
    };

var billingItems = await billingItemsSpec.ToListAsync();