在使用分組依據的LINQ查詢中使用DefaultIfEmpty()時出錯

我有以下 LINQ (to SQL Server) 查詢：

var railcarsByProduct = await (from r in DbContext.Railcars
                               let p = DbContext.ProductAliases
                                   .Where(pa => pa.Product.Company.CompanyCode == companyCode && pa.Alias == r.Product)
                                   .Select(pa => pa.Product.Name)
                                   .FirstOrDefault()
                               where r.Facility.Company.CompanyCode == companyCode && r.Departure == null
                               group r by p into productGroup
                               select new { Product = productGroup.Key, Count = productGroup.Count() }
                              ).ToListAsync();

這作業正常。但是，ProductAliases子查詢有可能回傳 null。在這種情況下，我想默認為r.Product.

我嘗試添加DefaultIfEmpty()到子查詢。

var railcarsByProduct = await (from r in DbContext.Railcars
                               let p = DbContext.ProductAliases
                                   .Where(pa => pa.Product.Company.CompanyCode == companyCode && pa.Alias == r.Product)
                                   .Select(pa => pa.Product.Name)
                                   .DefaultIfEmpty(r.Product)
                                   .FirstOrDefault()
                               where r.Facility.Company.CompanyCode == companyCode && r.Departure == null
                               group r by p into productGroup
                               select new { Product = productGroup.Key, Count = productGroup.Count() }
                              ).ToListAsync();

但這給出了一個錯誤。

'LINQ 運算式 'DbSet() .Where(pa => pa.Product.Company.CompanyCode == __companyCode_0 && pa.Alias == r.Outer.Outer.Product) .Select(pa => pa.Product.Name) .DefaultIfEmpty(r.Outer.Outer.Product)' 無法翻譯。以可翻譯的形式重寫查詢，或通過插入對“AsEnumerable”、“AsAsyncEnumerable”、“ToList”或“ToListAsync”的呼叫顯式切換到客戶端評估。

接下來，我嘗試處理group by子句中的 null 情況。

var railcarsByProduct = await (from r in DbContext.Railcars
                               let p = DbContext.ProductAliases
                                   .Where(pa => pa.Product.Company.CompanyCode == companyCode && pa.Alias == r.Product)
                                   .Select(pa => pa.Product.Name)
                                   .FirstOrDefault()
                               where r.Facility.Company.CompanyCode == companyCode && r.Departure == null
                               group r by p ?? r.Product into productGroup
                               select new { Product = productGroup.Key, Count = productGroup.Count() }
                              ).ToListAsync();

但這也給出了完全相同的錯誤。

我知道可以只洗掉所有行，然后將它們分組到 C# 代碼中。但是有沒有人看到我不需要這樣做的方式？

uj5u.com熱心網友回復：

嘗試使用類似下面的東西。

var railcarsByProduct = await (from r in DbContext.Railcars
                               let p = DbContext.ProductAliases
                                   .Where(pa => pa.Product.Company.CompanyCode == companyCode && pa.Alias == r.Product)
                                   .Select(pa => pa.Product.Name)
                                   .FirstOrDefault() ?? r.Product
                               where r.Facility.Company.CompanyCode == companyCode && r.Departure == null
                               group r by p into productGroup
                               select new { Product = productGroup.Key, Count = productGroup.Count() }
                              ).ToListAsync();

不確定它是否會起作用，但我知道 DefaultIfEmpty 很奇怪，或者過去對物體框架很奇怪。

uj5u.com熱心網友回復：

嘗試以下查詢：

var query = 
    from r in DbContext.Railcars
    from p in DbContext.ProductAliases
        .Where(pa => pa.Product.Company.CompanyCode == companyCode && pa.Alias == r.Product)
        .Select(pa => pa.Product.Name)
        .Take(1)
        .DefaultIfEmpty()
    where r.Facility.Company.CompanyCode == companyCode && r.Departure == null
    group r by p ?? r.Product into productGroup
    select new 
    { 
        Product = productGroup.Key, 
        Count = productGroup.Count() 
    };

標籤：C＃ 实体框架 林克 实体框架核心

上一篇：在Linq查詢中使用“out”

下一篇：檢查字串值的斷言包括字串陣列