I am just wondering anyone else run into this problem.
SubSonic generate incorrect SQL when using Group By or
Distinct with Paging. Is this known error?
Notice the group by is in the wrong place when use paging.
SubSonic SQL output Without Paging:
exec sp_executesql N'SELECT [dbo].[vwMapProject].[Id], [dbo].[vwMapProject].[Name]
FROM [dbo].[vwMapProject]
INNER JOIN [dbo].[Product_ProductComponent] ON [dbo].[vwMapProject].[ProductId] = [dbo].[Product_ProductComponent].[ProductId]
INNER JOIN [dbo].[ProductComponent] ON [dbo].[Product_ProductComponent].[ProductComponentId] = [dbo].[ProductComponent].[Id]
WHERE [dbo].[ProductComponent].[ExternalId] LIKE @ExternalId0
GROUP BY [dbo].[vwMapProject].[Id], [dbo].[vwMapProject].[Name]
',N'@ExternalId0 nvarchar(8)',@ExternalId0=N'GC767AV%'
SubSonic SQL output With Paging:
exec sp_executesql N'
SELECT *
FROM (SELECT ROW_NUMBER() OVER ( ORDER BY Id) AS Row,
[dbo].[vwMapProject].[Id], [dbo].[vwMapProject].[Name]
FROM [dbo].[vwMapProject]
INNER JOIN [dbo].[Product_ProductComponent] ON [dbo].[vwMapProject].[ProductId] = [dbo].[Product_ProductComponent].[ProductId]
INNER JOIN [dbo].[ProductComponent] ON [dbo].[Product_ProductComponent].[ProductComponentId] = [dbo].[ProductComponent].[Id]
GROUP BY [dbo].[vwMapProject].[Id], [dbo].[vwMapProject].[Name]
WHERE [dbo].[ProductComponent].[ExternalId] LIKE @ExternalId0
) AS PagedResults
WHERE Row >= 1 AND Row <= 20',N'@ExternalId0 nvarchar(8)',@ExternalId0=N'GC767AV%'
Best Answer
this should be fixed in the current release - 3.0.0.3. If you don't have it - please head to our project site as this is a bug that was reported early on and I spent some time trying to nail it :)