EF Core – SELECT queries involving multiple tables

by

When you’ve created tables that are related, you’ll often need to get data from both tables at once, or filter records from one table based on values in another table. In this article, I’ll show examples of executing queries like this where more than one table is involved.

You can do most queries using LINQ. However, sometimes you’ll have advanced scenarios where it’s difficult to make LINQ generate the query you want. In those advanced scenarios, I’d recommend writing raw SQL instead of trying to use LINQ. In the INNER JOIN scenario below, I’ll show both the LINQ and raw SQL ways of executing the join query.

Use Include() to populate linked entities

A show has many episodes, so the Show model has a List<Episode> property. By default, when you query for shows, the episodes list won’t be populated.

To populate episodes, you can use Include(), like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var shows = await context.Shows
		.Include(nameof(Show.Episodes))
		.AsNoTracking()
		.ToListAsync();

	foreach (var show in shows)
	{
		Console.WriteLine($"{show.Name} has {show.Episodes.Count()} episodes");
	}
}
Code language: C# (cs)

Running this query populates Show.Episodes and writes the following to the console:

Star Trek: Picard has 9 episodes

According to SQL Profiler, this executed the following query with a LEFT JOIN:

SELECT [s].[Id], [s].[Description], [s].[FirstYear], [s].[LastYear], [s].[Name], [s].[NumberOfEpisodes], [s].[NumberOfSeasons], [s].[YearsOnTV], [e].[Id], [e].[Number], [e].[Season], [e].[ShowId], [e].[Title]
FROM [Shows] AS [s]
LEFT JOIN [Episodes] AS [e] ON [s].[Id] = [e].[ShowId]
ORDER BY [s].[Id], [e].[Id]
Code language: SQL (Structured Query Language) (sql)

In other words, use Include() when you want to do a simple LEFT JOIN with no extra conditions.

Notice that this LEFT JOIN returns one row per episode, and EF Core maps it to a single show object and populates its episodes list.

INNER JOIN – Two tables

A show has many episodes. Let’s say you want to get all episodes for the show called Star Trek: Picard. I’ll show two ways to do this below – using LINQ and using raw SQL.

LINQ

Use LINQ to execute an INNER JOIN query on the Shows and Episodes table, filtering by Show.Name:

using (var context = new StreamingServiceContext(connectionString))
{
	var episodes = await (from episode in context.Episodes
					   join show in context.Shows on episode.ShowId equals show.Id
					   where show.Name == "Star Trek: Picard"
					   select episode)
					  .AsNoTracking()
					  .ToListAsync();

	foreach (var episode in episodes)
	{
		Console.WriteLine($"Episode {episode.Season}.{episode.Number} - {episode.Title}");
	}
}
Code language: C# (cs)

According to SQL Profiler, this generated the following query:

SELECT [e].[Id], [e].[Number], [e].[Season], [e].[ShowId], [e].[Title]
FROM [Episodes] AS [e]
INNER JOIN [Shows] AS [s] ON [e].[ShowId] = [s].[Id]
WHERE [s].[Name] = N'Star Trek: Picard'
Code language: SQL (Structured Query Language) (sql)

Running this produces the following output to the console:

Episode 1.1 - Remembrance
Episode 1.2 - Maps and Legends
Episode 1.3 - The End Is The Beginning
Episode 1.4 - Absolute Candor
Episode 1.5 - Stardust City Rag
Episode 1.6 - The Impossible Box
Episode 1.7 - Nepenthe
Episode 1.8 - Broken Pieces
Episode 1.9 - Et in Arcadia EgoCode language: plaintext (plaintext)

Raw SQL

Use FromSqlInterpolated() to execute raw SQL that does an INNER JOIN on the Shows and Episodes tables, filtering by Show.Name:

using (var context = new StreamingServiceContext(connectionString))
{
	var showName = "Star Trek: Picard";

	var episodes = await context.Episodes.FromSqlInterpolated(
		$@"SELECT e.* FROM Episodes e
		INNER JOIN Shows s ON e.ShowId = s.Id
		WHERE s.Name = {showName}")
		.AsNoTracking()
		.ToListAsync();
		
	foreach (var episode in episodes)
	{
		Console.WriteLine($"Episode {episode.Season}.{episode.Number} - {episode.Title}");
	}
}
Code language: C# (cs)

FromSqlInterpolated() parameterizes the query, so according to SQL Profiler, the following parameterized query is executed:

exec sp_executesql N'SELECT e.* 
                FROM Episodes e
                INNER JOIN Shows s ON e.ShowId = s.Id
                WHERE s.Name = @p0
',N'@p0 nvarchar(4000)',@p0=N'Star Trek: Picard'
Code language: SQL (Structured Query Language) (sql)

This generates the following output to the console:

Episode 1.1 - Remembrance
Episode 1.2 - Maps and Legends
Episode 1.3 - The End Is The Beginning
Episode 1.4 - Absolute Candor
Episode 1.5 - Stardust City Rag
Episode 1.6 - The Impossible Box
Episode 1.7 - Nepenthe
Episode 1.8 - Broken Pieces
Episode 1.9 - Et in Arcadia EgoCode language: plaintext (plaintext)

Subquery – WHERE EXISTS

The Movies table has a many-to-many relationship with the Actors table. These two tables are related via a linking table called ActorMovie.

Let’s say you want to select all movies that have Keanu in them. Fortunately, EF Core abstracts away the details and you can just write a clean, simple LINQ statement like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var moviesWithKeanu = await context.Movies
		.Where(t => t.Actors.Any(a => a.FirstName == "Keanu"))
		.AsNoTracking()
		.ToListAsync();

	foreach (var movie in moviesWithKeanu)
	{
		Console.WriteLine($"Keanu was in {movie.Name}");
	}
}
Code language: C# (cs)

According to SQL Profiler, this executes the following query containing a WHERE EXISTS subquery:

SELECT [m].[Id], [m].[BoxOfficeRevenue], [m].[Description], [m].[Director], [m].[Name], [m].[YearOfRelease]
FROM [Movies] AS [m]
WHERE EXISTS (
    SELECT 1
    FROM [ActorMovie] AS [a]
    INNER JOIN [Actors] AS [a0] ON [a].[ActorsId] = [a0].[Id]
    WHERE ([m].[Id] = [a].[MoviesId]) AND ([a0].[FirstName] = N'Keanu'))
Code language: SQL (Structured Query Language) (sql)

This outputs the following to the console:

Keanu was in John Wick
Keanu was in The Matrix
Code language: plaintext (plaintext)

Related Articles