LINQ method to return values as a single object including a nested array

Goal: Returning a single object of sum and a list of details.
{ Sum: 1, [ { Amount: 2, Hex: '#123456' }, { Amount: 1, Hex: '#123456' } ] }

Using the below, I cannot achieve the goal:

    var data = (await _context.Users
        .Where(u => u.Id == userId)
        .SelectMany(ue => ue.Expenses)
        .Where(ue => ue.CreatedOn.Date <= insightsFilter.To.Value.Date 
            && ue.CreatedOn.Date >= insightsFilter.From.Value.Date)
        .Include(ue => ue.UserExpenses)
        .Include(e => e.Category)
        .ToListAsync());
    
    var response = data.Select(e => new GetCategoriesDto {
        Sum = e.UserExpenses.Sum(ue => ue.Amount),
        Data = data.GroupBy(e => e.Category.Name.ToLower())
            .Select(cl => new GetDetailsDto {
                    Hex = "#123456"
            }).ToList()
    });

The output is a single array as such:

{ Sum: 3, Data: [  { Sum: 2, Amount: 2, Hex: '#123456' }, { Sum: 1, Amount: 1, Hex: '#123456' } ] }

Where Sum is repeated instead of being on top of the JSON object with a value of 2 + 1 = 3.

Can someone please let me know what I am doing wrong… Thanks!

Data is as follows:

- Users
 -- UserExpenses (Junction) - Contains Amount value.
  -- Expenses - Contains Category value.


    public class Expense
    {
        [Key]
        public int Id { get; set; }
        public Category Category { get; set; }
        public int UserId { get; set; }
        public User User { get; set; }
        public List<UserExpenseJunction> UserExpenses { get; set; } = new List<UserExpenseJunction>();
    }

    public class  UserExpenseJunction {
        [Key]
        public int Id { get; set; }
        public int UserId { get; set; }
        public User User { get; set; }
        public int ExpenseId { get; set; }
        public Expense Expense { get; set; }
        public decimal Amount { get; set; }
        public string Currency { get; set; }
}


    public class User
    {
        public int Id { get; set; }
        public string UserName { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public byte[] PasswordHash { get; set; }
        public byte[] PasswordSalt { get; set; }
        public IList<Expense> Expenses { get; set; }
        public IList<UserExpenseJunction> UserExpenses { get; set; }
        public bool Verified { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime UpdatedOn { get; set; }
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You are doing a Select on your list so of course it will create a GetCategoriesDto for each item the list.

you need to do the sum as a separate item so I think something like this should work (I haven’t tried it)

 var response = new {
       Sum = data.Select(e => e.UserExpenses.Sum(g => g.Amount)).Sum(),
       data.Select(e => new GetCategoriesDto {
           Data = data.GroupBy(e => e.Category.Name.ToLower())
            .Select(cl => new GetDetailsDto {
                    Amount = cl.Amount.Sum(),
                    Hex = "#123456"
            }).ToArray()
    });

It’s a little guess by me because you have some missing code in your question.

Method 2

response is a List, because you created it as a List: var response = data.Select(...).ToList().

Create the reponse as:

var response = new GetCategoriesDto 
{
  //Sum = sum of amounts, ...
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0