LINQ: Get a total count from a nested list inside projection

Question

I have the following LINQ statement:

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    
    });

I'm basically collecting stats for each of the branches in the list. In the projection statement, the the first calculation (WaitingInLobby) is Ok, I'm having issues with the TotalPSProvided and AverageAssistTime.

For each branch, there can be multiple Lobby records and each Lobby record can have 0 or more ServicesProvided entries. With TotalPSProvided I want to get the total of that per the branch, but what I'm getting is not correct - it seems like with the current query it gives the number of lobby records as the TotalPSProvided.

How can I modify the statement to return the correct stat?

Adding the entity definitions:

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; }
}

could you add your entities? `Branches` and `Lobbies` – Gabriel Llorico Aug 14 '19 at 07:32 — Gabriel Llorico, Aug 14 '19 at 07:32

Selmir · Accepted Answer · 2019-08-17T21:51:47.940

1

I think it's a typo in your query, you're counting ServicesProvided instead of adding them up, you're doing this :

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

instead of this :

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

edited Aug 17 '19 at 21:51

answered Aug 14 '19 at 08:00

Selmir

1,136
1
11
21

Thanks, this was my mistake. – devC Aug 14 '19 at 08:33

score 1 · Answer 2 · answered Aug 14 '19 at 08:09

The problem for TotalPSProvided is in the line:

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

Here you are simply counting the number of (branch,lobby) pairs that fulfill the where condition.

Rework into:

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

LINQ: Get a total count from a nested list inside projection

2 Answers2