Querying with LINQ in Entity Framework Core

Entity Framework Core (EF Core) integrates seamlessly with Language Integrated Query (LINQ) to provide a powerful and type-safe way to query your data.

Introduction to LINQ Querying

LINQ allows you to write queries in C# or Visual Basic that target data sources, including databases through EF Core. The EF Core query pipeline translates these LINQ queries into SQL (or another database-specific query language) for execution on the database server.

Basic Query Syntax

The most common way to query data is by using the LINQ query syntax, which resembles SQL. You typically start with a from clause, followed by other clauses like where, select, orderby, and group by.

Example: Retrieving all active users


using (var context = new BloggingContext())
{
    var activeUsers = from user in context.Users
                      where user.IsActive == true
                      orderby user.LastName
                      select user;

    foreach (var user in activeUsers)
    {
        Console.WriteLine($"User: {user.FirstName} {user.LastName}");
    }
}

Method Syntax

LINQ also provides a method syntax, which uses extension methods on IQueryable<T>. This syntax can sometimes be more concise and is often used in combination with query syntax.

Example: Retrieving all active users using method syntax


using (var context = new BloggingContext())
{
    var activeUsers = context.Users
                             .Where(user => user.IsActive == true)
                             .OrderBy(user => user.LastName);

    foreach (var user in activeUsers)
    {
        Console.WriteLine($"User: {user.FirstName} {user.LastName}");
    }
}

Filtering Data (where)

The where clause or Where() method is used to filter results based on one or more conditions.


// Find users whose first name starts with 'J'
var jUsers = from u in context.Users
             where u.FirstName.StartsWith("J")
             select u;

// Find users older than 30
var olderUsers = context.Users.Where(u => u.Age > 30);

Projection (select)

The select clause or Select() method is used to shape the results, creating new anonymous types or selecting specific properties.


// Select only the full name of users
var userNames = from u in context.Users
                select new { FullName = u.FirstName + " " + u.LastName };

// Select user's email and age
var userContactInfo = context.Users.Select(u => new { u.Email, u.Age });

Sorting Data (orderby)

The orderby clause or OrderBy()/OrderByDescending() methods are used to sort the results.


// Sort users by last name, then first name
var sortedUsers = from u in context.Users
                  orderby u.LastName, u.FirstName
                  select u;

// Sort by age in descending order
var usersByAgeDesc = context.Users.OrderByDescending(u => u.Age);

Grouping Data (group by)

The group by clause or GroupBy() method is used to group elements based on a key.


// Group users by their city
var usersByCity = from u in context.Users
                  group u by u.City into cityGroup
                  select new { City = cityGroup.Key, Count = cityGroup.Count() };

// Example of iterating through groups
foreach (var group in usersByCity)
{
    Console.WriteLine($"City: {group.City}, Number of users: {group.Count}");
}

Joins

EF Core supports LINQ joins to combine data from multiple tables.

Inner Join

The join clause or Join() method performs an inner join.


// Join Users with their Orders
var userOrders = from u in context.Users
                 join o in context.Orders on u.Id equals o.UserId
                 select new { UserName = u.FirstName, OrderId = o.Id, OrderDate = o.OrderDate };

// Using method syntax
var userOrdersMethod = context.Users.Join(context.Orders,
                                           user => user.Id,
                                           order => order.UserId,
                                           (user, order) => new { UserName = user.FirstName, OrderId = order.Id, OrderDate = order.OrderDate });

Group Join (Left Outer Join)

A group join followed by a `select` can simulate a left outer join.


var usersAndTheirOrders = from u in context.Users
                          join o in context.Orders on u.Id equals o.UserId into userOrdersGroup
                          from order in userOrdersGroup.DefaultIfEmpty()
                          select new { User = u.FirstName, OrderId = (int?)order.Id }; // Use nullable int? for OrderId if order is null

foreach (var item in usersAndTheirOrders)
{
    Console.WriteLine($"User: {item.User}, Order ID: {item.OrderId ?? -1}"); // Handle null OrderId
}

Projection and Navigation Properties

EF Core understands navigation properties, allowing you to easily include related data in your queries.


// Get blogs and their associated posts
var blogsWithPosts = context.Blogs
                            .Select(b => new {
                                BlogName = b.Title,
                                PostCount = b.Posts.Count(), // Using Count() to trigger loading related posts
                                LatestPostTitle = b.Posts.OrderByDescending(p => p.PublishedDate).FirstOrDefault().Title
                            });

// EF Core's `Include` method is a more explicit way to eager load related data for projection.
// However, for pure projection shaping, `Select` is key.
Important: When using LINQ queries with EF Core, the query is translated into SQL and executed on the database server. This is known as "Query Translation." EF Core optimizes these queries to be as efficient as possible.

Executing Queries

LINQ queries in EF Core are generally deferred execution. This means the LINQ query is not actually executed against the database until you iterate over the results or explicitly materialize them.

Tip: Use ToList() or ToArray() when you need to work with the data in memory multiple times to avoid re-executing the query against the database.

Common Query Operators

EF Core supports a wide range of LINQ operators. Here are some frequently used ones:

EF Core translates these operators into efficient SQL queries. For example, Skip() and Take() are translated into OFFSET and FETCH (or equivalent) clauses in SQL.


// Get the top 5 most recent posts
var top5Posts = context.Posts
                      .OrderByDescending(p => p.PublishedDate)
                      .Take(5);

// Get the second page of users (assuming 10 users per page)
var userPage2 = context.Users
                       .OrderBy(u => u.LastName)
                       .Skip(10)
                       .Take(10);

Query Translation Limitations

While EF Core's LINQ provider is very powerful, there are some LINQ operators and constructs that cannot be translated into SQL and will cause EF Core to load all data into memory before applying the operation.

Note: Always check the EF Core documentation for the specific database provider you are using to understand which LINQ operators are supported for translation and which will result in client-side evaluation.

Conclusion

Mastering LINQ querying with EF Core is essential for building efficient and maintainable data-driven applications. By leveraging LINQ, you can write expressive, type-safe queries that are translated into optimized database commands, reducing boilerplate code and improving developer productivity.