I have this test query and it produces 12 rows as expected.

            var query = qf.CommissionStatement
                .From(QueryTarget
                    .LeftJoin(CommissionStatementEntity.Relations.CommissionItemEntityUsingCommissionStatementID)
                    .LeftJoin(AllocationHelper.CreateAllocationSummaryQuery().As(AllocationSummaryAlias))
                    .On(CommissionStatementFields.ID == qf.Field(AllocationSummaryAlias, "FeeOrCommissionStatementID"))
                )
                .Select(() => new CommissionStatementAllocationTargetInfo
                              {
                                  CommissionStatementID = CommissionStatementFields.ID.ToValue<int>(),
                                  ProviderID = CommissionStatementFields.ProviderID.ToValue<int>(),
                                  Description = CommissionStatementFields.Description.ToValue<string>(),
                                  GrossFeesTotal = Functions.IIF(CommissionItemFields.PostingAccountID == feesAwaitingPaymentID, grossFeesTotalExpression, 0m).Sum().As("GFT").ToValue<decimal>(),
                                  GrossCommissionsTotal = Functions.IIF(CommissionItemFields.PostingAccountID != feesAwaitingPaymentID, grossCommissionsTotalExpression, 0m).Sum().As("GCT").ToValue<decimal>(),
                                  IsSelected = CreateCommissionStatementReceiptIsSelectedSubquery(commissionStatementReceiptID).ToValue(),
                                  AllocationCount = qf.Field(AllocationSummaryAlias, "AllocationCount").Count().ToValue<int?>() ?? 0,
                                  AllocationItemCount = qf.Field(AllocationSummaryAlias, "AllocationItemCount").Count().ToValue<int?>() ?? 0,
                                  AllocationTotalAmount = qf.Field(AllocationSummaryAlias, "AllocationTotalAmount").Sum().ToValue<decimal?>() ?? 0m,
                                  LastAllocationDate = qf.Field(AllocationSummaryAlias, "LastAllocationDate").Max().ToValue<DateTime?>()
                              }
                )
                .GroupBy(CommissionStatementFields.ID, CommissionStatementFields.ProviderID, CommissionStatementFields.Description);

This is the output

ID  ProviderID  Description GFT GCT LLBLV_1 AllocationCount AllocationItemCount AllocationTotalAmount   LastAllocationDate
72  6823    NULL    0.0000  131.1000    0   3   3   393.30  2011-04-27
85  6823    NULL    0.0000  128.3600    0   6   6   770.16  2011-05-05
86  6823    NULL    0.0000  100.7100    0   9   9   906.39  2011-05-05
102 6823    NULL    0.0000  26.3900 0   7   7   184.73  2011-05-05
320 6823    NULL    0.0000  124.5100    0   9   9   1120.59 2011-07-29
321 6823    NULL    0.0000  79.2100 0   7   7   554.47  2011-07-29
322 6823    NULL    0.0000  136.2500    0   3   3   408.75  2011-07-28
528 6823    NULL    0.0000  51.4300 0   5   5   257.15  2011-11-02
530 6823    NULL    0.0000  64.4300 0   3   3   193.29  2011-11-02
755 6823    NULL    0.0000  111.5700    0   3   3   334.71  2012-01-31
776 6823    NULL    0.0000  100.1500    0   0   0   NULL    NULL
777 6823    NULL    0.0000  112.6100    0   0   0   NULL    NULL

I now comment out the first LeftJoin and the two associated projections starting with "Gross" so I have

            var query = qf.CommissionStatement
                .From(QueryTarget
                    //.LeftJoin(CommissionStatementEntity.Relations.CommissionItemEntityUsingCommissionStatementID)
                    .LeftJoin(AllocationHelper.CreateAllocationSummaryQuery().As(AllocationSummaryAlias))
                    .On(CommissionStatementFields.ID == qf.Field(AllocationSummaryAlias, "FeeOrCommissionStatementID"))
                )
                .Select(() => new CommissionStatementAllocationTargetInfo
                              {
                                  CommissionStatementID = CommissionStatementFields.ID.ToValue<int>(),
                                  ProviderID = CommissionStatementFields.ProviderID.ToValue<int>(),
                                  Description = CommissionStatementFields.Description.ToValue<string>(),
                                  //GrossFeesTotal = Functions.IIF(CommissionItemFields.PostingAccountID == feesAwaitingPaymentID, grossFeesTotalExpression, 0m).Sum().As("GFT").ToValue<decimal>(),
                                  //GrossCommissionsTotal = Functions.IIF(CommissionItemFields.PostingAccountID != feesAwaitingPaymentID, grossCommissionsTotalExpression, 0m).Sum().As("GCT").ToValue<decimal>(),
                                  IsSelected = CreateCommissionStatementReceiptIsSelectedSubquery(commissionStatementReceiptID).ToValue(),
                                  AllocationCount = qf.Field(AllocationSummaryAlias, "AllocationCount").Count().ToValue<int?>() ?? 0,
                                  AllocationItemCount = qf.Field(AllocationSummaryAlias, "AllocationItemCount").Count().ToValue<int?>() ?? 0,
                                  AllocationTotalAmount = qf.Field(AllocationSummaryAlias, "AllocationTotalAmount").Sum().ToValue<decimal?>() ?? 0m,
                                  LastAllocationDate = qf.Field(AllocationSummaryAlias, "LastAllocationDate").Max().ToValue<DateTime?>()
                              }
                )
                .GroupBy(CommissionStatementFields.ID, CommissionStatementFields.ProviderID, CommissionStatementFields.Description);

I now get 13 rows

ID  ProviderID  Description LLBLV_1 AllocationCount AllocationItemCount AllocationTotalAmount   LastAllocationDate
NULL    NULL    NULL    0   0   0   NULL    NULL
72  6823    NULL    0   1   1   131.10  2011-04-27
85  6823    NULL    0   1   1   128.36  2011-05-05
86  6823    NULL    0   1   1   100.71  2011-05-05
102 6823    NULL    0   1   1   26.39   2011-05-05
320 6823    NULL    0   1   1   124.51  2011-07-29
321 6823    NULL    0   1   1   79.21   2011-07-29
322 6823    NULL    0   1   1   136.25  2011-07-28
528 6823    NULL    0   1   1   51.43   2011-11-02
530 6823    NULL    0   1   1   64.43   2011-11-02
755 6823    NULL    0   1   1   111.57  2012-01-31
776 6823    NULL    0   0   0   NULL    NULL
777 6823    NULL    0   0   0   NULL    NULL

I just don't understand where that additional top row has come from.

The generated SQL is

--Retrieval Query:
DECLARE @p2 bit; SET @p2=1
DECLARE @p4 bit; SET @p4=0
DECLARE @p5 int; SET @p5='6823'

SELECT
  [LPA_L3].[ID],
  [LPA_L3].[ProviderID],
  [LPA_L2].[Description],
  CASE WHEN CASE WHEN (0=1) THEN 1 ELSE 0 END=1 THEN @p2 ELSE @p4 END AS [LLBLV_1],
  COUNT([LPA_y1].[AllocationCount]) AS [AllocationCount],
  COUNT([LPA_y1].[AllocationItemCount]) AS [AllocationItemCount],
  SUM([LPA_y1].[AllocationTotalAmount]) AS [AllocationTotalAmount],
  MAX([LPA_y1].[LastAllocationDate]) AS [LastAllocationDate] 
FROM
  (( (
SELECT
  [Allocation].[AccountsTransactionID] AS [FeeOrCommissionStatementID],
  COUNT([Allocation].[ID]) AS [AllocationCount],
  COUNT([LPA_x4].[AllocatedItemCount]) AS [AllocationItemCount],
  SUM([LPA_x4].[AllocatedItemTotalAmount]) AS [AllocationTotalAmount],
  MAX([AccountsTransaction].[TaxpointDate]) AS [LastAllocationDate] 
FROM
  (( (
SELECT
  [AllocationItem].[AllocationID],
  COUNT([AllocationItem].[ID]) AS [AllocatedItemCount],
  SUM([AnalysisItem].[Amount]) AS [AllocatedItemTotalAmount] 
FROM
  ( [AnalysisItem]  
LEFT JOIN
  [AllocationItem] ON [AnalysisItem].[ID] = [AllocationItem].[ID]) 
GROUP BY
  [AllocationItem].[AllocationID]) [LPA_x4]  
INNER JOIN
  [Allocation] ON [Allocation].[ID] = [LPA_x4].[AllocationID]) 
INNER JOIN
  [AccountsTransaction] ON [AccountsTransaction].[ID] = [Allocation].[ID]) 
GROUP BY
  [Allocation].[AccountsTransactionID]) [LPA_y1]  
RIGHT JOIN
  [CommissionStatement] [LPA_L3] ON [LPA_L3].[ID] = [LPA_y1].[FeeOrCommissionStatementID]) 
RIGHT JOIN
  [AccountsTransaction] [LPA_L2] ON [LPA_L2].[ID] = [LPA_L3].[ID]) 
WHERE
  ( ( [LPA_L2].[LegalBodyID] = @p5)) 
GROUP BY
  [LPA_L3].[ID],
  [LPA_L3].[ProviderID],
  [LPA_L2].[Description]

The original working SQL looks like this:-

--Retrieval Query:
DECLARE @p1 smallint; SET @p1='104'
DECLARE @p3 int; SET @p3='0'
DECLARE @p5 int; SET @p5='0'
DECLARE @p7 decimal; SET @p7='0'
DECLARE @p8 smallint; SET @p8='104'
DECLARE @p10 int; SET @p10='0'
DECLARE @p12 int; SET @p12='0'
DECLARE @p14 decimal; SET @p14='0'
DECLARE @p16 bit; SET @p16=1
DECLARE @p18 bit; SET @p18=0
DECLARE @p19 int; SET @p19='6823'

SELECT
  [LPA_L2].[ID],
  [LPA_L2].[ProviderID],
  [LPA_L1].[Description],
  SUM(CASE WHEN CASE WHEN [LPA_L3].[PostingAccountID] = @p1 THEN 1 ELSE 0 END=1 THEN (COALESCE([LPA_L3].[Amount],
  @p3) + COALESCE([LPA_L3].[VATAmount],
  @p5)) ELSE @p7 END) AS [GFT],
  SUM(CASE WHEN CASE WHEN [LPA_L3].[PostingAccountID] <> @p8 THEN 1 ELSE 0 END=1 THEN (COALESCE([LPA_L3].[Amount],
  @p10) + COALESCE([LPA_L3].[VATAmount],
  @p12)) ELSE @p14 END) AS [GCT],
  CASE WHEN CASE WHEN (0=1) THEN 1 ELSE 0 END=1 THEN @p16 ELSE @p18 END AS [LLBLV_1],
  COUNT([LPA_y6].[AllocationCount]) AS [AllocationCount],
  COUNT([LPA_y6].[AllocationItemCount]) AS [AllocationItemCount],
  SUM([LPA_y6].[AllocationTotalAmount]) AS [AllocationTotalAmount],
  MAX([LPA_y6].[LastAllocationDate]) AS [LastAllocationDate] 
FROM
  ((((( [AccountsTransaction] [LPA_L1]  
INNER JOIN
  [CommissionStatement] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) 
LEFT JOIN
  [CommissionItem] [LPA_L5] ON [LPA_L2].[ID] = [LPA_L5].[CommissionStatementID]) 
LEFT JOIN
  [SaleAnalysisItem] [LPA_L4] ON [LPA_L4].[ID] = [LPA_L5].[ID]) 
LEFT JOIN
  [AnalysisItem] [LPA_L3] ON [LPA_L3].[ID] = [LPA_L4].[ID]) 
LEFT JOIN
  (
SELECT
  [Allocation].[AccountsTransactionID] AS [FeeOrCommissionStatementID],
  COUNT([Allocation].[ID]) AS [AllocationCount],
  COUNT([LPA_x7].[AllocatedItemCount]) AS [AllocationItemCount],
  SUM([LPA_x7].[AllocatedItemTotalAmount]) AS [AllocationTotalAmount],
  MAX([AccountsTransaction].[TaxpointDate]) AS [LastAllocationDate] 
FROM
  (( (
SELECT
  [AllocationItem].[AllocationID],
  COUNT([AllocationItem].[ID]) AS [AllocatedItemCount],
  SUM([AnalysisItem].[Amount]) AS [AllocatedItemTotalAmount] 
FROM
  ( [AnalysisItem]  
LEFT JOIN
  [AllocationItem] ON [AnalysisItem].[ID] = [AllocationItem].[ID]) 
GROUP BY
  [AllocationItem].[AllocationID]) [LPA_x7]  
INNER JOIN
  [Allocation] ON [Allocation].[ID] = [LPA_x7].[AllocationID]) 
INNER JOIN
  [AccountsTransaction] ON [AccountsTransaction].[ID] = [Allocation].[ID]) 
GROUP BY
  [Allocation].[AccountsTransactionID]) [LPA_y6] ON [LPA_L2].[ID] = [LPA_y6].[FeeOrCommissionStatementID]) 
WHERE
  ( ( [LPA_L1].[LegalBodyID] = @p19)) 
GROUP BY
  [LPA_L2].[ID],
  [LPA_L2].[ProviderID],
  [LPA_L1].[Description]

I noticed there are no right-joins in this, so maybe commenting out the LeftJoin makes the From invalid??

Which LLBLGen Runitme library version are you using? (build no.)

Walaa wrote:

Which LLBLGen Runitme library version are you using? (build no.)

4.0.13.627.

I wonder if this issue is related to the following thread of yours (one year ago) http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21137

Also:

• Your runtime library version is outdated, please download the last installer, just to make sure that you are using the latest fixes.

• It would help if you try to reduce your code to the minimal expression that still reproduces the problem.

• What does AllocationHelper.CreateAllocationSummaryQuery() return? Does it have relevance in the issue? (i.e. if you remove that join, then the query works the same).

• From the last generated SQL (the one with the commented joins), Could you spot the error? What is the thing that goes wrong with that SQL?

@daelmo: Got the latest version - made no difference.

@Walla: Yes, I believe you are right - the difference of a Right Join made me think the same thing.

I just tried taking the failing SQL output (the one that produces 13 rows) and changed that last joins from

RIGHT JOIN
  [CommissionStatement] [LPA_L3] ON [LPA_L3].[ID] = [LPA_y1].[FeeOrCommissionStatementID]) 
RIGHT JOIN
  [AccountsTransaction] [LPA_L2] ON [LPA_L2].[ID] = [LPA_L3].[ID]) 

to

RIGHT JOIN
  [CommissionStatement] [LPA_L3] ON [LPA_L3].[ID] = [LPA_y1].[FeeOrCommissionStatementID]) 
JOIN
  [AccountsTransaction] [LPA_L2] ON [LPA_L2].[ID] = [LPA_L3].[ID]) 

Bingo, that extra row disappeared

I reread that last thread and added a where

.Where(CommissionStatementEntity.GetEntityTypeFilter())

and this did the job too.

Just to clarify, If I add this EntityTypeFilter to every query of this type I write, is that always correct?

Yes a typeFilter will make sure it always work.