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.

score 2 · Answer 1 · answered Oct 01 '19 at 14:12

you can use a Dictionary that way you will have a key value pair. the key is ProductID and the value the list of Terms.

var dictionary = new Dictionary<int, List<Terms>>();
foreach (ProductTermAndPricingDataItem item in productInformationItems)
{
            if(dictionary.ContainsKey(item.ProductID))
            {
                dictionary[item.ProductID].Add(new Terms { Term = item.BillingPeriodName, Price = item.PriceAmount});
            }
            else
            {
                dictionary.Add(item.ProductID, new List<Terms>() { new Terms() {Term = item.BillingPeriodName, Price = item.PriceAmount             } });
            }  
 }

This solution doesn't return `ProductPricingGetDataItem` like you requested — Fourat, Oct 01 '19 at 14:30

score 1 · Accepted Answer · answered Oct 01 '19 at 14:29

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

score 1 · Answer 3 · answered Oct 01 '19 at 14:52

For LINQ lovers:

//Your initial data list
var productInformationItems = new List<ProductTermAndPricingDataItem>();
var productPricingGetDataItems = productInformationItems.ToLookup(item => item.ProductID)
                                                        .Select(grouping => new ProductPricingGetDataItem
                                                        {
                                                            ProductID = grouping.Key,
                                                            Terms = grouping.Select(item => new Terms
                                                            {
                                                                Price = item.PriceAmount,
                                                                Term = item.BillingPeriodName
                                                            }).ToList()
                                                        }).ToList();

In your exact case the result is :

Feel free to ask if something is not clear.

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

3 Answers3