Querying Data with Entity Framework Core

Entity Framework Core (EF Core) provides powerful and flexible ways to query your data. It leverages LINQ (Language Integrated Query) to allow you to write queries in C# or other .NET languages that are translated into efficient SQL queries executed against your database.

Basic Queries

The most common way to query data is by using the DbSet<TEntity> property on your DbContext. This provides an instance that represents a table or collection of entities.

Example: Retrieving all blogs

using (var context = new BlogContext())
{
    var blogs = context.Blogs.ToList();
    foreach (var blog in blogs)
    {
        Console.WriteLine($"- {blog.Name}");
    }
}

Filtering Data (Where Clause)

You can filter the results of your queries using the Where extension method, which corresponds to the SQL WHERE clause.

Example: Finding blogs with names starting with "Microsoft"

using (var context = new BlogContext())
{
    var msBlogs = context.Blogs
                         .Where(b => b.Name.StartsWith("Microsoft"))
                         .ToList();
    foreach (var blog in msBlogs)
    {
        Console.WriteLine($"- {blog.Name}");
    }
}

Ordering Data (OrderBy/OrderByDescending)

Use OrderBy and OrderByDescending to sort your query results, analogous to the SQL ORDER BY clause.

Example: Retrieving blogs ordered by name

using (var context = new BlogContext())
{
    var sortedBlogs = context.Blogs
                             .OrderBy(b => b.Name)
                             .ToList();
    foreach (var blog in sortedBlogs)
    {
        Console.WriteLine($"- {blog.Name}");
    }
}

Projection (Select Clause)

The Select method allows you to project specific properties from your entities, creating anonymous types or new objects. This is equivalent to the SQL SELECT clause.

Example: Retrieving only the names and URLs of blogs

using (var context = new BlogContext())
{
    var blogNamesAndUrls = context.Blogs
                                  .Select(b => new { b.Name, b.Url })
                                  .ToList();
    foreach (var item in blogNamesAndUrls)
    {
        Console.WriteLine($"- Name: {item.Name}, URL: {item.Url}");
    }
}

Navigating Relationships

EF Core excels at navigating relationships between entities. You can use Include to eagerly load related data.

Example: Retrieving blogs and their associated posts

using (var context = new BlogContext())
{
    var blogsWithPosts = context.Blogs
                                .Include(b => b.Posts) // Eagerly load Posts
                                .ToList();
    foreach (var blog in blogsWithPosts)
    {
        Console.WriteLine($"Blog: {blog.Name}");
        foreach (var post in blog.Posts)
        {
            Console.WriteLine($"  - Post: {post.Title}");
        }
    }
}

Querying Related Data

You can also query based on related data using LINQ's join capabilities or by filtering directly on included collections.

Example: Finding blogs that have at least one published post

using (var context = new BlogContext())
{
    var blogsWithPublishedPosts = context.Blogs
                                         .Where(b => b.Posts.Any(p => p.IsPublished))
                                         .ToList();
    foreach (var blog in blogsWithPublishedPosts)
    {
        Console.WriteLine($"- {blog.Name}");
    }
}

Performance Tip

Be mindful of performance when using Include. Loading too much related data can be inefficient. Consider using projections (Select) or deferred loading strategies when appropriate.

Executing Raw SQL Queries

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

Example: Executing a raw SQL query to get blog names

using (var context = new BlogContext())
{
    var blogNames = context.Blogs
                           .FromSqlRaw("SELECT BlogId, Name, Url FROM Blogs")
                           .ToList();
    foreach (var blog in blogNames)
    {
        Console.WriteLine($"- {blog.Name}");
    }
}