不计算 linq 左外联接查询中的空值
本文关键字:查询 空值 计算 linq | 更新日期: 2023-09-27 18:36:48
我有一个SQL查询,它完全可以做我想要的,但我在linq中需要它。它返回几个 AVC 行,并计算有多少状态为 1 的 PersonAVCPermission 链接到它
SELECT a.Id, a.Name, a.Address, COUNT(p.AVCID) AS Count
FROM AVC AS a
LEFT OUTER JOIN
(
SELECT PersonAVCPermission.AVCId
FROM PersonAVCPermission
WHERE PersonAVCPermission.Status = 1
) AS p
ON a.Id = p.AVCId
GROUP BY a.Id, a.Name, a.Address
我在linq中有这个查询,它做同样的事情,除了没有PersonAVCPermission时,它仍然计数为1
var yellows = odc.PersonAVCPermissions.Where(o => o.Status == (int)AVCStatus.Yellow);
var q = from a in odc.AVCs
from p in yellows.Where(o => o.AVCId == a.Id).DefaultIfEmpty()
group a by new { a.Id, a.Name, a.Address } into agroup
select new AVCListItem
{
Id = agroup.Key.Id,
Name = agroup.Key.Name,
Address = agroup.Key.Address,
Count = agroup.Count(o => o.Id != null)
};
我猜使用 DefaultIfEmpty() 它会在列表中放置空行,然后被计算在内,所以我尝试用 (o => o.Id != null) 排除它们,但它仍然将所有内容计算为至少一个
如果我不使用 DefaultIfEmpty(),它会完全跳过计数为 0 的行
我怎样才能排除它们,还是我做得完全错了?
使用 .任何()和让?
var yellows = odc.PersonAVCPermissions.Where(o => o.Status == (int)AVCStatus.Yellow);
var q = from a in odc.AVCs
let Y = (from p in yellows.Where(o => o.AVCId == a.Id) select p).Any()
where Y == true
group a by new { a.Id, a.Name, a.Address } into agroup
select new AVCListItem
{
Id = agroup.Key.Id,
Name = agroup.Key.Name,
Address = agroup.Key.Address,
Count = agroup.Count(o => o.Id != null)
};
您不需要联接,也不需要分组:
var q = from a in odc.AVCs
select new AVCListItem
{
Id = a.Id,
Name = a.Name,
Address = a.Address,
Count = yellows.Where(o => o.AVCId == a.Id).Count()
};