LINQ:从投影内的嵌套列表中获取总数

Question

我有以下 LINQ 语句：

var branchStatus = (from b in context.Branches
join l in context.Lobbies on b.BranchId equals l.BranchId into branchLobbyDetails
from bl in branchLobbyDetails.DefaultIfEmpty()
where (branches.Count == 0 || branches.Contains(bl.BranchId)) 
&& b.IsActive 
&& (bl == null ? true : bl.IsActive && !bl.IsArchived)
group bl by new { b.BranchId, b.BranchName } into grouped
select new BranchStatusDetailsDto
{
Id = grouped.Key.BranchId.ToString(),
BranchName = grouped.Key.BranchName,
WaitingInLobby = (grouped.Any() && grouped.First() != null) ? grouped.Count(l => l.Status == 1).ToString() : defaultEmpty,
TotalPSProvided = (grouped.Any() && grouped.First() != null) ? grouped.Where(l => l.Status != 6).Select(l => l.ServicesProvided).Count().ToString() : defaultEmpty,
AverageAssistTime = (grouped.Any() && grouped.First() != null) ? grouped.Where(l => l.Status == 5).Select(l => (TimeSpan)(l.CompletedTime - l.AssistedTime)).Average().ToString() : string.Empty    
});

我基本上是在收集列表中每个分支的统计数据。在投影语句中，第一个计算(WaitingInLobby)是好的，我在TotalPSProvided和AverageAssistTime方面遇到了问题。

对于每个分支，可以有多个大厅记录，每个大厅记录可以有 0 个或多个ServicesProvided条目。有了TotalPSProvided，我想获得每个分支的总数，但我得到的不正确 - 似乎在当前查询中，它给出了大厅记录的数量作为TotalPSProvided。

如何修改语句以返回正确的统计信息？

添加实体定义：

public partial class Branch
{
public int BranchId { get; set; }
public string BranchName { get; set; }
public string BranchCode { get; set; }
public string TimeZoneId { get; set; }
public bool IsActive { get; set; }
public DateTime LastModifiedDate { get; set; }
}
public partial class Lobby
{
public Lobby()
{
this.ServicesRequested = new List<ServicesRequested>();
this.ServicesProvided = new List<ServicesProvided>();
}
[Key]
[Required]
public long LobbyId { get; set; }
[Required]
[ForeignKey("Branch")]
public int BranchId { get; set; }
[ForeignKey("UserRequested")]
public int? RequestedUserId { get; set; }
[ForeignKey("GroupRequested")]
public int? RequestedGroupId { get; set; }
[ForeignKey("AddedByUser")]
public int? AddedByUserId { get; set; }
[ForeignKey("AssistedByUser")]
public int? AssistedByUserId { get; set; }
[MaxLength(1000)]
public string Comments { get; set; }
[Required]
public DateTime? AddedTime { get; set; }

public DateTime? AssistedTime { get; set; }

public DateTime? CompletedTime { get; set; }
[Required]
public bool IsActive { get; set; }

public bool IsArchived { get; set; }
//[ForeignKey("LobbyStatus")]
public int Status { get; set; }
[MaxLength(50)]
public string AccountNumber { get; set; }
[MaxLength(50)]
[Required]
public string FirstName { get; set; }
[MaxLength(50)]
[Required]
public string LastName { get; set; }
//[ForeignKey("Appointment")]
public long? FkAppointmentId { get; set; }
[MaxLength(150)]
public string Notes { get; set; }
[MaxLength(20)]
public string PhoneNumber { get; set; }
[MaxLength(50)]
public string EmailAddress { get; set; }
[MaxLength(2)]
//[Required]
[ForeignKey("PreferredLanguage")]
public string PreferredLanguageKey { get; set; }
[Required]
public bool ClosedByAutoWrapUp { get; set; }
/// <summary>
/// Stores the requested services as a pre-calculated string to support sorting
/// </summary>
public string RequestedServices { get; set; }

public DateTime? AddedLocalTime { get; set; }

public DateTime? AssistedLocalTime { get; set; }

public DateTime? CompletedLocalTime { get; set; }
[MaxLength(4)]
public string AccountNoLastFourDigits { get; set; }
[Required]
public DateTime LastModifiedDate { get; set; }
public virtual Branch Branch { get; set; }
public virtual User UserRequested { get; set; }
public virtual Group GroupRequested { get; set; }
public virtual User AssistedByUser { get; set; }
public virtual Language PreferredLanguage { get; set; }
public virtual List<ServicesProvided> ServicesProvided { get; set; }
public virtual List<ServicesRequested> ServicesRequested { get; set; }
}
public partial class ServicesProvided
{
[Key]
[Required]
public long ServiceProvidedId { get; set; }
[Required]
[ForeignKey("Lobby")]
public long LobbyId { get; set; }
[Required]
[ForeignKey("Service")]
public int ServiceId { get; set; }
[Required]
public DateTime LastModifiedDate { get; set; }
public virtual Lobby Lobby { get; set; }
public virtual Service Service { get; set; }
}

Answer 1

我认为这是您查询中的错字，您正在计算ServicesProvided而不是将它们相加，您正在这样做：

TotalPSProvided = (grouped.Any() && grouped.First() != null) ? grouped.Where(l => l.Status != 6).Select(l => l.ServicesProvided).Count().ToString() : defaultEmpty,

取而代之的是：

TotalPSProvided = (grouped.Any() && grouped.First() != null) ? grouped.Where(l => l.Status != 6).Sum(l => l.ServicesProvided).ToString() : defaultEmpty,

Answer 2

TotalPSProvided的问题在于：

grouped.Where(l => l.Status != 6).Select(l => l.ServicesProvided).Count()

在这里，您只需计算满足 where 条件的(分支、大厅)对的数量。

返工成：

TotalPSProvided = (grouped.Any() && grouped.First() != null) 
? grouped.Where(l => l.Status != 6)
.Sum(l => l.ServicesProvided.Count()).ToString() 
: defaultEmpty,