In the context of databases, aggregation functions are used to summarize and calculate values from a dataset. They are essential for obtaining general information and statistics, such as sums, averages, maximums, and minimums. Common examples include functions like “SUM”, “AVG”, “MAX”, and “MIN”, which help transform raw data into useful information.
In C#, LINQ (Language Integrated Query) supports these same aggregation operations, but directly integrated into the language. With LINQ, you can apply aggregation methods to data collections in an intuitive and efficient way. In this article, we will explore these methods with practical examples, demonstrating how to use them to obtain specific results efficiently.
Max
The Max method is used to find the highest value in a data collection. The return type of this method depends on the type of collection it is applied to. For example, if the collection consists of integers, the return will be an integer; if it consists of objects, the return will be an object. This method is useful in various scenarios, such as finding the highest price of a product or the largest 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 returned value will be 50, which is the largest element in the list. The LINQ Max method is equivalent to the MAX function in SQL, which is used to find the highest value in a column of a table.
Min
The Min method works as the opposite of the Max method, finding the smallest value in a collection. This can be useful for determining, for example, the date of a customer’s first purchase, the start of a specific period, the lowest value in a list of products, etc.
List<DateTime> dates = new List<DateTime>
{
new DateTime(2024, 12, 12),
new DateTime(2025, 2, 21),
new DateTime(2024, 2, 7)
};
var firstDate = dates.Min();
Console.WriteLine($"The first date is: {firstDate.ToShortDateString()}");
Here, we create an array of DateTime and then apply the Min method to find the earliest date, which in this case is February 7, 2024. It is easy to see that this method is similar to the MIN function in SQL.
MaxBy e MinBy
While the Max and Min methods are useful for finding maximum and minimum values by evaluating the value of the entire collection item, the MaxBy and MinBy methods allow retrieving the complete element that has the maximum or minimum value in a specific property. This is particularly useful when you want to obtain the full object that meets the criteria rather than just the evaluated value. For example:
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 person with the highest and lowest age in the list using age as the parameter. The person with the highest age is Carlos, at 30 years old, and the youngest is Ana, at 25 years old.
Count
The Count method allows us 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 criterion. The return type of this method is an int, representing the number of counted items.
To count all elements in a collection, you can use the Count method without passing any parameters. This usage is equivalent to the COUNT(*) function in SQL, 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 totalNumbers variable will receive the value 5, which is the number of items in the list.
To count only elements that meet a specific criterion, you can use a lambda expression as a parameter for the Count method. This approach is equivalent to the COUNT function with a WHERE clause in SQL, which counts the number of rows that satisfy a condition.
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 deliveredOrdersCount = orders.Count(o => o.Status == "Delivered");
Console.WriteLine($"Number of delivered orders: {deliveredOrdersCount}");
In our example, only two orders are registered as delivered.
Sum
The Sum method is used to calculate the sum of values in a collection. Depending on the type of collection and the elements it contains, you can use the Sum method in two ways: to sum all values directly or to sum only a specific property of the objects in the list. The return type of this method can be decimal, int, or another numeric type, depending on the type of elements in the collection.
To calculate the sum of all values in a collection, you can use the Sum method directly without passing any parameters. This usage is equivalent to the SUM function in SQL, which calculates the sum of a column in a table.
List<int> values = new List<int> { 10, 20, 30, 40, 50 };
int totalValues = values.Sum();
Console.WriteLine($"The sum of values is: {totalValues}");
In the example above, Sum will return the value 150.
To calculate the sum of a specific property, you can pass a lambda expression as a parameter to the Sum method:
List<Sale> sales = new List<Sale>
{
new Sale { Amount = 1000 },
new Sale { Amount = 2000 },
new Sale { Amount = 1500 }
};
decimal totalSales = sales.Sum(s => s.Amount);
Console.WriteLine($"The total sales amount is: {totalSales}");
In the example above, Sum will return the sum of the Amount property of the Sale objects, resulting in 4500.
Average
Finally, the Average method is used to calculate the average of values in a collection. This method is particularly useful in scenarios where you need to find the average of a dataset, such as the average grades of students in a class. The return type of this method is double, representing the calculated average.
To calculate the average of all values in a collection, you can use the Average method directly without passing parameters. This usage is equivalent to the AVG function in SQL, which calculates the average of a column in a table.
List<double> grades = new List<double> { 8.3, 9.8, 8.5, 7.4, 7.5 };
double average = grades.Average();
Console.WriteLine($"The average grade is: {average}");
The student’s average grade is 8.5.
To calculate the average of a specific property within a collection of objects, you can use a lambda expression as a parameter:
List<Sale> sales = new List<Sale>
{
new Sale { TotalAmount = 1000 },
new Sale { TotalAmount = 2000 },
new Sale { TotalAmount = 1500 }
};
double averageSales = sales.Average(s => s.TotalAmount);
Console.WriteLine($"The average sales amount is: {averageSales}");
In the example above, we calculate the average sales amount, which is 1500.
Conclusion
Aggregation methods in LINQ are essential for performing calculations and obtaining data summaries in collections. With these methods, you can easily carry out complex operations in a simple and efficient way. By practicing and exploring these methods, you will be able to manipulate and analyze data more effectively in your C# applications.