时间戳

首页 > 解决方案 > Entity Framework Core + 使用 Group By 计数

linq - Entity Framework Core + 使用 Group By 计数

问题描述

我有一个包含约 60 万条记录和 33 列的表。在我的项目中，我使用 EF Core (2.0.1) 从数据库中检索数据。我遇到以下代码问题：

 var theCounter = (from f in _context.tblData.Take(100000)
         group f by f.TypeId into data
         select new DataDto { ID = data.Key, Count = data.Count() }).ToList();

此代码是 REST API 的一部分，当我从 SOAP UI 对其进行测试时，我遇到了超时错误。当我测试代码时

采取(1000)

大约有 300 个独特的 TypeId。它工作正常。有什么想法可以让它发挥作用吗？

-- 编辑 1：

这是我在调试代码时看到的：

Microsoft.EntityFrameworkCore.Query:Warning: Query: '(from TblData <generated>_1 in DbSet<TblData> select [<generated>_1]).Take(__p_0)' uses a row limiting operation (Skip/Take) without OrderBy which may lead to unpredictable results.
Microsoft.EntityFrameworkCore.Query:Warning: Query: '(from TblData <generated>_1 in DbSet<TblData> select [<generated>_1]).Take(__p_0)' uses a row limiting operation (Skip/Take) without OrderBy which may lead to unpredictable results.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy([f].TypeId, [f])' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy([f].TypeId, [f])' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (131ms) [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT [t2].[Id], [t2].[at], [t2].[add], [t2].[AddDate], [t2].[aftc], [t2].[aftcd], [t2].[aid], [t2].[afl], [t2].[prdid], [t2].[cid], [t2].[TypeId], [t2].[env], [t2].[ext], [t2].[extddcode], [t2].[fn], [t2].[fn], [t2].[fic], [t2].[gid], [t2].[grp], [t2].[hnm], [t2].[IP], [t2].[icid], [t2].[ln], [t2].[lg], [t2].[pcid], [t2].[ret], [t2].[rts], [t2].[rnam], [t2].[sled], [t2].[seq], [t2].[sid], [t2].[styp]
FROM (
    SELECT TOP(@__p_0) [t1].[Id], [t1].[at], [t1].[add], [t1].[AddDate], [t1].[aftc], [t1].[aftcd], [t1].[aid], [t1].[afl], [t1].[prdid], [t1].[cid], [t1].[TypeId], [t1].[env], [t1].[ext], [t1].[extddcode], [t1].[fn], [t1].[fn], [t1].[fic], [t1].[gid], [t1].[grp], [t1].[hnm], [t1].[IP], [t1].[icid], [t1].[ln], [t1].[lg], [t1].[pcid], [t1].[ret], [t1].[rts], [t1].[rnam], [t1].[sled], [t1].[seq], [t1].[sid], [t1].[styp]
    FROM [TblData] AS [t1]
) AS [t2]
WHERE [t2].[TypeId] IS NOT NULL
ORDER BY [t2].[TypeId]

我认为它没有正确翻译。任何想法为什么？

-- 编辑 2：

我已将查询更改为：

    var query = _context.TblData
        .Select(a => new {ID = a.Id, TypeId= a.TypeId})
        .Distinct();

    var q1 = query.GroupBy(p => p.TypeId)
        .Select(g => new DataDto {TypeId= g.Key, Count = g.Count()});

    return await q1.ToListAsync();

但它被翻译成：

SELECT DISTINCT [a0].[Id], [a0].[TypeId] AS [TypeId]
FROM [tblData] AS [a0]
ORDER BY [a0].[TypeId]

当我直接在数据库中检查时，这个查询需要 14 秒才能执行。知道为什么它没有被翻译成类似的东西：

SELECT DISTINCT [a0].[Id], COUNT([TypeId]) AS [TypeId]
FROM [tblData] AS [a0]
GROUP BY COUNT([a0].[Id])
ORDER BY [a0].[TypeId]

标签： linqentity-framework-core

解决方案

我不得不将 EF Core 版本升级到 2.1，LINQ 现在已正确转换为 SQL。

推荐阅读