Selecting values from List

Question

I have the following List:

public class Products
{
 public string SKU;
 public int WarehouseID;
}

List<Products> products = new List<Products>();

which after populating the list I end up with the following data:

ProductCode|WarehouseID
SKU001|2
SKU001|3
SKU002|3
SKU003|3
SKU004|1
SKU004|5

I have multiple SKU's as the item can be supplied from more then one Warehouse location as it's in stock. In the case of SKU001, warehouse ID 2 has more stock than warehouse ID 3.

I need to select the items from the least number of warehouse locations. What I'm trying to end up with is something like

SKU001|3
SKU002|3
SKU003|3
SKU004|1

This limits product selection to only 2 locations as SKU001, SKU002 & SKU003 can all be obtained from warehouse ID 3. Ideally selecting from a location with the most stock but limiting the number of locations is more important.

I'm using Linq to try and achieve this while trying to loop each List item but am struggling as Linq's not a strong point for me. I tried to first get the highest count of repeating warehouse id's using

products.GroupBy(i => i).OrderByDescending(grp => grp.Count()).Select(grp => grp.Key).FirstOrDefault();

but am getting lost on the rest of the items. Any ideas on how I could achive this?

Answer 1

You can do that in multiple statements, first Get WarehouseID along with distinct products in each warehouse and their count like:

var query = products.GroupBy(r => r.WarehouseID)
                    .Select(grp => new
                    {

                        WarehouseID = grp.Key,
                        DistinctProducts = grp.Select(r => r.SKU).Distinct(),
                        DistinctCount = grp.Select(r => r.SKU).Distinct().Count(),
                    });

Later create a result list like:

List<Products> result = new List<Products>();

foreach (var item in query.OrderByDescending(r => r.DistinctCount)) //warehouse with most products
{
    if (!result.Any(r => item.DistinctProducts.Any(t => t == r.SKU)))
    {
        result.AddRange(item.DistinctProducts.Select(r => new Products { SKU = r, WarehouseID = item.WarehouseID }));
    }
}

For output:

foreach (var item in result)
{
    Console.WriteLine("{0} | {1}", item.SKU, item.WarehouseID);
}

Output:

SKU001 | 3
SKU002 | 3
SKU003 | 3
SKU004 | 1