EF Core Querying

Entity Framework Core (EF Core) provides powerful and flexible ways to query your data. Whether you're retrieving simple lists of entities or performing complex aggregations, EF Core's LINQ (Language Integrated Query) support simplifies data retrieval.

Introduction to Querying

The primary mechanism for querying data in EF Core is through the DbSet<TEntity> property on your derived DbContext. Each DbSet<TEntity> represents a collection of entities of a particular type in the database.

To query data, you typically use LINQ methods on a DbSet<TEntity>. EF Core translates these LINQ queries into SQL queries that are executed against the database.

Basic Queries

Retrieving all entities of a specific type is straightforward:


using (var context = new BloggingContext())
{
    var blogs = context.Blogs.ToList();
    // This translates to: SELECT * FROM Blogs
}

You can also filter results using LINQ's Where clause:


using (var context = new BloggingContext())
{
    var featuredBlogs = context.Blogs
                              .Where(b => b.IsFeatured)
                              .ToList();
    // This translates to: SELECT * FROM Blogs WHERE IsFeatured = TRUE
}

Projections

Often, you don't need to retrieve all properties of an entity. Projections, using LINQ's Select clause, allow you to shape the query results into anonymous types or specific DTOs (Data Transfer Objects).


using (var context = new BloggingContext())
{
    var blogNamesAndUrls = context.Blogs
                                 .Select(b => new { b.Name, b.Url })
                                 .ToList();
    // This translates to: SELECT Name, Url FROM Blogs
}

Including Related Data (Eager Loading)

When your entities have relationships, you might need to retrieve related data. EF Core's Include method enables eager loading, which fetches related entities in the same query.


using (var context = new BloggingContext())
{
    var blogsWithPosts = context.Blogs
                               .Include(b => b.Posts)
                               .ToList();
    // This translates to a SQL query with a JOIN to fetch blogs and their associated posts.
}

You can also include multiple levels of related data:


using (var context = new BloggingContext())
{
    var blogsWithAuthorsAndPosts = context.Blogs
                                         .Include(b => b.Posts)
                                         .ThenInclude(p => p.Author)
                                         .ToList();
}

Filtering Related Data

You can filter the included related data using ThenInclude and Where:


using (var context = new BloggingContext())
{
    var blogsWithFeaturedPosts = context.Blogs
                                       .Include(b => b.Posts.Where(p => p.IsFeatured))
                                       .ToList();
}

Other Querying Techniques

EF Core supports a wide range of LINQ operations, including:

OrderBy and OrderByDescending for sorting.
Skip and Take for pagination.
Count, Sum, Average, Min, Max for aggregations.
FirstOrDefault, SingleOrDefault for retrieving a single entity or null.
Grouping data using GroupBy.

Raw SQL Queries

In scenarios where LINQ might not be sufficient or for performance optimizations, EF Core allows you to execute raw SQL queries.


using (var context = new BloggingContext())
{
    var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs WHERE IsFeatured = 1");
    // Note: FromSqlRaw requires mapping to entity types.
}

Key Takeaways

Use DbSet<TEntity> and LINQ for querying.
EF Core translates LINQ to SQL.
Employ Select for projections to fetch only necessary data.
Use Include and ThenInclude for eager loading related entities.
Explore raw SQL queries for advanced or performance-critical scenarios.

Understanding these querying patterns is crucial for efficiently retrieving and manipulating data with EF Core.