LINQ到实体后组by, COUNT和WHERE不起作用

Question

我有以下LINQ-to-Entities查询MySQL DB

var data = (from agent in db.User
join agentrole in db.UserRole.DefaultIfEmpty() on agent.Id equals agentrole.UserId
join role in db.Role.DefaultIfEmpty() on agentrole.RoleId equals role.Id
join department in db.Department.DefaultIfEmpty() on role.DepartmentId equals department.Id
join client in db.Client.DefaultIfEmpty() on agent.Id equals client.AssignedUserId
join aggclient in db.AggClient.DefaultIfEmpty() on client.Id equals aggclient.ClientId
group new { agent, department, aggclient} by agent.Id into grp
select new
{
grp.Key,
agentName = grp.Max(a => a.agent.FirstName + " " + a.agent.LastName),
departmentNames = "",
newDepositorsCount = 0,
FTDSum = grp.Sum(a => a.aggclient.FirstDepositAmountEuro),
depcount =grp.Count(a => a.department != null),
aggclientfilter = grp.Where(a => a.aggclient != null && a.aggclient.FirstDepositAmount>0).Sum(a => a.aggclient.FirstDepositAmount)
});

在当前查询中，最后两个操作不起作用。实体无法解析count和,操作。

Answer 1

changeselect clauseto:

select new
{
grp.Key,
agentName = grp.agent.Max(a => a.FirstName + " " + a.LastName),
departmentNames = "",
newDepositorsCount = 0,
FTDSum = grp.aggclient.Sum(a => a.FirstDepositAmountEuro),
depcount = grp.department.Count(),
aggclientfilter = grp.aggclient.Where(a => a.FirstDepositAmount>0).Sum(a => a.FirstDepositAmount)
});

Answer 2

我假设您不使用EF Core 5。X，因为它支持过滤计数。

问题是这样的LINQ查询没有正确的SQL转换。但是有一些技巧可以返回所需的结果。

var data = 
from agent in db.User
join agentrole in db.UserRole on agent.Id equals agentrole.UserId into ga
from agentrole in ga.DefaultIfEmpty()
join role in db.Role on agentrole.RoleId equals role.Id into gr
from role in gr.DefaultIfEmpty()
join department in db.Department on role.DepartmentId equals department.Id into dg
from department in dg.DefaultIfEmpty()
join client in db.Client on agent.Id equals client.AssignedUserId
join aggclient in db.AggClient on client.Id equals aggclient.ClientId into acg
from aggclient in acg.DefaultIfEmpty()
group new { agent, department, aggclient} by agent.Id into grp
select new
{
grp.Key,
agentName = grp.Max(a => a.agent.FirstName + " " + a.agent.LastName),
departmentNames = "",
newDepositorsCount = 0,
FTDSum = grp.Sum(a => a.aggclient.FirstDepositAmountEuro),
depcount = grp.Sum(a => a.department != null ? 1 : 0),
aggclientfilter = grp.Sum(a => a.aggclient.FirstDepositAmount > 0 ? a.aggclient.FirstDepositAmount : 0)
};