Skip to content
Tutorials Center
My learning

Topics

Lesson 81 of 182 ยท Delegates, Events and LINQ

LINQ Practice: Reports and Transformations

Practice C# LINQ on a small dataset - top-N, grouped totals, pivot tables, flattening, paging, sliding windows with Zip and Chunk, with runnable worked examples.

Knowing the operators is one thing; reaching for the right combination when a question lands on your desk is another. This lesson is a workbook. We use one small dataset of orders and solve typical reporting tasks with it: top-N lists, totals per group, pivot tables, flattening nested data, paging, running totals and sliding windows. For each task we state the question first, build the answer in small steps, and explain the choices.

The examples assume you know LINQ Basics and the operators from LINQ Operators in Depth. Try to write each answer yourself before reading it; the "Your turn" exercises at the end are meant for that.

The dataset

Every TryIt in this lesson repeats the same data so that it stays self-contained. It is a list of order lines for a small shop: who ordered what, in which month, in what quantity and at which price.

C#
record OrderLine(int Id, string Customer, string Product, string Category, int Month, int Quantity, decimal UnitPrice)
{
    public decimal Total => Quantity * UnitPrice;
}

There are 12 lines with three customers (Ada, Ben, Cleo), four products, two categories and months 1 to 3. The first example prints the table so you can follow the numbers in later examples.

C# The dataset

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

For brevity the remaining examples show the query part and rely on the same OrderLine record and Data class. In the TryIts you will find them repeated at the bottom.

Top-N: the best customers

Question: Who are the two customers who spent the most?

Group by customer, sum, sort descending, take two:

C# Top 2 customers by revenue

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Notes: MaxBy and MinBy find one extreme without a sort. For a top-N list with ties, add a ThenBy so equal totals always come out in the same order; otherwise the result depends on the input order and "top 3" could be ambiguous.

Grouped totals and averages

Question: For each category, how many lines, how many units, what revenue, and what is the average line value?

C# Totals per category

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Computing grand once outside the query avoids re-summing for every group, which would be an accidental O(n squared) pattern (see Deferred Execution).

Pivot: months as columns

Question: Show revenue per customer with one column per month.

A pivot table is a group by two keys. We group by the row key (customer), then for each month compute the sum, defaulting to zero for months without orders:

C# Pivot table customer by month

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The first version is easy to read and fine for small data: it filters the group once per month. The tuple-keyed dictionary is better for big data because each line is visited once. Tuples make good composite keys: they have value equality and a sensible hash code.

Flattening: nested data to rows

Question: Each customer has a list of tags; list every distinct tag with the customers who have it.

SelectMany flattens, then GroupBy inverts the relationship:

C# SelectMany then GroupBy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The second form of SelectMany keeps the outer item with every inner item, so the information is not lost by flattening.

Running totals and percent change

Question: Per month, what was the revenue, the cumulative revenue, and the change from the previous month?

There is no built-in "running total" operator. Aggregate can build one, or a small loop with yield. Here is the readable approach: compute monthly totals, then use Zip against the same list shifted by one:

C# Running totals and month-over-month change

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Zip(xs.Skip(1)) is the standard LINQ idiom for "compare each element with the next one". It pairs element 0 with element 1, element 1 with element 2 and so on, and stops when the shifted copy runs out.

Paging

Question: Show the order lines in pages of 5, sorted by total value; display page 2, and report how many pages exist.

Skip((page - 1) * size).Take(size) is the standard recipe. Always sort before paging so pages are stable:

C# Paging with Skip and Take

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Skip/Take paging re-sorts and re-scans for each page. That is fine for in-memory lists of a few thousand items. Against a database, the provider turns Skip/Take into OFFSET/FETCH, and for very deep pages keyset paging (Where(x => x.Id > lastSeenId).Take(n)) is faster.

Sliding windows with Zip and Chunk

Question: Given daily temperatures, what is the 3-day moving average, and which days are hotter than both neighbours?

A window of three is the sequence zipped with itself shifted by one and two. Zip accepts three sequences at once:

C# Moving average and local peaks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Overlapping windows come from Zip and Skip; non-overlapping blocks come from Chunk. Nothing here modifies temps, and the windows query is enumerated twice, which is cheap on an array but a reason to call ToList() if the source were expensive.

A complete small report

Putting several pieces together, here is a report with a header per category, rows per product, and a footer, written in query syntax with nested groupings:

C# A full report

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Notice how the nested query is lazy inside the anonymous type: Products is enumerated twice (once to print, then again for the subtotal sums). For reports like this one it is cleaner to add .ToList() to the inner query, so each product row is computed once.

Your turn

Try these on the dataset before looking anything up. Each can be done with the operators in this lesson:

  1. Which product sold the most units in total? (GroupBy + MaxBy.)
  2. Which customers ordered in every month? (Group by customer, then Select(l => l.Month).Distinct().Count().)
  3. List customers who have never bought a Lamp. (Use Except on customer names, or All.)
  4. For each month, which customer spent the most? (Group by month, then in each group group by customer and take MaxBy.)
  5. Which pair of consecutive order IDs has the largest jump in total value? (Zip with Skip(1) and MaxBy.)

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How do you pair each element of a list with the next element?

    Zipping a sequence with itself shifted by one (Skip(1)) pairs element i with element i+1 and stops when the shifted copy ends. Chunk(2) would give non-overlapping blocks instead.
  2. 2. Why add ThenBy(l => l.Id) to a query that orders by total and then uses Skip and Take?

    With ties, the relative order of equal totals depends on the input order. A tie-breaker makes the ordering deterministic, so pages neither repeat nor skip items.
  3. 3. For "revenue per customer where the total is above 100", where does the Where(total > 100) go?

    The condition is about the group's total, so it must be applied after computing the totals, like SQL's HAVING. A Where before the grouping would filter individual rows instead.
  4. 4. Which operator flattens a list of customers, each with a list of tags, into one sequence of tags?

    SelectMany projects every customer to its tag list and concatenates the lists into one flat sequence. Select would give a sequence of lists.
  5. 5. What does values.Chunk(3) return for ten values?

    Chunk splits into arrays of at most the given size, so ten values give arrays of 3, 3, 3 and 1. Nothing is dropped.

Summary

  • Break a report into the question, the grouping key, the aggregate and the ordering; most reports are GroupBy + Select + OrderBy.
  • Use MaxBy/MinBy for a single extreme and OrderBy...Take(n) for top-N lists; add ThenBy for deterministic ties.
  • A pivot is a grouping by two keys; a tuple-keyed dictionary visits the data once.
  • SelectMany flattens nested data and, with a result selector, keeps the parent with every child.
  • Running totals come from Aggregate; neighbours and sliding windows come from Zip with Skip; non-overlapping blocks come from Chunk.
  • Page with OrderBy then Skip/Take, and compute shared values (such as a grand total) outside the query.

Next: Iterators and yield