CODEWITHSUNDEEP
c#linq
user3266638
user3266638

Reputation: 449

Getting a total count and sum using linq lambda

I have a data set that looks like this:

Code  Fruit

 A     Apple
 B     Apple
 C     Orange

And another that looks like this:

Code   Cost

 A       5
 B      10
 C       3

How can I turn this into a list with the total counts and sums?

To get a list that looks like this:

Fruit     Total Items    Total Cost

Apple         2              15
Orange        1              3

This is what I tried but it returns a duplicate list of apples. How can this be better written and done?

var totals = list1.Select(a => new totalsObject
{
    Fruit = a.Fruit,
    TotalItems = list2
        .Count(x => list1.Any(i => i.Code== x.Code && i.Fruit == a.Fruit)),
    TotalCost = list2
        .Where(x => list1.Any(i => i.Code== x.Code && i.Fruit == a.Fruit))
        .Sum(x => x.Cost)
})
.OrderBy(x => x.Fruit )
.ToList();

Upvotes: 0

Views: 1441

Answers (2)

ocuenca
ocuenca

Reputation: 39326

You can do a group join

var query= from e2 in list2
           join e1 in list1 on e1.Fruit equals e2.Fruit
           group new{e1.Fruit, e2.Cost} by e1.Fruit into g
           orderby g.Key
           select new totalsObject 
                  {
                    Fruit = g.Key,
                    TotalItems = g.Count(),
                    TotalCost= g.Sum(e=>e.Cost)
                  };

Upvotes: 2

Xiaoy312
Xiaoy312

Reputation: 14477

You are projecting list2 from list1. This is why you are getting the duplicates. Instead, you should join both lists and then group them:

var totals = list1
    .Join(list2, x => x.Code, x => x.Code, (a, b) => new { a.Fruit, b.Cost })
    .GroupBy(x => x.Fruit)
    .Select(g => new
    {
        Fruit = g.Key,
        TotalItems = g.Count(),
        TotalCost = g.Sum(x => x.Cost)
    })
    .OrderBy(x => x.Fruit)
    .ToList();

Upvotes: 3

Related Questions