Transpose a Custom Collection with Linq

Question

I have a BindingList of objects and each object has a nested list of items.

BindingList<QuoteboardRow> quoteboard = new BindingList<QuoteboardRow>();
quoteboard.Add(
    new QuoteboardRow()
    {
        Product= "Cement",
        Prices = new Dictionary<string, Price>() {
            { "SupplierA",
                new Price() { Low= 101, High= 102 }
            },
            { "SupplierB",
                new Price() { Low= 101, High= 102 }
            },
            { "SupplierC",
                new Price() { Low= 101, High= 102 }
            }
    }
);
dataGridView1.DataSource = quoteboard;

If I simply databind the collection to a datagridview, this is all I see:

I want to try to transpose the collection, so that it has a column for each of the suppliers, and a sub-column each for the Low and High prices, respectively. Basically, like a quoteboard with a column for each supplier or if the default datagridview supports some kind of grouping: sub-columns for the low and high price for each supplier. I manually populated some datagridview cells show what I'm trying to achieve:

I've been trying to make headway with code such as this snippet below, but to no avail.

var result = quoteboard
.SelectMany(x => x.Prices.Select((item, index) => new { Source = x.Product, item, index  }))
.GroupBy(i => i.index)
.Select(g => g.ToList())
.ToList();

I'd appreciate any help at all with this. In case there's a better way to databind the datagridview and collection, the datagridview is read-only and I'll be updating the collection to reflect any changes in the prices.

Answer 1

You can try by using a method that converts each item of QuoteboardRow to new type with each price listed as single item.

For each item to be printed in "transposed" way, you have to take an object and convert it to the same format as the Product/Name... Dictionary<string, object>

Method to convert Item to Dictionary

    public static Dictionary<string, object> ConvertItemToDictionary(QuoteboardRow x)
    {
        Dictionary<string, object> result = new Dictionary<string, object>();
        result.Add("Product", x.Product);
        foreach (var key in x.Prices.Keys)
        {
            x.Prices.TryGetValue(key, out Price price);
            result.Add($"{key}_L", price.Low);
            result.Add($"{key}_H", price.High);
        }
        return result;
    }

Use it in your main or other process,

    BindingList<QuoteboardRow> quoteboard = new BindingList<QuoteboardRow>();
    quoteboard.Add(
        new QuoteboardRow()
        {
            Product = "Cement",
            Prices = new Dictionary<string, Price>() 
            {
                { "SupplierA",
                    new Price() { Low= 101, High= 102 }
                },
                { "SupplierB",
                    new Price() { Low= 101, High= 102 }
                },
                { "SupplierC",
                    new Price() { Low= 101, High= 102 }
                }
            }
        }
    );

    var result = quoteboard.Select(x => ConvertItemToDictionary(x)).ToList();


    Console.WriteLine(JsonConvert.SerializeObject(result, Formatting.Indented));
    dataGridView1.DataSource = result;

In the console, i had this as the output, which is what would print correctly in a transposed way. (I added pillar as a second entry for testing)

[
  {
    "Product": "Cement",
    "SupplierA_L": 101,
    "SupplierA_H": 102,
    "SupplierB_L": 101,
    "SupplierB_H": 102,
    "SupplierC_L": 101,
    "SupplierC_H": 102
  },
  {
    "Product": "pillar",
    "SupplierA_L": 101,
    "SupplierA_H": 104,
    "SupplierB_L": 101,
    "SupplierB_H": 101,
    "SupplierC_L": 101,
    "SupplierC_H": 105
  }
]

Answer 2

I'd do it something like this:

public class PriceWrap
{
    public string Product { get; set; }

    public string Supplier { get; set; }

    // you can also add all price fields here, if you only want one level
    public Price Price { get; set; }
}


var bl = new BindingList<PriceWrap>();

foreach (var quoteboard in quoteboards)
{
    foreach (var kv in quoteboard.Prices)
    {
        var supplier = kv.Key;
        var price = kv.Value;

        bl.Add(new PriceWrap() {
            Product = quoteboard.Product,
            Supplier = supplier,
            Price = price
        });
    }
}

dataGridView1.DataSource = bl;

Result would be:

Product, SupplierA, LowA, HighA
Product, SupplierB, LowB, HighB
Product, SupplierC, LowC, HighC