How to Group by and Sum for list data has been assigned List when using EntityFramwork and Linq?

Question

Table data is:

Info
----------------------------------------
num    name   age    expenseN    cost    group
1      a      20
2      b      21 

InfoDetail
----------------------------------------
num    expenseN    cost       group
1      001         10.00      x
2      001         20.00      x
3      002         20.00      x
4      003         30.00      y

This is code:

public class _infoRepository
{
    public string name { get; set; }
    public int age { get; set; }
    public string expenseN { get; set; }
    public decimal cost { get; set; }
    public string group { get; set; }
}
public class _infoDetailRepository
{
    public string expenseN { get; set; }
    public decimal cost { get; set; }
    public string group { get; set; }
}


List<Info> result = new List<Info>();
var info = _infoRepository.Query(p => p.name = "a").FirstOrDefault();
var listInfoDetail = _infoDetailRepository.Query(p => p.group == "x").ToList();
for (int i = 0; i < listInfoDetail.Count; i++)
{
    result.Add(new Info()
    {
        name = info.name,
        age = info.age,
        expenseN = listInfoDetail[i].expenseN,
        cost = listInfoDetail[i].cost,
        group = listInfoDetail[i].group
    });
}
return result;

After running this code, the result of the result variable is as follows:

result
--------------------------------------------------
num    name   age    expenseN    cost      group
1      a      20     001         10.00     x
2      a      20     001         20.00     x
3      a      20     002         20.00     x

However, that was not the result I wanted, the result I expected was like this:

result
--------------------------------------------------
num    name   age    expenseN    cost      group
1      a      20     001         30.00     x
2      a      20     002         20.00     x

After all, i want to group by and sum the result variable to give me the desired result. Someone please help me in this situation, thanks

Possible duplicate of [Group By Multiple Columns](https://stackoverflow.com/questions/847066/group-by-multiple-columns) — mjwills, Mar 15 '18 at 11:54
hi Gert Arnold, `_infoRepository.Q` is all data taken from the `Info` table — Triet Pham, Mar 15 '18 at 13:46
If you ask a question you should show all code that's relevant to answering it. I.e. *show* the `.Query()` method, b/c I still don't know what it does *exactly* (f.e. whether it returns `IQueryable`). Also show the `Info` and `InfoDetail` classes so we can see how they are related. — Gert Arnold, Mar 15 '18 at 15:29
Because in my MVC system there are many files that are mapped together, so I'm afraid that will make my question too long — Triet Pham, Mar 16 '18 at 01:54

score 1 · Accepted Answer · answered Mar 15 '18 at 12:46

1

the listInfoDetail Must be grouped

Instead of

var listInfoDetail = _infoDetailRepository.Query(p => p.group == 
                     "x").ToList();

Use

 var listInfoDetail =_infoDetailRepository.Query(p => p.group =="x")
    .ToList().GroupBy(P=>new {P.expenseN, P.group})
    .Select(P=>new 
         {P.Key.expenseN, P.Key.group, cost=P.Sum(p=>p.cost)}
    );

answered Mar 15 '18 at 12:46

Peter Isaac

352
2
12

hi Peter Isaac, i want to keep my code intact, is there a way to Group by and Sum for the last result variable? – Triet Pham Mar 16 '18 at 04:17
what do you mean with last result variable? @TrietPham – Peter Isaac Mar 17 '18 at 10:26

score 0 · Answer 2 · answered Mar 15 '18 at 12:11

0

Update your for loop like:

for (int i = 0; i < listInfoDetail.Count; i++)
        {
            if (result.Count(j => j.expenseN == listInfoDetail[i].expenseN) == 0)
            {
                result.Add(new Info
                {
                    name = info.name,
                    age = info.age,
                    expenseN = listInfoDetail[i].expenseN,
                    cost = listInfoDetail.Where(j => j.expenseN == listInfoDetail[i].expenseN).Sum(j => j.cost),
                    group = listInfoDetail[i].group
                });
            }
        }

answered Mar 15 '18 at 12:11

Praneet Nadkar

823
7
16

hi Praneet Nadkar, i want to keep my code intact, is there a way to Group by and Sum for the last result variable? – Triet Pham Mar 16 '18 at 04:18
I am not sure what does last result variable mean. Do you mean the last item in the result list ? – Praneet Nadkar Mar 16 '18 at 06:18

How to Group by and Sum for list data has been assigned List when using EntityFramwork and Linq?

2 Answers2