I'm currently creating a site that showcases all my patients within a data table and I have to use FromSqlRaw in order to get the data from my database. I have a search funtion that allows me to search the patients within the table but upon entering the page I get this error when I use AsQueryable and no data is displayed in the table. It recommends me to use AsEnumerable but when I do I get an intellisense error. Any ideas on how to fix?
public async Task<IActionResult> Search(StaySearchViewModel model)
{
if (model.Cleared)
{
return Json(new
{
draw = model.Draw,
data = new object[] { },
recordsFiltered = 0,
recordsTotal = 0,
total = 0
});
}
var records = getSearchData(model);
//var records = System.Linq.Enumerable.AsEnumerable(getSearchData(model)); // Hard coding this an enumerable will break line 55, 57, and 64
//Sorting
if (!string.IsNullOrEmpty(model.SortOrder))
records = records.OrderBy(model.SortOrder);
var count = await records.CountAsync().ConfigureAwait(false);
records = records.Skip(model.Start);
if (model.Length != -1) records = records.Take(model.Length);
// Create models
var result = new List<SpStaySearchResultViewModel>();
try
{
await records.ForEachAsync(r =>
{
result.Add(new SpStaySearchResultViewModel()
{
BuildingName = r.BuildingName,
CaseManager = r.CaseManager,
CaseManagerId = r.CaseManagerId,
OccupantFileAs = r.OccupantFileAs,
StayOCFSNumber = r.StayOCFSNumber,
StayId = r.StayId,
MaxOfBillSentDate = r.MaxOfBillSentDate,
CountOfChildren = r.CountOfChildren,
StartDate = r.StartDate,
EndDate = r.EndDate,
OccupantId = r.OccupantId,
IsActive = r.IsActive,
});
}).ConfigureAwait(false);
}
catch (Exception e) { }
return Json(new
{
draw = model.Draw,
data = result,
recordsFiltered = count,
recordsTotal = count,
});
}
private IQueryable<spStaysSearch> getSearchData(StaySearchViewModel model)
{
var records = db.SpStaySearches.FromSqlRaw("dbo.spStaysSearch").AsQueryable();
if (model.OccupantId.HasValue)
records = records.Where(x => x.OccupantId == model.OccupantId);
if (!string.IsNullOrWhiteSpace(model.OccupantFileAs))
records = records.Where(x => x.OccupantFileAs == model.OccupantFileAs);
if (!string.IsNullOrWhiteSpace(model.BuildingName))
records = records.Where(x => x.BuildingName == model.BuildingName);
if (!string.IsNullOrWhiteSpace(model.CaseManager))
records = records.Where(x => x.CaseManager == model.CaseManager);
if (!string.IsNullOrWhiteSpace(model.BuildingName))
records = records.Where(x => x.BuildingName == model.BuildingName);
if (model.IntakeDateStart.HasValue && model.IntakeDateEnd.HasValue)
{
records = records.Where(x => x.StartDate >= model.IntakeDateStart && x.StartDate <= model.IntakeDateEnd);
}
else
{
if (model.IntakeDateStart.HasValue)
records = records.Where(x => x.StartDate >= model.IntakeDateStart);
if (model.IntakeDateEnd.HasValue)
records = records.Where(x => x.StartDate <= model.IntakeDateEnd);
}
if (model.ExitDateStart.HasValue && model.ExitDateEnd.HasValue)
{
records = records.Where(x => x.EndDate >= model.ExitDateStart && x.EndDate <= model.ExitDateEnd);
}
else
{
if (model.ExitDateStart.HasValue)
records = records.Where(x => x.EndDate >= model.ExitDateStart);
if (model.ExitDateEnd.HasValue)
records = records.Where(x => x.EndDate <= model.ExitDateEnd);
}
if (model.IsActive.HasValue)
records = records.Where(x => x.IsActive == model.IsActive);
return records;
}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…