CODEWITHSUNDEEP
c#linqgroup-by
Thelonias
Thelonias

Reputation: 2935

Trouble finding duplicates using LINQ expressions

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:

  1. Customer1 User1 $100
  2. Customer1 User1 $200
  3. Customer1 User1 $300
  4. Customer1 User2 $100
  5. Customer1 User2 $200

But what I want is

  1. Customer1 User1 $600
  2. 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;

Upvotes: 3

Views: 90

Answers (2)

devuxer
devuxer

Reputation: 42374

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;

Upvotes: 2

Krizz
Krizz

Reputation: 11552

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.

Upvotes: 2

Related Questions