EF Core 2.2 LINQ查询在EF Core 3.0中不起作用 [英] EF Core 2.2 LINQ query not working in EF Core 3.0

问题描述

下面的代码在EF Core 2.2上正常工作，但在EF Core 3.0上不工作

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

我遇到的错误：

处理LINQ表达式'AsQueryable（Select（
来源：NavigationTreeExpression
值：default（IGrouping）
表达式：（未处理的参数：e），
选择器：（p）=> p.CategoryName））''NavigationExpandingExpressionVisitor'
失败。这可能表示EF Core中存在错误或限制。
请参阅 https://go.microsoft.com/fwlink/?linkid=2101433 获取更多详细信息。

需要帮助

编辑：
如下解决了该问题

solved it as below

        var items = Context.Assets.AsEnumerable().GroupBy(p => p.CategoryName).Select(p => new AssetCategorySummary
        {
            CategoryId = p.Select(r => r.CategoryId).FirstOrDefault(),
            CategoryName = p.Select(r => r.CategoryName).FirstOrDefault(),
            TotalAsset = p.Count()
        }).ToList();

但我认为这并不有效。

but I think this is not effective.

推荐答案

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

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

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

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.

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

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.

第二，即使在SQL中，也不可能在SELECT子句中添加不属于 GROUP BY 或汇总。除非有人使用窗口函数，否则没有等效于 FirstOrDefault（）的聚合函数。

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.

要获取以下类别名称SQL，我们要么将其包含在GROUP BY中，要么使用CTE /子查询按ID进行分组，然后查找类别名称，例如：：

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

或

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

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

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

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

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

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

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

更新

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

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

此查询

 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查询：

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]

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

Using join generates the same SQL query.

这篇关于EF Core 2.2 LINQ查询在EF Core 3.0中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！