asp.net mvc 2 - Is it a lazy loading query or Eager Loading ? , Slow Query, Performance needed please -
first, know if query lazy loading or eager loading. read lot on both, , not sure if understand difference between each other.
2- query, query take lot of time execute. have suggest when see query. i'll modification needed speed query.
note: want opinion query , method.
thanks lot.
public searchlocationviewmodel getsearchlocationviewmodel( string civicnumber = null , string street = null, string city = null, list<int?> listcountryid = null, list<int?> liststateid = null, int isactive =1, string sortfield ="filenumber", string sortdirection = "asc" , list<int?> grpdescid1 = null, list<int?> grpdescid2 = null, list<int?> grpdescid3 = null, list<int?> grpdescid4 = null, int locationtypeid = -1, list<int?> listusageid = null) { if (grpdescid1 == null) { grpdescid1 = new list<int?>(); } if (grpdescid2 == null) { grpdescid2 = new list<int?>(); } if (grpdescid3 == null) { grpdescid3 = new list<int?>(); } if (grpdescid4 == null) { grpdescid4 = new list<int?>(); } if (listcountryid == null) { listcountryid = new list<int?>(); } if (liststateid == null) { liststateid = new list<int?>(); } if (listusageid == null) { listusageid = new list<int?>(); } grpdescid1.remove(grpdescid1.singleordefault(p => p < 0)); grpdescid2.remove(grpdescid2.singleordefault(p => p < 0)); grpdescid3.remove(grpdescid3.singleordefault(p => p < 0)); grpdescid4.remove(grpdescid4.singleordefault(p => p < 0)); listcountryid.remove(listcountryid.singleordefault(p => p < 0)); liststateid.remove(liststateid.singleordefault(p => p < 0)); listusageid.remove(listusageid.singleordefault(p => p < 0)); int lang = basestaticclass.currentlangid(); int userid = convert.toint32(session["userid"]); searchlocationviewmodel viewmodel = initsearchviewmodel(); igrprepository repgrp = new grprepository(_db); icountryrepository repcountry = new countryrepository(_db); iprovincerepository repprovince = new provincerepository(_db); viewmodel.perm = reppermission; viewmodel. civicnumber = civicnumber ; viewmodel. street = street; viewmodel. city = city; viewmodel. isactive =isactive; viewmodel. sortfield =sortfield; viewmodel. sortdirection = sortdirection ; viewmodel.listcountry = repcountry.getcountryforsearchbyuser(true,userid); viewmodel.listprovince = repprovince.getprovincesearchbyuserid(true, userid); viewmodel.listgrpdescid1 =grpdescid1; viewmodel.listgrpdescid2 = grpdescid2; viewmodel.listgrpdescid3 = grpdescid3; viewmodel.listgrpdescid4 = grpdescid4; viewmodel.listcountryid = listcountryid; viewmodel.liststateid = liststateid; viewmodel.locationtypeid = locationtypeid; viewmodel.listusageid = listusageid; var locationtype = new selectlist(repgeneric.gettextbycurrentlang<locationtype, locationtypetext>(), "id", "txt").tolist(); bc.adddropdownsearchvaluenonew(ref locationtype); var listusage = new selectlist(repgeneric.gettextbycurrentlang<usage, usagetext>(), "id", "txt").tolist(); viewmodel.listusage = listusage; viewmodel.listlocationtype = locationtype; var listgrp1 = new selectlist(repgrp.getallgrpdesctextforuserbylevel(userid, 1).asenumerable(), "grpdescid", "grpdesctxt").tolist(); var listgrp2 = new selectlist(repgrp.getallgrpdesctextforuserbylevel(userid, 2).asenumerable(), "grpdescid", "grpdesctxt").tolist(); var listgrp3 = new selectlist(repgrp.getallgrpdesctextforuserbylevel(userid, 3).asenumerable(), "grpdescid", "grpdesctxt").tolist(); var listgrp4 = new selectlist(repgrp.getallgrpdesctextforuserbylevel(userid, 4).asenumerable(), "grpdescid", "grpdesctxt").tolist(); var t1 = listgrp1.select(s => (int?)convert.toint32(s.value)); var t2 = listgrp2.select(s => (int?)convert.toint32(s.value)); var t3 = listgrp3.select(s => (int?)convert.toint32(s.value)); var t4 = listgrp4.select(s => (int?)convert.toint32(s.value)); viewmodel.listgrp1 = listgrp1; viewmodel.listgrp2 = listgrp2; viewmodel.listgrp3 = listgrp3; viewmodel.listgrp4 = listgrp4; viewmodel.listgrptogether = new list<selectlistitem>(); if(viewmodel.grpname1 != "") viewmodel.listgrptogether.add(new selectlistitem() { text = viewmodel.grpname1 ,value = "1"}); if (viewmodel.grpname2 != "") viewmodel.listgrptogether.add(new selectlistitem() { text = viewmodel.grpname2, value = "2" }); if (viewmodel.grpname3 != "") viewmodel.listgrptogether.add(new selectlistitem() { text = viewmodel.grpname3, value = "3" }); if (viewmodel.grpname4 != "") viewmodel.listgrptogether.add(new selectlistitem() { text = viewmodel.grpname4, value = "4" }); viewmodel.listgrptogether.insert(0, new selectlistitem() { text = viewres.generalstring.choose, value = "-1", selected = true }); int iuserid = convert.toint32(session["userid"]); //this use permission //get user permission group , province ienumerable<int?> usrgrpdesc = _db.usergroupdescs.where(p => p.userid == iuserid).select(s => s.grpdescid); ienumerable<int?> usrprovince = _db.userprovinces.where(p => p.userid == iuserid).select(s => s.prvid); var listlocation = s in _db.locations.where(p => p.isdelete == false && (isactive < 0 || isactive == (p.isactive == true ? 1 : 0)) && (locationtypeid < 0 || locationtypeid == p.locationtypeid) && (city == null || p.address.city.cityname.contains(city)) && (listusageid.count() == 0 || p.premises.select(gs => gs.usageid).intersect(listusageid).any()) && (listcountryid.count() == 0 || listcountryid.any(pl => pl == p.address.city.province.country.ctryid)) && (liststateid.count() == 0 || liststateid.any(pl => pl == p.address.city.province.prvid)) && (street == null || p.address.street.contains(street)) && (civicnumber == null || p.address.civicnumber.contains(civicnumber)) && ((grpdescid1.count() == 0 )|| p.grouplocations.select(gs => gs.grpdescid).intersect(grpdescid1).any()) && ((grpdescid2.count() == 0)|| p.grouplocations.select(gs => gs.grpdescid).intersect(grpdescid2).any()) && ((grpdescid3.count() == 0) || p.grouplocations.select(gs => gs.grpdescid).intersect(grpdescid3).any()) && ((grpdescid4.count() == 0 ) || p.grouplocations.select(gs => gs.grpdescid).intersect(grpdescid4).any()) && (p.grouplocations.select(gs => gs.grpdescid).intersect(usrgrpdesc).any()) && ((p.address.city == null || usrprovince.any(ps => ps.value == p.address.city.prvid))) ) select new locationviewmodel() { locationid = s.locationid, locationtypeid = s.locationtypeid, long = s.address.longitude, lat = s.address.latitude, filenumber = s.locationfile, state = s.address.city.province.prvname, city = s.address.city.cityname, address = s.address.civicnumber + " " + s.address.street, status = s.locationtype.locationtypetexts.where(h => h.txt != "" && h.langid == lang || h.langid == 1).orderbydescending(g => g.langid).firstordefault().txt, listgroupe1 = s.grouplocations.where(g=>g.grpdesc.grp.grplevel == 1).select(grploc => grploc.grpdesc.grpdesctexts.where(h => h.grpdesctxt != "" && (h.langid == lang || h.langid == 1)).orderbydescending(g => g.langid).firstordefault()).select(txt => txt.grpdesctxt), listgroupe2 = s.grouplocations.where(g => g.grpdesc.grp.grplevel == 2).select(grploc => grploc.grpdesc.grpdesctexts.where(h => h.grpdesctxt != "" && (h.langid == lang || h.langid == 1)).orderbydescending(g => g.langid).firstordefault()).select(txt => txt.grpdesctxt), listgroupe3 = s.grouplocations.where(g=>g.grpdesc.grp.grplevel == 3).select(grploc => grploc.grpdesc.grpdesctexts.where(h => h.grpdesctxt != "" && (h.langid == lang || h.langid == 1)).orderbydescending(g => g.langid).firstordefault()).select(txt => txt.grpdesctxt), listgroupe4 = s.grouplocations.where(g=>g.grpdesc.grp.grplevel == 4).select(grploc => grploc.grpdesc.grpdesctexts.where(h => h.grpdesctxt != "" && (h.langid == lang || h.langid == 1)).orderbydescending(g => g.langid).firstordefault()).select(txt => txt.grpdesctxt), defaultimgpath = s.locationpictures.where(p=>p.isdefault == true && p.isactive == true).firstordefault().filepath, haspremises = s.premises.any(p => p.isactive == true && p.isdelete == false) }; viewmodel.listlocation = listlocation.tolist(); return viewmodel; }
lazy loading deferring initialization of object until point @ needed. if returned listlocation caller, you've written above, no .tolist() (or other), you'd consuming lazily.
eager loading having results of query gathered @ time query defined. in case it'd retrieving results of linq query @ once (at time query constrcuted). .tolist() or .single() or other you.
i suspect you're consuming results linq query (var listlocation) later in code. code above using lazy-loaded approach.
you're showing you're calling .tolist(), you're indeed using eager-loading; though it's on different statement/line of code.
performance: i'm not 100% on being perf problem, i'd refactor linq this, using extension method .whereif(). it's heck of lot easier read , write.
var listlocation = s in _db.locations .where(p => p.isdelete == false) .whereif(isactive >= 0, p=> isactive == (p.isactive == true ? 1 : 0)) .whereif(locationtypeid >= 0, p=> locationtypeid == p.locationtypeid .whereif(city!=null, p=> p.address.city.cityname.contains(city)) //etc
Comments
Post a Comment