Ordering of joins can cause runtime exception with (!!)

[cmeeren]

Description

I am encountering a problem with the (!!) operator when used on a non-FK join (related: #590).

The following code compiles and runs fime:

query {
  for c in ctx.Dbo.Card do
  join lio in ctx.Dbo.LastInOut on (c.CdCardNumber = lio.LioCardNumber)
  join ac in ctx.Dbo.AccessControl on (c.CdCardNumber = ac.AcCardNumber)
  join pLio in (!!) ctx.Dbo.Project on (lio.LioProjectId = pLio.PrId)
  for pAc in ac.``dbo.Project by prId`` do
  select 0
}

However, if I switch the join pLio and for pAc lines, I get a runtime error (compiles fine):

query {
  for c in ctx.Dbo.Card do
  join lio in ctx.Dbo.LastInOut on (c.CdCardNumber = lio.LioCardNumber)
  join ac in ctx.Dbo.AccessControl on (c.CdCardNumber = ac.AcCardNumber)
  for pAc in ac.``dbo.Project by prId`` do
  join pLio in (!!) ctx.Dbo.Project on (lio.LioProjectId = pLio.PrId)
  select 0
}

System.Exception: Unknown: _arg1.Item3.get_DataContext().CreateRelated(_arg1.Item3, "FK_AccessControl_Project", "dbo.Project", "prId", "dbo.AccessControl", "acProjectId", Parents).Join(op_BangBang(Convert(Convert(Convert(FSI_0002.ctx)).CreateEntities("dbo.Project"))), pAc => _arg1.Item2.GetColumn("lioProjectId"), pLio => pLio.GetColumn("prId"), (pAc, pLio) => new AnonymousObject`5(Item1 = _arg1.Item1, Item2 = _arg1.Item2, Item3 = _arg1.Item3, Item4 = pAc, Item5 = pLio))
   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@1727-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 q)
   at <StartupCode$FSI_0045>.$FSI_0045.main@()

I don't know what more information I can provide. Let me know if you need anything. Unfortunately I have no idea how to create a minimal, isolated repro since this is dependent on our DB schema.

Known workarounds

Change the order of the lines (though I can't guarantee this is possible for all cases).

Related information

• MSSQL
• SQLProvider 1.1.62
• FSI (referencing net451 version of SQLProvider)

[Thorium]

To understand this better: What happens if you do both joins with (!!)?

[cmeeren]

The exact same behavior. And if I only have the other join with (!!), it works either way.

By the way, there were some errors in the first query. Fixed now.

[Thorium]

One workaround is always creating a view and querying that with SQLProvider, views are visible as tables.

Just saying because multiple left-joins usually leads to large item counts and filtering in in join-conditions, where correct indexing starts to be important. And indexes are more easy to monitor with SQL-tools.

LEFT JOIN (
    select ItemId, min(Created) as FirstItem, sum(something) as Sum
    from aTable
    group by ItemId
) as other on (mything.Id = other.ItemId and other.FirstItem<'2019-01-01') 

SQLProvider can go quite far, but it isn't a full fsharp-to-sql compiler.

[cmeeren]

Yep. Ideally I'd use SqlClient so I can use all the SQL tooling and have full control over the SQL, but our DB is currently still 2005, which SqlClient doesn't support, so... =/ (Update: Seems that's only a design-time limitation; I duplicated the DB on a local SQL Express instance, and can use that for design-time instead with SqlClient.)

[Thorium]

Please test, this should be fixed in NuGet package 1.1.63

[cmeeren]

Great, thanks!