Entity Framework Core: Advanced Querying

This document explores advanced querying techniques in Entity Framework Core (EF Core), enabling you to write more complex and efficient queries against your data sources.

1. Introduction to Advanced Querying

EF Core's LINQ provider translates your C# LINQ queries into SQL (or other database-specific queries). While basic querying is straightforward, mastering advanced features allows for better performance, more flexible data retrieval, and more expressive code.

2. Filtering and Projection

2.1 Complex Filtering with LINQ

Combine multiple conditions using logical operators like && (AND) and || (OR) in your Where clauses.

var expensiveProducts = context.Products
    .Where(p => p.Price > 100 && p.Category == "Electronics")
    .ToList();
            

2.2 Selecting Specific Properties (Projection)

Use Select to project only the data you need, reducing the amount of data transferred from the database.

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

3. Sorting and Pagination

3.1 Multiple Sorting Criteria

Chain OrderBy and ThenBy (or OrderByDescending and ThenByDescending) for multi-level sorting.

var sortedProducts = context.Products
    .OrderBy(p => p.Category)
    .ThenByDescending(p => p.Price)
    .ToList();
            

3.2 Implementing Pagination with Skip and Take

Efficiently retrieve data in chunks for features like pagination.

int pageNumber = 2;
int pageSize = 10;
var pagedProducts = context.Products
    .OrderBy(p => p.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();
            

4. Joins and Relationships

4.1 Inner Joins with LINQ

Use the Join method for explicit inner joins between collections. EF Core often handles implicit joins based on navigation properties automatically.

var ordersWithCustomerNames = context.Orders
    .Join(context.Customers,
          order => order.CustomerId,
          customer => customer.Id,
          (order, customer) => new { OrderId = order.Id, CustomerName = customer.Name })
    .ToList();
            

4.2 Group Joins (Left Outer Joins)

While EF Core doesn't have a direct GroupJoin method for left outer joins in LINQ, you can achieve this by structuring your query carefully, often involving DefaultIfEmpty.

// Example: Customers with their orders, showing customers with no orders
var customersWithOrders = context.Customers
    .Select(c => new
    {
        Customer = c,
        Orders = context.Orders.Where(o => o.CustomerId == c.Id).ToList()
    })
    .ToList();
// For true left join, more complex translation might be needed or handled by EF Core's navigation properties.
            

5. Aggregation Functions

EF Core supports common aggregation functions like Count, Sum, Average, Min, and Max.

var totalProducts = context.Products.Count();
var averageProductPrice = context.Products.Average(p => p.Price);
var highestPrice = context.Products.Max(p => p.Price);
            

6. Grouping Data

Use the GroupBy clause to group elements based on a key.

var productsByCategory = context.Products
    .GroupBy(p => p.Category)
    .Select(g => new { Category = g.Key, Count = g.Count() })
    .ToList();
            

7. Raw SQL Queries

In scenarios where LINQ is not expressive enough or for performance optimizations, you can execute raw SQL queries.

var allUsers = context.Users.FromSqlRaw("SELECT * FROM Users").ToList();

var userById = context.Users.FromSqlInterpolated($"SELECT * FROM Users WHERE Id = {userId}").ToList();
            

8. Client-Side vs. Server-Side Evaluation

EF Core tries to translate as much of your LINQ query as possible to SQL. However, some operations (like certain string methods or complex lambda expressions) might be evaluated on the client-side after fetching data from the database, which can be inefficient.

You can use .AsEnumerable() or .ToList() before applying client-side operations to explicitly trigger client-side evaluation.

// Server-side evaluation (preferred)
var productsStartingWithA = context.Products
    .Where(p => p.Name.StartsWith("A")) // Translated to SQL
    .ToList();

// Client-side evaluation (potentially less efficient)
var productsStartingWithB = context.Products
    .AsEnumerable() // Fetch all products, then filter in memory
    .Where(p => p.Name.StartsWith("B"))
    .ToList();
            

9. Query Tags

Assign tags to your queries for easier identification in the database profiler.

var customers = context.Customers
    .Tag("GetCustomerList")
    .Where(c => c.IsActive)
    .ToList();
            

10. Dynamic Querying with Expressions

For highly dynamic queries where filter criteria are determined at runtime, you can build Expression trees programmatically. This is an advanced topic often requiring helper libraries or custom logic.