Linq query loses order

Question

I have a view in my database that produces ordered results, but when I run a Linq query over that view, the results are no longer ordered (at least, according to the foreach I use to iterate over the results, and according to the debugger). Is this a known difficulty with Linq, or am I missing something?

Update: my view, from SQL Server 2008 (from design view):

SELECT     TOP (100) PERCENT UrlId, Title, Description, Url, Parent, ResourceKey, Published, dbo.udf_SiteMap_GetRoles(UrlId) AS Roles
FROM         dbo.SiteMap
ORDER BY DisplayOrder

The Linq query that builds the SiteMap:

SqlConnection connection = new SqlConnection(_connect);
DataContext dc = new DataContext(connection);
Table<NodeRoleEntity> siteMapTable = dc.GetTable<NodeRoleEntity>();

var rootQuery = from ne in siteMapTable
                where ne.ParentID == null
                select ne;
foreach (NodeRoleEntity rootNode in rootQuery)
{
   SiteMapNode root = rootNode.AsSiteMapNode(this);
   base.AddNode(root, _root);
   AddChildNodes(root, siteMapTable);
}

This query uses the SiteMap built above to render menus in my ASP.NET application:

StaticSiteMapProvider _provider = SiteMap.Providers["MySiteMap"] as StaticSiteMapProvider;
string cultureToken = _GetCulture().ToLower();
SiteMapNode cultureRoot = 
    (from SiteMapNode cr in _provider.RootNode.ChildNodes
     where cr.Description == cultureToken
     select cr).First();

int menuCount = 0;            
foreach (SiteMapNode node in cultureRoot.ChildNodes)
{
   _RenderMenu(node, menuCount.ToString(), writer);
   menuCount++;
}

It is the nodes in cultureRoot.ChildNodes that are ordered improperly (but the rows from which those nodes are derived are ordered).

Answer 1

When you select from a view it doesn't keep it's order. You have to put in in the LINQ:

SiteMapNode cultureRoot = (
   from SiteMapNode cr in _provider.RootNode.ChildNodes
   where cr.Description == cultureToken
   orderby cr.DisplayOrder
   select cr
).First();

Answer 2

ORDER BY clauses are strictly speaking not allowed in views in SQL Server. You have to add an OrderBy clause in your linq query.

See here: http://www.devx.com/dbzone/Article/8048

Answer 3

Your linq query is:

var rootQuery = from ne in siteMapTable
                where ne.ParentID == null
                select ne;

This will generate sql like so

SELECT *
FROM SiteMapTable
WHERE ParentID == @P1

You can take this sql to the database, run it and observe that your results are un-ordered. Here's what happens:

In the database, the text of the view will be placed as a subquery into your query.

SELECT *
FROM
(
   SELECT top 100 percent *
   FROM ...
   ORDER BY ...
) as SiteMapTable
WHERE ParentID == @P1

The query optimizer will notice that ordering was not asked for at the outer most level, and decide that order is not needed for the results. It will strip out/ignore the ordering in the sub-query. You can view the estimated execution plan to confirm this.