Querying Data with EF Core

Overview: This document provides a comprehensive guide to querying data using Entity Framework Core (EF Core). Learn how to retrieve, filter, sort, and project data from your database using LINQ and other powerful EF Core features.

Introduction to Querying

Entity Framework Core simplifies data access by allowing you to query your database using Language Integrated Query (LINQ). This approach offers strong typing, compile-time syntax checking, and IntelliSense support.

Basic Queries

The most common way to query data is by accessing the DbSet<TEntity> property on your derived DbContext instance. This property represents a collection of all entities in the context, or that can be queried from the database.

Retrieving All Entities

To retrieve all instances of an entity, you can use the ToList() or ToArray() extension methods.

using (var context = new YourDbContext())
{
    var allProducts = context.Products.ToList();
    // ... use allProducts
}
        

Filtering Data with LINQ WHERE

You can filter results using the LINQ Where() extension method.

using (var context = new YourDbContext())
{
    var expensiveProducts = context.Products
                                 .Where(p => p.Price > 100)
                                 .ToList();
    // ... use expensiveProducts
}
        

Projection

Selecting Specific Properties with LINQ SELECT

Use the LINQ Select() method to project query results into a new shape, typically an anonymous type or a specific DTO (Data Transfer Object).

using (var context = new YourDbContext())
{
    var productNamesAndPrices = context.Products
                                     .Select(p => new { p.Name, p.Price })
                                     .ToList();
    // ... use productNamesAndPrices
}
        

Sorting Data

Sorting with LINQ ORDERBY

You can sort query results using the LINQ OrderBy() and OrderByDescending() extension methods.

using (var context = new YourDbContext())
{
    var sortedProducts = context.Products
                                .OrderBy(p => p.Name)
                                .ToList();

    var reverseSortedProducts = context.Products
                                     .OrderByDescending(p => p.Price)
                                     .ToList();
    // ... use sortedProducts and reverseSortedProducts
}
        

Paging Data

Using LINQ SKIP and TAKE

For implementing pagination, use the LINQ Skip() and Take() methods.

using (var context = new YourDbContext())
{
    int pageNumber = 2;
    int pageSize = 10;
    var pageOfProducts = context.Products
                                .OrderBy(p => p.Name)
                                .Skip((pageNumber - 1) * pageSize)
                                .Take(pageSize)
                                .ToList();
    // ... use pageOfProducts
}
        

Working with Relationships

Including Related Data with LINQ Include

To retrieve related entities, use the Include() method. This is crucial for avoiding the "N+1" problem.

using (var context = new YourDbContext())
{
    var productsWithCategories = context.Products
                                      .Include(p => p.Category) // Assuming Category is a navigation property
                                      .ToList();
    // ... use productsWithCategories.Category
}
        

Explicit Loading

You can also load related data explicitly after the main entity has been loaded.

using (var context = new YourDbContext())
{
    var product = context.Products.Find(1);
    if (product != null)
    {
        context.Entry(product).Reference(p => p.Category).Load();
        // ... use product.Category
    }
}
        

Executing Raw SQL Queries

In scenarios where LINQ might be insufficient or for performance optimization, EF Core allows you to execute raw SQL queries.

using (var context = new YourDbContext())
{
    var productsFromSql = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50).ToList();
    // ... use productsFromSql
}
        

Advanced Querying Techniques

Grouping with LINQ GroupBy

Group entities based on a specified key.

using (var context = new YourDbContext())
{
    var productsByCategory = context.Products
                                  .GroupBy(p => p.Category.Name)
                                  .Select(g => new { CategoryName = g.Key, Count = g.Count() })
                                  .ToList();
    // ... use productsByCategory
}
        

Aggregating Data

Use LINQ aggregate functions like Count(), Sum(), Average(), Min(), and Max().

using (var context = new YourDbContext())
{
    var totalProductCount = context.Products.Count();
    var averagePrice = context.Products.Average(p => p.Price);
    // ...
}
        

Conclusion

EF Core provides a robust and flexible way to query data. By mastering LINQ with EF Core, you can build efficient and maintainable data access layers for your .NET applications.