entity-framework - EF Core 2.2 LINQ查询在EF Core 3.0中不起作用

Question

下面的代码在EF Core 2.2上正常工作，但在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();

我得到的错误:

Processing of the LINQ expression 'AsQueryable(Select<AssetCategory, string>(source: NavigationTreeExpressionValue: 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.

需要帮助

Answer 1

最初的查询有问题，但是EF Core将其隐藏在地毯下，从而减慢了一切。

当客户端评估在LINQ中引入SQL并在Entity Framework中删除时，它是邪恶的。我不认为为什么人们将它重新添加到EF Core中是一个好主意，但是现在它已经消失了是一件好事。原始查询也不会在EF 6.2中运行。

原始查询需要一些修复，这可能会导致性能提高。首先，从关系和导航属性生成联接是ORM的工作。

其次，即使在SQL中，也无法在SELECT子句中添加不属于GROUP BY或集合的字段。除非有人使用窗口函数，否则没有等效于FirstOrDefault()的聚合函数。

要在SQL中获取类别名称，我们必须将其包含在GROUP BY中，或者使用CTE /子查询按ID分组，然后查找类别名称，例如:

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

要么

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

LINQ中第一个查询的等效项是:

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

如果对实体进行了修改，例如Asset具有Category属性，则查询可以简化为:

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

尽管这需要进行一些测试，以确保它创建了一个合理的查询。过去有一些惊喜，我还没有时间在最终的EF Core 3.0中检查生成的SQL。

更新

LINQPad 6可以使用EF Core 3，甚至可以使用外键约束从数据库生成DbContext。

这个查询

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

生成一个不错的SQL查询:

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]

使用 join会生成相同的SQL查询。