EF Core Queries

Entity Framework Core (EF Core) provides powerful and flexible ways to query your data. This section delves into the various techniques and features available for retrieving data from your database using EF Core.

Introduction to LINQ

EF Core leverages Language Integrated Query (LINQ) to allow you to write queries in C# or F# that are translated into SQL by EF Core. This provides a strongly-typed and compile-time checked querying experience.

Basic Queries

The simplest form of a query involves retrieving all entities of a certain type from a DbSet:

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

Filtering Data

You can use LINQ's Where clause to filter the results based on specific criteria:

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

Sorting Data

Use OrderBy or OrderByDescending to sort your results:

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

Selecting Specific Properties

The Select clause allows you to project your query results into anonymous types or specific DTOs (Data Transfer Objects), fetching only the columns you need:

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

Advanced Querying Techniques

Projection with Anonymous Types

As shown above, anonymous types are commonly used for projections. They are convenient for ad-hoc queries where you don't need a dedicated class.

Projection with DTOs

For more complex scenarios or when reusing query results, defining a specific DTO is recommended:

public class ProductSummary
{
    public string ProductName { get; set; }
    public decimal ProductPrice { get; set; }
}

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

Including Related Data (Eager Loading)

To retrieve related entities along with your primary entities, use the Include method:

using (var context = new MyDbContext())
{
    var ordersWithDetails = context.Orders
                                 .Include(o => o.OrderItems) // Include OrderItems
                                 .ThenInclude(oi => oi.Product) // Then include the Product for each OrderItem
                                 .ToList();
}

Filtering Related Data

You can filter based on properties of related entities using Where:

using (var context = new MyDbContext())
{
    var ordersWithSpecificProduct = context.Orders
                                         .Where(o => o.OrderItems.Any(oi => oi.Product.Name == "Laptop"))
                                         .ToList();
}

Query Translation

EF Core's query pipeline translates your LINQ queries into SQL statements that are executed against the database. Understanding this translation is crucial for performance tuning.

Raw SQL Queries

In scenarios where LINQ translation is not sufficient or for performance optimization, you can execute raw SQL queries:

using (var context = new MyDbContext())
{
    var products = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50.0).ToList();
}

Executing Raw SQL Commands

For operations that don't return data (e.g., UPDATE, DELETE, INSERT), use ExecuteSqlRaw:

using (var context = new MyDbContext())
{
    var affectedRows = context.Database.ExecuteSqlRaw("UPDATE Products SET Price = Price * 1.1 WHERE Category = 'Electronics'");
    Console.WriteLine($"Updated {affectedRows} products.");
}

Further Reading

• EF Core Performance Considerations
• Defining EF Core Models