LINQ and Collections

LINQ is how C# developers query anything — in-memory lists, XML, and (through Entity Framework) SQL Server. The API looks identical everywhere, which is exactly why it bites people: the same line of code can run instantly in memory or fire a database query, depending on one interface.

IEnumerable vs IQueryable — the distinction that matters

IEnumerable<T> is a sequence you can iterate. LINQ methods on it (LINQ to Objects) execute as C# code, in memory, over data you already have.

IQueryable<T> is a description of a query — an expression tree. LINQ methods on it don't run anything; they build up the description. Entity Framework translates that description into SQL when you enumerate it (.ToList(), foreach, .First(), .Count()). This is deferred execution, and it's the single most important LINQ concept for the EF work later in this course.

The practical consequence: where you put a Where changes what the database does.

db.Orders.Where(o => o.Status == "Open").ToList();   // SQL: WHERE Status = 'Open'
db.Orders.ToList().Where(o => o.Status == "Open");   // SQL: entire table, filter in memory

Both return the same rows. The second one pulls every order across the network first. Keep composing on IQueryable as long as possible; call .ToList() once, at the end, when the query is fully shaped.

The operators you use daily

• Where — filter. Select — project into a new shape (this is where you map entities to ViewModels, or hand that job to AutoMapper's ProjectTo).
• OrderBy / ThenBy — always sort before paging with Skip/Take; SQL Server gives no stable order without it.
• GroupBy — grouping with aggregates (g.Count(), g.Sum(...)) translates to SQL GROUP BY; grouping and then touching each row does not translate well — watch the generated SQL.
• Join — explicit joins exist, but with EF you'll usually use navigation properties (order.Customer.Name) and let EF generate the join.
• FirstOrDefault / SingleOrDefault — Single* throws if more than one row matches; use it when duplicates would be a data bug you want to hear about.

Pitfall: multiple enumeration

A LINQ query is re-executed every time you enumerate it. This code queries the database twice:

var openOrders = db.Orders.Where(o => o.Status == "Open");
if (openOrders.Any()) ShowCount(openOrders.Count());

For in-memory sequences it means duplicate work; for IQueryable it means duplicate round-trips, and the results can even differ between enumerations. The fix is deliberate materialization: call .ToList() once, store the list, and reuse it. ReSharper's "possible multiple enumeration" warning is worth taking seriously — treat any IEnumerable you receive as single-pass unless you made it a list yourself.