為什么使用FromSqlRaw時，AsEnumerable不起作用？

我目前正在創建一個網站，在一個資料表中展示我所有的病人，我必須使用FromSqlRaw來從資料庫中獲取資料。我有一個搜索功能，允許我在表中搜索病人，但在進入頁面時，當我使用AsQueryable時，我得到這個錯誤，表中沒有顯示任何資料。它建議我使用AsEnumerable，但當我這樣做時，我得到一個intellisense錯誤。有什么辦法可以解決嗎？

 public async Task< IActionResult> Search(StaySearchViewModel model) 
    {
        if (model.Cleared)
        {
            return Json(new)
            {
                draw = model.Draw,
                data = new object[ ] { },
                recordsFiltered = 0,
                recordsTotal = 0,
                總數 = 0, 記錄總數 = 0, 總數 = 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）。


            //創建模型
            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)。
    {

            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) 。
            }
            elseif (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。
    }

uj5u.com熱心網友回復：

試試這個

你不能通過model.SortOrder對記錄進行排序，因為它與記錄沒有關系。 你只能做這樣的事情

 if (!string.IsNullOrEmpty（model.SortOrder）) records = records.OrderBy（r=>；r.Id）。

uj5u.com熱心網友回復：

因為你的源資料是一個存盤程序，你不能在它上面組成額外的查詢運算式。相反，你必須將其加載到記憶體中，正如錯誤所建議的那樣，通過列舉結果集。

包含相關資料
SQL Server不允許在存盤程序呼叫上進行合成，所以任何試圖在這種呼叫上應用額外查詢運算子的行為都會導致無效的SQL。在FromSqlRaw或FromSqlInterpolated方法之后立即使用AsEnumerable或AsyncEnumerable方法，以確保EF Core不會嘗試對存盤程序進行合成。

var records = db.SpStaySearches.FromSqlRaw("dbo.spStaysSearch"/span>)
                               .ToList()
                               .AsQueryable()

var sql = @"
選擇 
    tblStays.*, tblOccupant.OccupantID。
    tblOccupant.FileAs AS OccupantFileAs。
    IIF(tblStays.BuildingName LIKE 'Main Shelter',
           tblOccupant.OCFSMainNumber,
           tblOccupant.OCFSNorthNumber) AS StayOCFSNumber, 
    COALESCE([CountOccupantStayID], 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
) 在tblStays.StayID = OccupantStays_CountOfChildren.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）。