Please consider to extend Linq to add a new keyword leftjoin or rightjoin

[gafter]

@uyhung commented on Sun Feb 14 2016

Please consider to extend Linq to add a new keyword leftjoin or rightjoin. I'm sure a lot of .NET developers dream about this keyword when doing LEFT JOIN using SingleOrDefault() in conjunction with "into". Mimic left join using SingleOrDefault + "into" is very unclear. The same issue with how we do with Group By.
By supporting this keyword you really bring Linq to a higher level.

---

@DavidArno commented on Mon Feb 15 2016

Could you please expand this request with some details of how left and right joins are currently achieved with linq, how they might be achieved with method chaining and how the proposed query syntax might translate into a method chain?

---

@HaloFour commented on Mon Feb 15 2016

For reference:

How to: Perform Left Outer Joins (C# Programming Guide)

 var query = from person in people
             join pet in pets on person equals pet.Owner into gj
             from subpet in gj.DefaultIfEmpty()
             select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };

---

@svick commented on Mon Feb 15 2016

I don't think outer joins are actually a common operation in LINQ. In SQL, everything has to be returned in a single flat table, so outer join makes perfect sense. But in LINQ, you have group joins, which I believe are much more natural and easier to work with.

---

@HaloFour commented on Mon Feb 15 2016

@svick

If you're dealing with a 1:0..1 relationship and you want to flatten that result into an anonymous type you are still required to jump through these hoops. It's not pleasant and often trips up junior devs.

---

@svick commented on Mon Feb 15 2016

@HaloFour Yes, but what I'm trying to say is that you shouldn't need to flatten results in common cases. If you do, you're writing a more complicated query and in return you get something that's harder to work with. Just return the group directly (i.e. something like select new { person, pets = gj }).

---

@HaloFour commented on Mon Feb 15 2016

@svick

If you were flattening the results in the query it's likely that you need to get the final results in a flattened shape anyway, so using group join doesn't fix anything it just moves the problem downstream. Not to mention since queryable providers recognize the outer join pattern and understand the relationships that it entails they will create much more optimized SQL for it.

---

@uyhung commented on Fri Feb 19 2016

Guys I'm not talking about whether it's possible or there's a better way to do outer join in Linq, I just wanna suggest extending the LINQ to bring it up to a higher level of readability, stylish, more SQL-like. When I'm showing some examples of Linq to my friends who are working on other technology stacks such as PHP, Java etc. at the first glance looking at simple code samples they all say "wow it's great!", but when it comes to show demo about how to do Linq with outer join, group by, having etc... their face looks a little silly :)

---

@Unknown6656 commented on Tue Feb 16 2016

I know that this comment is slightly out of place, but I would rather like to see 'small' functions like Count, Sum, FirstOrDefault, LastOrDefault, Reverse etc. implemented in LINQ than LeftJoin or RightJoin -- but maybe this is my personal opinion :)

[orthoxerox]

Anyone willing to hammer out the precise translation rules?

[thomaslevesque]

I think it would be a very useful feature. How do we go about promoting this to a proposal?

Anyone willing to hammer out the precise translation rules?

Unless I'm missing something, the translation would be pretty simple. This:

from person in people
leftjoin pet in pets on person.Id equals pet.Id

would become:

from person in people
join pet in pets on person.Id equals pet.Id into tmp
from pet in tmp.DefaultIfEmpty()

[YairHalberstadt]

@thomaslevesque ideally modify the current C# 6 linq spec to include this. Then use the issue template to create a formal proposal.

[thomaslevesque]

Thanks

modify the current C# 6 linq spec

Does it even exist? Linq isn't mentioned here. Am I looking in the wrong place?

[YairHalberstadt]

It's here: https://github.com/dotnet/csharplang/blob/master/spec/expressions.md#query-expressions

[thomaslevesque]

@YairHalberstadt thanks!

[JohnGalt1717]

The current syntax is ugly and unnecessarily verbose adding additional parameters to the query for little reason. Please consider adding left/right join ASAP that just wraps this into one and makes it work effectively so that we can save our sanity.

[cheong00]

Agreed. However IMO the more serious problem lies in the LINQ syntax for full outer joins.

It's currently quite manageable if you only have two table, or maybe three. However it will easily go out-of-control as you add more table to join with other join types.

Yes, the thread suggested custom made .FullOuterJoin() can fix that (not tried though), however I wish to have something that baked into LINQ syntax.

[cheong00]

Btw, I should add that the reason why no full outer join support exists is probably related to the fact that some SQL servers do not support it.

[StevenRasmussen]

I really feel that this is in the wrong location, ie "Discussions". Discussions are far less discoverable since I believe most users go to "Issues" when searching for things. I also can't really find a good way to vote for this item here. I feel this has low visibility because of this. Anyone that works with LINQ on a day-to-day basis (which I would guess to be a large portion of C#/VB devs) would enthusiastically advocate for these enhancements! Please reconsider opening up #508.

Related items:
CSHarp Lang #508
DotNet Runtime #53236
EFCore #12793
EFCore #19296

[CyrusNajmabadi]

Same as with an issue:

[StevenRasmussen]

As far as I can tell.... voting in that location would be voting for the last comment by @Unknown6656... which I DON'T want to do because it is basically stating the opposite of what I want.... and by the number of supposed "votes" for this feature I think only proves my point! You're telling me that there are only 2 (or 11) depending on how you look at it) people in the entire Github community that want this feature?? That would be a ridiculous assumption based on all the "questions" on SO:

Again, I don't think that this is a feature that "few" developers want... rather the opposite! But due to the number of opened/closed issues and or lack of visibility of this "discussion" you are not getting a correct gauge of community interest.

Also, I don't know how you determine what should be a "discussion" vs an "issue"... and perhaps you follow different criteria than other dotnet repos but as far as I can tell the other repos have no problem with having issues that are clearly feature requests (which is what this is).

[HaloFour]

This discussion is a migration of an issue which was originally copied from another repo which is why the original comments are buried in the original post. Not ideal but I don't think the tooling provided a better way to do it at the time. The 11 upvotes would be for the proposal itself, not any one specific comment that was carried over in the migration. That may seem low, but that's probably par for the course for proposals on this repo.

The README for this repo describes how it expects Discussions/Issues to be used. The former are for requests and conversations around those requests while the latter are for formalized proposals that the language design team is tracking through the design process. Given Discussions are fairly new and optional it's expected that a lot of other repositories use them differently, if at all.

[CyrusNajmabadi]

voting in that location would be voting for the last comment by @Unknown6656..

It is not. It is showing support on the discussion.

Again, I don't think that this is a feature that "few" developers want...

I never said it was. I was just responding to your question on how you can vote. You can vote by clicking on the thumbs up. That serves as one signal to us (of many) that we use to gauge things.

Also, I don't know how you determine what should be a "discussion" vs an "issue"...

The distinction is:

This is also covered in our README: https://github.com/dotnet/csharplang#discussions

Issues are used by the team as the official way to track and work on changes taht are happening to the language spec. Discussions are the precursor for ideas to be hashed out and for things to go in an undirected fashion to allow lots of creativity where an issue can finally distill out of it.

perhaps you follow different criteria than other dotnet repos but as far as I can tell the other repos have no problem with having issues that are clearly feature requests (which is what this is).

We tried this. It was not compatible with our project and we had serious problems with it. We did not make the change to our development style lightly. It was a lot of discussion itself, and we trialed this out. The results were substantive and extremely beneficial.

Other projects can run themselves however they feel is best. Please allow us to do the same.

[thomaslevesque]

Again, I don't think that this is a feature that "few" developers want...

As someone who frequently uses the query syntax, including left joins, I wouldn't mind having this feature. But let's face it: there are many other features that would be much more beneficial. There are thousands of feature suggestions from users, and they can't all be implemented, so the team has to prioritize. And I think this one, even though it would be nice to have, would rank quite low in terms of priority.

[hauntingEcho]

This would be incredibly useful - my understanding is that this is the blocker for dotnet/efcore#12793 which is an ongoing source of frustration. The main SO topic for workarounds has passed the 250k view mark, and still can only return an IQueryable<Tuple<TLeft, TRight>> rather than properly working with navigation properties