Pairing up values in LINQ combined with GroupBy

Question

I have a list of transactions data, which I group by the ItemID field which basically gives me the data how many times the transaction was made:

  var _transactionsList = TransactionsData.GroupBy(x => x.ItemID).Select(pr => new TransactionsTabResults {
                            ItemID = pr.Key,
                            ItemPrice = pr.Select(x => x.ItemPrice).FirstOrDefault(), // the issue is here, prices are not matched for the exact product...
                            Title = pr.Select(x => x.Title).FirstOrDefault(),
                            TotalSoldItems = pr.Count(),
                            TotalRevenuePerItem = pr.Sum(y => y.ItemPrice),
                            AveragePrice = pr.Average(y => y.ItemPrice),
                            GalleryURL = pr.Select(x => x.GalleryURL).FirstOrDefault()
                        }).ToList();

The issue here is that after this LINQ the prices of the products are just not matched exactly as I'm expecting.

I've compared them to the data on eBay , and the prices are not matched exactly, rather they are shuffled around and none is matched with any...

How could I fix this ?

Edit: this isn't really a duplicate of the question as marked...

Rather, if I do group by the items by their prices, what am I going to be left with ? This isn't the solution ...

Edit: here is some sample data

ItemID: 282183606257 AmountPaid: 55.4
ItemID: 282183606257 AmountPaid: 43.5
ItemID: 282183606257 AmountPaid: 36.5

ItemID: 1218218553606234 AmountPaid: 15.4
ItemID: 1218218553606234 AmountPaid: 53.5
ItemID: 1218218553606234 AmountPaid: 66.5

ItemID: 282053079253 AmountPaid: 446.5
ItemID: 282053079253 AmountPaid: 246.5
ItemID: 282053079253 AmountPaid: 346.5

Basically these are transactions for the specific seller on eBay for the past 30 days... One item can be sold multiple times with different price (depending upon the moment of transaction);

I'm suspecting now that the reason why I'm getting wrong results because I'm grouping by the wrong value which is not actually unique, therefore I simply cannot assign the right value to each item ?

Answer 1

Your problem is with the ".FirstOrDefault()" selector INSIDE your select statement AFTER you have already grouped your data. You will get a "semi random" value for each of the grouped items.

This modification will give you all the aggregate data for the group as well as the individual rows that were aggregated.

var _transactionsList = TransactionsData.GroupBy(x => x.ItemID).SelectMany(pr =>
 pr.Select(item => new TransactionsTabResults()
 {

    ItemID = pr.Key,
    ItemPrice = item.ItemPrice,
    ItemTitle = item.ItemTitle,
    TotalSoldItems = pr.Count(),
    TotalRevenuePerItem = pr.Sum(y => y.ItemPrice),
    AveragePrice = pr.Average(y => y.ItemPrice),
})).ToList();

Answer 2

Firstly, I don't think there is anything wrong with your code, except the price you are getting is just one of the prices for the product. It would be better to have some criteria for selecting it, (such as the newest price).

If your Title and GalleryURL don't change, then you can add them to the groupBy.

For example, if you have a date field, then the following code will try to find the newest price.

var _transactionsList = TransactionsData
.GroupBy(x => new { x.ItemID, x.Title, x.GalleryURL })
.Select(pr => new TransactionsTabResults
{
    ItemID              = pr.Key.ItemID,
    Title               = pr.Key.Title,
    GalleryURL          = pr.Key.GalleryURL,

    ItemPrice           = pr.OrderByDescending(a=>a.Date).First().ItemPrice ,

    TotalSoldItems      = pr.Count(),
    TotalRevenuePerItem = pr.Sum(y => y.ItemPrice),
    AveragePrice        = pr.Average(y => y.ItemPrice),
}
).ToList();

If either of title or Gallery can change, then you need to remove them from the groupby and pick one of them.

If you want to debug your code, you can return the whole of the line that you are using to display the price (or even return all lines in the group), eg something like

(Rather than return every result for every item, you can filter it for one testID)

 var debug = TransactionsData
.Where(a=>a.ItemID = testID)
.GroupBy(x => new { x.ItemID, x.Title, x.GalleryURL })
.Select(pr => new  
{
    ItemID              = pr.Key.ItemID,
    Title               = pr.Key.Title,
    GalleryURL          = pr.Key.GalleryURL,

    LatestSale          = pr.OrderByDescending(a=>a.Date).First() ,
    AllSales            = pr.ToList(),

    ItemPrice           = pr.OrderByDescending(a=>a.Date).First().ItemPrice ,

    TotalSoldItems      = pr.Count(),
    TotalRevenuePerItem = pr.Sum(y => y.ItemPrice),
    AveragePrice        = pr.Average(y => y.ItemPrice),
}
).ToList();

Then you can decide if the problem is with your data or with your code.