Entity Framework Core Queries

This document covers how to retrieve data from your database using Entity Framework Core (EF Core) queries. EF Core offers powerful and flexible ways to query your data, from simple lookups to complex projections and filtering.

Introduction to Querying

EF Core uses LINQ (Language Integrated Query) to allow you to write queries in C# or VB.NET that are strongly typed and compiled. This provides a more productive and less error-prone querying experience compared to raw SQL.

LINQ to Entities

EF Core translates your LINQ queries into SQL statements that are executed against the database. The primary way to initiate a query is by accessing the DbSet<TEntity> property on your DbContext instance.

Basic Query Example

Let's assume you have a DbContext named MyDbContext with a DbSet<Product> property:

var context = new MyDbContext();
var products = await context.Products.ToListAsync();

Filtering Data

You can use LINQ's Where operator to filter your results based on specific criteria.

Filtering by Price

var expensiveProducts = await context.Products
    .Where(p => p.Price > 100)
    .ToListAsync();

Sorting Data

Use OrderBy and OrderByDescending to sort your results.

Sorting by Name (Ascending)

var sortedProducts = await context.Products
    .OrderBy(p => p.Name)
    .ToListAsync();

Projection

Projection allows you to select specific properties from your entities, creating new anonymous types or predefined DTOs (Data Transfer Objects).

Projecting Product Names and Prices

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

// Iterate through the results
foreach (var item in productNamesAndPrices)
{
    Console.WriteLine($"{item.Name}: {item.Price:C}");
}

Including Related Data (Navigation Properties)

EF Core supports eager loading related entities using the Include and ThenInclude methods.

Including Orders for a Customer

var customerWithOrders = await context.Customers
    .Include(c => c.Orders)
    .FirstOrDefaultAsync(c => c.Id == 1);

if (customerWithOrders != null)
{
    Console.WriteLine($"Customer: {customerWithOrders.Name}");
    foreach (var order in customerWithOrders.Orders)
    {
        Console.WriteLine($"- Order ID: {order.Id}, Date: {order.OrderDate}");
    }
}

Executing Raw SQL Queries

In some scenarios, you might need to execute raw SQL queries. EF Core provides methods for this as well.

Executing a Raw SQL Query

var productsFromSql = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50)
    .ToListAsync();

Querying with Parameters

It's crucial to use parameterized queries to prevent SQL injection vulnerabilities.

Parameterized Query Example

var productName = "Laptop";
var product = await context.Products
    .FirstOrDefaultAsync(p => p.Name == productName);

Paging Data

Implement paging using Skip and Take operators.

Get the Second Page of Products (10 items per page)

int pageNumber = 2;
int pageSize = 10;

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

Conclusion

Mastering EF Core queries is essential for efficiently retrieving and manipulating data in your applications. Explore the various LINQ operators and EF Core specific methods to build robust and performant data access logic.