Filtering, Sorting, and Grouping in Entity Framework

One of the most powerful features of Entity Framework is its integration with LINQ (Language Integrated Query), which allows us to write queries directly in C# in an intuitive and safe manner.

In this article, we will focus on three fundamental operations in LINQ: filtering, sorting, and grouping.

Filtering with Where

Filtering is one of the most common operations in any database query. It allows us to select only those records that meet a specific condition.

In Entity Framework, this is achieved using the Where method.

var results = context.Entities.Where(e => e.Property == value);

For example, suppose we have an entity Product with the properties Id, Name, Price, and Category.

var electronicProducts = context.Products
    .Where(p => p.Category == "Electronics")
    .ToList();

Filtering with multiple conditions

We can also combine multiple conditions using logical operators such as && (AND) and || (OR).

var expensiveElectronicProducts = context.Products
    .Where(p => p.Category == "Electronics" && p.Price > 500)
    .ToList();

For example, if we want to get products from the “Electronics” category with a price greater than 500, we can do:

Sorting with OrderBy and OrderByDescending

Sorting is another common operation that allows us to organize the results of a query in a specific order.

In Entity Framework, this is achieved using the OrderBy and OrderByDescending methods.

var results = context.Entities.OrderBy(e => e.Property);

Continuing with our example of products, if we want to get all products from the “Electronics” category sorted by price from lowest to highest, we can do:

var sortedProducts = context.Products
    .Where(p => p.Category == "Electronics")
    .OrderBy(p => p.Price)
    .ToList();

If we want to sort them from highest to lowest, we simply change OrderBy to OrderByDescending:

var sortedProductsDesc = context.Products
    .Where(p => p.Category == "Electronics")
    .OrderByDescending(p => p.Price)
    .ToList();

Sorting by multiple properties

If we need to sort by more than one property, we can use the ThenBy or ThenByDescending method after an OrderBy or OrderByDescending.

var sortedMultipleProducts = context.Products
    .OrderBy(p => p.Category)
    .ThenBy(p => p.Price)
    .ToList();

Grouping with GroupBy

We can also group elements of a collection. This is useful when we want to perform aggregate operations, such as counting, summing, or averaging values within each group.

For this, in Entity Framework, we have the GroupBy method, which groups the elements of the collection based on the value of the specified property.

var results = context.Entities.GroupBy(e => e.Property);

For example, suppose we want to group products by category and count how many products are in each category. We can do the following:

var productsByCategory = context.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        Category = g.Key,
        Count = g.Count()
    })
    .ToList();

Grouping with aggregate operations

In addition to counting, we can perform other aggregate operations, such as summing or averaging values within each group.

var averagePriceByCategory = context.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        Category = g.Key,
        AveragePrice = g.Average(p => p.Price)
    })
    .ToList();

For example, if we want to calculate the average price of products in each category.

Combining Filtering, Sorting, and Grouping

Of course, we can combine these operations as we wish to obtain the results we need.

var electronicProductsByBrand = context.Products
    .Where(p => p.Category == "Electronics")
    .OrderBy(p => p.Price)
    .GroupBy(p => p.Brand)
    .Select(g => new
    {
        Brand = g.Key,
        Products = g.ToList()
    })
    .ToList();