我需要将这个SQL
语句翻译成一个Linq-Entity
查询...
SELECT name, count(name) FROM people
GROUP by name
我需要将这个SQL
语句翻译成一个Linq-Entity
查询...
SELECT name, count(name) FROM people
GROUP by name
查询语法
var query = from p in context.People
group p by p.name into g
select new
{
name = g.Key,
count = g.Count()
};
方法语法
var query = context.People
.GroupBy(p => p.name)
.Select(g => new { name = g.Key, count = g.Count() });
但是在控制台/日志中一定要留意错误信息。如果你看到通知说无法将查询转换为 SQL 并且将在本地求值,那么你可能需要重写它。
Entity Framework 7(现已更名为Entity Framework Core 1.0 / 2.0)目前还不支持将GroupBy()
翻译成生成的 SQL 的GROUP BY
(即使在最终的1.0版本中,它也不会支持)。任何分组逻辑都会在客户端上运行,这可能会导致加载大量数据。
最终编写的代码将自动开始使用 GROUP BY,但现在如果将未分组的整个数据集加载到内存中会导致性能问题,则需要非常谨慎。
对于这种情况,您将不得不手动编写 SQL 并通过 EF 执行它。
如有疑问,请启动 Sql Profiler 并查看生成的内容 - 这也许是您一直应该做的。
https://blogs.msdn.microsoft.com/dotnet/2016/05/16/announcing-entity-framework-core-rc2
一个有用的扩展是将结果收集到 Dictionary
中以便快速查找(例如在循环中):
var resultDict = _dbContext.Projects
.Where(p => p.Status == ProjectStatus.Active)
.GroupBy(f => f.Country)
.Select(g => new { country = g.Key, count = g.Count() })
.ToDictionary(k => k.country, i => i.count);
最初的来源在这里: http://www.snippetsource.net/Snippet/140/groupby-and-count-with-ef-in-c
var query = this.DbContext.Notifications
.Where(n => n.Sent == false)
.GroupBy(n => new { n.AppUserId })
.Select(g => new { AppUserId = g.Key, Count = g.Count() });
var query2 = from n in this.DbContext.Notifications
where n.Sent == false
group n by n.AppUserId into g
select new { id = g.Key, Count = g.Count()};
这两个翻译的含义是相同的:
SELECT [n].[AppUserId], COUNT(*) AS [Count]
FROM [Notifications] AS [n]
WHERE [n].[Sent] = 0
GROUP BY [n].[AppUserId]
var query = context.People
.GroupBy(p => new {p.name})
.Select(g => new { name = g.Key.name, count = g.Count() });
group by + join rows data
,请参考 https://dev59.com/3mcs5IYBdhLWcg3w0HIa - yu yang Jian