LINQ to Entities - Simple Way to split resultset into multiple objects

Question

I have a table, containing weekly sales data from multiple years for a few hundred products. Simplified, I have 3 columns: ProductID, Quantity, [and Date (week/year), not relevant for the question]

In order to process the data, i want to fetch everything using LINQ. In the next step I would like create a List of Objects for the sales data, where an Object consists of the ProductId and an array of the corresponding sales data.

EDIT: directly after, I will process all the retrieved data product-by-product in my program by passing the sales as an array to a statistics software (R with R dot NET) in order to get predictions.

Is there a simple (built in) way to accomplish this?

If not, in order to process the sales product by product, should I just create the mentioned List using a loop?

Or should I, in terms of performance, avoid that all together and:

Fetch the sales data product-by-product from the database as I need it?

Or should I make one big List (with query.toList()) from the resultset and get my sales data product-by-product from there?

Answer 1

erm, something like

var groupedByProductId = query.GroupBy(p => p.ProductId).Select(g => new
        {
            ProdcutId = g.Key,
            Quantity = g.Sum(p => p.Quantity)
        });

or perhaps, if you don't want to sum and, instread need the quantities as an array of int ordered by Date.

var groupedByProductId = query.GroupBy(p => p.ProductId).Select(g => new
        {
            ProdcutId = g.Key,
            Quantities = g.OrderBy(p => p.Date).Select(p => p.Quantity).ToArray()
        });

or maybe you need to pass the data around and an anonymous type is inappropriate., you could make an IDictionary<int, int[]>.

var salesData = query.GroupBy(p => p.ProductId).ToDictionary(
        g => g.Key,
        g => g.OrderBy(p => p.Date).Select(p => p.Quantity).ToArray());

so later,

int productId = ...
int[] orderedQuantities = salesData[productId];

would be valid code (less the ellipsis.)