LINQ (Language Integrated Query) in C#

LINQ (Language Integrated Query) in C A deep-dive walkthrough of LINQ in C# — covering method syntax vs. query syntax, deferred vs. immediate execution and the bugs that arise from confusing them, the standard query operators grouped by purpose, how LINQ is really just extension methods built on the delegates and generics covered elsewhere in this series, LINQ to Objects vs. LINQ to Entities/SQL and expression trees, performance considerations, and the trade-offs that determine when LINQ is the clearer choice versus a plain loop. Table of Contents Introduction What LINQ Actually Is Method Syntax vs. Query Syntax Deferred Execution: The Single Most Important LINQ Concept Immediate Execution: ToList, ToArray, and the Conversion/Aggregation Operators The Standard Query Operators, Grouped by Purpose IEnumerable and Extension Methods: How LINQ Is Actually Built LINQ to Objects vs. LINQ to Entities/SQL: Expression Trees Multiple Enumeration: A Genuinely Common Bug Performance Considerations Composing Queries: Why Deferred Execution Enables This LINQ and Async: IAsyncEnumerable When LINQ Is the Wrong Tool Common Pitfalls Quick Reference Table Conclusion Introduction LINQ lets you query and transform collections — in-memory objects, database tables, XML, and more — using a consistent, declarative syntax directly inside C#, rather than writing imperative loops by hand or dropping into a separate query language for each different data source. Under the hood, LINQ isn't a separate feature bolted onto the language; it's built almost entirely from mechanisms this series has already covered in depth — generic interfaces ( IEnumerable ), extension methods, and delegates ( Func<T, TResult> , most commonly as lambdas) — composed together into the fluent, chainable style most C# developers know as .Where(...).Select(...) . This guide walks through LINQ's mechanics in depth: the two equivalent syntaxes, deferred execution (arguably the single concept most responsible for LINQ-related bugs when misunderstood), the standard operators grouped by what they actually do, and how LINQ to Objects differs fundamentally from LINQ to Entities/SQL via expression trees. var expensiveProducts = products .Where(p => p.Price > 100) // FILTER — keep only matching elements .OrderBy(p => p.Name) // SORT — order the remaining elements .Select(p => p.Name); // PROJECT — transform each element into something else

// Nothing has actually run yet (Section 3) — this just describes the query. // Iterating expensiveProducts (a foreach, or ToList()) is what actually executes it. 1. What LINQ Actually Is A consistent query syntax across genuinely different data sources LINQ to Objects → querying in-memory collections (List, arrays, etc.) LINQ to Entities → querying a database through Entity Framework LINQ to XML → querying XML documents Others → LINQ to SQL, PLINQ (parallel), and various third-party providers The genuinely distinctive idea behind LINQ is that the same Where , Select , OrderBy syntax works whether you're filtering an in-memory List or filtering rows in a SQL Server table through Entity Framework — the syntax is unified, even though (as Section 7 covers in depth) what actually happens underneath is fundamentally different depending on the data source. Every LINQ query is built from three things you already understand from this series IEnumerable (generics) → the sequence being queried Extension methods → how Where/Select/OrderBy attach themselves to that sequence Func<T, TResult> (delegates) → the lambda you pass in, describing what each operator should do There's no new fundamental language feature here — LINQ is a library, written using the extension method and generic delegate mechanisms this series' Generics and Delegates guides already cover, applied to IEnumerable specifically. Understanding this is what makes LINQ feel like a natural extension of C# rather than a separate thing to memorize, and Section 6 walks through exactly how this composition works. 2. Method Syntax vs. Query Syntax Method syntax: chained extension method calls, the more commonly used form var expensiveProducts = products . Where ( p => p . Price > 100 ) . OrderBy ( p => p . Name ) . Select ( p => p . Name ); This is what most real-world C# code looks like — a chain of method calls, each one an extension method on IEnumerable (Section 6), each taking a lambda describing what it should do. It reads left-to-right in the order operations actually apply, which most developers find intuitive once they're used to it. Query syntax: SQL-like keywords, translated by the compiler into method syntax var expensiveProducts = from p in products where p . Price > 100 orderby p . Name select p . Name ; This is functionally, exactly identical to the method syntax version above — the C# compiler translates from / where / orderby / select directly into the equivalent chain of Where / OrderBy / Select calls at compile time. Query syntax exists as an alternative, more SQL-familiar surface over the exact same underlying mechanism; it isn't a different LINQ, just different C# syntax for producing identical compiled code. Why method syntax dominates in practice, and where query syntax still shines // Query syntax handles a multi-source JOIN more readably than the method-syntax equivalent var results = from order in orders join customer in customers on order . CustomerId equals customer . Id select new { order . Id , customer . Name }; // The equivalent in method syntax is noticeably more awkward to read var results2 = orders . Join ( customers , o => o . CustomerId , c => c . Id , ( o , c ) => new { o . Id , c . Name }); Method syntax covers the full range of LINQ operators (some, like Count() or FirstOrDefault() , have no query-syntax equivalent at all and must be called as methods regardless), while query syntax is limited to a smaller subset of operators but reads more naturally for genuinely SQL-like operations, particularly joins and grouping with multiple clauses — many real codebases use query syntax specifically for a multi-table join and method syntax for everything else, mixing the two deliberately based on which reads more clearly for a given query's shape. 3. Deferred Execution: The Single Most Important LINQ Concept A LINQ query doesn't run when you write it — it runs when you enumerate it var query = products . Where ( p => p . Price > 100 ); // NOTHING has executed yet — this just builds a description Console . WriteLine ( "Query created, but not yet run." ); foreach ( var product in query ) // execution actually happens HERE, one element at a time { Console . WriteLine ( product . Name ); } This is the single most important concept in all of LINQ, and the source of more real-world bugs and confusion than any other aspect of the feature: writing products.Where(...) does not filter anything immediately — it builds a lazy, unexecuted description of the operation, which only actually runs when something iterates over it (a foreach , or a call to ToList() , Count() , First() , and similar). Until that moment, the query is inert. Why this matters: a deferred query re-evaluates against the CURRENT state of its source, every time var numbers = new List < int > { 1 , 2 , 3 }; var evenNumbers = numbers . Where ( n => n % 2 == 0 ); // deferred — not executed yet numbers . Add ( 4 ); // mutating the SOURCE list, AFTER the query was defined but BEFORE it's enumerated foreach ( var n in evenNumbers ) Console . WriteLine ( n ); // prints 2 AND 4 — the query saw the list's CURRENT state This is a genuinely surprising behavior to developers who assume Where immediately "snapshots" the matching elements at the point it's called — it doesn't; it re-evaluates against whatever the source collection actually contains at the moment of enumeration, which means a query defined once can produce different results on different enumerations if the underlying source changes in between, or even between two separate foreach loops over the same query variable. Iterator blocks and yield return : how deferred execution is actually implemented // A simplified illustration of what a deferred LINQ operator looks like underneath public static IEnumerable < T > Where < T >( this IEnumerable < T > source , Func < T , bool > predicate ) { foreach ( var item in source ) { if ( predicate ( item )) yield return item ; // execution PAUSES here, resumes on the NEXT MoveNext() call } } C#'s yield return (an iterator block) is the language feature underneath deferred execution — a method using yield return doesn't run to completion when called; it returns a state machine that produces one element at a time, only as the consumer asks for the next one via MoveNext() . This is why a foreach over a LINQ query genuinely processes elements one at a time, pulling from the source lazily, rather than computing the entire result set upfront. 4. Immediate Execution: ToList, ToArray, and the Conversion/Aggregation Operators Forcing a query to run right now, and capture a fixed snapshot of the result var numbers = new List < int > { 1 , 2 , 3 }; var evenNumbersSnapshot = numbers . Where ( n => n % 2 == 0 ). ToList (); // executes IMMEDIATELY, right here numbers . Add ( 4 ); // this mutation has NO effect on evenNumbersSnapshot — it's already a fixed List Console . WriteLine ( evenNumbersSnapshot . Count ); // still 1 (just the "2" that existed at ToList() time) .ToList() (and .ToArray() , .ToDictionary() , .ToHashSet() ) forces the deferred query to actually execute right then, materializing a real, independent, in-memory collection — unlike Section 3's deferred version, this snapshot is completely disconnected from any later changes to the source. Aggregation operators are also immediate — they have to be, by their nature int count = products . Where ( p => p . Price > 100 ). Count (); // must enumerate everything to count it — immediate decimal total = products . Sum ( p => p . Price ); // must visit every element to sum them — immediate Product cheapest = products . OrderBy ( p => p . Price ). FirstOrDefault (); // must find the actual first result — immediate Operators that produce a single, final value rather than another sequence ( Count() , Sum() , Average() , Max() , Min() , First() , FirstOrDefault() , Any() , All() ) are inherently immediate — there's no meaningful way to "defer" producing a single number or a single element, since producing it requires actually running the query to at least some extent. Choosing between deferred and immediate deliberately Deferred is appropriate when: the query will be enumerated once, shortly after being defined, and the source isn't expected to change in between — or when composing a larger query (Section 10) from smaller pieces. Immediate (ToList/ToArray) is appropriate when: you need a stable snapshot independent of later source mutations, or when the SAME query result will be enumerated multiple times (Section 8 covers why re-enumerating a deferred query repeatedly is often a real, avoidable performance cost). This is a genuine, deliberate design decision worth making explicitly rather than defaulting blindly to one or the other — Section 8 and Section 9 cover the concrete costs of getting this choice wrong in either direction. 5. The Standard Query Operators, Grouped by Purpose Filtering: keep only elements matching a condition var adults = people . Where ( p => p . Age >= 18 ); var firstAdult = people . First ( p => p . Age >= 18 ); // throws if none match var firstAdultOrNull = people . FirstOrDefault ( p => p . Age >= 18 ); // returns default(T) if none match Where is the workhorse filtering operator; First / FirstOrDefault combine filtering with taking exactly one result, differing in how they handle the "nothing matched" case — First throws an exception, FirstOrDefault returns default (per this series' Generics guide's Section 10 discussion of exactly what that means for a given T ). Projection: transform each element into something else var names = people . Select ( p => p . Name ); // one-to-one transformation var allPets = people . SelectMany ( p => p . Pets ); // flattens a collection-of-collections into one sequence var summaries = people . Select (( p , index ) => " { index } : { p . Name } " ); // the overload exposing each element's index Select is a one-to-one transformation (each input element produces exactly one output element); SelectMany is specifically for flattening — when each input element itself produces a sequence , SelectMany concatenates all of those sequences into one flat result, rather than producing a sequence-of-sequences the way Select would. Ordering var sorted = products . OrderBy ( p => p . Price ); // ascending var sortedDesc = products . OrderByDescending ( p => p . Price ); // descending var multiSort = products . OrderBy ( p => p . Category ). ThenBy ( p => p . Price ); // secondary sort key ThenBy / ThenByDescending chain onto an existing OrderBy to add secondary (and further) sort keys — worth knowing that calling OrderBy twice in a row does not achieve this; the second OrderBy call would simply re-sort everything by its own key, discarding the first sort entirely, which is exactly why ThenBy exists as a distinct operator rather than OrderBy being chainable directly. Grouping var byCategory = products . GroupBy ( p => p . Category ); foreach ( var group in byCategory ) { Console . WriteLine ( " { group . Key } : { group . Count ()} products" ); // group.Key is the grouping value foreach ( var product in group ) Console . WriteLine ( $" { product . Name } " ); // each group IS itself an IEnumerable } GroupBy produces a sequence of groups, where each group is both a key ( group.Key , the value the elements were grouped by) and, itself, an IEnumerable of the elements sharing that key — this is the LINQ equivalent of a SQL GROUP BY , and it's a genuinely common operator once querying anything beyond a flat, single-level filter/sort. Aggregation int total = products . Count (); decimal sum = products . Sum ( p => p . Price ); decimal average = products . Average ( p => p . Price ); Product mostExpensive = products . MaxBy ( p => p . Price ); // (C# 10+) — the ELEMENT with the max value, not just the value decimal customAggregate = products . Aggregate ( 0m , ( runningTotal , p ) => runningTotal + p . Price * 1.1m ); // fully custom Aggregate is the most general-purpose of these — it's a fold/reduce operation, taking a starting value (called the "seed") and a function combining the running result with each element in turn, which lets you express essentially any aggregation the more specific operators ( Sum , Average , Max ) don't directly provide. Set operations var union = listA . Union ( listB ); // all UNIQUE elements from either list var intersection = listA . Intersect ( listB ); // only elements present in BOTH var difference = listA . Except ( listB ); // elements in listA that are NOT in listB var distinct = products . Distinct (); // removes duplicates from a single sequence These treat sequences as mathematical sets — worth knowing they rely on the elements' equality comparison (per this series' OOP guide's discussion of value vs. reference equality) to determine what counts as "the same" element, which matters especially for Distinct() and Union() on custom reference types that haven't overridden Equals / GetHashCode . Partitioning and quantifying var firstThree = products . Take ( 3 ); var skipFirstThree = products . Skip ( 3 ); var page2 = products . Skip ( 10 ). Take ( 10 ); // a common pagination pattern: skip the first page, take the second bool anyExpensive = products . Any ( p => p . Price > 1000 ); // TRUE if AT LEAST ONE matches bool allInStock = products . All ( p => p . InStock ); // TRUE only if EVERY element matches Skip / Take are the standard building blocks for pagination; Any / All are quantifier operators answering "does at least one match" or "do all match" without needing to manually loop and check. 6. IEnumerable and Extension Methods: How LINQ Is Actually Built Every LINQ operator is an extension method on IEnumerable public static class Enumerable // this IS the real, actual class in System.Linq { public static IEnumerable < T > Where < T >( this IEnumerable < T > source , Func < T , bool > predicate ) { foreach ( var item in source ) if ( predicate ( item )) yield return item ; } } This is, in simplified form, genuinely what Where looks like inside .NET's own System.Linq namespace — an extension method (the this IEnumerable source parameter is what makes it callable as products.Where(...) rather than Enumerable.Where(products, ...) ), taking a Func<T, bool> delegate (per this series' Delegates guide) as its filtering logic, and using yield return (Section 3) to implement deferred execution. This is why any custom type implementing IEnumerable gets the ENTIRE LINQ operator set for free public class ProductCatalog : IEnumerable < Product > { private readonly List < Product > _products = new (); public IEnumerator < Product > GetEnumerator () => _products . GetEnumerator (); IEnumerator IEnumerable . GetEnumerator () => GetEnumerator (); } var catalog = new ProductCatalog (); var expensive = catalog . Where ( p => p . Price > 100 ); // works — ProductCatalog gets ALL of LINQ, automatically Because Where , Select , and every other standard operator are extension methods defined generically on IEnumerable , any type that implements that one interface — even a completely custom collection type you wrote yourself — automatically gains the full range of LINQ operators, with zero additional code, exactly the kind of broad, automatic reuse this series' Generics guide's Section 1 identifies as generics' core value proposition, here applied at the scale of an entire library's worth of operators. 7. LINQ to Objects vs. LINQ to Entities/SQL: Expression Trees LINQ to Objects: the lambda is compiled to ordinary, executable code var expensiveProducts = products . Where ( p => p . Price > 100 ); // products is a List, IN MEMORY // The lambda p => p.Price > 100 is compiled to a real Func<Product, bool> DELEGATE — // ordinary, JIT-compiled machine code that executes directly against each in-memory Product object. For IEnumerable -based LINQ (querying in-memory collections), the lambda you write is compiled exactly as this series' Delegates guide describes — into a real, executable delegate that runs directly against each element in memory, one at a time, per Section 3's deferred iteration. LINQ to Entities: the SAME lambda syntax, but compiled to a data structure describing the lambda, not executable code IQueryable < Product > query = dbContext . Products . Where ( p => p . Price > 100 ); // dbContext.Products is IQueryable // The SAME-LOOKING lambda p => p.Price > 100 is here compiled to an EXPRESSION TREE — // a data structure describing "compare a property access to a constant," which Entity Framework // then TRANSLATES into an actual SQL WHERE clause, executed on the database server, not in .NET at all. This is the crucial, easy-to-miss distinction: IQueryable (as opposed to IEnumerable ) causes the same lambda syntax to be compiled into an expression tree — a data structure representing the lambda's logic as data (an object graph describing "this is a property access," "this is a greater-than comparison," "this is a constant 100") rather than as executable code. A LINQ provider like Entity Framework walks that expression tree and translates it into the target query language (SQL, in EF's case), meaning the actual filtering happens on the database server, not by pulling every row into .NET memory first and filtering there. Why this distinction matters practically: not every C# expression can be translated // ❌ This throws at RUNTIME (or, in some providers, si