Code review: this order-reporting code mixes EF Core and LINQ to Objects. It calls ToList() before Where, uses Count() > 0, calls FirstOrDefault on a list inside a loop, does OrderBy().First(), enumerates the same query several times, loads related data inside a foreach, and builds IN lists with Contains over 50,000 ids. Find the LINQ performance mistakes and rewrite it.

Code under review (representative)

public async Task<OrderReport> BuildReportAsync(DateTime from, IList<int> customerIds)
{
    var orders = db.Orders.Include(o => o.Lines).ToList()                 // 1: ToList before Where
                   .Where(o => o.CreatedAt >= from);

    if (orders.Count() > 0) { /* ... */ }                                  // 2: Count() > 0 (and re-enumeration)

    var products = db.Products.ToList();
    foreach (var line in orders.SelectMany(o => o.Lines))
    {
        var product = products.FirstOrDefault(p => p.Id == line.ProductId);  // 3: FirstOrDefault in a loop
        line.ProductName = product?.Name;
    }

    var biggest = orders.OrderByDescending(o => o.Total).First();          // 4: sort to get max

    var total = orders.Sum(o => o.Total);                                   // 5: multiple enumeration
    var avg = orders.Average(o => o.Total);

    foreach (var o in orders)
        o.Customer = db.Customers.Single(c => c.Id == o.CustomerId);       // 6: query per row (N+1)

    var vip = db.Orders.Where(o => customerIds.Contains(o.CustomerId))     // 7: Contains over 50,000 ids
                       .ToList();
    ...
}

The problems, in order of impact

1. ToList() before Where — loads every order and every line (Include) into memory, tracked, then filters in C#. On a large table this is the dominant cost: huge SQL result, network transfer, memory, change-tracking overhead. Fix: filter and project in SQL.

6. Querying inside a loop (N+1) — one SELECT per order to load its customer: 10,000 orders → 10,000 round trips. Fix: join/project in the main query (o.Customer.Name), or load customers once into a dictionary.

7. Contains over 50,000 ids — EF Core translates list.Contains(x) either to a giant IN (@p0, @p1, ...) (older versions; SQL Server has a ~2,100 parameter limit and huge SQL text kills plan caching) or, in EF Core 8+, to an OPENJSON parameter on SQL Server — better, but 50,000 values is still a big payload and can produce poor cardinality estimates. Fix: batch the ids (e.g. Chunk(1000)), use a table-valued parameter/temp table join, or better, express the criterion in SQL (e.g. join on a VipCustomers table or a flag).

3. FirstOrDefault on a list inside a loop — O(lines × products). With 100,000 lines and 20,000 products that's 2 billion comparisons. Fix: ToDictionary once (or project the product name in SQL).

5. Enumerating the same query several times — orders is a deferred IEnumerable (because of .Where after ToList): Count(), SelectMany, OrderByDescending, Sum, Average and the foreach each re-run the filter over the in-memory list (and with an IQueryable, each would be a separate database query). Fix: materialise once into a list, or compute aggregates in one SQL query.

4. OrderByDescending().First() — sorts everything (O(n log n), buffers all items) to get one item. Fix: MaxBy(o => o.Total) in memory, or in SQL OrderByDescending(...).Select(...).FirstOrDefaultAsync() (which translates to TOP(1) ... ORDER BY, fine with an index).

2. Count() > 0 — counts all rows to test existence. Fix: Any() (or list.Count > 0 on a materialised list).

Other issues: synchronous ToList() in an async method (blocks threads), tracked entities for a read-only report (AsNoTracking), mutating entities (line.ProductName = ...) as a DTO hack, and DateTime vs DateTimeOffset time-zone ambiguity.

The rewrite

public async Task<OrderReport> BuildReportAsync(DateTimeOffset from, IReadOnlyCollection<int> customerIds, CancellationToken ct)
{
    // 1. Filter + project in SQL, no tracking, one round trip
    var rows = await db.Orders
        .AsNoTracking()
        .Where(o => o.CreatedAt >= from)
        .Select(o => new OrderRow(
            o.Id,
            o.Total,
            o.Customer.Name,                                    // join instead of N+1
            o.Lines.Select(l => new LineRow(l.ProductId, l.Product.Name, l.Quantity)).ToList()))
        .TagWith("Order report")
        .ToListAsync(ct);                                       // materialise ONCE

    if (rows.Count == 0) return OrderReport.Empty;

    // 2. In-memory aggregates over the materialised list (single pass each, no re-query)
    var biggest = rows.MaxBy(r => r.Total)!;
    var total = rows.Sum(r => r.Total);
    var average = total / rows.Count;

    // 3. VIP orders: batch the large id list (or join a VIP table in SQL)
    var vipOrderIds = new List<int>();
    foreach (var batch in customerIds.Chunk(1_000))
        vipOrderIds.AddRange(await db.Orders.AsNoTracking()
            .Where(o => batch.Contains(o.CustomerId) && o.CreatedAt >= from)
            .Select(o => o.Id)
            .ToListAsync(ct));

    return new OrderReport(rows, biggest, total, average, vipOrderIds);
}

If the report must aggregate over very large data, I'd push aggregates into SQL too (GroupBy with Sum/Count translated by EF Core) rather than loading rows. And for the 50,000-id case, a TVP or a staging table join is the robust long-term fix.

How I'd present the review

Lead with the two biggest wins (filter/project in SQL; remove N+1), quantify with evidence (SQL logs or Application Insights dependency counts), then the algorithmic fixes (dictionary lookups, MaxBy, Any), then hygiene (AsNoTracking, async, single materialisation). Suggest guardrails: EF Core query logging/tags in development, CA1851 (multiple enumeration), CA1827/CA1860 (Any/Count), and a performance test for the report.