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

Question

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 ? any fix ? Any help would be greatly appreciated. Thanks

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%'

Answer 1

According to his blog you should report issues at his github repo.

Answer 2

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

Answer 3

Hi Rob, I have the same issue in version 2.2 of Subsonic. Is there a separate fix released for version 2.x, or will you just have to make use of version 3.x?