IQueryable losing the ORDER BY

Question

Can anyone explain why this is happening?

Dim lReturn As IQueryable(Of Inventory) = context.Inventories.OrderBy(Function(o) o.SortOrder)

'' --- In the sql profiler, I can see Order by is added
'' Dim lst = lReturn.ToList 

If Not loggedInUser.IsAdmin Then
    lReturn = lReturn.Where(Function(x) x.AdminOnly = False)
    '' --- In the sql profiler, I can see Order by is added
    '' Dim lst = lReturn.ToList 

    lReturn = From f In lReturn
              Group Join a In context.AssignServices On f.ID Equals a.Id Into fas = Group
              From a In fas.Where(Function(x) x.AgentId = loggedInUser.CompanyId).DefaultIfEmpty()
              Where a.Assign = True OrElse f.isVisibleToAllAgents = True
              Select f
    '' --- In the sql profiler, I can see **Order by is not included**
    Dim lst = lReturn.ToList     ''*Problem is here*

End If

lReturn has already contained the Order By in the beginning which is a Lambda expression. If the user is not admin the last Linq query does not include order by for some reason. I am using the sql profiler to find out how the query looks and it is as follows

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
.........
[Extent1].[SortOrder] AS [SortOrder]
FROM  [dbo].[Inventories] AS [Extent1]
LEFT OUTER JOIN [dbo].[AssignService] AS [Extent2] ON ([Extent1].[ID] = [Extent2].[Id]) AND ([Extent2].[AgentId] = @p__linq__0)
WHERE (0 = [Extent1].[AdminOnly]) AND ([Extent2].[Assign] = 1 OR [Extent1].[isVisibleToAllAgents] = 1) ,N'@p__linq__0 int',@p__linq__0=112

Is it a regular behavior? or Am I missing something?

Answer 1

LINQ based ORMs have loosed rules around what particular order by patterns are supported. What's always supported (according to my experience) is ordering at the end of the query with only Where and Select operations after it.

Probably the order by gets lost at the join. Doesn't surprise me.