//Get list of records public static List <eMacroAreaMList> getRecordForSearch(OneFineRateAppUtil.jQueryDataTableParamModel param, out int TotalCount) { using (OneFineRateEntities db = new OneFineRateEntities()) { List <eMacroAreaMList> macroarea = new List <eMacroAreaMList>(); param.sSearch = param.sSearch == null ? "" : param.sSearch; //for searching // var objTblMacroArea1 = db.Database.SqlQuery<eMacroAreaMList>("uspGetMacroAreas"); var objTblMacroArea = (from m in db.tblMacroAreaMs join c in db.tblCountryMs on m.iCountryId equals c.iCountryId join s in db.tblStateMs on m.iStateId equals s.iStateId join ct in db.tblCityMs on m.iCityId equals ct.iCityId join u in db.tblUserMs on m.iActionBy equals u.iUserId into lt from T in lt.DefaultIfEmpty() join p in db.tblPropertyMs on m.iAreaId equals p.iAreaId into MP from Pr in MP.DefaultIfEmpty() select new { m.iAreaId, m.iCountryId, c.sCountry, m.iStateId, s.sState, m.iCityId, ct.sCity, m.sArea, cStatus = m.cStatus == "A" ? "Live" : "Disabled", m.dtActionDate, iActionBy = T.sFirstName + " " + T.sLastName, ipropId = MP.Count() } ).Distinct().Where(u => u.sCountry.Contains(param.sSearch) || u.sState.Contains(param.sSearch) || u.sCity.Contains(param.sSearch) || u.sArea.Contains(param.sSearch)).AsQueryable(); //var objTblMacroArea = objTblMacroArea1.Where(u => u.sCountry.Contains(param.sSearch) || u.sState.Contains(param.sSearch) // || u.sCity.Contains(param.sSearch) || u.sArea.Contains(param.sSearch)).AsQueryable(); //get Total Count for show total records TotalCount = objTblMacroArea.Count(); //for sorting switch (param.iSortingCols) { case 0: objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sCountry) : objTblMacroArea.OrderByDescending(u => u.sCountry); break; case 1: objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sState) : objTblMacroArea.OrderByDescending(u => u.sState); break; case 2: objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sCity) : objTblMacroArea.OrderByDescending(u => u.sCity); break; case 3: objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.sArea) : objTblMacroArea.OrderByDescending(u => u.sArea); break; case 4: objTblMacroArea = param.sortDirection == "asc" ? objTblMacroArea.OrderBy(u => u.cStatus) : objTblMacroArea.OrderByDescending(u => u.cStatus); break; } ////implemented paging var data = objTblMacroArea.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList(); //macroarea.AddRange(data.Select(item => (eMacroAreaMList)OneFineRateAppUtil.clsUtils.ConvertToObject(item, new eMacroAreaMList()))); //convert bll entity object to db entity object foreach (var item in data) { //item.cStatus = item.cStatus == "A" ? "Live" : "Disabled"; macroarea.Add((eMacroAreaMList)OneFineRateAppUtil.clsUtils.ConvertToObject(item, new eMacroAreaMList())); } return(macroarea); } }