Proposal: introduce left and right join clauses to C# query expression syntax

[roji]

Summary

• Specification: https://github.com/dotnet/csharplang/blob/main/proposals/left-right-join-in-query-expressions.md
• Tracking issue: [Proposal]: left join and right join query expression clauses #8947

Introduce left join and right join clauses into LINQ query expression syntax, e.g.:

from student in students
left join department in departments on student.DepartmentID equals department.ID
select new { student.Name, department?.Name }

Motivation

LINQ has a Join operator, which, like its SQL INNER JOIN counterpart, correlates elements of two sequences based on matching keys; the C# language has a corresponding join clause which translates to this operator (section 12.20.3.5 of the C# specs).

In addition to INNER JOIN, SQL also has LEFT JOIN, which returns outer elements even if there's no corresponding inner ones; LINQ and C#, in contrast, lack this operator. The LINQ conceptual documentation shows how to combine existing operators to achieve a left join:

var query =
    from student in students
    join department in departments on student.DepartmentID equals department.ID into gj
    from subgroup in gj.DefaultIfEmpty()
    select new
    {
        student.FirstName,
        student.LastName,
        Department = subgroup?.Name ?? string.Empty
    };

Or using method syntax:

var query = students
    .GroupJoin(departments, student => student.DepartmentID, department => department.ID, (student, departmentList) => new { student, subgroup = departmentList })
    .SelectMany(
        joinedSet => joinedSet.subgroup.DefaultIfEmpty(),
        (student, department) => new
        {
            student.student.FirstName,
            student.student.LastName,
            Department = department.Name
        });

Although functionality sufficient for expressing a left join operation, this combining approach has the following drawbacks:

• It's complicated, requiring combining multiple different LINQ operators in a specific way to form a complex construct, and is easy to accidentally get wrong. Many EF users have complained about the complexity of this construct for expressing a simple SQL LEFT JOIN.
• It is inefficient - the combination of operators adds significant overhead compared to a single operator using an internal lookup table (or "hash join", as Join is implemented).

dotnet/runtime#110292 is an approved API proposal for introducing LeftJoin and RightJoin operators into the System.Linq for .NET 10; see that proposal for additional performance information and benchmarks. Aside from allowing users to more easily express SQL LEFT/RIGHT JOIN when using a LINQ provider (such as EF Core), it would also allow both easier and more efficient in-memory left/right joins, exactly in the way that inner joins are already supported.

Along with the introduction of the operators into System.Linq, the C# query expression syntax can be extended with new left join and right join clauses which would translate to these new operators, allowing for simpler and more efficient code where C# query syntax is used.

Design

The syntax for the proposed left join and right join expression clauses would be identical to the existing join clause. For example:

from student in students
left join department in departments on student.DepartmentID equals department.ID
select new { student.Name, department?.Name }

Note that after the left join clause, department is nullable, as left join also returns students with no corresponding departments (unlike the existing join clause, which does not).

Similar to how the join clause internally translates to the LINQ Join operator, left join would translate to the new LeftJoin operator being added in dotnet/runtime#110292:

students.LeftJoin(
   departments,
   student => student.DepartmentID,
   department => department.CustomerID,
   (student, department) => new { student.Name, department?.Name })

right join represents the opposite operation, returning inner items when no corresponding outer ones are found:

from department in departments
right join student in students on department.ID equals student.DepartmentID
select new { department?.Name, student.Name }

This would translate to:

departments.RightJoin(
   students,
   department => department.CustomerID,
   student => student.DepartmentID,
   (department, student) => new { department?.Name, student.Name })

Notes

• See [API Proposal]: Introduce LeftJoin LINQ operator runtime#110292 for discussion on the naming for the new operators, and motivations for the adopted LeftJoin and RightJoin naming. For the proposed C# query expression, the clauses could be named left join/right join, or possibly leftjoin/rightjoin (similar to the orderby clause).

/cc @eiriktsarpalis @terrajobst @CyrusNajmabadi @dotnet/efteam

Previous related issues

• Please consider to extend Linq to add a new keyword leftjoin or rightjoin #361
• Add new keyword "leftjoin" to make LINQ more readable #508
• Add leftjoin/rightjoin syntax to linq to end the horrific syntax currently required runtime#53236
• Support the new .NET 10 LeftJoin operators efcore#12793

[houbi56]

Please also include a FullJoin!

[roji]

This is out of scope for this proposal. FullJoin has not been asked for much, and an efficient implementation requires more careful thinking compared to Left/RightJoin. If user demand for this manifests, we can always introduce this in the future.

[fubar-coder]

@houbi56 The API for left/right outer join was already approved during the .NET API review on 2024-12-03, but full join was omitted, because no demand was seen for this operation.

[HaloFour]

Kinda surprised this made it through review so easily. My opinion has been that new methods are just as problematic as new expression nodes, in that existing queryable providers must be updated to support both or they will explode at runtime. Obviously that's true of any and all methods, but such fundamental operations as LeftJoin and RightJoin might create an expectation of more universal support from developers, who now have to know whether they can use these methods/clauses or fall back to using the old GroupJoin approach depending on what they are targeting.

That's not to say that I'm opposed to the addition of these new methods, quite the contrary. I hope that their addition might help to open up the wider conversation about adding new expression nodes so that expression trees can support newer language features as well.

[CyrusNajmabadi]

Basically, I have no idea why we'd add restrictions when the Linq owners themselves are fine adding the method. Bcl and Linq have never had a problem adding new method. Why would we disallow it? Who is asking for it to be blocked?

[HaloFour]

Nobody is asking for restrictions, but I would hope the conversation around adding such fundamental and common LINQ methods would open the conversation to supporting them in C# language (as the alternative is kinda hoaky), which might open the door to discussing expression tree evolution in general. :)

[CyrusNajmabadi]

but I don't know why "new ?. node type that providers die on"

That's a decision for the expression tree owners. As long as that is their position, we'll follow it. But that decision doesn't extend any further beyond that API afaik. (Maybe there is a decision in the runtime for the past couple of decades where that differs?).

But as far as I can remember, Linq was absolutely fair game to enhance. It's only been the expr trees API that got locked.

[CyrusNajmabadi]

which might open the door to discussing expression tree evolution in general.

The expr trees owners can definitely decide if they want to change the rules. But it's orthogonal to any bcl API decision to me :-)

[roji]

Just to mention that there have been many LINQ method additions in recent years. For example, the Order operator was added in .NET 7 - there's no difference between that and LeftJoin/RightJoin. I absolutely don't think there is - or should be - any decision to not introduce new LINQ operator methods just because they'd create issues with existing queryable LINQ providers.

I personally happen to agree that there's no fundamental difference in terms of compat between adding a new LINQ method and adding a new expression tree node - but I'd really like to keep the conversation around evolving LINQ expression trees out of this language proposal (in the interests of keeping things focused and not making the discussion here unnecessarily complex/confused).

[CyrusNajmabadi]

I will champion this.

[orthoxerox]

@roji I've watched the API review and I've heard that you don't exclude the possibility of adding anti joins at a later date. Anti joins are a surprisingly useful concept that I use a lot when working with data frames. I know it's just a LeftJoin plus Where, but a dedicated anti join can be much faster, especially when the left and the right datasets are almost equal.

[roji]

We'd need a good perf investigation showing that dedicated SemiJoin/AntiJoin operations consistently out-perform the alternatives - that's definitely a possibility but is out of scope for this language proposal. Note that LeftJoin aren't only about performance, they're also about providing a much simpler alternative to the existing way of expressing LeftJoin (GroupJoin/SelectMany/DefaultIfEmpty); SemiJoin/AntiJoin don't really have this aspects (since they can already be expressed relatively cleanly via existing operators), and also do not have corresponding SQL operations (in SQL, semi-joins are expressed via EXISTS and a subquery, anti-joins via NOT EXISTS).

We can always consider introducing dedicated operators in the future based on the performance improvements and user interest.

[terrajobst]

I don't think I've ever seen a SQL dialect that had explicit support for semi joins. It seems to be an implementation detail of the plan generator. The canonical way in SQL would be an EXISTS predicate, which would be .Any(), wouldn't it?

[roji]

@terrajobst yeah, absolutely - explicit semi/anti join operators aren't really useful for the purposes of translating to SQL. However, the in-memory (enumerable, not queryable) operators could bring significant perf advantages, since they could e.g. use a hash/lookup internally (just like Join and LeftJoin do), which would likely perform better for most scenarios than the alternative with Any (which does a nested loop).

So this really would be a perf feature for in-memory LINQ operations, not for SQL translation.

[terrajobst]

Ah, gotcha. Same reason as for outer joins then.

[roji]

Yeah, though the outer joins also have the additional reason of being more complicated/annoying to express (with GroupJoin+SelectMany+DefaultIfEmpty) - so it's a usability issue there as well, in addition to the in-memory runtime perf improvement. In contrast, semi/anti don't really have a usability problem today (the Any way of expressing them is pretty good).