Can't joins tables from different databases

[tforkmann]

Description

Can't joins tables from different databases

Repro steps´

1. GetDataContext for Db1 and Db2

   let dmDme = SqlDme.GetDataContext connDme
   let dbEmd = SqlEmd.GetDataContext connEmd

2. Execute Query

let data () =
    try
        query { for  gasmeters in dbDme.Export.EmdInterfaceGaszaehler do
                join hz in dbEmd.Gas.StHeizzentrale  on (gasmeters.HzId = hz.HzId)
                join measures in dbEmd.Gas.MeStundenMesswert on (gasmeters.HzId = measures.HzId)
                where (measures.DatumH >= DateTime(2019,2,1) && measures.DatumH < DateTime(2019,2,2) )
                select ( hz.Name,hz.ZpBez,hz.HzId,measures.DatumH,measures.Zaehlerstand) }
        |> Seq.toArray
    with
    | exn -> 
        printfn "Could no finish query %A" exn.Message        
        failwithf "Could no finish query %A" exn.Message

Expected behavior

Execute query across two different databases

Actual behavior

Can't execute query because its only using the first datacontext

Known workarounds

None

Related information

• Used database: MSSQL
• Operating system: Win 10
• .NET Runtime, CoreCLR or Mono Version: dotnet core

[tforkmann]

@Thorium Is that somehow possible?

[Thorium]

As far as I know there is no direct support to this. How would we know the databases are located in the same server?

Of course if you have 2 separate connections and do some kind of Seq.toList to do the join on client side, that does work. ;-)

[tforkmann]

I know I can join manually... ;)

Maybe you could read the connection string and check for same server?

Tuomas Hietanen notifications@github.com schrieb am Do. 27. Juni 2019 um 15:34:

As far as I know there is no direct support to this. How would we know the databases are located in the same server?

Of course if you have 2 separate connections and do some kind of Seq.toList to do the join on client side, that does work. ;-)

[tforkmann]

old!

[mcspud]

As a separate note, you shouldn't be able to do this anyway. There is no way to ensure referential integrity between databases, so if you need to do this you will either need to set up a 3rd aggregate database, or do the join in memory alone