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