Closed object closed 5 years ago
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.
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:
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.
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.
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?
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.
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?
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.
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.
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
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.
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.
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.
That's good news! I will test it in our project.
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:
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+SqlQueryable
1[FSharp.Data.Sql.Common.SqlEntity]).GroupJoin(value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable
1[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](FSharpExpr
1 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.headBut 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