MSDN Documentation

EF Core Queries

This section covers how to retrieve data using Entity Framework Core. EF Core provides powerful and flexible ways to query your data, leveraging LINQ (Language Integrated Query) for expressive and type-safe queries.

Basic Querying with LINQ

The most common way to query data in EF Core is by using LINQ. You can write LINQ queries directly against your DbContext instance.

Retrieving All Entities

To retrieve all instances of an entity type, you can access the corresponding DbSet<TEntity> property on your context.


using (var context = new BloggingContext())
{
    var posts = context.Posts.ToList();
    // 'posts' now contains all Post entities from the database.
}

Filtering Data with LINQ Methods

Use LINQ methods like Where() to filter your results.


using (var context = new BloggingContext())
{
    var featuredPosts = context.Posts
        .Where(p => p.IsFeatured == true)
        .ToList();
    // 'featuredPosts' contains only the posts marked as featured.
}

Projecting Data with LINQ Select

Use Select() to project specific properties or create anonymous types.


using (var context = new BloggingContext())
{
    var postTitlesAndAuthors = context.Posts
        .Select(p => new { p.Title, AuthorName = p.Author.Name })
        .ToList();
    // 'postTitlesAndAuthors' is a list of objects with Title and AuthorName.
}

Querying Related Data

EF Core makes it easy to query data that includes related entities. You can use:

Eager Loading (Include() and ThenInclude())

Eager loading retrieves related entities in the same query. This is useful when you know you'll need the related data.


using (var context = new BloggingContext())
{
    var postsWithAuthorsAndComments = context.Posts
        .Include(p => p.Author)
        .Include(p => p.Comments)
            .ThenInclude(c => c.Author) // If comments have their own authors
        .ToList();
    // 'postsWithAuthorsAndComments' contains posts, their authors, and their comments.
}

Explicit Loading

Explicit loading allows you to load related entities on demand. This is useful for scenarios where you might not always need related data.


using (var context = new BloggingContext())
{
    var post = context.Posts.Find(1);
    await context.Entry(post)
        .Reference(p => p.Author)
        .LoadAsync(); // Load the author
    await context.Entry(post)
        .Collection(p => p.Comments)
        .LoadAsync(); // Load the comments
}

Lazy Loading (Requires configuration)

If configured, lazy loading allows related entities to be loaded automatically when accessed. This can be convenient but may lead to performance issues if not managed carefully.


// Assuming Lazy Loading is enabled in DbContext configuration

using (var context = new BloggingContext())
{
    var post = context.Posts.Find(1);
    var authorName = post.Author.Name; // Author is loaded automatically here if not already
}

Performance Considerations for Queries

Be mindful of the number of entities you retrieve and the complexity of your joins. Use .AsNoTracking() for read-only queries to improve performance.


var posts = context.Posts.AsNoTracking().ToList();

Executing Raw SQL Queries

For scenarios where LINQ is not sufficient or for performance optimization, EF Core allows you to execute raw SQL queries.

Executing a query that returns entities


using (var context = new BloggingContext())
{
    var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs").ToList();
}

Executing a query that returns scalar values


using (var context = new BloggingContext())
{
    var blogCount = context.Set<Blog>().FromSqlRaw("SELECT COUNT(*) FROM Blogs").FirstOrDefault();
    // Note: This example might need adjustment based on actual DbSet setup for scalar.
    // More typically, you'd use context.Database.ExecuteSqlRawAsync for non-query operations.
}

Security Note

When using raw SQL, always parameterize your queries to prevent SQL injection vulnerabilities.


var blogName = "MyBlog";
var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs WHERE Name = {0}", blogName).ToList();

Further Topics