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;
}
CodePudding user response：
Try this
var records = getSearchData(model).ToList();
var count = records.Count;
.....
You can't order records by model.SortOrder since it has nothing to do with records. You can only do something like this
if (!string.IsNullOrEmpty(model.SortOrder)) records = records.OrderBy(r=> r.Id);
CodePudding user response：
because your source data is a Stored Procedure, you cannot compose additional query expressions over the top of it. Instead you must load it into memory, as the error suggests, by enumerating the result set.
Including Related Data
SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.
The obvious way to interpret this in the code is to call .ToList() on the results from the SP, then to match your existing code pattern you can cast that result back to IQueryable:
var records = db.SpStaySearches.FromSqlRaw("dbo.spStaysSearch")
.ToList()
.AsQueryable()
Using
AsEnumerable()is sometimes problematic as there are many different libraries that you may have implemented that might all provide anAsEnumerable()extension method.
We have to do this because even in SQL you cannot simply select from an SP and then add where clauses to it, you first have to read the results into a temporary table or a table variable, then you can re-query from the result set, that is what we are effectively doing now, we are reading the results into a C# variable (.ToList()) and then composing an in-memory query over the top of that result.
If your search logic must be encapsulated in a stored procedure, then given the technical limitations, the usual expectation is that you would add the search arguments as optional parameters to the stored procedure, rather then tring to add filter clauses on top of the results in C#.
We can help with how to move your filter logic into
dbo.spStaysSearchbut you'll have to post the content of that SP, ideally as a new question.
Instead of using an SP at all, where we lose practically all the goodness that EF can offer us, an alternative approach is to replace your SP entirely with a raw SQL then the rest of your logic will work as expected.
var sql = @"
SELECT
tblStays.*, tblOccupant.OccupantID,
tblOccupant.FileAs AS OccupantFileAs,
IIF(tblStays.BuildingName LIKE 'Main Shelter',
tblOccupant.OCFSMainNumber,
tblOccupant.OCFSNorthNumber) AS StayOCFSNumber,
COALESCE([CountOfOccupantStayID], 0) AS CountOfChildren,
tblCaseManager.FileAs AS CaseManager,
StaysMaxBillSentDate.MaxOfBillSentDate
FROM tblStays
LEFT JOIN tblOccupantStays ON tblStays.StayID = tblOccupantStays.StayID
LEFT JOIN tblOccupant ON tblOccupantStays.OccupantID = tblOccupant.OccupantID
LEFT JOIN (
SELECT lkpOccStays.StayID
, COUNT(tblOccupantStays.OccupantStayID) AS CountOfOccupantStayID
FROM tblOccupantStays lkpOccStays
INNER JOIN tblOccupant lkpChild ON lkpOccStays.OccupantID = lkpChild.OccupantID
WHERE lkpChild.OccupantType LIKE 'Child'
GROUP BY lkpOccStays.StayID
) OccupantStays_CountOfChildren ON tblStays.StayID = OccupantStays_CountOfChildren.StayID
LEFT JOIN tblCaseManager ON tblStays.CaseManagerID = tblCaseManager.CaseManagerID
LEFT JOIN (SELECT tblStayBillingHx.StayID
, MAX(tblStayBillingHx.BillSentDate) AS MaxOfBillSentDate
FROM tblStayBillingHx
GROUP BY tblStayBillingHx.StayID
) StaysMaxBillSentDate ON tblStays.StayID = StaysMaxBillSentDate.StayID
";
var records = db.SpStaySearches.FromSqlRaw(sql);
In this way the SP is providing the structure of the resultset, which might be necessary if you are using the Database-First approach but you are no longer executing the SP at all.
The SQL in this answer is provided as a guide to the syntax only, there is not enough information available to determine the validity of the query or that the results conform to your business requirements.