Querying with Entity Framework Core

Entity Framework Core (EF Core) provides powerful and flexible ways to query your data. You can use LINQ to query your data context, and EF Core translates these LINQ queries into SQL (or other database-specific query languages).

LINQ to Entities

The primary way to query data in EF Core is by using Language Integrated Query (LINQ). You'll typically use the DbSet<TEntity> property on your derived DbContext instance.

Basic Query

To retrieve all entities of a specific type, you can simply access the corresponding DbSet.

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.ToList();
    // 'blogs' now contains all Blog entities from the database.
}
        

Filtering with Where

Use the Where extension method to filter results based on specific criteria.

using (var context = new BloggingContext())
{
    var featuredBlogs = context.Blogs
                               .Where(b => b.Rating > 4)
                               .ToList();
    // 'featuredBlogs' contains only blogs with a rating greater than 4.
}
        

Projecting Data with Select

The Select method allows you to project only the data you need, creating anonymous types or DTOs.

using (var context = new BloggingContext())
{
    var blogNamesAndUrls = context.Blogs
                                  .Select(b => new { b.Name, b.Url })
                                  .ToList();
    // 'blogNamesAndUrls' contains an anonymous type with Name and Url properties.
}
        

Ordering with OrderBy and OrderByDescending

Sort your query results using OrderBy and OrderByDescending.

using (var context = new BloggingContext())
{
    var sortedBlogs = context.Blogs
                             .OrderBy(b => b.Rating)
                             .ToList();
    // 'sortedBlogs' are ordered by Rating in ascending order.
}
        

Including Related Data

EF Core's lazy loading, eager loading, and explicit loading strategies help you manage related data.

Eager Loading with Include

Use the Include method to load related entities in the same query, avoiding the N+1 query problem.

using (var context = new BloggingContext())
{
    var blogsWithPosts = context.Blogs
                                .Include(b => b.Posts) // Eagerly load the Posts collection
                                .ToList();
    // Each Blog object in 'blogsWithPosts' will have its associated Posts loaded.
}
        

Chaining Include

You can chain Include calls to load multiple levels of related data.

using (var context = new BloggingContext())
{
    var blogsWithAuthorsAndPosts = context.Blogs
                                        .Include(b => b.Author)
                                        .Include(b => b.Posts)
                                        .ThenInclude(p => p.Comments) // Further include related data
                                        .ToList();
}
        

Query Execution

EF Core queries are deferred by default. The query is only executed against the database when you materialize the results (e.g., by calling ToList(), FirstOrDefault(), or iterating over the query).

Deferred Execution

The following example demonstrates deferred execution. The SQL query is not sent to the database until ToList() is called.

using (var context = new BloggingContext())
{
    var query = context.Blogs.Where(b => b.Name.Contains("Example"));

    // The query is built here, but no SQL is executed yet.

    var blogs = query.ToList(); // SQL is executed here.
}
        

Advanced Querying

Paging with Skip and Take

Implement pagination by using Skip and Take.

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

    var pagedBlogs = context.Blogs
                            .OrderBy(b => b.Name)
                            .Skip((pageNumber - 1) * pageSize)
                            .Take(pageSize)
                            .ToList();
}
        

Aggregation with Count, Sum, Average, etc.

Perform aggregate operations directly on your queries.

using (var context = new BloggingContext())
{
    int blogCount = context.Blogs.Count();
    int totalPosts = context.Posts.Sum(p => p.Views);
    double averageRating = context.Blogs.Average(b => b.Rating);
}
        

Executing Raw SQL

For complex scenarios or when LINQ doesn't cover a specific database feature, you can execute raw SQL queries.

using (var context = new BloggingContext())
{
    var blogsFromSql = context.Blogs
                              .FromSqlRaw("SELECT * FROM Blogs WHERE Hostname LIKE '%microsoft.com'")
                              .ToList();

    // For parameterized queries:
    string searchString = "example.com";
    var blogsFromSqlParameterized = context.Blogs
                                          .FromSqlInterpolated($"SELECT * FROM Blogs WHERE Hostname = {searchString}")
                                          .ToList();
}
        

Further Reading

• EF Core Performance
• EF Core Advanced Topics