实体框架获取分组依据为的所有列

Question

我们正在将报表从SQL迁移到EF。在这个过程中，我偶然发现了以下查询：

SELECT
pl.clientid,
pl.loginname,
COUNT(*)
FROM
(
SELECT
DISTINCT
s.clientid,
s.doctype
FROM
Specifications s
) A
INNER JOIN portal_logins pl ON A.clientid = pl.clientid
WHERE
A.clientid != '0'
GROUP BY
A.clientid,
A.doctype

我目前的解决方案是以下

return await _nfContext.Specifications
.Select( x => new
{
Clientid = x.Clientid,
Doctype = x.Doctype
})
.Distinct()
.Join(
_nfContext.PortalLogins,
s => s.Clientid,
p => p.Clientid,
(spec, login) => new
{
loginName = login.Loginname,
clientId  = spec.Clientid,
doctype   = spec.Doctype
}
)
.Where(x => x.clientId != "0")
.GroupBy(c => new
{
c.clientId,
c.doctype,
c.loginName
})
.Select(result => new TotalActiveConnections
{
ActiveConnections = result.Count(),
ClientId = result.Key.clientId,
LoginName = result.Key.loginName
})
.OrderByDescending(x => x.ActiveConnections)
.ToListAsync()
.ConfigureAwait(false);

我的问题是，我需要Group by中描述的所有三列，但我不想按登录名进行分组。有人知道我能做什么吗？提前感谢您的帮助。。。。

Answer 1

您应该只使用Min或Max来获得任何随机的login值

.GroupBy(c => new
{
c.clientId,
c.doctype
})
.Select(result => new TotalActiveConnections
{
ActiveConnections = result.Count(),
ClientId = result.Key.clientId,
LoginName = result.Min(g => g.loginName),
})

大概它在SQL中起作用是因为您使用的是MySQL/MariaDB，并且没有打开ONLY_FULL_GROUP_BY