This documentation covers the essential aspects of querying data using Entity Framework Core (EF Core). EF Core provides a powerful and flexible way to retrieve data from your database using LINQ (Language Integrated Query).

Basic Querying with LINQ

The most common way to query data in EF Core is by using LINQ to Entities. You can write LINQ queries directly against your DbContext and its DbSet properties.


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

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

LINQ Query Syntax vs. Method Syntax

LINQ supports two primary syntax styles:

  • Method Syntax: Uses extension methods like Where(), Select(), OrderBy(). This is generally more common in EF Core.
  • Query Syntax: Uses a syntax that resembles SQL, with keywords like from, where, select.

Example using Query Syntax:


using (var context = new BloggingContext())
{
    var blogs = from b in context.Blogs
                where b.Rating > 4
                orderby b.Url
                select b;

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

Projection

Projection allows you to select specific properties from your entities, creating new anonymous types or DTOs (Data Transfer Objects). This is a crucial optimization technique to avoid fetching unnecessary data.


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

    foreach (var item in blogNamesAndCounts)
    {
        Console.WriteLine($"{item.Name} has {item.PostCount} posts.");
    }
}

Filtering

The Where() method is used to filter data based on specific criteria.


var expensiveProducts = context.Products
                               .Where(p => p.Price > 100)
                               .ToList();

Ordering

Use OrderBy() and OrderByDescending() to sort your query results.


var sortedBlogs = context.Blogs
                         .OrderBy(b => b.CreatedDate)
                         .ToList();

Paging

Implement paging using Skip() and Take() to retrieve a subset of results.


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

Filtering and Ordering Combined

You can chain multiple methods together to create complex queries.


var recentHighRatedPosts = context.Posts
                                  .Where(p => p.Blog.Rating > 4 && p.CreatedDate > DateTime.Now.AddMonths(-3))
                                  .OrderByDescending(p => p.Likes)
                                  .Take(5)
                                  .ToList();
Tip: Always execute your query using methods like ToList(), ToArray(), FirstOrDefault(), or Count() to translate the LINQ query into SQL and send it to the database. If you don't, the query might be executed at an unexpected time or not at all.

Related Topics