Querying Data with Entity Framework Core

Entity Framework Core (EF Core) provides powerful and flexible ways to query your data. This section covers the fundamental concepts and common patterns for retrieving data from your database.

Introduction to LINQ Queries

EF Core leverages Language Integrated Query (LINQ) to allow you to write queries in C# or Visual Basic, which are then translated into SQL and executed by the database. This provides compile-time type checking and a familiar programming experience.

Basic Querying with LINQ

The most common way to query is by using the DbSet<TEntity> property on your DbContext. You can then apply LINQ operators to filter, sort, and project your data.

using (var context = new BloggingContext())
{
    // Retrieve all blogs
    var allBlogs = context.Blogs.ToList();

    // Find blogs with a specific URL
    var specificBlog = context.Blogs
                              .Where(b => b.Url.Contains("microsoft.com"))
                              .FirstOrDefault();

    // Project blog titles and author names
    var blogTitles = context.Blogs
                            .Select(b => new { b.Title, AuthorName = b.Author.Name })
                            .ToList();
}
            

Filtering Data with Where

The Where extension method is used to filter sequences based on a predicate.

Examples:

• Filtering by a specific property value.
• Using complex conditions with logical operators (&&, ||).
• Filtering based on related entities.

// Find blogs from a specific country
var usBlogs = context.Blogs
                     .Where(b => b.Country == "USA")
                     .ToList();

// Find posts that contain the word "EF Core" and were published recently
var relevantPosts = context.Posts
                           .Where(p => p.Content.Contains("EF Core") && p.PublishedDate > DateTime.Now.AddDays(-30))
                           .ToList();
            

Ordering Data with OrderBy and OrderByDescending

OrderBy and OrderByDescending are used to sort the results of a query.

// Order blogs by name alphabetically
var sortedBlogs = context.Blogs
                         .OrderBy(b => b.Name)
                         .ToList();

// Order posts by publication date, newest first
var recentPosts = context.Posts
                         .OrderByDescending(p => p.PublishedDate)
                         .ToList();
            

Projecting Data with Select

The Select method projects each element of a sequence into a new form. This is useful for selecting specific columns or creating anonymous types.

// Get a list of blog URLs and their associated author names
var blogInfo = context.Blogs
                      .Select(b => new { Url = b.Url, Author = b.Author.Name })
                      .ToList();
            

Including Related Data with Include

By default, EF Core does not load related entities. The Include method allows you to eagerly load related data.

// Load blogs and their associated authors
var blogsWithAuthors = context.Blogs
                              .Include(b => b.Author)
                              .ToList();

// Load blogs, their authors, and their posts
var blogsWithEverything = context.Blogs
                                .Include(b => b.Author)
                                .Include(b => b.Posts)
                                .ToList();
            

Executing Queries

LINQ queries are deferred execution by default. They are only executed when you materialize the results, typically by calling methods like:

• ToList()
• ToArray()
• First(), FirstOrDefault()
• Single(), SingleOrDefault()
• Any()
• Count(), LongCount()
• Sum(), Average(), Min(), Max()

Raw SQL Queries

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

// Execute a raw SQL query
var blogsFromSql = context.Blogs.FromSqlRaw("SELECT * FROM Blogs WHERE Country = {0}", "Canada").ToList();
            

Next Steps

Explore how to save data, manage database schema with migrations, and delve into more advanced querying techniques.