Advanced Entity Framework Queries

This section covers more sophisticated querying techniques in Entity Framework, enabling you to retrieve and manipulate data efficiently. Mastering these techniques is crucial for building robust and scalable applications.

Introduction to Advanced Querying

Entity Framework (EF) provides powerful LINQ-to-Entities capabilities that allow you to express complex queries in a readable and maintainable way. Beyond basic filtering and projection, EF supports advanced scenarios like:

Lazy Loading vs. Eager Loading

Entity Framework determines how to load related entities. The two primary strategies are:

Lazy Loading

Related entities are loaded automatically when they are first accessed. This is the default behavior for navigation properties if configured.


using (var context = new MyDbContext())
{
    var customer = context.Customers.Find(1);
    // Orders are loaded here when accessed for the first time
    var orders = customer.Orders.ToList();
}

While convenient, lazy loading can lead to performance issues (the "N+1 problem") if not used carefully, as it can result in many individual database queries.

Eager Loading

Related entities are loaded along with the main entity in a single database query using Include or ThenInclude.


using (var context = new MyDbContext())
{
    var customersWithOrders = context.Customers
        .Include(c => c.Orders) // Eagerly load Orders
        .Where(c => c.Id == 1)
        .ToList();
}

ThenInclude is used to chain includes for multiple levels of relationships.


using (var context = new MyDbContext())
{
    var customersWithOrderDetails = context.Customers
        .Include(c => c.Orders.Select(o => o.OrderDetails)) // Eagerly load Orders and their OrderDetails
        .ToList();
}

Projection into Anonymous Types

When you only need a subset of properties from an entity, projecting into an anonymous type can be more efficient than loading the entire entity.


using (var context = new MyDbContext())
{
    var customerInfo = context.Customers
        .Where(c => c.City == "London")
        .Select(c => new { c.Id, c.Name, OrderCount = c.Orders.Count() })
        .ToList();

    foreach (var info in customerInfo)
    {
        Console.WriteLine($"Customer ID: {info.Id}, Name: {info.Name}, Orders: {info.OrderCount}");
    }
}

Joining Data

LINQ's Join operator can be used to combine data from multiple entities, similar to SQL JOINs.


using (var context = new MyDbContext())
{
    var customerOrders = context.Customers.Join(
        context.Orders, // Inner sequence
        customer => customer.Id, // Outer key selector
        order => order.CustomerId, // Inner key selector
        (customer, order) => new { CustomerName = customer.Name, OrderId = order.Id, OrderDate = order.OrderDate } // Result selector
    ).ToList();

    foreach (var co in customerOrders)
    {
        Console.WriteLine($"Customer: {co.CustomerName}, Order ID: {co.OrderId}, Date: {co.OrderDate}");
    }
}

Often, navigation properties can achieve the same result more idiomatically:


using (var context = new MyDbContext())
{
    var customersAndTheirOrders = context.Customers
        .Where(c => c.Orders.Any()) // Ensure customer has orders
        .Select(c => new { c.Name, Orders = c.Orders.Select(o => o.Id) })
        .ToList();
}

Grouping and Aggregation

GroupBy, Count, Sum, Average, Min, and Max are powerful for data summarization.

Grouping by City


using (var context = new MyDbContext())
{
    var customersByCity = context.Customers
        .GroupBy(c => c.City)
        .Select(g => new { City = g.Key, Count = g.Count() })
        .ToList();

    foreach (var group in customersByCity)
    {
        Console.WriteLine($"City: {group.City}, Customer Count: {group.Count}");
    }
}

Aggregating Order Totals


using (var context = new MyDbContext())
{
    var totalOrderValue = context.Orders.Sum(o => o.TotalAmount);
    Console.WriteLine($"Total Order Value: {totalOrderValue}");

    var averageOrderValue = context.Orders.Average(o => o.TotalAmount);
    Console.WriteLine($"Average Order Value: {averageOrderValue}");
}

Paging and Sorting

OrderBy, OrderByDescending, Skip, and Take are essential for implementing pagination.


using (var context = new MyDbContext())
{
    int pageNumber = 2;
    int pageSize = 10;
    var skipCount = (pageNumber - 1) * pageSize;

    var pagedProducts = context.Products
        .OrderBy(p => p.Name)
        .Skip(skipCount)
        .Take(pageSize)
        .ToList();

    foreach (var product in pagedProducts)
    {
        Console.WriteLine($"Product: {product.Name}");
    }
}

Executing Raw SQL Queries

For scenarios where LINQ-to-Entities is not suitable or for performance-critical operations, you can execute raw SQL queries.

Executing a query that returns entities


using (var context = new MyDbContext())
{
    var customers = context.Customers.FromSqlRaw("SELECT * FROM dbo.Customers WHERE City = {0}", "New York").ToList();
}

Executing a query that returns a scalar value


using (var context = new MyDbContext())
{
    var customerCount = context.Database.ExecuteSqlRaw("SELECT COUNT(*) FROM dbo.Customers");
    Console.WriteLine($"Total Customers: {customerCount}");
}

Be cautious when using raw SQL. It bypasses some EF optimizations and can introduce SQL injection vulnerabilities if not parameterized correctly. Use parameterized queries to mitigate risks.

Further Reading