Entity Framework Querying

This section covers how to retrieve data from your database using Entity Framework Core. We'll explore LINQ to Entities, querying with LINQ to Objects, and common querying patterns.

Introduction to Querying

Entity Framework provides powerful ways to query your data using Language Integrated Query (LINQ). LINQ allows you to write type-safe queries directly in your .NET language (like C# or VB.NET) that are translated into SQL queries by Entity Framework.

LINQ to Entities

LINQ to Entities is the primary way to query data with Entity Framework. It enables you to use LINQ syntax to interact with your database context. The EF query provider translates your LINQ queries into SQL statements that are executed against the database.

Basic Queries

Retrieving all entities of a certain type is straightforward:


using (var context = new MyDbContext())
{
    var products = context.Products.ToList();
    foreach (var product in products)
    {
        Console.WriteLine(product.Name);
    }
}

Filtering Data (`Where` clause)

You can filter results using the Where extension method:


using (var context = new MyDbContext())
{
    var expensiveProducts = context.Products
                                 .Where(p => p.Price > 100.00m)
                                 .ToList();
}

Ordering Data (`OrderBy` and `OrderByDescending`)

Sort your results:


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

    var reverseSortedProducts = context.Products
                                       .OrderByDescending(p => p.Price)
                                       .ToList();
}

Selecting Specific Properties (`Select` clause)

Retrieve only the data you need to improve performance:


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

    foreach (var item in productNamesAndPrices)
    {
        Console.WriteLine($"Name: {item.Name}, Price: {item.Price}");
    }
}

Projection with Anonymous Types and DTOs

You can project results into anonymous types or Data Transfer Objects (DTOs).

When projecting into a DTO, ensure that the DTO's properties match the names and types of the selected data.

Paging Results (`Skip` and `Take`)

Implement pagination by skipping a certain number of records and taking a specified count:


using (var context = new MyDbContext())
{
    int pageNumber = 2;
    int pageSize = 10;
    var productsOnPage = context.Products
                                .OrderBy(p => p.ProductId) // Ensure consistent ordering for paging
                                .Skip((pageNumber - 1) * pageSize)
                                .Take(pageSize)
                                .ToList();
}

Querying Related Data

Entity Framework allows you to query data from related entities efficiently.

Eager Loading (`Include`)

Load related data along with the main entity to avoid multiple database queries:


using (var context = new MyDbContext())
{
    var ordersWithCustomers = context.Orders
                                     .Include(o => o.Customer) // Load the Customer navigation property
                                     .ToList();

    foreach (var order in ordersWithCustomers)
    {
        Console.WriteLine($"Order ID: {order.OrderId}, Customer Name: {order.Customer.Name}");
    }
}

Explicit Loading

Load related data on demand after the main entity has been retrieved:


using (var context = new MyDbContext())
{
    var order = context.Orders.Find(1);
    if (order != null)
    {
        context.Entry(order)
               .Reference(o => o.Customer)
               .Load(); // Load the related Customer

        Console.WriteLine($"Order ID: {order.OrderId}, Customer Name: {order.Customer.Name}");
    }
}

Lazy Loading (Requires configuration)

Related data is loaded automatically when a navigation property is accessed. This requires specific configuration and virtual navigation properties.

Be mindful of lazy loading in production environments as it can lead to performance issues if not managed carefully (e.g., the N+1 query problem).

Querying with Raw SQL

In certain scenarios, you might need to execute raw SQL queries. Entity Framework provides methods for this.

Executing Raw SQL Queries

Retrieve data as a list of entities:


using (var context = new MyDbContext())
{
    var productsFromSql = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Category = {0}", "Electronics").ToList();
}

Executing Raw SQL Commands (for non-query operations)

Execute SQL commands that don't return data:


using (var context = new MyDbContext())
{
    var rowsAffected = context.Database.ExecuteSqlRaw("UPDATE Products SET Price = Price * 1.10 WHERE Category = {0}", "Books");
}

Querying Considerations

Performance: Always consider the performance implications of your queries. Use Select to project only necessary data, and leverage Include judiciously.
ToList() vs. Deferred Execution: LINQ queries are generally executed when you iterate over them or call methods like ToList(), FirstOrDefault(), etc.
Change Tracking: By default, Entity Framework tracks changes to retrieved entities. This is important for subsequent update operations.
Database Translation: Understand that not all LINQ operations can be translated directly to SQL. EF Core might execute some parts in-memory, which can be less efficient.