0

Environment:

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

The following query:

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

returns:

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

Other queries, including joins have been working great, but this is the first left join I have tried.

edit: I've simplified the code example to the smallest reproducing example.

jackmott
  • 1,112
  • 8
  • 16
  • The only method call I see in there is `DefaultIfEmpty()`. Have you tried removing it? – Fyodor Soikin Apr 01 '16 at 16:10
  • Yes, I have. Having that method call present or not does not change the error at all. – jackmott Apr 01 '16 at 16:11
  • Have you searched for the error (“unrecognized method call”) with C# tag? e.g [Search text contains with QueryOver](http://stackoverflow.com/questions/11601590/search-text-contains-with-queryover) – Guy Coder Apr 01 '16 at 17:01
  • I have, but haven't found anything that is obviously applicable so far. I'm only checking for equality on simple types. – jackmott Apr 01 '16 at 17:05
  • I don't use PostgresSQL 9.5 but found [SQLProvider on Nuget is outdated (was Postgresql Missing Columns)](https://www.bountysource.com/issues/32012360-sqlprovider-on-nuget-is-outdated-was-postgresql-missing-columns) and [PostgreSQL Npgsql Versioning](https://github.com/fsprojects/SQLProvider/issues/188) The solution was to not use the NuGet version and install the latest version from the site. – Guy Coder Apr 01 '16 at 17:19
  • Was worth a try, but building with the latest SqlProvider code from github still nets the same error. – jackmott Apr 01 '16 at 17:31
  • It looks like as of Oct 15 2015, leftOuterJoin just isn't supported by SqlProvider – jackmott Apr 01 '16 at 19:30

1 Answers1

3

It looks like as of Oct 15 2015, leftOuterJoin just isn't supported by SqlProvider

Quoting maintainer pezipink from github

For outer joins, the leftOuterJoin keyword is not supported as it requires us to implement groupJoin which in turns requires a bunch of other stuff that has not been done yet. If your join is between fields with foreign keys setup, you can get it to left join by using the special (!!) operator within the for.. syntax, eg for prod in (!!) table1.MyForeignKey do...

jackmott
  • 1,112
  • 8
  • 16