MSDN Documentation

Entity Framework: Querying Data

Entity Framework (EF) provides powerful and flexible ways to query your data. Whether you are using the Code-First, Database-First, or Model-First approach, EF offers intuitive APIs to retrieve data from your database.

LINQ to Entities

The primary mechanism for querying data in Entity Framework is LINQ to Entities. LINQ (Language Integrated Query) allows you to write queries in C# or VB.NET that are translated into SQL and executed against your database. This provides a strongly typed and compile-time checked querying experience.

Basic Queries

To query data, you typically start by accessing a DbSet<TEntity> property on your derived DbContext instance. This property represents a collection of entities of a specific type.

using (var context = new MyDbContext())
{
    // Get all customers
    var allCustomers = context.Customers.ToList();

    // Get a customer by ID
    var customer = context.Customers.Find(1);

    // Filter customers by city
    var customersInLondon = context.Customers
                                .Where(c => c.City == "London")
                                .ToList();
}
            

Filtering and Sorting

LINQ provides standard methods for filtering and sorting data:

• Where(): Filters a sequence of values based on a predicate.
• OrderBy() / OrderByDescending(): Sorts the elements of a sequence in ascending or descending order.
• ThenBy() / ThenByDescending(): Performs a subsequent sort on the elements of a sequence in ascending or descending order.

using (var context = new MyDbContext())
{
    var highValueOrders = context.Orders
                                .Where(o => o.TotalAmount > 1000)
                                .OrderByDescending(o => o.OrderDate)
                                .ToList();
}
            

Projection

You can project your query results into a new shape, selecting only the properties you need. This is often more efficient than retrieving entire entities when you only require a subset of data.

using (var context = new MyDbContext())
{
    var customerNamesAndEmails = context.Customers
                                      .Select(c => new { c.FirstName, c.LastName, c.Email })
                                      .ToList();

    foreach (var item in customerNamesAndEmails)
    {
        Console.WriteLine($"{item.FirstName} {item.LastName} - {item.Email}");
    }
}
            

Navigation Properties and Joins

Entity Framework simplifies querying related data through navigation properties. You can use the Include() method to eagerly load related entities, or use Select() with nested projections to achieve joins.

Eager Loading

Eager loading retrieves related data along with the primary query in a single database round trip.

using (var context = new MyDbContext())
{
    // Load customers and their related orders
    var customersWithOrders = context.Customers
                                   .Include(c => c.Orders)
                                   .ToList();

    foreach (var customer in customersWithOrders)
    {
        Console.WriteLine($"Customer: {customer.FirstName}");
        foreach (var order in customer.Orders)
        {
            Console.WriteLine($"  Order ID: {order.OrderId}, Date: {order.OrderDate}");
        }
    }
}
            

Explicit Loading

Explicit loading allows you to load related entities on demand after the primary query has been executed.

using (var context = new MyDbContext())
{
    var customer = context.Customers.Find(1);
    if (customer != null)
    {
        context.Entry(customer)
               .Collection(c => c.Orders)
               .Load(); // Load all orders for this customer

        context.Entry(customer)
               .Reference(c => c.PrimaryAddress)
               .Load(); // Load the primary address
    }
}
            

Lazy Loading

Lazy loading automatically loads related data when a navigation property is accessed for the first time. This requires virtual navigation properties and can lead to the "N+1 problem" if not used carefully.

Querying Over Related Data

You can also query entities based on the data in their related entities.

using (var context = new MyDbContext())
{
    // Find customers who have placed an order in 2023
    var customersWithRecentOrders = context.Customers
                                         .Where(c => c.Orders.Any(o => o.OrderDate.Year == 2023))
                                         .ToList();
}
            

Paging

To implement paging, you can use Skip() and Take() methods.

using (var context = new MyDbContext())
{
    int pageNumber = 2;
    int pageSize = 10;

    var pagedCustomers = context.Customers
                                .OrderBy(c => c.LastName)
                                .Skip((pageNumber - 1) * pageSize)
                                .Take(pageSize)
                                .ToList();
}
            

Executing Raw SQL Queries

In scenarios where LINQ to Entities is not sufficient or for performance optimization, you can execute raw SQL queries.

using (var context = new MyDbContext())
{
    // Querying for entities
    var products = context.Products.FromSqlRaw("SELECT * FROM dbo.Products WHERE Category = {0}", "Electronics");

    // Executing commands that don't return entities
    context.Database.ExecuteSqlRaw("UPDATE dbo.Customers SET IsActive = 0 WHERE LastLogin < {0}", DateTime.Now.AddYears(-1));
}
            

Performance Considerations

• Projection: Select only the data you need using Select().
• Eager Loading: Use Include() judiciously. Over-eager loading can lead to fetching too much data.
• Lazy Loading: Be aware of the potential N+1 problem.
• Raw SQL: Use raw SQL for complex queries or when LINQ to Entities doesn't translate efficiently.
• AsNoTracking(): For read-only queries, use AsNoTracking() to prevent EF from tracking entities, which improves performance.

using (var context = new MyDbContext())
{
    var readOnlyProducts = context.Products
                                .AsNoTracking()
                                .Where(p => p.Price > 50)
                                .ToList();
}