markrendle/Simple.Data — Issue #100: Using "As" with the field being sorted on produces incorrect SQL
Author: grumpydev
Created: 2011-10-21 08:53:17+00:00
State: open

Haven't delved into the code, so not sure if this is a "core" or SQL Server issue, but if I have this:

.Select(
db.GeneratedKeys.KeyGenerationSessionId.As("DT_RowId"),
db.GeneratedKeys.Key,
db.GeneratedKeys.Description)

Where KeyGenerationSessionId is the PK, and therefore the default sort order, the SQL it generates includes the "AS [DT_RowId]" in the order by:

ROW_NUMBER() OVER(ORDER BY [dbo].[GeneratedKeys].[KeyGenerationSessionId] AS [DT_RowId]) AS [_#_]

If I remove the .As("DT_RowId"), or remove it from the first line and then add db.GeneratedKeys.KeyGenerationSessionId.As("DT_RowId") further down (so bring back the id as "itself" and also as DT_RowId) then it works fine.