EF Core Queries - Microsoft Docs

Querying Data with EF Core

This document covers the various ways you can query data using Entity Framework Core (EF Core). EF Core provides a powerful and flexible API for retrieving data from your database.

Introduction to LINQ

EF Core leverages Language Integrated Query (LINQ) to allow you to write queries in C# or other .NET languages. This provides compile-time checking, IntelliSense, and a familiar programming model.

Basic Queries

The most basic way to query data is to use the DbSet<TEntity> property on your DbContext. This returns an IQueryable<TEntity>, which can then be queried using LINQ methods.

csharp
var blogs = context.Blogs
                 .Where(b => b.Url.Contains("microsoft"));

foreach (var blog in blogs)
{
    Console.WriteLine($"Blog URL: {blog.Url}");
}
            

Filtering Data

The Where() extension method is used to filter query results. You can use various conditions to specify which entities to retrieve.

csharp
var specificBlog = context.Blogs
                          .FirstOrDefault(b => b.BlogId == 1);

var recentPosts = context.Posts
                         .Where(p => p.PublishedDate > DateTime.UtcNow.AddDays(-30))
                         .OrderByDescending(p => p.PublishedDate);
            

Ordering Data

Use the OrderBy() and OrderByDescending() methods to sort query results.

csharp
var orderedBlogs = context.Blogs
                          .OrderBy(b => b.Rating)
                          .ThenBy(b => b.Url);
            

Projection

Projection allows you to select specific properties from your entities or create new types to hold the query results. This is often done using Select().

csharp
var blogTitles = context.Blogs.Select(b => b.Url);

var blogInfo = context.Blogs.Select(b => new {
    Title = b.Title,
    Url = b.Url,
    PostCount = b.Posts.Count()
});
            

Including Related Data

EF Core supports loading related entities using navigation properties. The Include() method is used for eager loading.

csharp
var blogsWithPosts = context.Blogs
                            .Include(b => b.Posts)
                            .ToList();

var blogsWithPostsAndAuthors = context.Blogs
                                       .Include(b => b.Posts)
                                            .ThenInclude(p => p.Author)
                                       .ToList();
            

EF Core translates LINQ queries into SQL. Complex LINQ queries might not always translate optimally. It's good practice to inspect the generated SQL using logging.

Lazy Loading

Lazy loading is an optional feature that allows related entities to be loaded automatically when they are first accessed. This requires specific configuration and proxy creation.

Asynchronous Queries

For I/O-bound operations like database queries, it's recommended to use asynchronous methods to avoid blocking threads. EF Core provides asynchronous versions of many query operations, typically ending with Async.

csharp
var blogs = await context.Blogs
                         .Where(b => b.Url.Contains("microsoft"))
                         .ToListAsync();
            

Raw SQL Queries

In cases where LINQ translation is not possible or optimal, you can execute raw SQL queries. EF Core provides methods for this, such as FromSqlRaw and ExecuteSqlRaw.

csharp
var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs WHERE Url LIKE '%microsoft.com%'");

context.Database.ExecuteSqlRaw("UPDATE Blogs SET Rating = 5 WHERE Name = 'My Blog'");
            

When using raw SQL queries, be mindful of SQL injection vulnerabilities. Always parameterize your queries if they include user-supplied input.

Query Tags

You can add descriptive tags to your queries, which can be useful for logging and debugging purposes.

csharp
var blog = context.Blogs
                  .TagWith("Get blog by ID")
                  .FirstOrDefault(b => b.BlogId == 1);
            