How to left join on two differente database/Connection key

[Leojet86]

Hello, I'm trying stuff on Serene and I'm stuck on something since the last two days.

Let's say I have my Administration Module with a sub-module Tenants, on DefaultDB connectionString, all coming from Serene template.

And I also have another module called Service, with a sub-module Customers, on another connectionString called CustomerDB.

In SSMS, I can left join on [DefaultDB].[dbo].[Tenants].[customerID] = [CustomerDB].[dbo].[Customers].[ID] all is fine and I get my data.

But in Serene, Sergen generated code with object as TenantsRow and CustomersRow.

Both has their own connectionKey such as:

[ConnectionKey("Default"), TableName("[dbo].[Tenants]")] and [ConnectionKey("CustomerDB"), TableName("[dbo].[Customers]")]

How can I do a left join, on a row, on another ConnectionKey ? Ex:

[ConnectionKey("Default"), TableName("[dbo].[Tenants]")]
[LeftJoin("acstm", "[CustomerDB].[dbo].[Customers]", "acstm.[id] = T0.[id]")]
public sealed class TenantsRow : Row, IIdRow, INameRow
{
. . . 

this does not work at all. It tries to search for [CustomerDB].[dbo].[Customers] inside the DB linked to ConnectionKey "Default" where of course it does not exist.

[adalberto-argente]

Sugestion

Create a view on Default Database using data from CustomerDB

Sample

Create view Customers as Select * from [CustomerDB].[dbo].[Customers]

[Leojet86]

@nydus-zz, Oh I had forgotten about view in sql! This should work yes. Thanks for the suggestion!

[Leojet86]

Quick follow up. It does indeed work. I Created a view in SQL where my left join on two DB is done.

Using sergen I generated the ViewRow and could get my data easily, thanks!