sql server - Why does this query take so long in Entity Framework, when it's so fast in SQL? -

i have table named geographicallocations, contains 57k rows. if run following query in sql server management studio, takes 1-2 seconds complete...

select * geographicallocations active=1 

if equivalent in linqpad, takes 2-3 seconds...

geographicallocations.where (gl => gl.active) 

however, same code in repository uses ef4 takes 10-11 seconds...

list<geographicallocation> geographicallocations = new salestrackercrmentities()   .createobjectset<geographicallocation>()   .where(g => g.active)   .tolist(); 

by way, we're stuck ef4 moment, please don't suggest upgrading. i'd love to, there other factors stopping moment.

any idea how can speed up? users complaining slow response. it's not database, sql query fast. looks ef, don't know what.

i ran sql profiler on it, , see sql sent. took less second run in ssms, profiler showed 10 second gap between start , end of batch. not sure what's going on here, have thought ef pull out data, materialise entities, looks might doing goes along.

anyway, grateful ideas how can improve performance here.

when using linq expressions against dbset you're taking advantage of iqueryable interface. iqueryables evaluated sql statements executed against database. if want speed performance can query dbset property raw sql statement this:

var locations = db.geographicallocations.sqlquery("select * geographicallocations active = 1").tolist();