EF Core - SELECT Queries Involving Multiple Tables

In this article, I’ll demonstrate how to execute queries involving multiple tables, using both LINQ and raw SQL.

While most queries can be done using LINQ, there are some advanced scenarios where generating the query you want can be tricky. In those cases, raw SQL can provide a cleaner solution.

Using Include() to Populate Linked Entities

Let’s consider a scenario where a series has multiple seasons. The Series model has a List<Season> property. By default, querying a series won’t load its seasons.

To include the seasons in the query, you can use Include(), like so:

using (var context = new MediaContext(connectionString))
{
    var series = await context.Series
        .Include(nameof(Series.Seasons))
        .AsNoTracking()
        .ToListAsync();

    foreach (var s in series)
    {
        Console.WriteLine($"{s.Title} has {s.Seasons.Count()} seasons");
    }
}

SQL Generated:

This code will execute a query with a LEFT JOIN to populate the Seasons collection:

SELECT [s].[Id], [s].[Title], [s].[YearStarted], [s].[YearEnded], [se].[Id], [se].[SeasonNumber], [se].[SeriesId]
FROM [Series] AS [s]
LEFT JOIN [Seasons] AS [se] ON [s].[Id] = [se].[SeriesId]
ORDER BY [s].[Id], [se].[Id]

Here, each row for a series is repeated for every season, but EF Core will map it correctly to a single Series object and populate the Seasons list.

INNER JOIN - Two Tables

Imagine a Movie has many Actors. If you want to list all actors for the movie The Matrix, here’s how you could do it using both LINQ and raw SQL.

LINQ

using (var context = new MediaContext(connectionString))
{
    var actors = await (from actor in context.Actors
                        join movie in context.Movies on actor.MovieId equals movie.Id
                        where movie.Title == "The Matrix"
                        select actor)
                       .AsNoTracking()
                       .ToListAsync();

    foreach (var actor in actors)
    {
        Console.WriteLine($"Actor: {actor.Name}");
    }
}

SQL Generated:

SELECT [a].[Id], [a].[Name], [a].[MovieId]
FROM [Actors] AS [a]
INNER JOIN [Movies] AS [m] ON [a].[MovieId] = [m].[Id]
WHERE [m].[Title] = N'The Matrix'

Console Output:

Actor: Keanu Reeves
Actor: Laurence Fishburne
Actor: Carrie-Anne Moss

Raw SQL

Alternatively, you can execute this query using raw SQL:

using (var context = new MediaContext(connectionString))
{
    var movieTitle = "The Matrix";
    var actors = await context.Actors.FromSqlInterpolated(
        $@"SELECT a.* FROM Actors a
           INNER JOIN Movies m ON a.MovieId = m.Id
           WHERE m.Title = {movieTitle}")
        .AsNoTracking()
        .ToListAsync();

    foreach (var actor in actors)
    {
        Console.WriteLine($"Actor: {actor.Name}");
    }
}

SQL Generated:

exec sp_executesql N'SELECT a.* 
                    FROM Actors a
                    INNER JOIN Movies m ON a.MovieId = m.Id
                    WHERE m.Title = @p0',N'@p0 nvarchar(4000)',@p0=N'The Matrix'

Console Output:

Actor: Keanu Reeves
Actor: Laurence Fishburne
Actor: Carrie-Anne Moss

Subquery – WHERE EXISTS

Now, let's consider a many-to-many relationship between the Movies and Directors tables, which is linked through a table called DirectorMovie. If you want to list all movies directed by "Christopher Nolan," you can use a subquery with the WHERE EXISTS clause.

using (var context = new MediaContext(connectionString))
{
    var moviesByNolan = await context.Movies
        .Where(m => m.Directors.Any(d => d.Name == "Christopher Nolan"))
        .AsNoTracking()
        .ToListAsync();

    foreach (var movie in moviesByNolan)
    {
        Console.WriteLine($"Christopher Nolan directed {movie.Title}");
    }
}

SQL Generated:

SELECT [m].[Id], [m].[Title], [m].[ReleaseYear]
FROM [Movies] AS [m]
WHERE EXISTS (
    SELECT 1
    FROM [DirectorMovie] AS [dm]
    INNER JOIN [Directors] AS [d] ON [dm].[DirectorId] = [d].[Id]
    WHERE [m].[Id] = [dm].[MovieId] AND [d].[Name] = N'Christopher Nolan')

Console Output:

Christopher Nolan directed Inception
Christopher Nolan directed Interstellar

When working with multiple tables in EF Core, you can use Include() for simple relationships, LINQ for straightforward queries, or raw SQL for complex join scenarios.