Sql – SubSonic generate incorrect SQL when using Group By or Distinct with Paging

sqlsubsonic

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 :)

Related Topic