Linq statment ignoring order by

2 posts, 0 answers
  1. julian Welby Everard
    julian Welby Everard avatar
    13 posts
    Member since:
    Mar 2009

    Posted 02 Nov 2010 Link to this post

    Hi

    I have found the following issue with ORM and Linq, when running the following Linq query the order by clause is ignored, this is an example of one of the linq statments, these are used in dropdown list when loading on demand to extract 10 rows at a time

    lstJobRoles = (From EJobRoles In scope.Extent(Of PdJobRole)() _
                   Join EJobRolesML In scope.Extent(Of PdJobRolesML)() On EJobRoles.JobRoleRef Equals EJobRolesML.JobRoleRef _
                   Skip (intRecoredCount) _
                   Take (10) _
                   Where EJobRolesML.LanguageCode = strlanguageCode _
                        And EJobRoles.JobRoleActive = True _
                   Order By EJobRolesML.JobRoleDesc Ascending _
                   Select EJobRolesML).ToList()

    The 2 tables are actualy views and join by clause in this case is not the primary key, the pdJobRole table contains the data while the pdJobRolesML table contains the descriptions.

    When this query is run and using SQL profiler i get the following statments
    declare @p1 int
    set @p1=2
    exec sp_prepexec @p1 output,N'@p0 varchar(2),@__TAKE int',N'SELECT  TOP(@__TAKE) a.[JobRoleID] AS COL1, b.[JobRoleMLID] AS COL2 FROM [pdJobRoles] a JOIN [pdJobRolesML] AS b ON (a.[JobRoleRef] = b.[JobRoleRef]) WHERE b.[LanguageCode] = @p0               AND a.[JobRoleActive] = 1 ',@p0='EN',@__TAKE=10
    select @p1
    go
    The statment is the first time it is run, it returns 10 rows correctly by as you can see it is not ordered in anyway

    the next query runs return the following code
    declare @p1 int
    set @p1=4
    exec sp_prepexec @p1 output,N'@p0 varchar(2),@__TAKE int,@__SKIP int',N'WITH __OAPAGED AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY @@SPID) AS OA_ROWNUM FROM ( 
    SELECT  TOP(@__TAKE) a.[JobRoleID] AS COL1, b.[JobRoleMLID] AS COL2 FROM [pdJobRoles] a JOIN [pdJobRolesML] AS b ON (a.[JobRoleRef] = b.[JobRoleRef]) WHERE b.[LanguageCode] = @p0               AND a.[JobRoleActive] = 1 
     ) AS __OATMPSET ) SELECT * FROM __OAPAGED WHERE [OA_ROWNUM] > @__SKIP AND [OA_ROWNUM] <= @__TAKE ORDER BY [OA_ROWNUM] ASC',@p0='EN',@__TAKE=20,@__SKIP=10
    select @p1
    This statment returns rowns 21-30 correctly but again it is not ordered by the column specified in the linq statment.

    Is there anyway to get this to work as requested in the linq statment

    Thanks

  2. julian Welby Everard
    julian Welby Everard avatar
    13 posts
    Member since:
    Mar 2009

    Posted 03 Nov 2010 Link to this post

    Answer from support ticked was

    I changed my query from ....
    lstJobRoles = (From EJobRoles In scope.Extent(Of PdJobRole)() _ 
                   Join EJobRolesML In scope.Extent(Of PdJobRolesML)() On EJobRoles.JobRoleRef Equals EJobRolesML.JobRoleRef _ 
                   Skip (intRecoredCount) _ 
                   Take (10) _ 
                   Where EJobRolesML.LanguageCode = strlanguageCode _ 
                        And EJobRoles.JobRoleActive = True _ 
                   Order By EJobRolesML.JobRoleDesc Ascending _ 
                   Select EJobRolesML).ToList()

    to

    lstJobRoles = (From EJobRoles In scope.Extent(Of PdJobRole)() _ 
                   Join EJobRolesML In scope.Extent(Of PdJobRolesML)() On EJobRoles.JobRoleRef Equals EJobRolesML.JobRoleRef _ 
                   Where EJobRolesML.LanguageCode = strlanguageCode _ 
                        And EJobRoles.JobRoleActive = True _ 
                   Order By EJobRolesML.JobRoleDesc Ascending _ 
                   Select EJobRolesML _
                   Skip (intRecoredCount) _ 
                   Take (10) _ 
                   ).ToList()

    Note moving the Skip and Take

    Julian
  3. DevCraft banner
Back to Top