// This LINQ looks simple but hides three performance traps:
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

// Trap 1: EF Core translates LINQ to SQL on every call — query compilation overhead
// Trap 2: Multiple Includes can trigger cartesian explosion
// Trap 3: Loading all columns even if you need two

// Without compiled query — LINQ compiled on every call
public async Task<List<OrderDto>> GetOrdersAsync(int customerId)
{
    return await context.Orders
        .Where(o => o.CustomerId == customerId)
        .Select(o => new OrderDto(o.Id, o.Total, o.Status))
        .ToListAsync();
}

// With compiled query — compilation happens once at startup
private static readonly Func<AppDbContext, int, IAsyncEnumerable<OrderDto>> GetOrdersQuery =
    EF.CompileAsyncQuery((AppDbContext ctx, int customerId) =>
        ctx.Orders
            .Where(o => o.CustomerId == customerId)
            .Select(o => new OrderDto(o.Id, o.Total, o.Status)));

public async Task<List<OrderDto>> GetOrdersCompiledAsync(int customerId)
{
    var results = new List<OrderDto>();
    await foreach (var dto in GetOrdersQuery(context, customerId))
        results.Add(dto);
    return results;
}

// Compiled queries work well in a repository — define as static fields
public class OrderRepository(AppDbContext context)
{
    private static readonly Func<AppDbContext, int, string, IAsyncEnumerable<Order>>
        ByCustomerAndStatus = EF.CompileAsyncQuery(
            (AppDbContext ctx, int customerId, string status) =>
                ctx.Orders
                    .Where(o => o.CustomerId == customerId && o.Status == status)
                    .OrderByDescending(o => o.CreatedAt));

    public async Task<List<Order>> GetByStatusAsync(int customerId, string status, CancellationToken ct)
    {
        var orders = new List<Order>();
        await foreach (var o in ByCustomerAndStatus(context, customerId, status)
            .WithCancellation(ct))
        {
            orders.Add(o);
        }
        return orders;
    }
}

// BAD: cartesian explosion
// 1 order × 10 items × 3 tags = 30 rows returned for 1 order
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Tags)   // second collection = cartesian product
    .ToListAsync();

// Generated SQL:
// SELECT * FROM Orders o
// JOIN Items i ON i.OrderId = o.Id
// JOIN Tags t ON t.OrderId = o.Id
// → 10 × 3 = 30 rows for a single order

// GOOD: split into separate queries — avoids multiplication
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Tags)
    .AsSplitQuery()   // two separate SELECT statements, EF Core joins in memory
    .ToListAsync();

// Generated SQL — two queries:
// SELECT * FROM Orders
// SELECT * FROM Items WHERE OrderId IN (...)
// SELECT * FROM Tags WHERE OrderId IN (...)

// Set split query as the global default
builder.Services.AddDbContext<AppDbContext>(opts =>
    opts.UseNpgsql(connectionString,
        npgsql => npgsql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)));

// BAD: load entire entity when you only need two columns
var orders = await context.Orders.ToListAsync();
foreach (var o in orders)
    Console.WriteLine($"{o.Id}: {o.Status}");   // only uses Id and Status

// GOOD: project to DTO — smaller payload, faster query
var dtos = await context.Orders
    .Select(o => new OrderSummaryDto(o.Id, o.Status))
    .ToListAsync();

// EF Core generates: SELECT Id, Status FROM Orders
// vs the bad version: SELECT * FROM Orders (all 15 columns)

// BAD: load all, modify in memory, save changes — N round trips
var orders = await context.Orders
    .Where(o => o.Status == "Pending" && o.CreatedAt < cutoff)
    .ToListAsync();

foreach (var order in orders)
    order.Status = "Cancelled";

await context.SaveChangesAsync();
// Generates: SELECT, then UPDATE for each row individually

// GOOD: single UPDATE statement
await context.Orders
    .Where(o => o.Status == "Pending" && o.CreatedAt < cutoff)
    .ExecuteUpdateAsync(s =>
        s.SetProperty(o => o.Status, "Cancelled")
         .SetProperty(o => o.UpdatedAt, DateTime.UtcNow));

// Generates: UPDATE Orders SET Status='Cancelled', UpdatedAt=... WHERE Status='Pending' AND CreatedAt < ...

// Bulk delete
await context.AuditLogs
    .Where(l => l.CreatedAt < DateTime.UtcNow.AddDays(-90))
    .ExecuteDeleteAsync();

// Generates: DELETE FROM AuditLogs WHERE CreatedAt < ...

// FromSqlRaw — map raw SQL to an entity type
var orders = await context.Orders
    .FromSqlRaw("""
        SELECT o.*
        FROM Orders o
        INNER JOIN (
            SELECT CustomerId, MAX(Total) AS MaxTotal
            FROM Orders
            GROUP BY CustomerId
        ) t ON o.CustomerId = t.CustomerId AND o.Total = t.MaxTotal
        """)
    .AsNoTracking()
    .ToListAsync();

// FromSqlInterpolated — safe parameterization, prevents SQL injection
int customerId = 42;
var orders2 = await context.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE CustomerId = {customerId}")
    .AsNoTracking()
    .ToListAsync();

// SqlQuery for non-entity types (EF Core 7+)
var totals = await context.Database
    .SqlQuery<decimal>($"SELECT Total FROM Orders WHERE Status = 'Paid'")
    .ToListAsync();

// BAD: change tracking overhead on every loaded entity — unnecessary for reads
var orders = await context.Orders.ToListAsync();

// GOOD: skip change tracking — 20-30% faster for read-only paths
var orders = await context.Orders
    .AsNoTracking()
    .Where(o => o.Status == "Paid")
    .ToListAsync();

// Set globally for a context used only for reads
builder.Services.AddDbContext<ReadAppDbContext>(opts =>
    opts.UseNpgsql(connectionString)
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

// Tag queries so they appear in database logs and APM tools
var orders = await context.Orders
    .TagWith("GetOrdersByCustomer — CustomerController.GetOrders")
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

// Generated SQL:
// -- GetOrdersByCustomer — CustomerController.GetOrders
// SELECT * FROM Orders WHERE CustomerId = @p0

// OFFSET pagination — simple but slow on large tables (DB scans all skipped rows)
var page = await context.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync();

// KEYSET (cursor) pagination — fast regardless of table size
// Client sends the last seen values as the cursor
var page = await context.Orders
    .Where(o => o.CreatedAt < lastSeenCreatedAt ||
               (o.CreatedAt == lastSeenCreatedAt && o.Id < lastSeenId))
    .OrderByDescending(o => o.CreatedAt).ThenByDescending(o => o.Id)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync();