New in .NET 10.0 [24]: LINQ operators RightJoin() and LeftJoin() in EF Core
The LINQ operators LeftJoin() and RightJoin(), introduced in .NET 10.0, are already supported for database access in Entity Framework Core 10.0.
(Image: Pincasso / Shutterstock.com)
- Dr. Holger Schwichtenberg
.NET 10.0 introduces the LINQ operators LeftJoin() and RightJoin(), which also work with all database management systems in Entity Framework Core 10.0: The Object Relational Mapper (ORM) translates them into corresponding SQL commands, see the associated Issues for LeftJoin and RightJoin.
Previously, one had to awkwardly form a Left (Outer) Join and Right (Outer) Join as in LINQ-to-Objects using GroupJoin() and SelectMany() as well as DefaultIfEmpty().
Some code examples below show the use of RightJoin() and LeftJoin() with Entity Framework Core 10.0 in conjunction with a relational database. Even in Entity Framework, one could already achieve the result of RightJoin() and LeftJoin() via GroupJoin() and SelectMany() with DefaultIfEmpty(). The examples start with the old variant and then show the new variant.
(Image:Â King / stock.adobe.com)
What's new in .NET 11.0: On November 17, 2026, Dr. Holger Schwichtenberg and other experts will present the changes for developers in .NET SDK, C# 15.0, and more at the online conference betterCode() .NET 11.0. Discounted early bird tickets are available until the program is released.
The following code shows LeftJoin() in a database with the tables “Flight” and “Pilot”:
/// <summary>
/// LeftJoin(): Suche alle FlĂĽge, zu denen es keinen Piloten gibt
/// </summary>
public void EFC10_LeftJoin()
{
CUI.Demo(nameof(EFC10_LeftJoin));
var ctx = new DA.WWWings.WwwingsV1EnContext();
#region --------------- ALT
CUI.H2("ALT: Suche alle FlĂĽge, zu denen es keinen Piloten gibt via GroupJoin() und SelectMany()");
var fluegeOhnePilotAlt = ctx.Flights
.GroupJoin(
ctx.Pilots,
f => f.PilotPersonId,
p => p.PersonId,
(f, pilots) => new { Flight = f, Pilots = pilots.DefaultIfEmpty() }
)
.SelectMany(
fp => fp.Pilots,
(fp, p) => new
{
fp.Flight.FlightNo,
fp.Flight.Departure,
fp.Flight.Destination,
fp.Flight.FlightDate,
PilotId = fp.Flight.PilotPersonId == null ? "n/a" : fp.Flight.PilotPersonId.ToString(),
GivenName = p.Employee.Person.GivenName,
Surname = p.Employee.Person.Surname
}
)
.Where(x => x.Surname == null)
.Take(20)
.ToList();
Console.WriteLine("Gefundene FlĂĽge: " + fluegeOhnePilotAlt.Count);
foreach (var item in fluegeOhnePilotAlt)
{
Console.WriteLine($"{item.FlightNo} {item.Departure}->{item.Destination} am {item.FlightDate}: Pilot {item.PilotId} {item.GivenName} {item.Surname}");
}
#endregion
#region --------------- NEU
CUI.H2("NEU: Suche alle FlĂĽge, zu denen es keinen Piloten gibt via LeftJoin()");
var fluegeOhnePilotNeu = ctx.Flights
.LeftJoin(
ctx.Pilots,
f => f.PilotPersonId,
p => p.PersonId,
(f, p) => new
{
f.FlightNo,
f.Departure,
f.Destination,
f.FlightDate,
PilotId = f.PilotPersonId == null ? "n/a" : f.PilotPersonId.ToString(),
p.Employee.Person.GivenName,
p.Employee.Person.Surname,
}).Where(x => x.Surname == null).Take(20).ToList();
Console.WriteLine("Gefundene FlĂĽge: " + fluegeOhnePilotNeu.Count);
foreach (var item in fluegeOhnePilotNeu)
{
Console.WriteLine($"{item.FlightNo} {item.Departure}->{item.Destination} am {item.FlightDate}: Pilot {item.PilotId} {item.GivenName} {item.Surname}");
}
#endregion
#region --------------- Kontrolle
CUI.H3("Zur Kontrolle:");
// Zur Kontrolle:
if (fluegeOhnePilotNeu.Count() > 0)
{
var f = ctx.Flights.Find(fluegeOhnePilotNeu[0].FlightNo);
Console.WriteLine(f.ToNameValueString());
}
#endregion
}
The following SQL command results from this LINQ command with LeftJoin():
SELECT TOP(@p) [f].[FlightNo], [f].[Departure], [f].[Destination], [f].[FlightDate], CASE
WHEN [f].[Pilot_PersonID] IS NULL THEN 'n/a'
ELSE COALESCE(CONVERT(varchar(11), [f].[Pilot_PersonID]), '')
END AS [PilotId], [p0].[GivenName], [p0].[Surname]
FROM [Operation].[Flight] AS [f]
LEFT JOIN [People].[Pilot] AS [p] ON [f].[Pilot_PersonID] = [p].[PersonID]
LEFT JOIN [People].[Employee] AS [e] ON [p].[PersonID] = [e].[PersonID]
LEFT JOIN [People].[Person] AS [p0] ON [e].[PersonID] = [p0].[PersonID]
WHERE [p0].[Surname] IS NULL
Videos by heise
The following code shows RightJoin() in a database with the tables “Flight” and “Pilot”:
/// <summary>
/// Gibt zu den letzten drei angelegten Piloten alle FlĂĽge aus
/// </summary>
public void EFC10_RightJoin()
{
CUI.Demo(nameof(EFC10_RightJoin));
var ctx = new DA.WWWings.WwwingsV1EnContext();
CUI.H2("Alt: Gibt zu den letzten drei angelegten Piloten alle FlĂĽge aus via GroupJoin() und SelectMany()");
var ctx2 = new DA.WWWings.WwwingsV1EnContext();
#region --------------- ALT
var pilotenMitFlugAlt = ctx.Pilots
.OrderByDescending(x => x.PersonId)
.Take(3)
.GroupJoin(
ctx.Flights,
p => p.PersonId,
f => f.PilotPersonId,
(p, flights) => new { Pilot = p, Flights = flights.DefaultIfEmpty() }
)
.SelectMany(
pf => pf.Flights,
(pf, f) => new
{
PilotId = pf.Pilot.PersonId,
pf.Pilot.Employee.Person.GivenName,
pf.Pilot.Employee.Person.Surname,
Flight = f,
f.Departure,
f.Destination,
}
)
.OrderBy(x => x.PilotId)
.ToList();
foreach (var p in pilotenMitFlugAlt)
{
Console.WriteLine($"Pilot #{p.PilotId} {p.GivenName} {p.Surname} fliegt " + (p.Flight != null ? $"Flug #{p.Flight?.FlightNo} {p.Flight.Departure}->{p.Flight.Destination} am {p.Flight.FlightDate}" : "bisher keinen Flug"));
}
Console.WriteLine("Anzahl: " + pilotenMitFlugAlt.Count);
#endregion
#region --------------- NEU
CUI.H2("Neu: Gibt zu den letzten drei angelegten Piloten alle FlĂĽge aus via RightJoin()");
var pilotenMitFlugNeu = ctx.Flights
.RightJoin(
ctx.Pilots.OrderByDescending(x => x.PersonId).Take(3),
f => f.PilotPersonId,
p => p.PersonId,
(f, p) => new
{
PilotId = p.PersonId,
p.Employee.Person.GivenName,
p.Employee.Person.Surname,
Flight = f,
f.Departure,
f.Destination,
}).OrderBy(x => x.PilotId).ToList();
foreach (var p in pilotenMitFlugNeu)
{
Console.WriteLine($"Pilot #{p.PilotId} {p.GivenName} {p.Surname} fliegt " + (p.Flight != null ? $"Flug #{p.Flight?.FlightNo} {p.Flight.Departure}->{p.Flight.Destination} am {p.Flight.FlightDate}" : "bisher keinen Flug"));
}
Console.WriteLine("Anzahl: " + pilotenMitFlugNeu.Count);
#endregion
#region --------------- Kontrolle
CUI.H2("Nur zur Kontrolle: Gibt zu den letzten drei angelegten Piloten alle FlĂĽge aus via Navigation Property");
var pilotenMitFlug2 = ctx.Pilots.Include(p => p.Employee).ThenInclude(p => p.Person).Include(p => p.Flights).OrderByDescending(x => x.PersonId).Take(3).OrderBy(x => x.PersonId).ToList();
int count = 0;
foreach (Pilot p in pilotenMitFlug2)
{
if (p.Flights.Count == 0)
{
count++;
Console.WriteLine($"Pilot #{p.PersonId} {p.Employee.Person.GivenName} {p.Employee.Person.Surname} fliegt bisher keinen Flug");
}
else
{
foreach (var f in p.Flights)
{
count++;
Console.WriteLine($"Pilot #{p.PersonId} {p.Employee.Person.GivenName} {p.Employee.Person.Surname} fliegt Flug #{f.FlightNo} {f.Departure}->{f.Destination} am {f.FlightDate}");
}
}
}
Console.WriteLine("Anzahl: " + count);
#endregion
}
The following SQL command results from the LINQ command with RightJoin():
SELECT [p0].[PersonID] AS [PilotId], [p1].[GivenName], [p1].[Surname], [f].[FlightNo], [f].[Airline], [f].[Departure], [f].[Destination], [f].[FlightDate], [f].[FreeSeats], [f].[Memo], [f].[NonSmokingFlight], [f].[Pilot_PersonID], [f].[Seats], [f].[Timestamp]
FROM [Operation].[Flight] AS [f]
RIGHT JOIN (
SELECT TOP(@p) [p].[PersonID]
FROM [People].[Pilot] AS [p]
ORDER BY [p].[PersonID] DESC
) AS [p0] ON [f].[Pilot_PersonID] = [p0].[PersonID]
INNER JOIN [People].[Employee] AS [e] ON [p0].[PersonID] = [e].[PersonID]
INNER JOIN [People].[Person] AS [p1] ON [e].[PersonID] = [p1].[PersonID]
ORDER BY [p0].[PersonID]
It should also be explicitly pointed out that the new operators RightJoin() and LeftJoin(), including the previously existing operators Join() and GroupJoin(), are only needed for connecting tables for which there is no navigation relationship in the object model.
For example, instead of the complex RightJoin() with an existing navigation relationship in the object model, one can achieve the same output result with an Include(). In this case, however, you do not get a flat list with data from Pilot and Flight, but an object hierarchy, hence two nested foreach loops.
The following code uses an Include() via navigation relationship instead of RightJoin():
CUI.H2("Gibt zu den letzten drei angelegten Piloten alle FlĂĽge aus via Navigation Property");
var pilotenMitFlug2 = ctx.Pilots.Include(p => p.Employee).ThenInclude(p => p.Person).Include(p => p.Flights).OrderByDescending(x => x.PersonId).Take(3).OrderBy(x => x.PersonId).ToList();
foreach (Pilot p in pilotenMitFlug2)
{
if (p.Flights.Count == 0)
{
Console.WriteLine($"Pilot #{p.PersonId} {p.Employee.Person.GivenName} {p.Employee.Person.Surname} fliegt bisher keinen Flug");
}
else
{
foreach (var f in p.Flights)
{
Console.WriteLine($"Pilot #{p.PersonId} {p.Employee.Person.GivenName} {p.Employee.Person.Surname} fliegt Flug #{f.FlightNo} {f.Departure}->{f.Destination} am {f.FlightDate}");
}
}
}
Entity Framework Core only performs Inner Joins in this case:
SELECT [p0].[PersonID], [p0].[FlightHours], [p0].[FlightSchool], [p0].[LicenseDate], [p0].[LicenseType], [e].[PersonID], [e].[EmployeeNo], [e].[HireDate], [e].[Supervisor_PersonId], [p1].[PersonID], [p1].[Birthday], [p1].[City], [p1].[Country], [p1].[EMail], [p1].[GivenName], [p1].[Memo], [p1].[Photo], [p1].[Surname], [f].[FlightNo], [f].[Airline], [f].[Departure], [f].[Destination], [f].[FlightDate], [f].[FreeSeats], [f].[Memo], [f].[NonSmokingFlight], [f].[Pilot_PersonID], [f].[Seats], [f].[Timestamp]
FROM (
SELECT TOP(@p) [p].[PersonID], [p].[FlightHours], [p].[FlightSchool], [p].[LicenseDate], [p].[LicenseType]
FROM [People].[Pilot] AS [p]
ORDER BY [p].[PersonID] DESC
) AS [p0]
INNER JOIN [People].[Employee] AS [e] ON [p0].[PersonID] = [e].[PersonID]
INNER JOIN [People].[Person] AS [p1] ON [e].[PersonID] = [p1].[PersonID]
LEFT JOIN [Operation].[Flight] AS [f] ON [p0].[PersonID] = [f].[Pilot_PersonID]
ORDER BY [p0].[PersonID], [e].[PersonID], [p1].[PersonID]
(dmk)
