c# - Complex Linq-To-Entities query with deferred execution: prevent OrderBy being used as a subquery/projection -

i built dynamic linq-to-entities query support optional search parameters. quite bit of work producing performant sql , nearly there, stumble across big issue orderby gets translated kind of projection / subquery containing actual query, causing extremely inperformant sql. can't find solution right. maybe can me out :)

i spare complete query long , complex, translate simple sample better understanding:

i'm doing this:

// start base query     var query = in db.articles             a.userid = 1;  // apply optional conditions if (tagparam != null)     query = query.where(a => a.tag = tagparam);  if (authorparam != null)     query = query.where(a => a.author = authorparam);  // ... , on ...  // want 50 recent articles, want apply take , orderby query = query.orderbydescending(a => a.published); query = query.take(50); 

the resulting sql strangely translates orderby in container query:

select top 50 id, published, title, content (select id, published, title content       articles       userid = 1       , author = @paramauthor) order published desc 

note top 50 got moved outer query. in case use take(50), top 50 sql statement correctly applied inner query above (the outer query wouldn't exist). when use orderby, linq-to-entities uses container query approach.

this causes bad execution plan inner query takes articles apply parameters disk , pass them outer query - , there, orderby , top processed. in case, can hundred thousands of lines. tried move order manually inner statement , execute - produces better results existing indexes allow sql server find top 50 rows in right order without reading rows disk.

is there way can ef append order clause inner query? or other trick working right?

any appreciated :)

edit: additional information, tests less complex queries showed optimizer handles such subquery scenarios well. in scenario, optimizer fails on unfortunately , moves hundrets of thousands of rows through query plan. moving orderby inner query solves , optimizer right.

edit 2: after couple of hours of more testing seems issue wrong execution plan sql server issue not caused created container query. while move of order , top clause inside query did fix issue initially, can't reproduce anymore, sql server started using bad execution plan here (while data in db remained unchanged). move of order clause might caused sql server take other statistics account seems not due better/more clean query design. however, still want know why ef uses container query here , if can influence behavior. if not improve performance, @ least make debugging easier if generated ef queries more straightforward , not convoluted.