使用linq在EFCore中計算評分平均值-有解無憂

用戶可以按城市、品牌、服務型別和評級搜索公司。我開發了這樣的查詢，但在評級部分出現錯誤。錯誤資訊如下：

LINQ 運算式 'DbSet .Join( outer: DbSet,inner: f => f.Id, outerKeySelector: c => c.FirmId,innerKeySelector: (f, c) => new TransparentIdentifier<Firm, Comment>( outer = f ,inner = c )) .GroupBy( source: ti => ti.Outer, keySelector: ti => new {firm = ti.Outer, rating = ti.Inner.Rate })' 無法翻譯。以可翻譯的形式重寫查詢，或通過插入對 AsEnumerable()、AsAsyncEnumerable()、ToList() 或 ToListAsync() 的呼叫顯式切換到客戶端評估。有關詳細資訊，請參閱 https://go.microsoft.com/fwlink/?linkid=2101038。

添加評分部分后我遇到了這個錯誤。我怎樣才能解決這個問題？我認為以這種方式提取資料不會有效。我應該如何開發這個地方？我還與您分享了我用于查詢的代碼。

public async Task<IEnumerable<Firm>> GetFirmsForCustomerSearch(int cityId, int brandId, int serviceTypeId, int rate)
        {
           var query = from firms in AracsalContext.Firm select firms;
            
            if (brandId > 0)
                query = from firms in query
                        join firmBrands in AracsalContext.Firmbrand on new { f1 = firms.Id, f2 = brandId } equals new { f1 = firmBrands.FirmId, f2 = firmBrands.BrandId }
                        select firms;

            if (serviceTypeId > 0)
                query = from firms in query
                        join firmServices in AracsalContext.Firmservice on new { f1 = firms.Id, f2 = serviceTypeId } equals new { f1 = firmServices.FirmId, f2 = firmServices.ServiceId }
                        select firms;

            if (cityId > 0)
                query = from firms in query
                        where firms.CityId == cityId
                        select firms;

            if (rate > 0)
            {
                query = from firms in query
                        join comments in AracsalContext.Comment on firms.Id equals comments.FirmId
                        group new
                        {
                            firm = firms,
                            rating = comments.Rate
                        } by firms into g
                        where g.Average(r => r.rating) > rate
                        select g.Key;
            }


            var result = await query.ToListAsync();
            return result;
        }

非常感謝。齋月

uj5u.com熱心網友回復：

SQL 中的分組有局限性 - 您只能回傳分組鍵和聚合結果。要回傳整個記錄，您必須加入原始查詢增益。

if (rate > 0)
{
    filteredByRate = 
        from firms in query
        join comments in AracsalContext.Comment on firms.Id equals comments.FirmId
        group new
        {
            rating = comments.Rate
        } by new { firms.Id } into g
        where g.Average(r => r.rating) > rate
        select g.Key;

    query = 
        from films in query
        join f in filteredByRate on films.Id equals f.Id
        select films;
}

uj5u.com熱心網友回復：

您需要按一些 Id 進行分組，例如 Companies.Id

query = from firms in query
                        join comments in AracsalContext.Comment on firms.Id equals comments.FirmId
                        group new
                        {
                            firm = firms,
                            rating = comments.Rate
                        } by firms.Id into g
                        where g.Average(r => r.rating) > rate
                        select g.Key;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/356610.html

標籤：C＃表现林克 asp.net核心查询优化

上一篇：了解C#FromService

下一篇：ASP.NETCore6-異步流以及額外的元資料