Meta Description: Learn how to efficiently implement filtering, sorting, and paging in a console application using Entity Framework Core (EF Core). This step-by-step guide uses a simple e-commerce example to show you how to manage large datasets and improve application performance.
Introduction
In any application that deals with data, it is crucial to implement efficient filtering, sorting, and paging mechanisms to present manageable and relevant data to users. In this article, we’ll focus on implementing these features in a console application using Entity Framework Core (EF Core).
We will use a simple e-commerce example, where we query products, orders, and inventory. This example will show how to return only the products that meet specific criteria, how to order them, and how to paginate the results for optimal performance and user experience.
Where to Write These Methods
In your console application, you should place these methods inside the relevant service classes you’ve already set up, such as ProductService, OrderService, or InventoryService.
For each section below, I will indicate where each method should be placed:
-
ProductService: Handles all product-related operations, such as filtering, sorting, and paging products. -
OrderService: Handles queries related to orders. -
InventoryService: Manages inventory-related queries.
These services should interact with the AppDbContext to access the database and retrieve the relevant data.
1. Filtering in EF Core
Method Location: ProductService
Filtering allows you to retrieve only the relevant records from the database. In EF Core, you can use LINQ queries with Where() to filter data based on conditions.
Here’s how you can filter products by category in the ProductService:
public List<Product> GetProductsByCategory(string categoryName)
{
return _context.Products
.Where(p => p.Category.Name == categoryName)
.Include(p => p.Inventory) // Eagerly loading Inventory data
.ToList();
}
In this example, we are filtering the products to show only those that belong to a specified category. The Where clause helps narrow down the results based on the category name.
Usage Example (Inside your Program.cs):
var productService = new ProductService(new AppDbContext());
var electronicsProducts = productService.GetProductsByCategory("Electronics");
foreach (var product in electronicsProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
2. Sorting in EF Core
Method Location: ProductService
Sorting allows you to arrange data in a specific order, such as by price or name. In EF Core, this can be done using the OrderBy() and OrderByDescending() methods.
Here’s how you can sort products by price in ascending order:
public List<Product> GetProductsSortedByPrice()
{
return _context.Products
.OrderBy(p => p.Price)
.ToList();
}
To sort in descending order:
public List<Product> GetProductsSortedByPriceDescending()
{
return _context.Products
.OrderByDescending(p => p.Price)
.ToList();
}
Usage Example:
var sortedProducts = productService.GetProductsSortedByPrice();
foreach (var product in sortedProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
3. Paging in EF Core
Method Location: ProductService
Paging is essential when handling large datasets. Instead of retrieving all records at once, you can fetch a subset of records, making the query more efficient and user-friendly.
In EF Core, paging is implemented using Skip() and Take() methods. Here's how you can retrieve a paged list of products:
public List<Product> GetPagedProducts(int pageNumber, int pageSize)
{
return _context.Products
.OrderBy(p => p.Name) // Ensure consistent ordering
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
}
In this example, Skip() is used to skip records from the previous pages, and Take() limits the results to the page size.
Usage Example:
var pagedProducts = productService.GetPagedProducts(2, 10); // Get page 2 with 10 products
foreach (var product in pagedProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
4. Combining Filtering, Sorting, and Paging
Often, you’ll want to combine filtering, sorting, and paging in a single query. Here’s an example that combines all three:
Method Location: ProductService
public List<Product> GetFilteredSortedPagedProducts(string categoryName, int pageNumber, int pageSize)
{
return _context.Products
.Where(p => p.Category.Name == categoryName) // Filter by category
.OrderBy(p => p.Price) // Sort by price
.Skip((pageNumber - 1) * pageSize) // Apply paging
.Take(pageSize)
.ToList();
}
Usage Example:
class Program
{
static async Task Main(string[] args)
{
//CallProdcut();
//CallInventory();
//callOrder();
// await TestQueryPerformanceAsync();
// Call the new methods for filtering, sorting, and paging
CallFilteredProducts();
CallSortedProducts();
CallPagedProducts();
}
private static void CallFilteredProducts()
{
using (var context = new AppDbContext())
{
var productService = new ProductService(context);
// Retrieve filtered products by category
var filteredProducts = productService.GetProductsByCategory("Electronics");
Console.WriteLine("Filtered Products by Category 'Electronics':");
foreach (var product in filteredProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
}
}
private static void CallSortedProducts()
{
using (var context = new AppDbContext())
{
var productService = new ProductService(context);
// Retrieve sorted products by price (ascending)
var sortedProducts = productService.GetProductsSortedByPrice();
Console.WriteLine("Sorted Products by Price (Ascending):");
foreach (var product in sortedProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
}
}
private static void CallPagedProducts()
{
using (var context = new AppDbContext())
{
var productService = new ProductService(context);
// Retrieve paged products (page 1, 10 items per page)
var pagedProducts = productService.GetPagedProducts(1, 10);
Console.WriteLine("Paged Products (Page 1, 10 products per page):");
foreach (var product in pagedProducts)
{
Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
}
}
}
}
How It Works:
-
CallFilteredProducts():- Retrieves and prints products filtered by the category "Electronics".
-
CallSortedProducts():- Retrieves and prints products sorted by price in ascending order.
-
CallPagedProducts():- Retrieves and prints the first page of products, with 10 products per page.
Each method is called from Main(), and they interact with the ProductService to perform filtering, sorting, and paging.
Conclusion
By implementing filtering, sorting, and paging in your console application using EF Core, you can greatly improve the performance and scalability of your data access layer. With these techniques, you can handle large datasets more effectively and present users with relevant, manageable information.
These methods can be added to your existing service classes, such as ProductService, to ensure your application is organized and follows good practices.
Let me know if you'd like to modify or add anything to this draft!
Top comments (0)