LINQ with EF Core: what translates and common pitfalls — LINQ — Language Integrated Query

11. LINQ with EF Core: what translates and common pitfalls

Advanced★ Must knowLesson 11 of 12

Projection with Select into DTOs fetches only needed columns and needs no tracking.
Methods EF can't translate (your own C# methods) cause an exception (EF Core 3.0+ no longer silently evaluates on the client, except in the final Select).
Contains on a local list → WHERE Id IN (...) (EF 8+ can send it as a JSON parameter for plan reuse).
N+1: looping over orders and touching o.Customer with lazy loading → one query per order. Use Include or projection.
Count() vs Any(): use AnyAsync() for existence.
First without OrderBy → nondeterministic order.
String comparisons follow DB collation (often case-insensitive in SQL Server).
Use async operators (ToListAsync, FirstOrDefaultAsync) in web apps.

csharp

var summaries = await db.Customers
    .Where(c => c.IsActive)
    .Select(c => new CustomerSummary(c.Id, c.Name, c.Orders.Count, c.Orders.Sum(o => (decimal?)o.Total) ?? 0))
    .OrderByDescending(s => s.Total)
    .Take(10)
    .ToListAsync(ct);