MSSQL: AddJoin not using alias, thus its not usable in some cases

[florianendrich]

If you join two tables with the same exposed names from e.g. different Schemas, the Initialize stored procedure does not use an alias, so creation fails.

Example

Here is a short example to illustrate the setup:

var setup = new SyncSetup(new string[] { "Sales.Customers", "Sales.Orders", "HR.Orders" });

var plsFilter = new SetupFilter("Customers", "Sales");
plsFilter.AddJoin(Join.Left, "Orders", "Sales").On("Customers", "CustomerID", "Orders", "CustomerID", "Sales", "Sales");
plsFilter.AddJoin(Join.Left, "Orders", "HR").On("Customers", "CustomerID", "Orders", "CustomerID", "Sales", "HR");

plsFilter.AddParameter("OrderLimit", DbType.Int16);
plsFilter.AddWhere("OrderTotal", "Orders", "OrderLimit", "Sales");

This results in SQL similar to the following:

CREATE PROCEDURE [Sales].[Customers_OrderLimitinitialize]
    @sync_min_timestamp bigint = NULL,
    @OrderLimit smallint
AS
BEGIN
;WITH 
  [Customers_tracking] AS (
    SELECT [CT].[CustomerID], 
    CAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [sync_update_scope_id], 
    [CT].[SYS_CHANGE_VERSION] as [sync_timestamp],
    CASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]
    FROM CHANGETABLE(CHANGES [Sales].[Customers], @sync_min_timestamp) AS [CT]
    )
SELECT DISTINCT 
      [base].[CustomerID]
    , [base].[FirstName]
    , [base].[LastName]
    , [base].[Email]
    , [base].[PhoneNumber]
    , [side].[sync_row_is_tombstone] as [sync_row_is_tombstone]
FROM [Sales].[Customers] [base]
LEFT JOIN [Customers_tracking] [side] ON [base].[CustomerID] = [side].[CustomerID]
LEFT JOIN [Orders] ON [base].[CustomerID] = [Orders].[CustomerID]
LEFT JOIN [Orders] ON [base].[CustomerID] = [Orders].[CustomerID]

WHERE (
(
 (
   ([Sales].[Orders].[OrderTotal] = @OrderLimit)
  )
 OR [side].[sync_row_is_tombstone] = 1
)
AND 
    ([side].[sync_timestamp] > @sync_min_timestamp OR @sync_min_timestamp IS NULL)
)
UNION
SELECT
      [side].[CustomerID]
    , [base].[FirstName]
    , [base].[LastName]
    , [base].[Email]
    , [base].[PhoneNumber]
    , [side].[sync_row_is_tombstone] as [sync_row_is_tombstone]
FROM [Sales].[Customers] [base]
RIGHT JOIN [Customers_tracking] [side] ON [base].[CustomerID] = [side].[CustomerID]
WHERE ([side].[sync_timestamp] > @sync_min_timestamp AND [side].[sync_row_is_tombstone] = 1);

END

As you can see, the JOIN selection does not set an alias.

Error Encountered

As MSSQL can not tell which table to join, this results in: {"The objects \"Orders\" and \"Orders\" in the FROM clause have the same exposed names. Use correlation names to distinguish them."}

Suggested Fix Allow setting an alias on AddJoin e.g.

var plsFilter = new SetupFilter("Customers", "Sales");
plsFilter.AddJoin(Join.Left, "Orders", "Sales").Alias("SalesOrders").On("Customers", "CustomerID", "Orders", "CustomerID", "Sales", "Sales");
plsFilter.AddJoin(Join.Left, "Orders", "HR").Alias("HROrders").On("Customers", "CustomerID", "Orders", "CustomerID", "Sales", "HR");

[florianendrich]

Forgot to mention that to have AddJoin from different schemas, its probably necessary to change from QuotedShortName to QuotedFullName in SqlBuilderProcedure.cs CreateFilterCustomJoins.