Entity Framework Core Querying: Advanced

This document delves into the more sophisticated querying capabilities of Entity Framework Core (EF Core), enabling you to retrieve and manipulate data with precision and efficiency. We will explore techniques such as projections, filtering with complex conditions, sorting, grouping, and the use of LINQ operators that translate effectively to SQL.

Projections

Projections allow you to select specific properties from your entities, creating new anonymous types or strongly-typed DTOs (Data Transfer Objects). This is crucial for performance as it reduces the amount of data fetched from the database.

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

foreach (var product in products)
{
    Console.WriteLine($"{product.Name}: {product.Price:C}");
}
            

Filtering with Complex Conditions

You can combine multiple conditions using logical operators like && (AND) and || (OR) within your Where clauses. EF Core will translate these into the appropriate SQL WHERE clauses.

var expensiveElectronics = context.Products
    .Where(p => p.Category == "Electronics" && p.Price > 500);

var discountedOrNewProducts = context.Products
    .Where(p => p.IsDiscounted || p.IsNewArrival);
            

Using string.Contains and other String Methods

EF Core supports many common string methods, translating them into SQL functions like LIKE.

var productsContainingWord = context.Products
    .Where(p => p.Description.Contains("wireless"));

var productsStartingWith = context.Products
    .Where(p => p.Name.StartsWith("A"));
            

Sorting

EF Core supports ordering results using OrderBy and OrderByDescending. You can also chain multiple sorting criteria.

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

Grouping

The GroupBy operator is powerful for aggregating data. It groups entities based on a specified key and allows you to perform aggregate functions on each group.

var productsByCategory = context.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        CategoryName = g.Key,
        ProductCount = g.Count(),
        AveragePrice = g.Average(p => p.Price)
    });

foreach (var group in productsByCategory)
{
    Console.WriteLine($"Category: {group.CategoryName}, Count: {group.ProductCount}, Avg Price: {group.AveragePrice:C}");
}
            

Paging

Retrieve specific pages of data using Skip and Take. This is essential for implementing features like pagination in your UI.

int pageSize = 10;
int pageNumber = 2; // Get the second page

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

Executing Raw SQL Queries

In scenarios where LINQ is not sufficient or for performance optimization, you can execute raw SQL queries. Use this cautiously to avoid SQL injection vulnerabilities.

var rawProducts = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100);

var productCount = context.Database.ExecuteSqlRaw("UPDATE Products SET Price = Price * 1.05 WHERE Category = 'Electronics'");
            

Security Note: When using FromSqlRaw or ExecuteSqlRaw, always sanitize or parameterize your input to prevent SQL injection attacks. Use FromSqlInterpolated for interpolated strings, which handles parameterization automatically.

Complex Joins and Relationships

EF Core automatically handles relationships defined in your model. You can leverage LINQ's Join or implicit navigation properties to query related data.

// Using navigation properties
var ordersWithCustomerNames = context.Orders
    .Select(o => new { OrderId = o.Id, CustomerName = o.Customer.Name });

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

Advanced LINQ Operators

EF Core translates many other LINQ operators into SQL, including:

• Any(): Checks if any element satisfies a condition.
• All(): Checks if all elements satisfy a condition.
• Count(): Counts elements.
• Sum(), Average(), Min(), Max(): Aggregate functions.
• Distinct(): Removes duplicate rows.

Mastering these advanced querying techniques will significantly enhance your ability to work with data efficiently and effectively using Entity Framework Core.