Querying Data with EF Core

Entity Framework Core (EF Core) provides powerful and flexible ways to query data from your database. Whether you're retrieving a single record, a collection of entities, or performing complex aggregations, EF Core offers intuitive APIs to accomplish your goals.

Introduction to LINQ

The primary mechanism for querying data in EF Core is Language Integrated Query (LINQ). LINQ allows you to write queries in C# or Visual Basic directly within your application code, leveraging the type safety and compile-time checking of the .NET language.

Basic Queries

Retrieving all entities of a certain type is straightforward:


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

Filtering results using a Where clause is also common:


using (var context = new MyDbContext())
{
    var blogsFromSeattle = context.Blogs
                                .Where(b => b.Location == "Seattle")
                                .ToList();
    // 'blogsFromSeattle' contains only blogs located in Seattle.
}

Projection

You can project query results into anonymous types or specific DTOs (Data Transfer Objects) to select only the data you need:


using (var context = new MyDbContext())
{
    var blogNamesAndRatings = context.Blogs
                                    .Select(b => new { b.Name, b.Rating })
                                    .ToList();
    // 'blogNamesAndRatings' is a list of objects with 'Name' and 'Rating' properties.
}

Ordering, Grouping, and Aggregations

EF Core supports standard LINQ operations like ordering (OrderBy, OrderByDescending), grouping (GroupBy), and aggregations (Count, Sum, Average, Min, Max).


using (var context = new MyDbContext())
{
    var orderedBlogs = context.Blogs
                            .OrderBy(b => b.Name)
                            .ToList();

    var postsPerBlog = context.Blogs
                            .Select(b => new { b.Name, PostCount = b.Posts.Count() })
                            .ToList();
}

Query Translation

When you execute a LINQ query, EF Core translates it into SQL (or the appropriate database query language) and executes it on the database server. This client-side evaluation is minimized for performance. EF Core is smart enough to translate most LINQ operations, but some complex operations might require client-side evaluation or different approaches.

Executing Raw SQL Queries

In scenarios where LINQ doesn't cover your needs or for performance optimization, you can execute raw SQL queries:


using (var context = new MyDbContext())
{
    var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs WHERE Location = {0}", "London");
    // 'blogs' contains results from the raw SQL query.
}

Microsoft Docs