Is there a way to 'seal' usages of aliases within a Dynamic Query? I looked at TargetAlias but it seems not to be applicable here.

For example, below are two methods creating DynamicQuerys - one uses the other as a dynamic table. This works but only because I used "y" in CreateAccountsTransactionAllocationStateQuery(). If I used "x" then it fails, presumably because it is merging the two queries rather than treating them as distinct units.

The full SQL query I hand-wrote can just use "x" in both cases.

What I would like to do is create a small library of DynamicQuery-creating methods like CreateAllocationSummaryQuery() and then reuse them in various other queries some which will be multi-level. Having to know what aliases have already been used is a code-smell so that is the reason for asking whether there is a way to 'hide' the implementation detail somehow.

        static DynamicQuery CreateAllocationSummaryQuery()
        {
            var qf = new QueryFactory();

            return qf.Allocation
                .From(QueryTarget
                    .InnerJoin(CreateAllocationItemSummaryQuery().As("x"))
                    .On(AllocationFields.ID == qf.Field("x", "AllocationID")))
                .Select(() => new
                              {
                                  FeeOrCommissionStatementID = AllocationFields.AccountsTransactionID.As("FeeOrCommissionStatementID").ToValue<int>(),
                                  TotalAllocatedItems = qf.Field("x", "TotalAllocatedItems").Sum().As("TotalAllocatedItems").ToValue<int>(),
                                  TotalAllocatedAmount = qf.Field("x", "TotalAllocatedAmount").Sum().As("TotalAllocatedAmount").ToValue<decimal>(),
                                  LastAllocationDate = AllocationFields.TaxpointDate.Max().As("LastAllocationDate").ToValue<DateTime>()
                              }
                )
                .GroupBy(AllocationFields.AccountsTransactionID);
        }

        static DynamicQuery CreateAccountsTransactionAllocationStateQuery()
        {
            var qf = new QueryFactory();

            return qf.AccountsTransaction
                .From(QueryTarget
                    .LeftJoin(CreateAllocationSummaryQuery().As("y"))
                    .On(AccountsTransactionFields.ID == qf.Field("y", "FeeOrCommissionStatementID")))
                .Select(() => new
                              {
                                  ID = AccountsTransactionFields.ID.ToValue<int>(),
                                  LegalBodyID = AccountsTransactionFields.LegalBodyID.ToValue<int?>(),
                                  //GrossTransactionAmount = AccountsTransactionFields.Amount.Sum().ToValue<decimal>() + AccountsTransactionFields.VATAmount.Sum().ToValue<decimal>(),
                                  AllocatedAmount = qf.Field("y", "TotalAllocatedAmount").Sum().ToValue<decimal>(),
                                  TotalAllocatedItems = qf.Field("y", "TotalAllocatedItems").Count().ToValue<int>(),
                                  LastAllocationDate = qf.Field("y", "LastAllocationDate").Max().ToValue<DateTime>()
                              }
                )
                .GroupBy(AccountsTransactionFields.ID, AccountsTransactionFields.LegalBodyID, AccountsTransactionFields.TaxpointDate);
        }

• What is the generated SQL for that code?
• What is your hand-wrote SQL?
• What is your LLBLGen version and runtime library version?
• What is wrong if you use "X" as alias in your second method (bad SQL, exception, unexpected results, etc)?

Here is the hand-written SQL using 'x' for both virtual tables

SELECT
    at.ID,
    at.LegalBodyID,
    SUM(COALESCE(at.Amount, 0) + COALESCE(at.VATAmount, 0)) AS GrossTransactionAmount,
    SUM(x.TotalAllocatedAmount) AS AllocatedAmount,
    COUNT(x.TotalAllocatedItems) AS TotalAllocatedItems,
    MAX(x.LastAllocationDate) AS LastAllocationDate
FROM AccountsTransaction at
LEFT JOIN
(
    SELECT
        a.AccountsTransactionID AS FeeOrCommissionStatementID,
        SUM(x.TotalAllocatedItems) AS TotalAllocatedItems,
        SUM(x.TotalAllocatedAmount) AS TotalAllocatedAmount,
        MAX(at.TaxpointDate) AS LastAllocationDate
    FROM Allocation a JOIN AccountsTransaction at ON at.ID = a.ID
    JOIN
    (
        SELECT
            ai.AllocationID,
            COUNT(ai.ID) AS TotalAllocatedItems,
            SUM(ani.Amount) AS TotalAllocatedAmount
        FROM AllocationItem ai JOIN AnalysisItem ani ON ani.ID = ai.ID
        GROUP BY ai.AllocationID
    ) x on x.AllocationID = a.ID
    GROUP BY a.AccountsTransactionID
) x ON x.FeeOrCommissionStatementID = at.ID
GROUP BY at.ID, at.LegalBodyID

Here is the SQL generated when using 'y' as one of the virtual tables. It produces exactly the same output as the hand-written SQL.

DECLARE @p2 int; SET @p2='0'
DECLARE @p4 int; SET @p4='0'

SELECT
  [LPA_L2].[ID],
  [LPA_L2].[LegalBodyID],
  SUM((COALESCE([LPA_L2].[Amount],
  @p2) + COALESCE([LPA_L2].[VATAmount],
  @p4))) AS [GrossTransactionAmount],
  SUM([LPA_y1].[TotalAllocatedAmount]) AS [TotalAllocatedAmount],
  COUNT([LPA_y1].[TotalAllocatedItems]) AS [TotalAllocatedItems],
  MAX([LPA_y1].[LastAllocationDate]) AS [LastAllocationDate] 
FROM
  ( (
SELECT
  [Allocation].[AccountsTransactionID] AS [FeeOrCommissionStatementID],
  SUM([LPA_x3].[TotalAllocatedItems]) AS [TotalAllocatedItems],
  SUM([LPA_x3].[TotalAllocatedAmount]) AS [TotalAllocatedAmount],
  MAX([AccountsTransaction].[TaxpointDate]) AS [LastAllocationDate] 
FROM
  (( (
SELECT
  [AllocationItem].[AllocationID],
  COUNT([AllocationItem].[ID]) AS [TotalAllocatedItems],
  SUM([AnalysisItem].[Amount]) AS [TotalAllocatedAmount] 
FROM
  ( [AnalysisItem]  
LEFT JOIN
  [AllocationItem] ON [AnalysisItem].[ID] = [AllocationItem].[ID]) 
GROUP BY
  [AllocationItem].[AllocationID]) [LPA_x3]  
INNER JOIN
  [Allocation] ON [Allocation].[ID] = [LPA_x3].[AllocationID]) 
INNER JOIN
  [AccountsTransaction] ON [AccountsTransaction].[ID] = [Allocation].[ID]) 
GROUP BY
  [Allocation].[AccountsTransactionID]) [LPA_y1]  
RIGHT JOIN
  [AccountsTransaction] [LPA_L2] ON [LPA_L2].[ID] = [LPA_y1].[FeeOrCommissionStatementID]) 
GROUP BY
  [LPA_L2].[ID],
  [LPA_L2].[LegalBodyID]

Here is the SQL generated when using 'x' for both virtual tables Exception thrown is "An exception was caught during the execution of a retrieval query: The multi-part identifier "TIPS.dbo.AnalysisItem.Amount" could not be bound."

DECLARE @p2 int; SET @p2='0'
DECLARE @p4 int; SET @p4='0'

SELECT
  [LPA_L2].[ID],
  [LPA_L2].[LegalBodyID],
  SUM((COALESCE([LPA_L2].[Amount],
  @p2) + COALESCE([LPA_L2].[VATAmount],
  @p4))) AS [GrossTransactionAmount],
  SUM([LPA_x1].[TotalAllocatedAmount]) AS [TotalAllocatedAmount],
  COUNT([LPA_x1].[TotalAllocatedItems]) AS [TotalAllocatedItems],
  MAX([LPA_x1].[LastAllocationDate]) AS [LastAllocationDate] 
FROM
  ( (
SELECT
  [Allocation].[AccountsTransactionID] AS [FeeOrCommissionStatementID],
  SUM([LPA_x3].[TotalAllocatedItems]) AS [TotalAllocatedItems],
  SUM([LPA_x3].[TotalAllocatedAmount]) AS [TotalAllocatedAmount],
  MAX([AccountsTransaction].[TaxpointDate]) AS [LastAllocationDate] 
FROM
  (( (
SELECT
  [AllocationItem].[AllocationID],
  COUNT([AllocationItem].[ID]) AS [TotalAllocatedItems],
  SUM([AnalysisItem].[Amount]) AS [TotalAllocatedAmount] 
FROM
  [AllocationItem]   
GROUP BY
  [AllocationItem].[AllocationID]) [LPA_x3]  
INNER JOIN
  [Allocation] ON [Allocation].[ID] = [LPA_x3].[AllocationID]) 
INNER JOIN
  [AccountsTransaction] ON [AccountsTransaction].[ID] = [Allocation].[ID]) 
GROUP BY
  [Allocation].[AccountsTransactionID]) [LPA_x1]  
RIGHT JOIN
  [AccountsTransaction] [LPA_L2] ON [LPA_L2].[ID] = [LPA_x1].[FeeOrCommissionStatementID]) 
GROUP BY
  [LPA_L2].[ID],
  [LPA_L2].[LegalBodyID]

LLBLGen Version is 4.0 final (June 26th, 2013); runtime version is 4.0.13.627

The problem is that the engine runs into some fields which target a derived table. The only way to know which derived table they're referring to is by alias. So it does a visit on the set of derived tables, and determines which are known and by which alias. If you re-use the alias in nested derived tables (which works in SQL Server, it doesn't in some other DB's btw, so it's not really ok) these will give false positives and things fail.

The code I refer to above is mainly used for linq, however with the re-use of aliases in your queryspec query you run into a limitation of it which in practice isn't common (as aliases are assigned by hand in queryspec).

To work with aliases inside the methods you wrote, you can do the following (as they're used inside the methods only anyway)

var alias = "A" + Guid.NewGuid().ToString().GetHashCode();

GetHashCode returns an int, so it's not going to overflow the alias length. The guid assures you're not re-using aliases in 2 calls of the same method.

That's a reasonable workaround. Thanks.