LINQ without surprises: execution, snapshots, ordering, and money

A query is a description of work

LINQ becomes predictable when you distinguish a description of work from the results of that work. Consider a receipt with prices 12, 8, and 25. You want the total of prices at least ten. The query describes a filter followed by an aggregation, and the answer is 37. That small example is useful because every element can be followed by hand: 12 is kept, 8 is rejected, and 25 is kept.

The interesting questions begin when the source changes, the query runs twice, or the source is much larger than the example. Does assigning Where to a variable read every element? Does taking the first result avoid the rest of the source? Does converting to a list create independent copies of the objects? These questions concern execution and ownership, not merely syntax. This guide uses LINQ to Objects over in-memory .NET sequences unless a section explicitly discusses a provider. Keep that scope in mind before transferring an assumption to a database query.

C# example

using System;
using System.Linq;

var prices = new[] { 12m, 8m, 25m };
var total = prices.Where(price => price >= 10m).Sum();
Console.WriteLine(total);

Read the shape of each operator

An operator's input and output types help you understand its role. Where receives a sequence and a predicate and returns a sequence of the same element type. Select receives a sequence and a transformation and may change the element type. Sum receives numeric values, or a selector producing them, and returns a single numeric answer. This sequence-to-sequence or sequence-to-value distinction gives you a first map of the pipeline.

Read the example aloud without translating it into punctuation: start with prices, keep prices meeting the minimum, and add the remaining values. Then ask whether the final result should be a sequence or a scalar. An accidental Select that produces Booleans does something different from Where, which uses Booleans to decide which original elements survive. During practice, inspect the intermediate element type after each step. If a query grows difficult to explain, give an intermediate sequence a meaningful name. A longer program with a visible data flow can be easier to maintain than a single expression whose transformations are unclear.

References: Microsoft Learn: Enumerable.Where

Deferred execution changes when observation happens

Where uses deferred execution. Constructing the query does not evaluate its predicate for every element immediately; enumeration drives the filtering. The source below receives another matching price before the query is enumerated, so the output includes that new price. The variable named selected holds a query over the list, not a saved collection of matches from the moment of assignment.

This behavior is useful when a query should reflect the source at the time it is consumed. It is surprising when the author intended a snapshot. Name that decision explicitly in application code. A report prepared for approval often needs stable results, whereas an interactive view may intentionally refresh from current data. Do not solve the distinction by sprinkling ToList everywhere. First identify the point at which the program should observe its inputs. Also avoid modifying a List while its active enumerator is reading it; deferred execution does not turn ordinary collections into concurrent data structures or guarantee a consistent view while another writer changes them.

C# example

using System;
using System.Collections.Generic;
using System.Linq;

var prices = new List<decimal> { 12m, 8m, 25m };
var selected = prices.Where(price => price >= 10m);
prices.Add(15m);
Console.WriteLine(string.Join(", ", selected));

References: Microsoft Learn: Enumerable.Where

Use an iterator to see demand travel upstream

An iterator makes execution timing visible without requiring a debugger. The source below prints a message immediately before yielding each number. Where tests values as they arrive. Take requests two matching values, and the source can stop once those matches have been supplied. The output therefore shows only the visits needed to produce 2 and 4. This is a useful way to understand streaming behavior.

The logging is instructional instrumentation, rather than a recommendation to put observable effects in ordinary predicates. Remove it after the experiment. Next change Take to request three results and predict the additional source visits. Then change the predicate to one that never matches and explain why the source must be exhausted. The exercise reveals a fundamental limit: a query can stop early only when enough information exists to determine its result. The cost depends on the source and the distribution of matching values, so counting operators alone does not tell you how much work a query performs.

C# example

using System;
using System.Collections.Generic;
using System.Linq;

foreach (var value in Numbers().Where(n => n % 2 == 0).Take(2))
    Console.WriteLine($"Result {value}");

static IEnumerable<int> Numbers()
{
    for (var n = 1; n <= 6; n++)
    {
        Console.WriteLine($"Visit {n}");
        yield return n;
    }
}

References: Microsoft Learn: Deferred execution and lazy evaluation

Deferred does not always mean streaming

A deferred query can still need substantial work before producing its first result. Sorting is the classic example. A normal enumeration of an OrderBy query must account for the whole input to establish order. Grouping also needs to organize source elements before its groups can be consumed. These operations behave differently from a simple filter that can test one element and immediately pass it downstream.

When reviewing a pipeline, mark the places that may buffer data. Ask whether the source is finite, how large it can become, and whether the first result must appear promptly. A perfectly reasonable sort over a hundred receipt lines can be unsuitable for an unbounded event stream. Runtime implementations may optimize particular combinations and terminal operations, so avoid treating an illustrative algorithm as a permanent implementation guarantee. Measure the actual operation that matters. The conceptual distinction remains valuable: returning an enumerable does not promise constant memory, and postponing execution does not mean each result can be produced independently of all the others.

References: Microsoft Learn: Intermediate materialization · Microsoft Learn: Enumerable.OrderBy

Materialize at a deliberate boundary

ToList and ToArray enumerate a source and store the observed elements in a collection. This creates a useful boundary when later work must reuse the same membership and order without rerunning the original query. For a review screen, materialize the rows once, compute the displayed total from those rows, and export those same rows if approval is meant to cover exactly what the person saw.

The boundary costs memory and time proportional to what is stored, and it is not a transaction over an independently changing external system. It also does not generally clone each object. If the elements are references to mutable line-item objects, the new list contains references to those same objects. A later property change can still alter what the rows display. If the application requires a stable value snapshot, project the needed fields into appropriately immutable values and then materialize. That is a different design from merely calling ToList on a sequence of mutable entities. Choose based on what must remain stable: membership, order, values, or all three.

References: Microsoft Learn: Enumerable.ToList

Count repeated enumeration as repeated work

A query variable can be enumerated more than once. Calling Count and then Sum on a filtered sequence can perform the filter twice. That may be inexpensive for a small array, but a custom iterator might read a file, execute a costly calculation, or observe different data on each pass. IEnumerable describes an enumeration capability; it does not promise that every pass is cheap, replayable, or equivalent.

If you need both a count and a total, decide between materializing once and calculating both in a single explicit loop. The right choice depends on whether you also need the individual elements later. A loop is often the clearest option for several related aggregates over a streaming source. Do not force every data-processing task into a dense Aggregate expression simply to keep a LINQ style. For a reproducible experiment, add a visit counter to a custom source, run both terminal operations, and inspect the counter. Then materialize once and compare. This teaches actual execution behavior more directly than assuming a query variable acts like a cache.

Ask for existence with an existence question

If you only need to know whether a matching element exists, Any expresses that question directly. It can stop once a match establishes the answer. Counting every match to compare the count with zero asks for more information than the caller needs. The intent matters even when a particular source or runtime can optimize a count operation, because a future source may have different costs.

The opposite question deserves equal care. All asks whether every element satisfies a condition, and an empty sequence satisfies that universal condition. If a receipt is valid only when it contains at least one line and every line is valid, the emptiness requirement must be stated separately. This is a good example of a query being logically correct while the business requirement is incomplete. Write the expected result for an empty input before choosing the operator. For validation, also decide whether you need only a yes-or-no answer or a complete list of errors; early termination is useful for the former but may prevent useful feedback for the latter.

C# example

using System;
using System.Linq;

var quantities = new[] { 2, 0, 3 };
Console.WriteLine(quantities.Any(q => q == 0));
Console.WriteLine(quantities.All(q => q > 0));

References: Microsoft Learn: Enumerable.Any

Keep predicates and projections free of hidden changes

A predicate that increments a total, sends a message, or updates a database couples business behavior to enumeration timing. A later maintainer may add Any for a quick check, enumerate twice for display, or introduce Take. The side effects then occur a different number of times. The query still looks like data selection, but it now contains an execution protocol that is easy to violate.

Prefer transformations that return values without changing unrelated state. Put required actions in an explicit loop whose ownership and error handling are visible. For example, select the rows that require notification, materialize a bounded batch if that matches the requirement, and then send notifications in a separate operation that records outcomes. This also makes partial failures easier to describe. A query that merely computes notification requests can be tested without sending anything. During code review, inspect lambdas for assignment, mutation, logging that affects behavior, and calls with external effects. A compact expression is not automatically a simple one if its meaning depends on when someone happens to enumerate it.

Order, filter, and limit in the intended order

Operator order can change the answer. Taking three records and then filtering them means finding matches within the first three records. Filtering first and then taking three means finding the first three matches in the source. Neither is universally correct. A search page and a sampling tool may need different behavior even when they use the same operators.

The same care applies to sorting. If a display needs the cheapest five eligible items, eligibility must be established before the final selection, and the ordering must represent price. Add a deterministic tie-breaker when equal prices need a predictable display order. ThenBy extends an existing ordering; a fresh OrderBy establishes a new primary ordering rather than merely adding another key. For pagination over changing data, a stable ordering alone does not solve every consistency problem, but an unspecified order makes pages difficult to reason about from the start. Write a small dataset containing ties, ineligible rows, and fewer results than requested. Those cases reveal the actual meaning of the pipeline.

C# example

using System;
using System.Linq;

var prices = new[] { 18m, 5m, 12m, 8m, 25m };
var cheapestEligible = prices
    .Where(price => price >= 10m)
    .OrderBy(price => price)
    .Take(2)
    .ToArray();
Console.WriteLine(string.Join(", ", cheapestEligible));

References: Microsoft Learn: Enumerable.OrderBy

Choose a single-element operator as a contract

First, FirstOrDefault, Single, and SingleOrDefault communicate different expectations. First is appropriate when the order matters and any later matches are acceptable. Single is appropriate when exactly one match is required. SingleOrDefault permits no match but still rejects multiple matches. Replacing SingleOrDefault with FirstOrDefault can hide duplicate data rather than fixing its cause.

Default values also need interpretation. For a sequence of integers, the default value is zero, which may also be a valid stored value. For a reference type, a missing result is commonly null and needs an explicit branch. Decide whether the application needs to distinguish absence from a legitimate default-valued element. A lookup that requires unique customer identifiers should report duplicated identifiers clearly rather than quietly selecting whichever record appears first. Practice with zero, one, and two matches. These three cases are small enough to inspect directly and are the minimum useful set for checking a uniqueness assumption. The chosen operator should tell the next reader what shape of data the application expects.

References: Microsoft Learn: Enumerable.SingleOrDefault

Make grouping keys match the domain

Grouping creates buckets according to a key and its equality rules. If a report groups product codes, decide whether code ABC and code abc mean the same product. An explicit comparer can express an ordinal case-insensitive policy when that matches the domain. Human names, language-sensitive labels, identifiers, and file paths may require different policies, so a universal lowercasing rule is rarely a complete design.

After grouping, project each group into the values the report needs: its key, line count, and total amount. Avoid carrying entire groups into later layers if the application only needs a summary. Also decide how to handle an empty or missing category. A bucket named Unknown may be a valid reporting policy, while silently merging all missing identifiers can conceal bad input. Use a sample with repeated keys and a sample with keys differing only in case. Explain the intended buckets before running the query. This checks the semantic rule independently of the convenient syntax that implements it.

C# example

using System;
using System.Linq;

var lines = new[]
{
    new Line("books", 12m),
    new Line("tools", 8m),
    new Line("books", 25m)
};
foreach (var group in lines.GroupBy(line => line.Category))
    Console.WriteLine($"{group.Key}: {group.Sum(line => line.Amount)}");

record Line(string Category, decimal Amount);

References: Microsoft Learn: Enumerable.GroupBy

Do not make a dictionary choose your duplicate policy

ToDictionary is useful when the result should have unique keys, and it rejects duplicate keys under its selected comparer. That failure is valuable when duplicates violate an invariant. If duplicates are expected, decide whether to keep the first, keep the last, combine amounts, or preserve all matches with a lookup or grouping. Each option carries a different meaning.

For a product catalogue, duplicate identifiers may indicate corrupt data that should stop publication. For sales events, repeated product identifiers are normal and should probably be aggregated. A hurried workaround that groups and takes the first item can silently discard information. Name the decision and check it with conflicting values. Also consider the cost of building an index compared with the number of lookups. Constructing a dictionary for a single search over a tiny list adds complexity without much benefit, while repeated scans of a large collection can make an index worthwhile. Treat the chosen structure as part of the application's workload design, not a generic LINQ optimization to apply everywhere.

References: Microsoft Learn: Enumerable.ToDictionary

Keep money calculations separate from display formatting

For ordinary currency examples, decimal is a sensible starting type because common decimal fractions can be represented exactly. This does not remove the need for a rounding policy or a currency model. Do not add amounts from different currencies merely because their CLR type is decimal. A report summing euros and dollars without conversion can produce a perfectly precise answer to the wrong question.

Choose whether rounding applies to each line, each tax component, or the final total. The two paths can produce different answers, especially across many fractional calculations. Make the midpoint rule explicit when the requirement requires one. Keep formatted text outside the numeric query; sorting strings such as 9.00 and 12.00 lexicographically does not express numeric ordering. For exported data, choose a documented machine-readable format and culture. For display, choose the person's locale or the report's specified locale. Test the exact boundary values involved in the rule, and include a case whose rounded line total differs from rounding only after aggregation. That example forces the policy into view.

References: Microsoft Learn: Floating-point numeric types

Understand empty and nullable aggregates

An empty input is an ordinary possibility for a filter, even if the original source contains many elements. Before computing an aggregate, ask what no matching rows should mean. Decimal Sum returns zero for an empty sequence. An average over non-nullable numeric values has no answer for an empty sequence and throws. Nullable overloads have their own behavior, so inspect the exact overload rather than assuming every aggregate treats absence the same way.

The reporting policy may require more information than a single amount. A zero total can mean a set of free items, a balanced set of positive and negative entries, or no rows at all. Return or display the count alongside the amount when that distinction matters. Likewise, treating every missing amount as zero may make the arithmetic succeed while hiding incomplete data. Count missing values explicitly if the report needs to expose them. Create three test datasets: no matching entries, matching entries all valued at zero, and entries with a missing amount. Decide the expected result for each before composing the aggregate.

References: Microsoft Learn: Enumerable.Sum · Microsoft Learn: Enumerable.Average

Keep resource lifetimes around enumeration

A deferred sequence may depend on something outside the sequence object: a file reader, a database context, or another resource with a limited lifetime. Returning a query does not extend that lifetime automatically. If a method creates a resource in a using scope and returns work that will read it later, the caller may enumerate only after disposal. The apparent separation between query construction and execution is now a correctness issue.

For a modest report, completing the read and returning a materialized collection is often the simplest ownership model. For a large stream, an iterator can manage the resource across enumeration, but the consumer must still dispose enumeration appropriately and understand that abandoning work has cleanup implications. Avoid hiding file access inside an otherwise innocent-looking property. Name APIs according to whether they return stored data or a stream that performs work. When reviewing the code, trace both the data flow and the resource owner. Ask who opens the resource, who enumerates the sequence, and what happens if enumeration stops early or throws halfway through.

References: Microsoft Learn: using statement

Distinguish LINQ to Objects from a query provider

LINQ is a pattern used by more than one execution engine. IEnumerable-based LINQ to Objects runs .NET functions over local elements. IQueryable-based providers can inspect expression trees and translate supported operations into another language, commonly SQL. A method that works in an in-memory lambda may not be translatable by a provider, and a database can have different null, collation, and ordering semantics from an ordinary list.

Keep the execution boundary visible when working with an actual provider. Materializing a query can move subsequent processing into memory, which changes both cost and behavior. Pulling a large table into a list before filtering can waste network traffic and memory. Conversely, a deliberately small materialized result can be the right boundary for an unsupported local calculation. Verify the provider's generated query and its documented behavior for the operation you use. A playground example over an array is excellent for understanding C# and Enumerable, but it does not validate the database execution plan or prove that an application query is efficient under production data volumes.

References: Microsoft Learn: Write LINQ queries

Measure the workload, then choose the clearest implementation

Performance claims need a workload. Record the size of the source, the fraction of matches, how often the query runs, whether it is enumerated repeatedly, and what result the caller actually consumes. A query that is excellent for ten rows can allocate too much for a million. A dictionary built once can be appropriate for thousands of lookups but unnecessary for one. Runtime versions and source types can also affect implementation optimizations.

Begin with a readable pipeline and a correctness dataset. Then measure the relevant path with representative data and a proper benchmarking approach when the decision matters. If several aggregates require a single pass, a direct loop may communicate the work most clearly. If a transformation is naturally a sequence of independent steps, LINQ may be easier to read and verify. Preserve tests for the empty case, ties, duplicates, mutation boundaries, and numeric policy while changing the implementation. The useful outcome is not a blanket preference for loops or queries. It is a program whose answer, execution timing, ownership, and resource costs are understood by the people maintaining it.

Sources & further reading

Technical references checked against primary documentation. Browser playground examples target the SharpRune .NET 9 profile; production server guidance describes a separate hosting environment.

Illustration created with ImageGen for SharpRune. Article and examples are educational; validate behavior in your target runtime.

Put an idea into practice