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 way.

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 a Product entity with the properties Id, Name, Price, and Category.

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

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

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 product example, if we want to get all products from the “Electrónica” 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();

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, Entity Framework provides 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

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

Combining Filtering, Sorting, and Grouping

Of course, we can combine these operations as we wish to get 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();