Entity Framework Core

Querying Data with Entity Framework Core

Entity Framework Core (EF Core) provides powerful and flexible ways to query your data using LINQ (Language Integrated Query). LINQ allows you to write queries directly in C# or other .NET languages, which are then translated into SQL by EF Core.

Basic Queries

The most common way to query data is by using the DbSet<TEntity> property on your DbContext instance. This represents a collection of all entities in the context, or that can be queried from the database.

Retrieving All Entities

To retrieve all entities of a specific type:

using (var context = new MyDbContext())
{
    var allProducts = context.Products.ToList();
    // allProducts will contain all Product entities from the database
}
                

Filtering Data with Where

You can filter results using the LINQ Where extension method:

using (var context = new MyDbContext())
{
    var expensiveProducts = context.Products
                                   .Where(p => p.Price > 100)
                                   .ToList();
    // expensiveProducts will contain only Product entities with Price > 100
}
                

Selecting Specific Properties with Select

Use the Select method to project your query results into a new shape, often selecting only specific properties:

using (var context = new MyDbContext())
{
    var productNamesAndPrices = context.Products
                                       .Select(p => new { p.Name, p.Price })
                                       .ToList();
    // productNamesAndPrices is a list of anonymous objects with Name and Price
}
                

Ordering Results with OrderBy and OrderByDescending

Sort your results using OrderBy or OrderByDescending:

using (var context = new MyDbContext())
{
    var sortedProducts = context.Products
                                .OrderBy(p => p.Name) // Ascending by Name
                                .ToList();

    var reverseSortedProducts = context.Products
                                       .OrderByDescending(p => p.Price) // Descending by Price
                                       .ToList();
}
                

Paging with Skip and Take

Implement pagination by using Skip and Take:

using (var context = new MyDbContext())
{
    int pageSize = 10;
    int pageNumber = 2; // Get the second page

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

Querying Related Data

EF Core excels at handling relationships between entities. You can query related data using navigation properties.

Eager Loading with Include

To load related entities in the same query (to avoid N+1 problem):

using (var context = new MyDbContext())
{
    var ordersWithCustomers = context.Orders
                                     .Include(o => o.Customer) // Load the related Customer
                                     .ToList();
    // Each order in ordersWithCustomers will have its Customer populated
}
                

Explicit Loading

Load related data only when you need it:

using (var context = new MyDbContext())
{
    var order = context.Orders.Find(1); // Get a specific order
    if (order != null)
    {
        context.Entry(order)
               .Reference(o => o.Customer) // Specify the reference to load
               .Load(); // Execute the load
        // Now order.Customer is populated
    }
}
                

Lazy Loading (requires configuration)

If lazy loading is enabled, related data is loaded automatically when a navigation property is accessed for the first time. This often requires virtual navigation properties and specific configuration.

Querying with Raw SQL

In scenarios where LINQ might be cumbersome or when you need to leverage database-specific features, EF Core allows you to execute raw SQL queries.

using (var context = new MyDbContext())
{
    var products = context.Products.FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50).ToList();
}
                

using (var context = new MyDbContext())
{
    int productId = 5;
    var product = context.Products.Find(productId); // Find is optimized for primary key lookup

    if (product != null)
    {
        Console.WriteLine($"Found product: {product.Name}");
    }
    else
    {
        Console.WriteLine($"Product with ID {productId} not found.");
    }
}
                    

using (var context = new MyDbContext())
{
    string categoryName = "Electronics";
    int count = context.Products
                       .Where(p => p.Category.Name == categoryName)
                       .Count();

    Console.WriteLine($"Number of products in {categoryName}: {count}");
}
                    

Understanding these querying patterns will allow you to efficiently retrieve and manipulate data within your .NET applications using Entity Framework Core.