LINQ Aggregate Operators: Beyond Sum and Count

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

Enter fullscreen mode Exit fullscreen mode

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!

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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();

Enter fullscreen mode Exit fullscreen mode

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(',')
);

Enter fullscreen mode Exit fullscreen mode

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;
        }
    );

Enter fullscreen mode Exit fullscreen mode

Nested Object Building

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

Enter fullscreen mode Exit fullscreen mode

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}");

Enter fullscreen mode Exit fullscreen mode

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();

Enter fullscreen mode Exit fullscreen mode

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();

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

If you materialize first, aggregation happens in memory:

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

Enter fullscreen mode Exit fullscreen mode

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!