Entity Framework Core Querying

Introduction to EF Core Querying

Entity Framework Core (EF Core) provides a powerful and flexible way to query your data using Language Integrated Query (LINQ). This section dives deep into how to retrieve data efficiently and effectively.

EF Core translates LINQ queries into SQL queries that are executed against your database. This abstraction allows you to work with your domain models directly without needing to write raw SQL in most cases.

LINQ Queries

LINQ (Language Integrated Query) is a fundamental feature of .NET that enables expressive and type-safe querying of data sources. EF Core leverages LINQ to provide a seamless querying experience.

There are two primary syntaxes for writing LINQ queries:

• Method Syntax: Uses extension methods provided by LINQ.
• Query Syntax: Uses a declarative syntax that resembles SQL.

Both syntaxes are compiled into the same underlying expression trees, and EF Core can translate both into efficient SQL.

Querying Data

To query data, you'll typically use the DbSet<TEntity> property on your DbContext.

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

    // Retrieve a single blog by ID
    var blog = context.Blogs.Find(1);

    // Retrieve a single blog asynchronously
    var blogAsync = await context.Blogs.FindAsync(1);
}
                    

The ToList() and Find() methods are examples of methods that execute the query immediately and return the results.

Filtering Data

Use the Where() extension method to filter your results based on specific criteria.

using (var context = new BloggingContext())
{
    // Find blogs with more than 5 posts
    var popularBlogs = context.Blogs
        .Where(b => b.Posts.Count > 5)
        .ToList();

    // Find blogs whose URL contains "microsoft"
    var msBlogs = context.Blogs
        .Where(b => b.Url.Contains("microsoft"))
        .ToList();
}
                    

EF Core is smart enough to translate these LINQ methods into SQL WHERE clauses.

Sorting Data

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

using (var context = new BloggingContext())
{
    // Get blogs ordered by their URL alphabetically
    var sortedBlogs = context.Blogs
        .OrderBy(b => b.Url)
        .ToList();

    // Get posts ordered by their creation date descending
    var recentPosts = context.Posts
        .OrderByDescending(p => p.Date)
        .ToList();
}
                    

You can chain multiple OrderBy calls for secondary sorting criteria.

Projection

Projection allows you to select specific properties or shape the data into a new type using Select().

using (var context = new BloggingContext())
{
    // Select only the blog's URL and Title
    var blogUrlsAndTitles = context.Blogs
        .Select(b => new { b.Url, b.Title })
        .ToList();

    // Select blog title and the count of its posts
    var blogTitleAndPostCount = context.Blogs
        .Select(b => new
        {
            BlogTitle = b.Title,
            PostCount = b.Posts.Count
        })
        .ToList();
}
                    

Projection is a powerful technique for optimizing queries by fetching only the data you need.

Joining Data

Use LINQ's Join() or implicit joins via navigation properties to combine data from related entities.

using (var context = new BloggingContext())
{
    // Join Blogs with Posts where the blog ID matches
    var blogsAndPosts = context.Blogs
        .Join(context.Posts, blog => blog.BlogId, post => post.BlogId, (blog, post) => new { Blog = blog, Post = post })
        .ToList();

    // Using navigation properties (more common and readable)
    var postsWithBlogInfo = context.Posts
        .Select(p => new { p.Title, p.Blog.Url })
        .ToList();
}
                    

EF Core will translate these joins into SQL JOIN clauses.

Change Tracking

By default, EF Core tracks changes made to entities retrieved from the database. This is crucial for the SaveChanges() operation.

using (var context = new BloggingContext())
{
    // Querying with change tracking (default)
    var blogToUpdate = context.Blogs.FirstOrDefault(b => b.BlogId == 1);
    if (blogToUpdate != null)
    {
        blogToUpdate.Url = "https://newurl.com";
        await context.SaveChangesAsync(); // Changes are tracked and saved
    }

    // Querying without change tracking
    var blogReadOnly = context.Blogs.AsNoTracking().FirstOrDefault(b => b.BlogId == 2);
    // Changes to blogReadOnly here won't be saved by SaveChangesAsync
}
                    

Raw SQL Queries

While LINQ is preferred, EF Core allows you to execute raw SQL queries when necessary.

using (var context = new BloggingContext())
{
    // Executing a raw SQL query that returns entities
    var blogsFromSql = context.Blogs.FromSqlRaw("SELECT * FROM Blogs").ToList();

    // Executing a raw SQL query with parameters
    var userFromSql = await context.Users
        .FromSqlRaw("SELECT * FROM Users WHERE UserName = {0}", userName)
        .FirstOrDefaultAsync();

    // Executing a raw SQL query that returns scalar values
    var count = await context.Database.ExecuteSqlRawAsync("UPDATE Posts SET Content = 'Updated' WHERE PostId = 1");
}
                    

Be cautious when using raw SQL, especially with user input, to prevent SQL injection vulnerabilities. Always use parameterized queries.

Advanced Querying Topics

• Lazy Loading: Automatically loads related entities when accessed.
• Eager Loading: Explicitly loads related entities using Include() or ThenInclude().
• Explicit Loading: Loads related entities on demand using Load().
• Query Tags: Add comments to generated SQL queries for easier debugging.
• Database Functions: Call database functions from LINQ.

Explore these advanced features to further optimize your data retrieval strategies.