原始查询有问题，但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.