sql server 2008 - Entity Framework with LINQ using CONTAINS in WHERE clause very slow with large integer list -
i have function first queries function in database returns list of employees user allowed see. run in couple ms. using list query employee table employees user allowed see. when run generated sql in query analyzer takes few milliseconds. when runs entity framework taking on 8 seconds.
initially had list of allowedemployees read http://mikeinba.blogspot.ca/2009/09/speeding-up-linq-contains-queries-with.html , tried using hashset instead performace still bad. how can performance similar in sql query analyzer? doing wrong?
i using sql 2008 , ef5.0
public iqueryable<employee> findallbywithpermissions(int eid, expression<func<employee, bool>> predicate) { if (predicate != null) { hashset<int> allowedemployees = securityrepository.getpermissableemployees(eid); return datacontext.set<employee>().where(predicate).where(p => allowedemployees.contains(p.employeeid)).asqueryable<employee>(); ; } throw new argumentnullexception("predicate value must passed findby<t,tkey>."); }
it seems when writes sql have in clause takes long time. there few thousand permissable employees why generating sql take long?
return iqueryable
instead of hashset, , join
employee iqueryable
.
var availableemployees = securityrepository.getpermissableemployees(eid); var allemployees= datacontext.set<employee>(); query = item in allemployees.where(predicate) join t in availableemployees on item.employeeid equals t.employeeid select item;
Comments
Post a Comment