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

Question

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

Could you please provide sample input as valid C# code, including class definitions, and also, if possible, the expected output as a C# literal? — Enigmativity
– Enigmativity, Commented Aug 14, 2020 at 8:03
What is ue.Expenses? If it's a collection of Expenses your model is wrong. First priority is to get the model right, then the query. — Gert Arnold
– Gert Arnold, Commented Aug 14, 2020 at 8:14
@GertArnold Why so? Please check the update with the classes. — user1027620
– user1027620, Commented Aug 14, 2020 at 8:19
@GertArnold Added (A user created an expense, but the same expense can have a list of users - contributors to that expense) — user1027620
– user1027620, Commented Aug 14, 2020 at 8:21

Salik Rafiq · Accepted Answer · 2020-08-14 11:28:21Z

2

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.

answered Aug 14, 2020 at 11:28

Salik Rafiq

1841 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomas Chabada · Accepted Answer · 2020-08-14 07:45:25Z

0

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, ...
}

answered Aug 14, 2020 at 7:45

Tomas Chabada

3,0391 gold badge20 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related