// BAD: N+1 queries - one query per employee's department
var employees = await repo.GetAllAsync(e => e.CompanyId == companyId, ct);
foreach (var emp in employees)
{
    var dept = await deptRepo.GetByIdAsync(emp.DepartmentId, ct);  // N queries!
}

// GOOD: Eager loading with loadRelatedEntities
var employees = await repo.GetAllAsync(
    e => e.CompanyId == companyId,
    ct,
    loadRelatedEntities: e => e.Department);  // Single query with join

// GOOD: Batch load related entities
var employees = await repo.GetAllAsync(e => e.CompanyId == companyId, ct);
var deptIds = employees.Select(e => e.DepartmentId).Distinct().ToList();
var departments = await deptRepo.GetByIdsAsync(deptIds, ct);
var deptMap = departments.ToDictionary(d => d.Id);
employees.ForEach(e => e.Department = deptMap.GetValueOrDefault(e.DepartmentId));

// BAD: Fetching entire entity when only ID needed
var employee = await repo.GetByIdAsync(id, ct);
return employee.Id;

// GOOD: Projection to fetch only needed data
var employeeId = await repo.FirstOrDefaultAsync(
    query => query
        .Where(Employee.UniqueExpr(userId, companyId))
        .Select(e => e.Id),  // Only fetch ID column
    ct);

// BAD: Sequential queries that could run in parallel
var count = await repo.CountAsync(filter, ct);
var items = await repo.GetAllAsync(filter, ct);
var stats = await statsRepo.GetAsync(companyId, ct);

// GOOD: Parallel tuple queries
var (count, items, stats) = await (
    repo.CountAsync((uow, q) => queryBuilder(uow, q), ct),
    repo.GetAllAsync((uow, q) => queryBuilder(uow, q).PageBy(skip, take), ct),
    statsRepo.GetAsync(companyId, ct)
);

protected override async Task<Result> HandleAsync(Query req, CancellationToken ct)
{
    // Define query once, reuse for count and data
    var queryBuilder = repo.GetQueryBuilder((uow, q) => q
        .Where(Employee.OfCompanyExpr(RequestContext.CurrentCompanyId()))
        .WhereIf(req.Statuses.Any(), e => req.Statuses.Contains(e.Status))
        .WhereIf(req.DepartmentId.IsNotNullOrEmpty(), e => e.DepartmentId == req.DepartmentId)
        .PipeIf(req.SearchText.IsNotNullOrEmpty(), q =>
            fullTextSearch.Search(q, req.SearchText, Employee.SearchColumns())));

    // Parallel execution
    var (total, items) = await (
        repo.CountAsync((uow, q) => queryBuilder(uow, q), ct),
        repo.GetAllAsync((uow, q) => queryBuilder(uow, q)
            .OrderByDescending(e => e.CreatedDate)
            .PageBy(req.SkipCount, req.MaxResultCount), ct)
    );

    return new Result(items, total);
}

// Builds efficient query with only needed conditions
var query = repo.GetQueryBuilder((uow, q) => q
    .Where(e => e.CompanyId == companyId)  // Always applied
    .WhereIf(status.HasValue, e => e.Status == status)  // Only if provided
    .WhereIf(deptIds.Any(), e => deptIds.Contains(e.DepartmentId))
    .WhereIf(dateFrom.HasValue, e => e.CreatedDate >= dateFrom)
    .WhereIf(dateTo.HasValue, e => e.CreatedDate <= dateTo));

// Define searchable columns in entity
public static Expression<Func<Employee, object?>>[] DefaultFullTextSearchColumns()
    => [e => e.FullName, e => e.Email, e => e.EmployeeCode, e => e.FullTextSearch];

// Use full-text search service
.PipeIf(searchText.IsNotNullOrEmpty(), q => fullTextSearch.Search(
    q,
    searchText,
    Employee.DefaultFullTextSearchColumns(),
    fullTextAccurateMatch: true,  // Exact phrase match
    includeStartWithProps: [e => e.FullName, e => e.EmployeeCode]  // Prefix matching
));

// Single field index - for equality queries
{ "CompanyId": 1 }

// Compound index - for filtered queries
{ "CompanyId": 1, "Status": 1, "CreatedDate": -1 }

// Text index - for full-text search
{ "FullName": "text", "Email": "text", "EmployeeCode": "text" }

// Sparse index - for optional fields
{ "ExternalId": 1, sparse: true }

-- Covering index for common query
CREATE INDEX IX_Employee_Company_Status
ON Employees (CompanyId, Status)
INCLUDE (FullName, Email, CreatedDate);

-- Filtered index for active records
CREATE INDEX IX_Employee_Active
ON Employees (CompanyId, CreatedDate)
WHERE Status = 'Active' AND IsDeleted = 0;

-- Full-text index
CREATE FULLTEXT INDEX ON Employees (FullName, Email)
KEY INDEX PK_Employees;

// GOOD: Keyset pagination for large datasets (cursor-based)
var items = await repo.GetAllAsync(q => q
    .Where(e => e.CompanyId == companyId)
    .Where(e => e.Id > lastId)  // Cursor
    .OrderBy(e => e.Id)
    .Take(pageSize), ct);

// GOOD: Offset pagination for moderate datasets
var items = await repo.GetAllAsync(q => q
    .Where(filter)
    .OrderByDescending(e => e.CreatedDate)
    .PageBy(skip, take), ct);  // Platform helper

// BAD: Skip without limit (fetches all then skips)
var items = await repo.GetAllAsync(q => q.Skip(1000), ct);

// Bulk insert
await repo.CreateManyAsync(entities, ct);

// Bulk update (with optimization flags)
await repo.UpdateManyAsync(
    entities,
    dismissSendEvent: true,  // Skip entity events for performance
    checkDiff: false,        // Skip change detection
    ct);

// Bulk delete by expression
await repo.DeleteManyAsync(e => e.Status == Status.Deleted && e.DeletedDate < cutoffDate, ct);

// MongoDB - Check indexes used
db.employees.find({ companyId: "x", status: "Active" }).explain("executionStats")

// SQL Server - Check execution plan
SET STATISTICS IO ON
SELECT * FROM Employees WHERE CompanyId = 'x' AND Status = 'Active'