I retrieve pages of data using a EntityCollection. When I use SQL2000 compatibility this succeeds. However in SQL2005 it fails. The reason for this is that in 2005 the WITH statement is used instead of a temporary table.

When I call the GetMulti() method using a relation to one other table, it returns for each related row a separate row in the result set. The query generated by LLBLGen tries to only return unique rows by using DISTINCT. However, because in the result set a row number (ROW_NUMBER())is returned, rows are always unique and thus duplicate rows are returned.

Example of generated SQL (table names have been changed to protect the innocent):

exec sp_executesql N'WITH __actualSet AS (SELECT DISTINCT TOP 51 [C].[dbo].[PT].[T_ID] AS [T_ID], 
[C].[dbo].[PT].[IC_ID] AS [IC_ID], [C].[dbo].[PT].[Status], [C].[dbo].[PT].[PS] , 
ROW_NUMBER() OVER(ORDER BY [C].[dbo].[PT].[T_ID] DESC) AS __rowcnt FROM  ( [C].[dbo].[PT]  INNER JOIN 
[C].[dbo].[GM]  ON  [C].[dbo].[PT].[T_ID]=[C].[dbo].[GM].[T_ID]) ) SELECT * FROM __actualSet WHERE 
[__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC',N'@__rownoStart int,@__rownoEnd int',@__rownoStart=40,@__rownoEnd=50

Patrick

That indeed looks like a bug. For the time being , use sqlserver2000 compatibility. I'll try to fix this.

It's not solveable.

See this query:

WITH __actualSet AS 
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY _tmpSet.[ProductID] ASC) AS __rowcnt 
    FROM
    (
        SELECT DISTINCT TOP 9 [AdventureWorks].[Production].[Product].[ProductID] AS [ProductId], [AdventureWorks].[Production].[Product].[Name], [AdventureWorks].[Production].[Product].[ProductNumber], [AdventureWorks].[Production].[Product].[MakeFlag], [AdventureWorks].[Production].[Product].[FinishedGoodsFlag], [AdventureWorks].[Production].[Product].[Color], [AdventureWorks].[Production].[Product].[SafetyStockLevel], [AdventureWorks].[Production].[Product].[ReorderPoint], [AdventureWorks].[Production].[Product].[StandardCost], [AdventureWorks].[Production].[Product].[ListPrice], [AdventureWorks].[Production].[Product].[Size], [AdventureWorks].[Production].[Product].[SizeUnitMeasureCode], [AdventureWorks].[Production].[Product].[WeightUnitMeasureCode], [AdventureWorks].[Production].[Product].[Weight], [AdventureWorks].[Production].[Product].[DaysToManufacture], [AdventureWorks].[Production].[Product].[ProductLine], [AdventureWorks].[Production].[Product].[Class], [AdventureWorks].[Production].[Product].[Style], [AdventureWorks].[Production].[Product].[ProductSubcategoryID] AS [ProductSubcategoryId], [AdventureWorks].[Production].[Product].[ProductModelID] AS [ProductModelId], [AdventureWorks].[Production].[Product].[SellStartDate], [AdventureWorks].[Production].[Product].[SellEndDate], [AdventureWorks].[Production].[Product].[DiscontinuedDate], [AdventureWorks].[Production].[Product].[rowguid] AS [Rowguid], [AdventureWorks].[Production].[Product].[ModifiedDate]
        FROM  
        ( 
            [AdventureWorks].[Production].[Product]  INNER JOIN [AdventureWorks].[Purchasing].[PurchaseOrderDetail]  
            ON  [AdventureWorks].[Production].[Product].[ProductID]=[AdventureWorks].[Purchasing].[PurchaseOrderDetail].[ProductID]
        ) 
        WHERE 
        ( 
            ( [AdventureWorks].[Purchasing].[PurchaseOrderDetail].[OrderQty] = 60)
        ) 
    ) AS _tmpSet
)
SELECT * FROM __actualSet 
WHERE [__rowcnt] > 4 AND [__rowcnt] <= 8 ORDER BY [__rowcnt] ASC

This one works, it gives the proper results. It's a modified version of the generated query which looks like:

WITH __actualSet AS 
(
    SELECT DISTINCT TOP 9 [AdventureWorks].[Production].[Product].[ProductID] AS [ProductId], [AdventureWorks].[Production].[Product].[Name], [AdventureWorks].[Production].[Product].[ProductNumber], [AdventureWorks].[Production].[Product].[MakeFlag], [AdventureWorks].[Production].[Product].[FinishedGoodsFlag], [AdventureWorks].[Production].[Product].[Color], [AdventureWorks].[Production].[Product].[SafetyStockLevel], [AdventureWorks].[Production].[Product].[ReorderPoint], [AdventureWorks].[Production].[Product].[StandardCost], [AdventureWorks].[Production].[Product].[ListPrice], [AdventureWorks].[Production].[Product].[Size], [AdventureWorks].[Production].[Product].[SizeUnitMeasureCode], [AdventureWorks].[Production].[Product].[WeightUnitMeasureCode], [AdventureWorks].[Production].[Product].[Weight], [AdventureWorks].[Production].[Product].[DaysToManufacture], [AdventureWorks].[Production].[Product].[ProductLine], [AdventureWorks].[Production].[Product].[Class], [AdventureWorks].[Production].[Product].[Style], [AdventureWorks].[Production].[Product].[ProductSubcategoryID] AS [ProductSubcategoryId], [AdventureWorks].[Production].[Product].[ProductModelID] AS [ProductModelId], [AdventureWorks].[Production].[Product].[SellStartDate], [AdventureWorks].[Production].[Product].[SellEndDate], [AdventureWorks].[Production].[Product].[DiscontinuedDate], [AdventureWorks].[Production].[Product].[rowguid] AS [Rowguid], [AdventureWorks].[Production].[Product].[ModifiedDate] 
        , ROW_NUMBER() OVER(ORDER BY [AdventureWorks].[Production].[Product].[ProductID] ASC) AS __rowcnt 
    FROM  
    ( 
        [AdventureWorks].[Production].[Product]  INNER JOIN [AdventureWorks].[Purchasing].[PurchaseOrderDetail]  
        ON  [AdventureWorks].[Production].[Product].[ProductID]=[AdventureWorks].[Purchasing].[PurchaseOrderDetail].[ProductID]
    ) 
    WHERE 
    ( 
        ( 
            [AdventureWorks].[Purchasing].[PurchaseOrderDetail].[OrderQty] = @OrderQty1
        )
    ) 
) 
SELECT * FROM __actualSet WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd 
ORDER BY [__rowcnt] ASC

Which fails as it allows duplicates.

Now, as you can see, the first one contains _tmpSet in the rowcount function. This is required as it has to work with the set in the select. The problem is that it can only see the columns in the select list of the actual query, not the columns in the full set build by the FROM clause of the actual query because the ROW_NUMBER function is applied to full set AFTER the select. This means that if you sort on a field not in the select list (which can happen), the query will fail as rowcount can't address that column, as it's not in the select list. It's not possible to refer to a column in the ORDER BY clause in ROW_NUMBER() in the first example which is available IN the SELECT statement's FROM clause, because ROW_NUMBER() can't see it.

One could argue, for making it possible, to add the sortcolumns to the select list of the actual query and then it could work, by eliminating the same columns again in the actual select on the WITH set (the last line).

This still could lead to false positives, as the additional values probably would create non-unique rows.

ROW_NUMBER has a partitioning clause, but that doesn't lead to any solution as far as I can see as that will re-do the rowcounting per partition, exactly NOT what I want (I want rowcounting to use the same value for EQUAL values, which isn't what partitioning does, it assigns new counts to the same values and equal counts to different values.

So the only solution I can think of which always works is by switching to sqlserver2000 mode for a query which uses 1:n relations in the query, and thus a temptable. The sad thing is that with selfservicing, you can't control this on a per-call basis.

If someone has a different solution, please step forward, I don't see how a CTE based query could solve this problem without running the risk of crashes due to unresolvable column names in some scenarios.

I'll add code which checks if there are relations specified and if so, if the type is 1:n and inner/left join. If so, the query can lead to dupes and the temptable approach will be used.

That would give some relieve to this I think. It still strikes me why on earth MS didn't use a silly ROWCOUNT() function per row together with a more useful DISTINCT clause, namely one which accepts column descriptions so you can specify on which columns to check for dupes, like Oracle can.

The switch to temptable if at least one 1:n relation with inner/left/cross join is found is available in the next build.