In the context of databases, aggregation functions are used to summarize and calculate values from a data set. They are essential for obtaining general insights and statistics, such as sums, averages, maximums, and minimums. Common examples include functions like SUM, AVG, MAX, and MIN, which help turn raw data into useful information.
In C#, LINQ (Language Integrated Query) supports these same aggregation operations, but integrated directly into the language. With LINQ, you can apply aggregation methods to data collections in an intuitive and efficient way. In this article, we’ll explore these methods with practical examples, demonstrating how to use them to efficiently obtain specific results. If you’d like to learn more about LINQ methods, check out the articles “Three Essential LINQ Methods for .NET Developers” and “Methods for Retrieving a Single Record in LINQ.”
Max
The Max method is used to find the highest value in a data collection. The return type depends on the type of the collection. For example, if the collection contains integers, the result will be an integer; if it’s a collection of objects, the result will be an object. This method is useful in various scenarios, such as finding the highest product price or the maximum value in a list of numbers.
List<int> numbers = new List<int> { 10, 50, 30, 40, 20 };
int maxValue = numbers.Max();
Console.WriteLine($"The highest value is: {maxValue}");
In the example above, the method returns 50, which is the highest number in the list. LINQ’s Max method is equivalent to SQL’s MAX function, used to find the highest value in a table column.
Min
The Min method works the opposite way of Max, finding the smallest value in a collection. This can be useful for determining, for instance, a customer’s first purchase date, the start of a specific period, the lowest product price, etc.
List<DateTime> dates = new List<DateTime>
{
new DateTime(2024, 3, 12),
new DateTime(2024, 4, 21),
new DateTime(2024, 2, 7)
};
var earliestDate = dates.Min();
Console.WriteLine($"The earliest date is: {earliestDate.ToShortDateString()}");
Here, we created a DateTime array and applied the Min method to find the earliest date, which is February 7, 2024. It’s easy to see that this method resembles the SQL MIN function.
MaxBy and MinBy
While Max and Min are useful for finding the maximum or minimum values by evaluating the item as a whole, MaxBy and MinBy allow you to retrieve the complete element that contains the maximum or minimum value based on a specific property. This is particularly helpful when you need the full object that matches the condition, not just the value.
List<Person> people = new List<Person>
{
new Person { Name = "Ana", Age = 25 },
new Person { Name = "Carlos", Age = 30 },
new Person { Name = "Bianca", Age = 28 }
};
Person oldestPerson = people.MaxBy(p => p.Age);
Person youngestPerson = people.MinBy(p => p.Age);
Console.WriteLine($"Oldest person: {oldestPerson.Name}");
Console.WriteLine($"Youngest person: {youngestPerson.Name}");
In the code above, we specified that we wanted to find the oldest and youngest person in the list using the age as a parameter. The oldest is Carlos (30 years old), and the youngest is Ana (25 years old).
Count
The Count method allows you to determine the number of elements in a collection. It can be used in two ways: to count all elements or to count only those that meet a specific condition. The return type is an int, representing the number of items counted.
To count all elements, use Count with no parameters. This is equivalent to SQL’s COUNT(*), which counts all rows in a table.
List<int> numbers = new List<int> { 10, 20, 30, 40, 50 };
int totalNumbers = numbers.Count();
Console.WriteLine($"Total numbers: {totalNumbers}");
In this case, the variable totalNumbers will be 5, the number of items in the list.
To count only elements that meet a specific condition, use a lambda expression as a parameter. This approach is similar to SQL’s COUNT with a WHERE clause.
List<Order> orders = new List<Order>
{
new Order { Id = 1, Status = "Delivered" },
new Order { Id = 2, Status = "Pending" },
new Order { Id = 3, Status = "Delivered" },
new Order { Id = 4, Status = "Canceled" }
};
int deliveredOrders = orders.Count(o => o.Status == "Delivered");
Console.WriteLine($"Number of delivered orders: {deliveredOrders}");
In our example, only two orders are marked as delivered.
Sum
The Sum method is used to calculate the total of values in a collection. Depending on the collection and element types, you can use Sum in two ways: to sum all values directly, or to sum a specific property from the objects. The return type is numeric—decimal, int, or another depending on the elements’ types.
To calculate the sum of all values in a collection:
List<int> values = new List<int> { 10, 20, 30, 40, 50 };
int total = values.Sum();
Console.WriteLine($"Sum of values: {total}");
This will return 150.
To sum a specific property, use a lambda expression:
List<Sale> sales = new List<Sale>
{
new Sale { Value = 1000 },
new Sale { Value = 2000 },
new Sale { Value = 1500 }
};
decimal totalSales = sales.Sum(s => s.Value);
Console.WriteLine($"Total sales: {totalSales}");
In this example, the sum of the Value property will be 4500.
Average
Finally, the Average method is used to calculate the average of values in a collection. It’s especially useful when you need to find the average of a data set, like students’ grades. The return type is double, representing the calculated average.
To calculate the average of all values in a collection:
List<double> grades = new List<double> { 8.3, 9.8, 8.5, 7.4, 7.5 };
double average = grades.Average();
Console.WriteLine($"Average grade: {average}");
The average grade is 8.5.
To calculate the average of a specific property:
List<Sale> sales = new List<Sale>
{
new Sale { TotalValue = 1000 },
new Sale { TotalValue = 2000 },
new Sale { TotalValue = 1500 }
};
double averageSales = sales.Average(s => s.TotalValue);
Console.WriteLine($"Average sales value: {averageSales}");
In this example, the average sale value is 1500.
Conclusion
Aggregation methods in LINQ are essential for performing calculations and summarizing data in collections. With these methods, you can easily perform complex operations in a simple and efficient manner. With practice and exploration, you’ll be able to manipulate and analyze data more effectively in your C# applications.