使用defaultifempty（）在linq查询中使用Group的错误

Question

我有以下LINQ（到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（） 。 .select（pa =＆gt; pa.product.name） 。可以通过插入“可忽略的”，“ asasyncenumerable”，“ tolist”或“ tolistAsync'的呼叫来重写可以翻译的形式的查询，或者明确切换到客户端评估。

接下来，我尝试通过子句在组中处理无效案例。

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＃代码。但是，有人看到了我不需要这样做的方法吗？

Answer 1

尝试使用以下类似的内容。

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

不确定它是否会起作用，但我知道过去的默认设备很奇怪，或者过去对实体框架很奇怪。

Answer 2

尝试以下查询：

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