LINQ join query to two tables with the same model

Question

I have two tables in a database with the same structure. The tables have many columns, the types and names are absolutely the same. Let's say their names are OldTable and NewTable. In my project I have one model for these tables. I need to perform an inner join on tables. When i try it i have SQL Query with the same table, for example:

Select column1 from OldTable oldData
Inner Join OldTable newData on oldData.Column1 = newData.Column1

What is wrong?

Sample code that i use:

var oldCtx = _oldDataSource.DataContext.Rows;
var newCtx = _newDataSource.DataContext.Rows;

var query = from oldData in oldCtx
            join newData in newCtx
            on new { oldData.Column1, oldData.Column2, oldData.Column3, oldData.Column4, oldData.Column5 }
            equals new { newData.Column1, newData.Column2, newData.Column3, newData.Column4, newData.Column5 }
            where oldData.StartTime >= dateStart && oldData.StartTime < dateStart + timeSpan
            select newData;

var newCdrs = query.ToList();

Result query is:

DECLARE @dateStart TimeStamp -- DateTime
SET     @dateStart = TIMESTAMP '2024-01-22 15:54:33.000000'
DECLARE @CallStartTime TimeStamp -- DateTime
SET     @CallStartTime = TIMESTAMP '2024-01-22 15:54:34.000000'

SELECT
    newData.Column1,
    newData.Column2,
    newData.Column3,
    newData.Column4,
    newData.Column5,
.....

FROM
    DEV3.OldTable  oldData
        INNER JOIN DEV3.OldTable newData ON (oldData.COLUMN1 = newData.COLUMN1 OR oldData.COLUMN1 IS NULL AND newData.COLUMN1 IS NULL) AND (oldData.COLUMN2 = newData.COLUMN2 OR oldData.COLUMN2 IS NULL AND newData.COLUMN2 IS NULL) AND (oldData.COLUMN3 = newData.COLUMN3 OR oldData.COLUMN3 IS NULL AND newData.COLUMN3 IS NULL) AND (oldData.COLUMN4 = newData.COLUMN4 OR oldData.COLUMN4 IS NULL AND newData.COLUMN4 IS NULL) AND (oldData.COLUMN5 = newData.COLUMN5 OR oldData.COLUMN5 IS NULL AND newData.COLUMN5 IS NULL)
WHERE
    oldData.START_TIME >= :dateStart AND oldData.START_TIME < :CallStartTime

But I expected JOIN DEV3.NewTable

Answer 1

In linq2db for such cases you can use TableName, SchemaName, ServerName extensions. They are defined for ITable interface.

var query = from oldData in oldCtx
            join newData in newCtx.TableName("NewData")
            on new { oldData.Column1, oldData.Column2, oldData.Column3, oldData.Column4, oldData.Column5 }
            equals new { newData.Column1, newData.Column2, newData.Column3, newData.Column4, newData.Column5 }
            where oldData.StartTime >= dateStart && oldData.StartTime < dateStart + timeSpan
            select newData;

Also for such queries, you do not need two DataContexts, one is enough.

var rows = _oldDataSource.DataContext.Rows;

var query = from oldData in rows
            join newData in rows.TableName("NewData")
            on new { oldData.Column1, oldData.Column2, oldData.Column3, oldData.Column4, oldData.Column5 }
            equals new { newData.Column1, newData.Column2, newData.Column3, newData.Column4, newData.Column5 }
            where oldData.StartTime >= dateStart && oldData.StartTime < dateStart + timeSpan
            select newData;