Entity Framework Core Queries

Querying Data with Entity Framework Core

Entity Framework Core (EF Core) provides powerful mechanisms for retrieving data from your database. The primary way to query data is by using LINQ (Language Integrated Query) against your DbContext.

Basic Queries

The simplest way to query is to access a collection of entities exposed by your DbContext, which typically corresponds to a table in your database. EF Core translates these LINQ queries into SQL.

                    
// Assuming 'dbContext' is an instance of your DbContext
var allProducts = await dbContext.Products.ToListAsync();

This code retrieves all records from the 'Products' table.

Filtering Data (Where Clause)

You can filter results using the Where extension method, which translates to a SQL WHERE clause.

                    
var expensiveProducts = await dbContext.Products
                                    .Where(p => p.Price > 100.0m)
                                    .ToListAsync();

Ordering Data (OrderBy, OrderByDescending)

Sort your results using OrderBy and OrderByDescending.

                    
var productsSortedByName = await dbContext.Products
                                    .OrderBy(p => p.Name)
                                    .ToListAsync();

var productsSortedByPriceDesc = await dbContext.Products
                                    .OrderByDescending(p => p.Price)
                                    .ToListAsync();
                    
                

Selecting Specific Properties (Select)

If you only need a subset of columns, use the Select method to project your results into anonymous types or specific DTOs (Data Transfer Objects).

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

// Example with a DTO
public class ProductSummary {
    public string Name { get; set; }
    public decimal Price { get; set; }
}

var productSummaries = await dbContext.Products
                                    .Select(p => new ProductSummary { Name = p.Name, Price = p.Price })
                                    .ToListAsync();
                    
                

Paging Data (Skip, Take)

Implement pagination using Skip and Take to retrieve data in chunks.

                    
int pageNumber = 1;
int pageSize = 10;

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

Including Related Data (Include, ThenInclude)

EF Core supports eager loading of related entities using Include and ThenInclude. This is useful when you need to access navigation properties in a single query.

                    
// Assuming Product has a navigation property 'Category'
var productsWithCategories = await dbContext.Products
                                .Include(p => p.Category)
                                .ToListAsync();

// Assuming Category has a navigation property 'Products' and Product has 'Supplier'
var categoriesWithProductsAndSuppliers = await dbContext.Categories
                                .Include(c => c.Products)
                                    .ThenInclude(p => p.Supplier)
                                .ToListAsync();
                    
                

Performance Tip: Use Include judiciously, as it can lead to fetching more data than necessary. Consider projection or lazy loading (with caution) for better performance in certain scenarios.

Executing Raw SQL

For complex scenarios or performance optimizations, you can execute raw SQL queries.

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

var productCount = await dbContext.Database.ExecuteSqlRawAsync("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = 1");

Query Caching

EF Core has built-in query caching for translated LINQ queries. This can improve performance for frequently executed queries.

Asynchronous Operations

Always use asynchronous methods like ToListAsync(), FirstOrDefaultAsync(), etc., in ASP.NET Core applications to avoid blocking threads and improve scalability.

Exploring these querying techniques will significantly enhance your ability to interact with your data layer effectively.