Errors using DefaultIfEmpty() in LINQ query with group by

Question

I have the following LINQ (to SQL Server) query:

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

This is working fine. However, there is the possibility that the ProductAliases subquery will return null. In that case, I want to default to r.Product.

I tried adding DefaultIfEmpty() to the subquery.

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

But this gives an error.

'The LINQ expression '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)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Next, I tried handling the null case within the group by clause.

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

But this also gives the exact same error.

I understand could just bring down all the rows and then group them in C# code. But does anyone see a way where I wouldn't need to do that?

Answer 1

Try the following query:

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