Trouble finding duplicates using LINQ expressions

Question

I have a list of ForecastData and it looks like this:

public class ForecastData
{
    public string SalesID {get;set;}
    public string Customer {get;set;}
    public string Vendor {get;set;}
    public string Division {get;set;}
    public int Year {get;set;}
    public decimal Amount {get;set;}
}

I need to display a list of every distinct "SalesID" for each "Customer" that has an Amount > 0 where the year is THIS YEAR.

Currently, I'm grouping by Customer, but because I can have multiple "Amounts" for the same Customer and SalesID in my dataset, I'm not getting the results I expect. My results show:

Customer1 User1 $100
Customer1 User1 $200
Customer1 User1 $300
Customer1 User2 $100
Customer1 User2 $200

But what I want is

Customer1 User1 $600
Customer1 User2 $300

Here's my expression:

var forecasts = (List<ForecastData>)cache.Get(_RAW_FORECAST_DATA_KEY, null);

foreach(var custGroup in forecasts.Where(f => f.Year == DateTime.Now.Year).GroupBy(f => f.Customer))
{
    if(custGroup.Count() > 1) // There's more than one forecast for this customer
    {
        foreach(var instance in custGroup)
        {
            toReturn.Add(new MultipleCustomer(custGroup.Key)
            {
                Salesperson = instance.SalesPersonId,
                Forecast = instance.Amount
            });
        }
    }
}

return toReturn;

Have a look at [this](http://stackoverflow.com/a/491832/1466627). — Bob., Nov 20 '12 at 21:42
You will definitely need to do a Sum on your instance.Amount. You're group by will only do a sort on the customer objects — Matt R, Nov 20 '12 at 21:45

score 2 · Accepted Answer · edited Nov 20 '12 at 22:33

2

I think if you change your query so that you have an "outer" GroupBy and an "inner" GroupBy, it will solve your problem:

var forecasts = (List<ForecastData>)cache.Get(_RAW_FORECAST_DATA_KEY, null);

var forecastGroups = forcasts
    .Where(f => f.Year = DateTime.Now.Year)
    .GroupBy(f => f.Customer)
    .Where(grp => grp.Count() > 1)
    .Select(grp => new { Key = grp.Key, SalesGroups = grp.GroupBy(f => f.SalesId) });

foreach(var custGroup in forecastGroups)
{
    if(custGroup.SalesGroups.Count() > 1)
    {
        foreach(var salesGroup in custGroup.SalesGroups)
        {
            toReturn.Add(new MultipleCustomer(custGroup.Key)
            {
                Salesperson = salesGroup.Key,
                Forecast = salesGroup.Sum(f => f.Amount)
            });
        }
    }
}

return toReturn;

edited Nov 20 '12 at 22:33

Thelonias

2,918
3
29
63

answered Nov 20 '12 at 21:43

devuxer

41,681
47
180
292

I did this, but I'm still not getting the results I need. I feel like I need to first group by Customer, and if there's a Count > 1, THEN group by DISTINCT Salesperson and if there's more than one, I want to display all salespeople's amount sum for that customer – Thelonias Nov 20 '12 at 21:55
I just updated my answer, but based on your comment, I think I need to revise it a bit more. – devuxer Nov 20 '12 at 22:01
Nah, didn't work. I agree with you though that I need an "outer" and "inner" group by...or at least I think I do. – Thelonias Nov 20 '12 at 22:10
I don't think I have access to my "Customer" grouping anymore, the only Key I get is salesperson (from that last GroupBy). Because of this, I couldn't create a `MultipleCustomer` object, which needs a Customer string, a Salesperson string, and a total forecast. – Thelonias Nov 20 '12 at 22:16
I just updated with the rest of the method. Please let me know if that helps. – devuxer Nov 20 '12 at 22:17
Yeah, you have "custGroup.Key", but that doesn't work because custGroup is an IEnumable>, so there is no key. – Thelonias Nov 20 '12 at 22:19
I see the problem...fixed. (I hope!) Note the change to the final `Select` in the query. – devuxer Nov 20 '12 at 22:21
GOT IT! Awesome, thanks so much. I had to make 1 small change to make it return exactly what I need (added if(custGroup.SalesGroups.Count() > 1) around the inner "foreach". Thanks again! – Thelonias Nov 20 '12 at 22:26

score 2 · Answer 2 · answered Nov 20 '12 at 22:07

Try the following:

foreach(var custGroup in forecasts.Where(f => f.Year == DateTime.Now.Year).GroupBy(f => new { f.Customer, f.SalesPersonId }).Where(k => k.Count() > 1).Select(k => new { Customer = k.Key.Customer, SalesPersonId = k.Key.SalesPersonId, TotalAmount = k.Sum(x => x.Amount) } )
{
           toReturn.Add(new MultipleCustomer(custGroup.Customer)
           {
              Salesperson = custGroup.SalesPersonId,
              Forecast = custGroup.TotalAmount
           });
       }
   }
}

I have been writing it from my head. No C# compiler nearby, so there might be some errors.

The key to your problem here is that you need to aggregate your items summing their Amount's.

This just ends up giving me EVERY forecast, where the Amount is summed by a Customer/Salesperson grouping — Thelonias, Nov 20 '12 at 22:21

Trouble finding duplicates using LINQ expressions

2 Answers2