leftOuterJoin is different from C# version, workaround doesn't work for double left join

[Lanayx]

I'm creating this issue as a discussion about leftOuterJoin operator. Current implementation is inconsistent with C# version and libraries are reluctant to support F# version as well which leads to lack of very useful tools.
For example let's take Linq2Db issue that is not fixed for years or SQLProvider workaround which introduced another operator.
The key issue can be visible here

C#

.GroupJoin(
   name,
   c => c.ParentId,
   p => p.ParentId,
   (o, gr) => new { o = o; gr = gr })
.SelectMany(
   t => t.gr.DefaultIfEmpty(),
   (o, i) => new { o = o; gr = gr; i = i}
)

F# equivalent when applying leftOuterJoin

.GroupJoin(
   name,
   c => c.ParentId,
   p => p.ParentId,
   (o, gr) => new { o = o; gr = gr.DefaultIfEmpty() })
.SelectMany(
   t => t.gr,
   (o, i) => new { o = o; gr = gr; i = i}
)

Microsoft documented C# behaviour of DefaultIfEmpty() insertion which is different from F# behaviour, which leads to the issues.
So my question is shouldn't a breaking change be made to equalize left joins and let F# developers use C# tools?

[dsyme]

@Lanayx Interesting, I wasn't aware of this issue.

Could you add F# source code to illustrate the issue as a sort of failing test that we can run to detect the difference?

We may have to fix this by adding a leftOuterJoinCompat or something.

[Lanayx]

@dsyme You can find a source (which is a failing test) in Linq2Db repo

[cartermp]

So my question is shouldn't a breaking change be made to equalize left joins and let F# developers use C# tools?

The default answer is generally no, but we do have a precedent of doing things with byref to fix horribly broken semantics and align with how the runtime evolves. I don't think this applies, but I think it's worth considering a change if:

• This can be changed in a way that doesn't knowingly break an equally (or more) common scenario
• If the issue is so severe/broken that anyone relying on behavior today would gladly change their code to accommodate this

[Lanayx]

@cartermp To think of it can only break the code that supported only F# left join and didn't support C# left join. I think it can only be some deep niche project.

[dsyme]

@cartermp Having thought this over I do suspect that it may be best just to fix this directly.

In particular I don't know of any F#-specific LINQ implementations which would be exposed to this which don't also go through the query processing code in FSharp.Core.

The relevant lines would be either

• https://github.com/Microsoft/visualfsharp/blob/master/src/fsharp/FSharp.Core/Query.fs#L250 or
• https://github.com/Microsoft/visualfsharp/blob/master/src/fsharp/FSharp.Core/Query.fs#L1533

However it's hard to be 100% certain, it looks like the process of adding the DefaultIfEmpty needs to be pushed into the continuation

[dsyme]

@Lanayx What happens if you use this, i.e. a groupJoin then subsequent select, much as in C#?

        query { for i in db do 
                 groupJoin j in db on (i.Name = j.Name) into group
                 for x in group.DefaultIfEmpty() do 
                 select x } 

or

        query { for i in db do 
                 groupJoin j in db on (i.Name = j.Name) into group
                 for x in group.DefaultIfEmpty() do 
                 yield x } 

[dsyme]

As an aside, there is a relevant test of the query tree for leftOuterJoin here, the results are indeed matching the report above.

This is the test today:

        query { for i in db do 
                 leftOuterJoin j in db on (i.Name = j.Name) into group
                 yield group }

giving query

db.GroupJoin(db, i => i.Name, j => j.Name, 
    (i, group) => new AnonymousObject`2(Item1 = i, Item2 = group.DefaultIfEmpty())
).Select(_arg1 => _arg1.Item2)"

Changing the query to:

        query { for i in db do 
                 groupJoin j in db on (i.Name = j.Name) into group
                 for x in group.DefaultIfEmpty() do 
                 yield x }  

gives

db.GroupJoin(db, i => i.Name, j => j.Name,
    (i, group) => new AnonymousObject`2(Item1 = i, Item2 = group)
).SelectMany(
    _arg1 => _arg1.Item2.DefaultIfEmpty(),
    (_arg1, x) => x
)"

which looks plausible. Changing to use select

        query { for i in db do 
                 groupJoin j in db on (i.Name = j.Name) into group
                 for x in group.DefaultIfEmpty() do 
                 select x } 

gives

db.GroupJoin(db, i => i.Name, j => j.Name,
    (i, group) => new AnonymousObject`2(Item1 = i, Item2 = group)
).SelectMany(_arg1 => 
    _arg1.Item2.DefaultIfEmpty(),
    (_arg1, _arg2) => new AnonymousObject`3(Item1 = _arg1.Item1, Item2 = _arg1.Item2, Item3 = _arg2)
).Select(tupledArg => tupledArg.Item3)"

which also looks plausible (the final Select allows any F# computation in the end select)

[Lanayx]

@dsyme
Thank you for your comments, groupJoin and DefaultIfEmpty work fine with both select and yield as for linq2db

[dsyme]

OK, I will rename this to "deprecate leftOuterJoin and give proper approach in error message"

[Lanayx]

Linking linq2db/linq2db#1813 as it looks like the workaround only works for single leftOuterJoin and doesn't work for double join