Lightspeed generates incorrect SQL

using LightSpeed 4.0.1440.20769

It appears that the LightSpeed provider generates incorrect SQL for the following LINQ query.

result =
(
    from c in unitOfWork.DocumentAccesses
    where c.CmsLanguage == language
    group c by c.DocumentId
    into g
    orderby g.Count() descending
    select g.Key
).Take(take)

which generates the following SQL (using the Sql 2008 provider)

SELECT
  g1.*
FROM
  (
    SELECT
  g1.DocumentId,
  ROW_NUMBER() OVER(ORDER BY g1.DocumentId) as RowNumber
FROM
  (
    SELECT
      DocumentAccess.DocumentId AS [DocumentId]
    FROM
      DocumentAccess DocumentAccess
    WHERE
      DocumentAccess.CmsLanguage = 'en-NZ'
    GROUP BY
      DocumentAccess.DocumentId
  )
  g1

) g1 WHERE RowNumber <= 5

Notice that the Order By is incorrect, it should be ordering on the count.

If I plug this into a LinqToSql provider, the following (correct) sql is generated

SELECT TOP (5) [t1].[DocumentId]
FROM (
SELECT COUNT(*) AS [value], [t0].[DocumentId]
FROM [DocumentAccess] AS [t0]
WHERE [t0].[CmsLanguage] = @p0
GROUP BY [t0].[DocumentId]
) AS [t1]
ORDER BY [t1].[value] DESC

Edit: Use correct LinqToSql query

Thanks for reporting this - I have added in a fix for this which will be available in the next nightly build (20120710). This solves the issue on the repro cases I have looked at here so please let me know if you continue to see any issues after you have a chance to look at this against your solution.

Jeremy

Just installed it now; works great, thank you!