Have count of records in one to many navigation property-CodePudding

I have 4 tables 1-Cart 2-CartItems 3-CartItemDetails. Tables 1 and 2 are one to many, tables 2 and 3 are one to many.

I want to retrieve some data from Table 1 and 2 and count of the CartItemDetails.

public class CartDto
{
    public long CartId { get; set; }       
    public int FinalPrice { get; set; }
    public int CartItemDetailCount { get; set; }
    public List<CartItemDto> CartItems { get; set; }
}

public class CartItemDto
{
    public long Id { get; set; }        
    public long PackageId { get; set; }
    public string PackageTitle { get; set; }     
}

so I did this:

var cart = await _context.School_Carts                    
    .Include(p => p.CartItems)
    .ThenInclude(p => p.CartItemDetails)
    .Where(p => p.BrowserId == BrowserId)
    .FirstOrDefaultAsync();

var result = new CartDto()
{
    FinalPrice = cart.FinalPrice,
    CartId = cart.Id,
    CartItemDetailCount = cart.CartItems.Select(p=>p.CartItemDetails.Count()),
    CartItems = cart.CartItems.Select(p => new CartItemDto
    {                       
        PackageTitle = p.Package.Title,
        Id = p.Id,
        PackageId = p.PackageId,
    }).ToList(),
};

How can I have the count in this situation?

CodePudding user response：

Believe that you need to flatten the CartItems list to combine all items from the CartItemDetails into a single list via .SelectMany().

CartItemDetailCount = cart.CartItems.SelectMany(x => x.CartItemDetails).Count()

Demo @ .NET Fiddle