c# - Why does AsEnumerable not work when using FromSqlRaw

Question

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

Answer 1

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