Which LINQ operators cause immediate execution, and how do you choose between ToList, ToArray, ToDictionary, ToHashSet, ToLookup and the async versions? Explain the cost of each, single-item operators like First, Single and Any, and how to materialise efficiently.

1. How ToList/ToArray size their buffers

• If the source implements ICollection<T>, ToList/ToArray call CopyTo with an exact-size buffer — one allocation.
• If the source is a LINQ iterator that knows its count (IIListProvider<T>/Select over a list), it pre-sizes too.
• Otherwise, ToArray uses a growing builder (doubling segments, then a final exact copy); ToList uses List<T> growth (may end with spare capacity).
• Enumerable.TryGetNonEnumeratedCount (.NET 6) exposes whether a count is available without enumerating.

2. ToDictionary vs ToLookup vs GroupBy

• ToDictionary enforces uniqueness (throws ArgumentException with the duplicate key).
• ToLookup is an immutable one-to-many map; indexing a missing key returns an empty sequence (no exception).
• GroupBy is deferred; each enumeration regroups.

3. Single semantics in SQL

EF Core translates SingleOrDefault to SELECT TOP(2) and throws if two rows come back. That makes it slightly more expensive than FirstOrDefault (TOP(1)), but it's the right choice for unique lookups where duplicates mean corrupted data. For primary keys, FindAsync(id) also checks the change tracker first (no query if already tracked).

4. Count() vs Count vs Any()

list.Count            // property, O(1)
list.Count()          // extension, detects ICollection<T>, O(1)
query.Count()         // enumerates or COUNT(*) in SQL
query.Any()           // stops at first element / EXISTS

CA1827/CA1829/CA1860 analysers suggest Any(), Count property, and Length/Count comparisons instead of Count().

5. Aggregates on empty sequences

• Sum() of an empty sequence is 0.
• Min()/Max()/Average() on an empty sequence of non-nullable value types throw InvalidOperationException; on nullable types they return null. In EF Core, Max on an empty set translates to SQL returning NULL — if the result type isn't nullable, materialisation throws. Use .Select(x => (decimal?)x.Total).Max() or DefaultIfEmpty.

6. Async materialisation pitfalls

• Mixing sync (ToList) and async (ToListAsync) on IQueryable — sync blocks a thread on I/O.
• Calling ToListAsync on an IEnumerable (not IQueryable) doesn't compile unless it's an IAsyncEnumerable.
• AsAsyncEnumerable() streams but keeps the connection open while iterating — do work quickly or buffer.

Try it yourself

1. Benchmark Count() > 0 vs Any() on a lazy Where over 1 million items.
2. Call ToDictionary on data with duplicate keys and read the exception; fix with DistinctBy or ToLookup.
3. Compare SQL for First, Single and Any with ToQueryString().
4. Call Max() on an empty EF query of decimal and fix it with a nullable projection.

using System.Collections.Frozen;

namespace Linq.Immediate;

public sealed record Product(string Sku, string Category, decimal Price);

public static class Program
{
    public static void Main()
    {
        List<Product> products =
        [
            new("A1", "Tools", 20m),
            new("B2", "Garden", 35m),
            new("C3", "Tools", 15m),
        ];

        // Scalars and quantifiers
        Console.WriteLine(products.Any(p => p.Price > 30m));          // stops at B2
        Console.WriteLine(products.Count(p => p.Category == "Tools")); // full scan
        Console.WriteLine(products.Sum(p => p.Price));

        // Element operators
        var firstTool = products.First(p => p.Category == "Tools");
        var maybeShed = products.FirstOrDefault(p => p.Category == "Sheds"); // null, no exception
        var b2 = products.Single(p => p.Sku == "B2");                    // exactly one expected

        // Conversions chosen by usage
        Product[] snapshot = products.Where(p => p.Price > 10m).ToArray();
        Dictionary<string, Product> bySku = products.ToDictionary(p => p.Sku);      // throws on duplicate SKU
        ILookup<string, Product> byCategory = products.ToLookup(p => p.Category);   // one-to-many
        HashSet<string> categories = products.Select(p => p.Category).ToHashSet();
        FrozenSet<string> blockedSkus = new[] { "Z9", "Y8" }.ToFrozenSet();         // build once, read often

        Console.WriteLine($"{firstTool.Sku} {maybeShed is null} {b2.Price} {snapshot.Length}");
        Console.WriteLine($"{bySku["C3"].Price} {byCategory["Tools"].Count()} {byCategory["Sheds"].Count()}");
        Console.WriteLine($"{categories.Contains("Garden")} {blockedSkus.Contains("A1")}");

        try { products.ToDictionary(p => p.Category); }
        catch (ArgumentException) { Console.WriteLine("Duplicate key: use ToLookup or GroupBy instead"); }
    }
}

// EF Core equivalents (always async in ASP.NET Core):
// var exists = await db.Products.AnyAsync(p => p.Sku == sku, ct);
// var product = await db.Products.SingleOrDefaultAsync(p => p.Sku == sku, ct);
// var list = await db.Products.Where(p => p.Category == c).AsNoTracking().ToListAsync(ct);