For many developers, November isn’t just about fall foliage—it’s also when the new .NET version drops… and version 10 has finally landed!
In this article, I’ll focus on one small significant addition: we finally have leftJoin() and rightJoin() functions available in LINQ syntax when working with Entity Framework Core 10.
The sample project for this article is available on my Github
Left join and Right join
If you’ve opened this article, I assume you already know what left and right joins are, but for all vibe coders out there (😜), here’s a quick mental refresh.
For this example, I’ve created a small database structured like this:
Let’s say we want to extract all characters (characters table) who don’t have a residence (residences table) on a planet (planets table).
The tables are populated as follows:
As you can see, Han Solo, Grogu, and Mando have no residence (obviously, they’re wanderers! 😁), and the planets Jakku, Naboo, and Coruscant appear to have no registered inhabitants. This Imperial database is seriously out of date 😅
If we wanted to extract all characters without a residence using a query, we’d select from the characters table (SELECT) and look for all matches in the residences table (LEFT JOIN). The resulting query would look like this:
select
c.Id as 'CharacterId',
c.Name as 'CharacterName'
from Characters c
left join Residences r on
c.Id = r.CharacterId
where r.PlanetId is nullWe’d use a right join if we started from the Residences table and wanted to extract its relationships with the Characters table.
select
c.Id as 'CharacterId',
c.Name as 'CharacterName'
from Residences r
right join Characters c on
c.Id = r.CharacterId
where r.PlanetId is nullℹ️ Unlike an inner join, left and right joins also return rows where there’s no match.
Before EF Core 10
Prior to this release, writing a simple left or right join was more complicated and forced you to either write a custom query or use GroupJoin() with the DefaultIfEmpty() method.
Personally, I always used SqlQueryRaw to keep the code simpler and more readable, sacrificing EF’s caching advantages but winning on simplification.
Sticking with our earlier example, the two possible solutions were:
var query = _context.Database.SqlQueryRaw<string>(
@"
select c.Name
from Characters c
left join Residences r on
c.Id = r.CharacterId
where r.PlanetId is null
"
);Or
var query = _context.Characters.GroupJoin(
_context.Residences,
residence => residence.Id,
character => character.CharacterId,
(character, residenceList) => new { character, residences = residenceList }
).SelectMany(
charResidence => charResidence.residences.DefaultIfEmpty(),
(charResidence, residence) => new
{
CharacterId = charResidence.character.Id,
CharacterName = charResidence.character.Name,
PlanetId = residence != null ? residence.PlanetId : (int?)null
}
)
.Where(c => c.PlanetId == null);The visual and syntactic complexity of the second approach is pretty obvious. In my day-to-day development, I favor simpler code over more complex alternatives, regardless of the tool: I’d sacrifice using LINQ (which is useful in many cases) in favor of raw queries… at least until now!
New functions
LINQ, which simplifies our work through its fluent syntax, has introduced the .leftJoin() and .rightJoin() functions, streamlining the syntax of the second approach we just saw. Here’s how you write a left join:
var query = await _context.Characters.LeftJoin(
_context.Residences, //table to join
character => character.Id, //Key to match first table
residence => residence.CharacterId, //Key to match join table
(character, residence) => new
{
CharacterId = character.Id,
CharacterName = character.Name,
PlanetId = residence != null ? residence.PlanetId : (int?)null
})
.Where(c => c.PlanetId == null)
.ToListAsync();
And for the right join:
var query = await _context.Residences.RightJoin(
_context.Characters, //table to join
residence => residence.CharacterId, //Key to match first table
character => character.Id, //Key to match join table
(residence, character) => new
{
residenceCharacterId = character.Id,
residenceCharacterName = character.Name,
planetId = residence != null ? residence.PlanetId : (int?)null
}
)
.Where(c => c.planetId == null)
.ToListAsync();
To simplify even further, let’s compare the LINQ syntax with SQL:
The first parameter of both functions is the table to join, the second and third are the keys from both tables that should be joined. The fourth parameter is the declaration of an anonymous object (in my case, but it could also be a DTO).
From here on, it’s just standard LINQ syntax, and it even allows us to chain multiple join constructs:
var query = await _context.Characters.LeftJoin(
_context.Residences,
character => character.Id,
residence => residence.CharacterId,
(character, residence) => new
{
CharacterId = character.Id,
CharacterName = character.Name,
PlanetId = residence != null ? residence.PlanetId : (int?)null
})
.Where(c => c.PlanetId != null)
.RightJoin(
_context.Planets,
charResidence => charResidence.PlanetId,
planets => planets.Id,
(charResidence, planets) => new
{
CharacterName = charResidence == null ? null : charResidence.CharacterName,
PlanetName = planets.Name
}
)
.Where(p => p.CharacterName != null)
.ToListAsync();
Note: In this example, I could have used a regular join instead of the initial left join.
Conclusion
This introduction fills a pretty significant gap that’s useful in any context where we use the Entity Framework ORM. In the GitHub repository shared at the beginning of this article, you’ll find a starting point to begin experimenting. The next step is to start using it in your own projects.
Until next time!
