Sort using LINQ query

Question

A sequence of information about goods goodList of type Good and a sequence of prices of goods in various stores storePriceList of type StorePrice are given. Each element of the goodList sequence includes the Product SKU, Category, Country of origin fields. Each element of the storePriceList sequence includes the Product SKU, Store Title, Price fields.

For each country of origin get the number of stores offering goods manufactured in that country, as well as the minimum price for goods from this country for all stores (CountryStat values). If no product is found for a certain country that is presented in any store, then the number of stores and the minimum price is assumed to be 0. Sort the list by country of origin.

Example:

goodList: new[]
{
    new Good{Id = 1, Country = "Ukraine", Category = "Food"},
    new Good{Id = 2, Country = "Ukraine", Category = "Food"},
    new Good{Id = 3, Country = "Ukraine", Category = "Food"},
    new Good{Id = 4, Country = "Ukraine", Category = "Food"},
    new Good{Id = 5, Country = "Germany", Category = "Food"},
    new Good{Id = 6, Country = "Germany", Category = "Food"},
    new Good{Id = 7, Country = "Germany", Category = "Food"},
    new Good{Id = 8, Country = "Germany", Category = "Food"},
    new Good{Id = 9, Country = "Greece", Category = "Food"},
    new Good{Id = 10, Country = "Greece", Category = "Food"},
    new Good{Id = 11, Country = "Greece", Category = "Food"},
    new Good{Id = 12, Country = "Italy", Category = "Food"},
    new Good{Id = 13, Country = "Italy", Category = "Food"},
    new Good{Id = 14, Country = "Italy", Category = "Food"},
    new Good{Id = 15, Country = "Slovenia", Category = "Food"}
}

storePriceList: new[]
{
    new StorePrice{GoodId = 1, Price = 1.25M, Shop = "shop1"},
    new StorePrice{GoodId = 3, Price = 2.25M, Shop = "shop1"},
    new StorePrice{GoodId = 5, Price = 4.25M, Shop = "shop1"},
    new StorePrice{GoodId = 7, Price = 9.25M, Shop = "shop1"},
    new StorePrice{GoodId = 9, Price = 11.25M, Shop = "shop1"},
    new StorePrice{GoodId = 11, Price = 12.25M, Shop = "shop1"},
    new StorePrice{GoodId = 13, Price = 13.25M, Shop = "shop1"},
    new StorePrice{GoodId = 14, Price = 14.25M, Shop = "shop1"},
    new StorePrice{GoodId = 5, Price = 11.25M, Shop = "shop2"},
    new StorePrice{GoodId = 4, Price = 16.25M, Shop = "shop2"},
    new StorePrice{GoodId = 3, Price = 18.25M, Shop = "shop2"},
    new StorePrice{GoodId = 2, Price = 11.25M, Shop = "shop2"},
    new StorePrice{GoodId = 1, Price = 1.50M, Shop = "shop2"},
    new StorePrice{GoodId = 3, Price = 4.25M, Shop = "shop3"},
    new StorePrice{GoodId = 7, Price = 3.25M, Shop = "shop3"},
    new StorePrice{GoodId = 10, Price = 13.25M, Shop = "shop3"},
    new StorePrice{GoodId = 14, Price = 14.25M, Shop = "shop3"},
    new StorePrice{GoodId = 3, Price = 11.25M, Shop = "shop4"},
    new StorePrice{GoodId = 2, Price = 14.25M, Shop = "shop4"},
    new StorePrice{GoodId = 12, Price = 2.25M, Shop = "shop4"},
    new StorePrice{GoodId = 6, Price = 5.25M, Shop = "shop4"},
    new StorePrice{GoodId = 8, Price = 6.25M, Shop = "shop4"},
    new StorePrice{GoodId = 10, Price = 11.25M, Shop = "shop4"},
    new StorePrice{GoodId = 4, Price = 15.25M, Shop = "shop5"},
    new StorePrice{GoodId = 7, Price = 18.25M, Shop = "shop5"},
    new StorePrice{GoodId = 8, Price = 13.25M, Shop = "shop5"},
    new StorePrice{GoodId = 12, Price = 14.25M, Shop = "shop5"},
    new StorePrice{GoodId = 1, Price = 3.25M, Shop = "shop6"},
    new StorePrice{GoodId = 3, Price = 2.25M, Shop = "shop6"},
    new StorePrice{GoodId = 1, Price = 1.20M, Shop = "shop7"}
}

Expected Result:

expected: new[]
{
    new CountryStat{Country = "Germany", MinPrice = 3.25M, StoresNumber = 5},
    new CountryStat{Country = "Greece", MinPrice = 11.25M, StoresNumber = 3},
    new CountryStat{Country = "Italy", MinPrice = 2.25M, StoresNumber = 4},
    new CountryStat{Country = "Slovenia", MinPrice = 0.0M, StoresNumber = 0},
    new CountryStat{Country = "Ukraine", MinPrice = 1.20M, StoresNumber = 7},
});

I had an idea to group storedPriceList by GoodId and then select min Price, but I have no idea what to do next.

Answer 1

var result = goodList
        .Select(x => x.Country).Distinct()
        .GroupJoin(
            goodList.Join(storePriceList, good => good.Id, price => price.GoodId,
                (good, goodsGroup) =>
                    new
                    {
                        Good = good,
                        Prices = goodsGroup
                    }), country => country, goods => goods.Good.Country,
            (country, goods) => new
            {
                Country = country,
                Goods = goods
            })
        .AsEnumerable()
        .Select(x =>
            new CountryStat
            {
                Country = x.Country,
                MinPrice = x.Goods.Any() ? x.Goods.Select(y => y.Prices).Min(y => y.Price) : decimal.Zero,
                StoresNumber = x.Goods.Any() ? x.Goods.Select(y => y.Prices).DistinctBy(y => y.Shop).Count() : 0
            })
        .OrderBy(x => x.Country)
        .ToList();

Answer 2

1. goodList left join storePriceList by matching Id with GoodId

2. Group by Country

3. Select:

   3.1. Get min value of Price

   3.2. Remove Shop with null, distinct value and perform count

4. Order by Country

(
    from a in goodList
    join b in storePriceList on a.Id equals b.GoodId into ab
    from b in ab.DefaultIfEmpty()
    group new 
    { 
        Country = a.Country, 
        Price = b == null ? 0 : b.Price,
        Shop = b == null ? null : b.Shop
    } by a.Country into g
    select new
    {
        Country = g.Key,
        MinPrice = g.Min(x => x.Price),
        StoresNumber = g.Where(x => x.Shop != null)
                        .Select(x => x.Shop)
                        .Distinct()
                        .Count()
    }
)
.OrderBy(x => x.Country)
.ToList();

Demo @ .NET Fiddle

Answer 3

you could Select countries in a list and remove repeated elements (hence a list of all countries). and from there you can easily divide the main list into list of list of goods per country (List<(string Country, List<prices>)>)