Creating key-value pairs from SQL result that have duplicate data

Question

My problem at the moment is I have results coming back from a SQL query that returns a result like this:

125 Month   10.00   Wholesale
125 Year    20.00   Wholesale
126 Month   20.00   Wholesale
126 Year    30.00   Wholesale
127 Month   40.00   Wholesale
127 Year    50.00   Wholesale

where integer column is the ID of the column. when the data gets returned to the C# calling code, it is placed into an object followering this structure:

PuctName; }

I am just having issues with how to create the terms without causing an endless amount of loops.

Answer 1

You can use Linq and GroupBy:

List<ProductPricingGetDataItem> grouped = productInformationItems.GroupBy(
                                        p => p.ProductID,
                                        (key, g) => new ProductPricingGetDataItem() { ProductID = key, Terms = g.Select(x => new Terms(x.BillingPeriodName, x.PriceAmount)).ToList() }).ToList();

In order for that code to work, you need to add a constructor to Terms :

public Terms(string term, decimal price)
{
    Term = term;
    Price = price;
}

Fiddle with working example : https://dotnetfiddle.net/EE2BpP