entity framework - EF Core 2.2 LINQ query not working in EF Core 3.0

Question

Below code works fine at EF Core 2.2 bu not working on EF core 3.0

 var items = (from asset in Context.Assets
              join assetCategory in Context.AssetCategories on asset.CategoryId equals assetCategory.Id
              group assetCategory by assetCategory.Id into assetCategories
              select new AssetCategorySummary
              {
                  CategoryId = assetCategories.Key,
                  CategoryName = assetCategories.Select(p => p.CategoryName).FirstOrDefault(),
                  TotalAsset = assetCategories.Count()
              }).ToListAsync();

the error I am getting:

Processing of the LINQ expression 'AsQueryable(Select<AssetCategory, string>( source: NavigationTreeExpression Value: default(IGrouping<Guid, AssetCategory>) Expression: (Unhandled parameter: e), selector: (p) => p.CategoryName))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

need help please

Answer 1

The original query had problems but EF Core hid it under the carpet, slowing everything down.

Client-side evaluation was evil when it was introduced in LINQ to SQL and removed in Entity Framework. I can't think why people though it would be a good idea to add it back to EF Core, but it's a good thing it's gone now. The original query wouldn't run in EF 6.2 either.

The original query needs a bit of fixing, which will probably result in performance improvements. First of all, it's the ORM's job to generate joins from relations and navigation properties.

Second, even in SQL it's impossible to add a field in the SELECT clause that isn't part of GROUP BY or an aggregate. There's no aggregate function equivalent to FirstOrDefault() unless one uses a windowing function.

To get the category name in SQL, we'd have to either include it in GROUP BY or use a CTE/subquery to group by ID and then look up the category name, eg :

SELECT CategoryID,CategoryName,Count(*)
FROM Assets inner join AssetCategories on CategoryID=AssetCategories.ID
GROUP BY CategoryID,CategoryName

or

SELECT CategoryID,CategoryName,Cnt
FROM (select CategoryID, Count(*) as Cnt
      from Assets
      group by CategoryID) a 
INNER JOIN AssetCategories on CategoryID=AssetCategories.ID

The equivalent of the first query in LINQ would be :

 var items = (from asset in Context.Assets
              join assetCategory in Context.AssetCategories on asset.CategoryId equals assetCategory.Id
              group asset by new {assetCategory.Id,assetCategory.CategoryName} into summary
              select new AssetCategorySummary
              {
                  CategoryId   = summary.Key.Id,
                  CategoryName = summary.Key.Name,
                  TotalAsset   = summary.Count()
              }).ToListAsync();

If the entities are modified so eg Asset has an Category property, the query could be reduced to :

 var items = (from asset in Context.Assets
              group asset by new {asset.Category.Id,asset.Category.CategoryName} into summary
              select new AssetCategorySummary
              {
                  CategoryId   = summary.Key.Id,
                  CategoryName = summary.Key.Name,
                  TotalAsset   = summary.Count()
              }).ToListAsync();

This need some testing though to ensure it creates a sane query. There have been some surprises in the past and I haven't had the time to check the generated SQL in the final EF Core 3.0

Update

LINQPad 6 can use EF Core 3 and even generates a DbContext from a database using the foreign key constraints.

This query

 var items = (from asset in Context.Assets
              group asset by new {asset.Category.Id,asset.Category.CategoryName} into summary
              select new AssetCategorySummary
              {
                  CategoryId   = summary.Key.Id,
                  CategoryName = summary.Key.Name,
                  TotalAsset   = summary.Count()
              }).ToListAsync();

generates a nice SQL query :

SELECT [a0].[ID] AS [CategoryId], [a0].[CategoryName], COUNT(*) AS [TotalAsset]
FROM [Assets] AS [a]
INNER JOIN [AssetCategories] AS [a0] ON [a].[CategoryID] = [a0].[ID]
GROUP BY [a0].[ID], [a0].[CategoryName]

Using join generates the same SQL query.