asp.net-mvc - 在实体框架核心方面需要帮助

Question

在 asp.net core mvc 中使用下面的 EF Core 查询时，我在获取确切数据时遇到问题，当客户表中没有特定帐户的记录时，当我将 count() 与 left 一起使用时，我得到 1 而不是 0加入以下 EF 查询。

有人可以帮助解决这个问题。

var AccountData = from acclist in _dbcontext.Accout
                               join departmentlist in _dbcontext.Departments on acclist.DepartmentId equals departmentlist.DepartmentId
                               join statuslist in _dbcontext.Statuses on acclist.StatusId equals statuslist.StatusId
                               join customerlist in _dbcontext.Customers on acclist.CustomerId equals customerlist.CustomerId into leftjoin
                               from custlist in leftjoin.DefaultIfEmpty()
                               where acclist.CaseStatusId == 2
                               group custlist by new { acclist.CaseID, acclist.AccName, acclist.AccNumber, acclist.CaseName, departmentlist.DepartmentName, statuslist.StatusName } into g
                               select new
                               {
                                   _accId = g.Key.AccountID,
                                   _accName = g.Key.AccountName,
                                   _accNumber = g.Key.AccNumber,
                                   _status = g.Key.StatusName,
                                   _department = g.Key.DepartmentName,
                                   _caseName = g.Key.CaseName,
                                   _dataSize = g.Sum(customerlist => customerlist.DataSize),
                                   _customerCount = g.Count()
                               };

实际的 T-SQL 查询是

select Account.CaseID, Account.AccNumber,Account.AccountName,
Account.CaseName, Departments.DepartmentName,Statuses.StatusName,
Sum(Customer.DataSize), Count(CustomerID)
from Account
inner join Departments on Account.DepartmentId = Departments.DepartmentId
inner join Statuses on Account.StatusId = statuses.StatusId
left join Customer on Account.CustomerId = Customer.CustomerId
where Account.StatusId = 2
group by Account.CaseID,Account.AccNumber,Account.AccountName,Account.CaseName,
Departments.DepartmentName,Statuses.StatusName

标签： asp.net-mvcentity-frameworkasp.net-core