This is a migrated thread and some comments may be shown as answers.

Linq statment ignoring order by

1 Answer 63 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
julian Welby Everard
Top achievements
Rank 1
julian Welby Everard asked on 02 Nov 2010, 12:11 PM
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

1 Answer, 1 is accepted

Sort by
0
julian Welby Everard
Top achievements
Rank 1
answered on 03 Nov 2010, 05:57 PM
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
Tags
LINQ (LINQ specific questions)
Asked by
julian Welby Everard
Top achievements
Rank 1
Answers by
julian Welby Everard
Top achievements
Rank 1
Share this question
or