How do you perform filtering, sorting, and paging using EF Core?

In Entity Framework Core (EF Core), you can perform filtering, sorting, and paging through LINQ queries. This allows you to effectively retrieve subsets of data from the database while optimizing performance and ensuring efficient data retrieval. Here’s how to implement each of these operations:

1. Filtering

Filtering allows you to retrieve only the data that meets specific criteria. You can use the Where method to filter your query results based on conditions.

Example:

using (var dbContext = new AppDbContext())
{
    // Filter products where the price is greater than 20
    var filteredProducts = dbContext.Products
                                     .Where(p => p.Price > 20)
                                     .ToList();
}

2. Sorting

Sorting allows you to order your query results based on one or more fields. You can use the OrderBy method for ascending order and OrderByDescending for descending order.

Example:

using (var dbContext = new AppDbContext())
{
    // Sort products by price in ascending order
    var sortedProducts = dbContext.Products
                                   .OrderBy(p => p.Price)
                                   .ToList();

    // Sort products by name in descending order
    var sortedProductsDesc = dbContext.Products
                                       .OrderByDescending(p => p.Name)
                                       .ToList();
}

3. Paging

Paging is used to limit the number of records returned in a single query, which is particularly useful for displaying data in chunks (like on a web page). You can use the Skip and Take methods to implement paging.

Example:

using (var dbContext = new AppDbContext())
{
    int pageNumber = 1; // Current page number
    int pageSize = 10;  // Number of records per page

    // Skip the first (pageNumber - 1) * pageSize records and take the next pageSize records
    var pagedProducts = dbContext.Products
                                  .Skip((pageNumber - 1) * pageSize)
                                  .Take(pageSize)
                                  .ToList();
}

Combining Filtering, Sorting, and Paging

You can combine filtering, sorting, and paging in a single query. Here’s an example that incorporates all three:

using (var dbContext = new AppDbContext())
{
    int pageNumber = 1; // Current page number
    int pageSize = 10;  // Number of records per page

    var pagedFilteredSortedProducts = dbContext.Products
                                               .Where(p => p.Price > 20) // Filtering
                                               .OrderBy(p => p.Name)     // Sorting
                                               .Skip((pageNumber - 1) * pageSize) // Paging
                                               .Take(pageSize)           // Take records
                                               .ToList();
}

Summary

In EF Core, you can easily perform filtering, sorting, and paging using LINQ. The Where method is used for filtering data based on conditions, OrderBy and OrderByDescending for sorting, and Skip and Take for implementing paging. By combining these techniques, you can retrieve and manipulate data efficiently, making your applications more responsive and user-friendly.