"unrecognised method call value" on a left outer join

[object]

Description

I tried to execute left join queries both on Oracle and MS SQL Server databases but they result in exception.

Repro steps

I first tried Oracle, then MS SQL Server, here what I did:

1. Assuming I have tables SnapshotStore and EventJournal, both having column PersistenceId.

query { for s in db.Dbo.SnapshotStore do
leftOuterJoin e in db.Dbo.EventJournal on (s.PersistenceId = e.PersistenceId) into result
for r in result.DefaultIfEmpty() do
select (s.PersistenceId, r.PersistenceId) }
|> Seq.head

This query resulted in the following exception:

System.Exception: unrecognised method call value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable1[FSharp.Data.Sql.Common.SqlEntity]).GroupJoin(value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable1[FSharp.Data.Sql.Common.SqlEntity]), s => s.GetColumn("PersistenceID"), e => e.GetColumn("PersistenceID"), (s, result) => new AnonymousObject2(Item1 = s, Item2 = result.DefaultIfEmpty())) at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedOuter(CanEliminate canElim, FSharpExpr tm) at Microsoft.FSharp.Linq.QueryModule.clo@1727-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr1 q)
at <StartupCode$FSI_0005>.$FSI_0005.main@()

Then I tried different syntax, using (!!) operator like it is shown in examples:

query { for s in db.Dbo.SnapshotStore do
for e in (!!) s.main.EventJournal by PersistenceId do
for r in result.DefaultIfEmpty() do
select (s.PersistenceId, r.PersistenceId) }
|> Seq.head

But this doesn't compile, the error "main.EventJournal by PersistenceId is not defined". I tried different variations here but the expression inside backticks is not recognized.

This is strange because there are some examples using both syntax conventions and a unit test, but I am not able to make outer join working at all.

Related information

.NET FX 4.61
SQLProvider 1.1.42

[object]

UPDATE: The following syntax worked:

query { for s in db.Dbo.SnapshotStore do
join e in (!!) db.Dbo.EventJournal on (s.PersistenceId = e.PersistenceId)
select (s.PersistenceId, e.PersistenceId) }
|> Seq.head

But in this case the generated statement is INNER JOIN, not OUTER JOIN.

[Thorium]

This is duplicate of #235.

You can do LEFT (OUTER) JOIN as follows:

  query { 
    for s in db.Dbo.SnapshotStore do
    for e in (!!) ``s.main.EventJournal by PersistenceId`` do
    select (s.PersistenceId, e.PersistenceId) }
  |> Seq.head

leftOuterJoin as Linq-operator is not supported, because it would need some work:

• Currently the join projections skip the LINQ-lambda. It shouldn't.
• ...and group-by should be implemented to support multiple tables groupBy across different tables #527
• Also, fix nullability issues on option types Left join operator - broken? #540

[Thorium]

I have had a few cases where I want to left-join by non-primary key, for example:

   -- Get all the people, and if they have a new car:
   SELECT p.Name, c.Brand, c.RegistrationYear
   FROM person AS p 
   LEFT JOIN cars AS c ON (p.Id = c.PersonId AND c.RegistrationYear > 2015)

Because this kind of queries get more and more complex by time, and because less people understand LINQ than SQL, and because SQL index hitting monitoring is not so trivial, I ended up creating SQL view and querying that with SQLProvider. The views are as easy to query as the tables.

[object]

What doesn't work is to get provider to understand expression s.main.EventJournal by PersistenceId. I get "The value or constructor '`s.main.EventJournal by PersistenceId' is not defined. I tried to change it to whatever might sound reasonable but none of these stringified expressions were recognized as valid. This is MS SQL database and I am doing from an fsx script. Standard notation is recognized but AFAIK in outer joins I must use the one similar to what you wrote.

[Thorium]

I do use fktable by key and it works. Hmm. I'm using case-insensitive database, are you using case sensitive SQL database?

What is result.DefaultIfEmpty you referred to?

[object]

AFAIK MS SQL is case-insensitive, but perhaps provider expects different casing anyway. I will check.
result.DefaultIfEmpty is not significant here, I copied it from other examples. The main challenge is to make provider understand string expression.

[object]

Hmm, you mentioned using fktable, but in the test I mentioned there is no foreign key involved. I have two tables, both of them have PersistenceId as primary key and I tried to outer join on them. So such scenario isn't currently supported?

[Thorium]

Right. I was thinking could we do something like for e in (!!) s.LeftJoinBy(someLambda) or something, but currently there is no easy way to do this. Sub-queries in IN-clauses are supported and on some cases can be used as workaround.

[object]

I see. Yes, doing (!!) left join over lambdas would solve it, I could verify/fix Oracle implementation but it looks like common part would be a demanding work. I will check if I can use subquery.

[Thorium]

Now that I think of it, from the users point of view, because (!!) is IQ<_> -> IQ<_>, I would like to just be able to prefix any join with (!!) to turn it to left join.

        query {
            for cust in dc.Main.Customers do
            join ord in (!!) dc.Main.Orders on (cust.CustomerId = ord.CustomerId)
            select 1
        } |> Seq.toList

[object]

Yes, exactly. Lightweight and easy to understand.
Actually this is how I though it works and tried it in the beginning, only to discover that it still generates inner joins.

[Thorium]

My random thougths on this...
The basic infrastructure is already there, SqlRuntime.Linq.fs is setting many "Join"s explicitly to OuterJoin = false.
Instead of that, it could be just pattern-matched from the Linq-expression-tree.
But to do that, the op_bangbang (!!) should be somehow visible in the tree.
I can't see that in there. So maybe the bangbang is either compiled away or evaluated at some point before constructing the expression tree.
That would mean that the operator should do some effect to the expression tree (or type hierachy) to be then found, e.g. similar way as sortBy changes SqlQueryable to SqlOrderedQueryable.

[object]

Yes, it looks like it is compiled away, since resulting statement is an inner join. I am not familiar with this code but can try to have a look sometime next week.