Compiled Queries और Performance

// With tracking (default) — for entities you'll modify
var tracked = await _db.Products.FirstOrDefaultAsync(p => p.Id == 1);

// Without tracking — for read-only
var dto = await _db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .Select(p => new ProductDto { Id = p.Id, Name = p.Name })
    .ToListAsync();

// Define once, reuse many times
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery(
        (AppDbContext db, int id) =>
            db.Products.AsNoTracking().FirstOrDefault(p => p.Id == id));

// Usage
var product = await GetProductById(_db, 42);

// Load entire entity (over-fetch)
var all = await _db.Products.ToListAsync();

// Projection: only fetch needed columns
var names = await _db.Products
    .AsNoTracking()
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();
// SQL: SELECT Id, Name, Price FROM Products

// Configure compound index in OnModelCreating
modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.Status })
    .HasFilter("[Status] = 0")  // filtered index
    .IncludeProperties(o => o.PlacedAt);

// The query below will use this index efficiently
var pending = await _db.Orders
    .AsNoTracking()
    .Where(o => o.CustomerId == 5 && o.Status == OrderStatus.Pending)
    .ToListAsync();

int page = 2, pageSize = 25;

var products = await _db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();
// SQL: ... ORDER BY Name OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY

// Total count for pagination
int totalCount = await _db.Products
    .Where(p => p.IsActive)
    .CountAsync();

// Existence check — faster than Count > 0
bool hasExpensive = await _db.Products
    .AnyAsync(p => p.Price > 1000m);
// SQL: IF EXISTS (SELECT 1 FROM ...) SELECT 1

var newProducts = Enumerable.Range(1, 1000)
    .Select(i => new Product { Name = $"Product {i}", Price = i * 1.5m })
    .ToList();

_db.Products.AddRange(newProducts);
await _db.SaveChangesAsync();
// Batched: typically 100-1000 rows per round-trip

// LIKE pattern search (SQL-side)
var results = await _db.Products
    .Where(p => EF.Functions.Like(p.Name, "%keyboard%"))
    .AsNoTracking()
    .ToListAsync();

// Date difference (SQL-side)
var recentOrders = await _db.Orders
    .Where(o => EF.Functions.DateDiffDay(o.PlacedAt, DateTime.UtcNow) <= 7)
    .ToListAsync();

builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlServer(connStr)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging() // dev only!
       .EnableDetailedErrors());     // dev only!

app.MapGet("/products", async (
    AppDbContext db,
    string? search,
    int page = 1, int size = 20) =>
{
    var query = db.Products
        .AsNoTracking()
        .Where(p => p.IsActive);

    if (!string.IsNullOrEmpty(search))
        query = query.Where(p => EF.Functions.Like(p.Name, $"%{search}%"));

    var total = await query.CountAsync();
    var items = await query
        .OrderBy(p => p.Name)
        .Skip((page - 1) * size).Take(size)
        .Select(p => new { p.Id, p.Name, p.Price })
        .ToListAsync();

    return Results.Ok(new { total, items });
});