leftOuterJoin causing "unrecognized method call" with Postgresql

[jackmott]

Environment:

• Windows 10
• PostgresSQL 9.5
• .NET 4.6.1
• Npgsql 3.0.5
• SQLProvider 0.0.11-alpha

Query:

 query {
            for timing in db.Public.Timings do
            leftOuterJoin sensor in db.Public.Sensors on (timing.Transpondercode = sensor.Sensorcode) into r1              
            select timing
        }        

Error:

   System.Exception: unrecognised method call
    at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e)
    at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence)   
    at Microsoft.FSharp.Linq.QueryModule.clo@1735-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 )
    at AlphaFront.Db.getTimings(Double startTime, Double endTime) in C:\projects\AlphaFront\AlphaFront\Db.fs:line 488

This occurs whether I call DefaultIfEmpty() on r1 and r2 or not, and it occurs if I simplify the select clause to just return timing

Queries without left joins have been working great.

[jackmott]

It appears I have figured out, via mining other issues, that leftOuterJoin is just not supported, except by use of the !! operator, maybe? Some more details on how that works would be great.

[pezipink]

Hello, yes we simply do not support leftOuterJoin yet because it also requires groupJoin to be implemented and it has not been done. I put the !! operator in as it was a cheeky way I could easily support left joins for things with properly defined relationships, without having to implement groupJoin and friends.

In theory it will be supported one day.

[jackmott]

What is an order of magnitude on the effort involved in adding support? If I take this up am I looking at weeks, months, or a deep deep rabbit hole? =)

[pezipink]

It's a pretty tough one! You would be basically be implementing all of aggregation with a bonus of having left joins along the way ...

[Thorium]

You can now use (!!) also with traditional joins to convert them to left joins.
Not perfect (as you could want to join x > y condition), but works for the initial problem.