Can't join two queries when one has a `where` clause

[MaybeSacred]

Describe the bug When running a composable query with two composed queries which each have where clauses, an exception is thrown. It appears to be the case that the FilterClause is added to the join condition

To Reproduce Sample code

let organizations (ctx :Sql.dataContext) = query {
    for c in ctx.Dbo.Organizations do
    where (c.Deleted = false)
    select c
    }

Either second query definition:

let users (ctx :Sql.dataContext) = query {
    for c in ctx.Dbo.Users do
    where (c.Deleted = false)
    select c
    }

let users (ctx :Sql.dataContext) = query {
    for c in ctx.Dbo.Users do
    select c
    }

Composed query:

let organizationsByUsers (ctx :Sql.dataContext) = query {
    for o in organizations ctx do 
    join u in users ctx on (o.Id = u.OrganizationId)
    select (o, u)
    }

A query that does work:

let organizationsByUsers (ctx :Sql.dataContext) = query {
    for o in organizations ctx do 
    join u in ctx.Dbo.Users on (o.Id = u.OrganizationId)
    where (u.Deleted = false)
    select (o, u)
    }

Expected behavior The first composable query should work, joining across two other queries

Additional context Two errors are thrown, one when the second query has a where clause, and the second when there is no where clause:

System.Exception: 'Unexpected join destination entity expression (FilterClause (And ([("c", KeyColumn "Deleted", Equal, Some false)], None), BaseTable ("c", { Schema = "Dbo" Name = "User" Type = "" }))).'

System.Exception: 'Unexpected join destination entity expression (Projection (value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).Select(c => c), BaseTable ("", { Schema = "Dbo" Name = "User" Type = "" }))).'

[MaybeSacred]

This seems to be related to #396

[Thorium]

I think the problem is composing functions. organizations and users are Sql.dataContext -> IQueryable<_>

So if you share your ctx and have these as nested functions inside a bigger function, it probably works like here.

But deserializing functions to nested SQL queries is an endless rabbit hole: Is it F#-to-SQL-compiler? What if there are side effects?

The other issue is not related as it's not trying to serialize F# functions to part of LINQ queries. In the same composable query page there are a few workarounds that you can use.

[MaybeSacred]

As the final example shows, the nested queryables do work in certain instances. I have several working queries that compose several function layers deep:

let groups (ctx :Sql.dataContext) = query {
    for c in ctx.Dbo.Group do
    where (c.Deleted = false)
    select c
    }

let users (ctx :Sql.dataContext) = query {
    for c in ctx.Dbo.Users do
    where (c.Deleted = false)
    select c
    }

let groupsByUsers (ctx :Sql.dataContext) =
    query {
        for u in users ctx do 
        join ug in ctx.Dbo.UserGroup on (u.UserId = ug.UserId)
        join g in ctx.Dbo.Group on (ug.GroupId = g.Id)
        where (ug.Deleted = false && g.Deleted = false)
        select (u, ug, g)
    }

let meetingsByGroups (ctx :Sql.dataContext) = query {
    for g in groups ctx do
    join mg in ctx.Dbo.MeetingGroup on (g.Id = mg.GroupId)
    join m in ctx.Dbo.Meeting on (mg.MeetingId = m.Id)
    where (mg.Deleted = false && m.Deleted = false)
    select (g, mg, m)
    }

let meetingsByGroupsByUsers (ctx :Sql.dataContext) = query {
    for (u, _, g) in groupsByUsers ctx do
    join (g', gm, m) in meetingsByGroups ctx on (g.Id = g'.Id)
    select (u, g, gm, m)
    }

let meetingByGroupForUser (ctx :Sql.dataContext) userId meetingId = 
    let id = Users.value userId
    query {
        for (u,_, _, m) in meetingsByGroupsByUsers ctx do
        where (m.MeetingId = meetingId && u.Id = id)
        select m
    }

Note that meetingsByGroupsByUsers joins on the same table id, my suspicion is that a bug causes the join to be pushed to the first table, but in this case that happens to be the same LINQ/SQL anyways

An example that also does not work and generates the same exception as above, with no nested functions:

let testGroupsForOrganization (ctx :Sql.dataContext) id =
    let orgQ = query {
        for c in ctx.Dbo.Organization do
        where (c.Deleted = false)
        select c
        }
    let groupQ = query {
        for sg in ctx.Dbo.Group do
        where (sg.Deleted = false)
        select sg
        }
    query {
    for o in orgQ do 
    join g in groupQ on (o.Id = g.OrganizationId)
    where (o.ExternalId = id)
    select (o, g)
    }

Unexpected join destination entity expression (FilterClause (And ([("g", KeyColumn "Deleted", Equal, Some false)], None), BaseTable ("g", { Schema = "Dbo" Name = "Group" Type = "" }))). Stack Trace: LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) line 718 QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) line 1815 IQueryMethods.Execute[a,b](FSharpExpr`1 q) line 1928

[Thorium]

Ok... :-) What is the SQL you expect the non-working query should generate?

Currently we don't do nested select clauses like this:

select d.b from (select a as b from c) d

...because:

• it's not trivial, syntax differences between databases.
• not even supported in all databases SQLProvider supports, as far as I know
• hard to trace hitting DB indexes, which is already hard with SQLProvider
• generating huge and slow sql clauses, usually inefficient

I know EF does it, but debugging what happens in EF is just pain, and EF is a nightmare for database administrators.

So, with nested queries to be compiled to non-nested selects, it's basically working well on 90% of the cases, but then if the table aliases are changed multiple times, LINQ trees comes messy. We can add support for these case-by-case but I expect we'll never capture the full expressiveness what SQL can do. That's why, when your SQL starts to reach...let's say 10 lines... I'd just create a sql view and query that view with SQLProvider just like a normal table.

[Thorium]

Thiking of this, we could add here something like

and SelectData = 
   | LinkQuery of LinkData 
   | GroupQuery of GroupData 
   | CrossJoin of alias * Table 
   | SubQuery of SqlExpr*SelectData // this would be new

and instead of raising error, put the nested things there, then when calling convert, try to get the sub-query SqlQuery and merge it with main SqlQuery, and try to detect alias-conflicts and other possible problems, it would result to the same query as:

let testGroupsForOrganization (ctx :Sql.dataContext) id =
    query {
    for o in ctx.Dbo.Organization do 
    join g in ctx.Dbo.Group on (o.Id = g.OrganizationId)
    where (o.Deleted = false && o.ExternalId = id && g.Deleted = false)
    select (o, g)
    }

...but then the next problem is that, what if you do "take 3" in your join-sub-query, it shouldn't be the same as taking 3 of the final result.