LINQ Aggregate Operators: Beyond Sum and Count

Everyone knows Count() and Sum(). But LINQ's aggregate family goes deeper — and the general-purpose Aggregate() operator can implement any of them, plus custom reductions you haven't imagined yet.

Let's explore what's beyond the basics.

The Usual Suspects

var products = dbContext.Products;

var count = products.Count();                      // Number of items
var conditionalCount = products.Count(p => p.Price > 100);  // Filtered count

var sum = products.Sum(p => p.Price);              // Total price
var average = products.Average(p => p.Price);     // Mean price
var min = products.Min(p => p.Price);             // Lowest price
var max = products.Max(p => p.Price);             // Highest price

These translate to SQL aggregates — executed in the database, not in memory. Efficient.

The Null Trap

What happens when the sequence is empty?

var emptyProducts = Enumerable.Empty<Product>();

emptyProducts.Count();    // 0 — safe
emptyProducts.Sum(p => p.Price);     // 0 — safe (by design)
emptyProducts.Average(p => p.Price); // InvalidOperationException!
emptyProducts.Min(p => p.Price);     // InvalidOperationException!
emptyProducts.Max(p => p.Price);     // InvalidOperationException!

Sum and Count handle emptiness gracefully. The others throw.

The fix: use nullable overloads:

decimal? avg = emptyProducts.Average(p => (decimal?)p.Price);  // null instead of exception
decimal? min = emptyProducts.Min(p => (decimal?)p.Price);      // null
decimal? max = emptyProducts.Max(p => (decimal?)p.Price);      // null

// Or with DefaultIfEmpty
decimal avg = emptyProducts
    .Select(p => p.Price)
    .DefaultIfEmpty()  // Provides 0 if empty
    .Average();        // Returns 0

Fun fact: This null behavior matches SQL. SELECT AVG(Price) FROM Products WHERE 1=0 returns NULL, not zero. LINQ chose to throw exceptions because returning a misleading default (like 0 for average) could hide bugs. The nullable overloads give you SQL semantics when you want them.

MinBy and MaxBy: The Element, Not the Value

New in .NET 6, these return the element with the min/max value, not the value itself:

// Old way — two passes
var minPrice = products.Min(p => p.Price);
var cheapest = products.First(p => p.Price == minPrice);

// New way — one pass
var cheapest = products.MinBy(p => p.Price);  // Returns the Product, not the price
var mostExpensive = products.MaxBy(p => p.Price);

MinBy/MaxBy are cleaner and more efficient. In-memory, it's one iteration instead of two.

Note: these are LINQ to Objects only — EF Core doesn't translate them to SQL. Use OrderBy().First() for database:

// EF Core equivalent
var cheapest = await dbContext.Products
    .OrderBy(p => p.Price)
    .FirstOrDefaultAsync();

The General-Purpose Aggregate

Aggregate() is the Swiss Army knife. It takes:

1. A seed value
2. A function that combines accumulator + element
3. (Optionally) a result selector

Build any aggregation you want:

// Sum, implemented manually
var sum = numbers.Aggregate(0m, (acc, n) => acc + n);

// Product (multiply all values)
var product = numbers.Aggregate(1, (acc, n) => acc * n);

// String concatenation
var csv = names.Aggregate("", (acc, name) => acc + "," + name).TrimStart(',');

// Better string concat with StringBuilder
var csv = names.Aggregate(
    new StringBuilder(),
    (sb, name) => sb.Append(name).Append(","),
    sb => sb.ToString().TrimEnd(',')
);

Real-World Aggregate Patterns

Running Total

var runningTotals = transactions
    .Aggregate(
        new List<(string Name, decimal Running)>(),
        (list, t) => 
        {
            var lastTotal = list.LastOrDefault().Running;
            list.Add((t.Name, lastTotal + t.Amount));
            return list;
        }
    );

Nested Object Building

var menuTree = menuItems
    .Where(m => m.ParentId == null)
    .Aggregate(
        new List<MenuNode>(),
        (list, item) => 
        {
            list.Add(BuildNode(item, menuItems));
            return list;
        }
    );

Counting by Condition

var counts = products.Aggregate(
    (Active: 0, Inactive: 0),
    (counts, p) => p.IsActive 
        ? (counts.Active + 1, counts.Inactive) 
        : (counts.Active, counts.Inactive + 1)
);

Console.WriteLine($"Active: {counts.Active}, Inactive: {counts.Inactive}");

Aggregate vs GroupBy for Summaries

Need multiple aggregates by group? GroupBy with Select:

var categoryStats = products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    {
        Category = g.Key,
        Count = g.Count(),
        Total = g.Sum(p => p.Price),
        Average = g.Average(p => p.Price),
        Min = g.Min(p => p.Price),
        Max = g.Max(p => p.Price)
    })
    .ToList();

This translates to efficient SQL with GROUP BY and aggregate functions.

LongCount for Big Data

Count() returns int. If you have more than 2.1 billion items (rare, but possible):

long count = dbContext.HugeTable.LongCount();

Database vs Memory Aggregation

When aggregating on IQueryable, EF executes in the database:

// Executed in SQL — efficient
var dbSum = dbContext.Products.Sum(p => p.Price);

If you materialize first, aggregation happens in memory:

// Executed in C# — pulls all data first
var memSum = dbContext.Products.ToList().Sum(p => p.Price);

For large datasets, database aggregation is dramatically faster. Always aggregate before .ToList() when possible.

The Rule

1. Aggregate in database — call Sum/Count on IQueryable, not after ToList()
2. Watch for nulls — use nullable overloads for Min/Max/Average on possibly-empty sequences
3. Need the element? Use MinBy/MaxBy (.NET 6+) or OrderBy().First()
4. Custom reduction? Aggregate() can implement anything
5. Per-group stats? GroupBy + aggregate functions

---

Next time, we'll tackle EF Core's raw SQL escape hatch — FromSqlRaw, ExecuteSqlRaw, and when to break out of LINQ entirely. Hope to see you!