Is it a lazy loading query or Eager Loading ? , Slow Query, Performance needed please

First, I would like to know if this query is Lazy loading or Eager loading. I Read a lot on both, and not sure if I understand the difference between each other.

2- I Get this query, This query take a lot of time to execute. Anybody have some suggest when you see this query. I'll do all modification needed to speed up this query.

Note: I just want your opinion about this query and method.

Thanks a 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?> ListUsage开发者_如何学PythonID = 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 is use for Permission
            //Get all the user permission about group and 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 = from 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 is deferring initialization of an object until the point at which it is needed. If you returned your ListLocation back to its caller, as you've written above, with no .ToList() (or other), then you'd be consuming this lazily.

Eager loading is having the results of your query gathered at the time that the query is defined. In this case it'd be retrieving the results of your LINQ query all at once (at the time that the query is constrcuted). Usually a .ToList() or .Single() or other will do this for you.

I suspect you're consuming the results your LINQ query (var ListLocation) later in your code. Your code above is using a lazy-loaded approach.

You're showing that you're calling .ToList(), so you're indeed using eager-loading; even though it's on a different statement/line of code.

Performance: I'm not 100% on this being your perf problem, but I'd refactor your LINQ into something like this, using an extension method .WhereIf(). It's a heck of a lot easier to read and write.

 var ListLocation =  from 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

---

If you're using this, and it works, then you're probably lazy loading, since you don't have any calls to .Include().