//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);
}
}
