linq - Force "TOP 100 PERCENT" in EF "sub-query" queryable -

update: mistaken top 100 percent generating better query plan (the plan still better reasonably sized top n, , has parameter sniffing).

while still think focused question has merit, not "a useful solution" problem², , might not yours ..

---

i running queries sql server optimizes poorly. statistics appear correct, , sql server chooses 'worse' plan performs seek on millions of records though estimated , actual values same - question not that¹.

in problematic queries of simplified form:

select * x join y on .. join z on .. z.q = .. 

however (and since know cardinalities better, apparently) following form consistently results in much better query plan:

select * x join (   -- result set here 'quite small'   select top 100 percent *   y on ..   join z on ..   z.q = ..) t on .. 

in l2s take function can used limit top n, "problem" have approach requires finite/fixed n such query hypothetically break, instead of running slow forced materialization.

while choose 'very large' value top n this, ironically (wrt initial problem), increases sql query execution time value of n increases. expected intermediate result expected few dozen few hundred records. current code have runs top 100 , then, if such detected contain many results, runs query again without limit: feels kludge .. on top of kludge.

the question then: can ef/l2e/linq query generate equivalent of top 100 percent on ef queryable?

(forcing materialization via tolist not option because result should ef queryable , remain in linq entities, not linq objects.)

while dealing ef4, if [only] possible in later version of ef would accept such answer - such useful knowledge , answer question asked.

---

¹ if wishing answer "don't that" or "alternative", please make secondary answer or aside along answer actual question being asked. otherwise, feel free use comments.

---

² in addition top 100 percent not generating better query plan, forgot include 'core issue' @ stake, bad parameter sniffing (instance sql server 2005).

the following query takes long to complete while direct variable substitution runs "in blink of eye" indicating issue parameter sniffing.

declare @x int set @x = 19348659  select op.* orderelement oe join orderrateplan rp on oe.orderselementid = rp.orderselementid join orderprice op on rp.ordersrateplanid = op.ordersrateplanid oe.ordersproductid = @x 

the kludged-but-workable query

select   op.* orderprice op join (   -- choosing 'small value of n' runs fast , slows down   -- value of n increases n >> 1000 "takes long".   -- using top 100 percent "takes long".   select top 100     rp.*   orderelement oe   join orderrateplan rp on oe.orderselementid = rp.orderselementid   oe.ordersproductid = @x ) rp on rp.ordersrateplanid = op.ordersrateplanid 

yes, can own query.

db.sqlquery<something>("select * x ...");