LINQ

Language Integrated Query: one set of methods for filtering, shaping and grouping data, in memory or translated to SQL by EF Core.

Where, Select and OrderBy

Also called LINQ, method syntax, query syntax

What: LINQ methods chain together: Where filters, Select shapes each item into something new, OrderBy and ThenBy sort, Take and Skip page.
Why: The same chain works on a List in memory and on a DbSet, where EF Core turns it into SQL, so you learn it once.

var top = orders
    .Where(o => o.Status == "Paid" && o.Total > 1000m)
    .OrderByDescending(o => o.Total)
    .Select(o => new { o.Id, o.Customer, o.Total })
    .Take(10)
    .ToList();

Watch out

Calling ToList() first and filtering afterwards, which loads every row before throwing most away.

Deferred execution

Also called lazy evaluation, multiple enumeration, ToList

What: A LINQ query does nothing when you write it. It runs when you enumerate it: in a foreach, or with ToList, Count, First and similar.
Why: Enumerating the same query twice runs it twice; against a database that is two round trips.

var paid = orders.Where(o => o.IsPaid);  // nothing ran yet
var count = paid.Count();   // runs the query
var list = paid.ToList();   // runs it again

var once = orders.Where(o => o.IsPaid).ToList();

Watch out

Returning an IEnumerable from a method over a DbContext that is disposed before the caller enumerates it.

GroupBy and aggregates

Also called Sum, Count, Average, GroupBy

What: GroupBy buckets items by a key; Sum, Count, Average, Min and Max reduce a sequence or each group to one value.
Why: Reports, dashboards and totals are grouping and aggregation, the most common work in banking and back-office systems.

var byBranch = transactions
    .GroupBy(t => t.BranchCode)
    .Select(g => new
    {
        Branch = g.Key,
        Count = g.Count(),
        Total = g.Sum(t => t.Amount),
    })
    .OrderByDescending(x => x.Total);

Watch out

A GroupBy EF Core cannot translate to SQL fails at runtime; check the generated SQL in the logs.

First, Single and the OrDefault versions

Also called FirstOrDefault, SingleOrDefault, Any

What: First takes the first match and throws if there is none. Single throws if there is not exactly one. The OrDefault versions return null or default instead of throwing on none.
Why: Picking the right one documents intent: Single for a unique email, FirstOrDefault for the latest login, Any for an existence check.

var user = await db.Users
    .SingleOrDefaultAsync(u => u.Email == email, ct);
if (user is null) return Results.NotFound();

bool taken = await db.Users.AnyAsync(u => u.Email == email);

Watch out

Using Count() > 0 to check existence; Any() stops at the first match.

IEnumerable vs IQueryable

Also called IQueryable, expression tree, AsEnumerable

What: IEnumerable runs LINQ in memory with C# delegates. IQueryable builds an expression tree that a provider such as EF Core translates to SQL.
Why: Filtering on an IQueryable happens in the database. Once a query becomes IEnumerable, every later step runs in your app over whatever was loaded.

IQueryable<Order> q = db.Orders;
q = q.Where(o => o.Total > 1000m);   // becomes SQL WHERE

IEnumerable<Order> e = db.Orders.AsEnumerable();
e = e.Where(o => o.Total > 1000m);   // loads ALL, then filters

Watch out

Accepting IEnumerable<Order> in a helper that filters, which silently pulls the whole table into memory.

Choosing a collection

Also called List, Dictionary, HashSet, Queue

What: List<T> keeps order and allows duplicates. Dictionary<TKey, TValue> finds a value by key in about constant time. HashSet<T> answers "is it in there" fast. Queue<T> and Stack<T> give first-in-first-out and last-in-first-out.
Why: The wrong collection turns a lookup into a scan: list.Contains inside a loop over 100,000 items is slow, a HashSet is not.

var byId = customers.ToDictionary(c => c.Id);
if (byId.TryGetValue(id, out var c)) Console.WriteLine(c.Name);

var seen = new HashSet<string>();
if (!seen.Add(txRef)) Console.WriteLine("duplicate");

Watch out

Indexing a Dictionary with dict[key] for a key that may be missing, which throws KeyNotFoundException.

Go deeper