Группировка по 2-ум полям в LINQ

154

28 марта 2018, 02:11

Есть 2 набора данных - питомцы:

Pet[] pets =
        {
            new Pet() { Name = "Vasya", Type = "Cat", Owner = "John" },
            new Pet() { Name = "Borya", Type = "Dog", Owner = "Dean" },
            new Pet() { Name = "Kim", Type = "Hedgehog", Owner = "Mary" },
            new Pet() { Name = "Joka", Type = "Dog", Owner = "John" },
            new Pet() { Name = "Mursick", Type = "Cat", Owner = "Dean" },
            new Pet() { Name = "Mick", Type = "Cat", Owner = "Mary" },
            new Pet() { Name = "John", Type = "Hedgehog", Owner = "John" },
            new Pet() { Name = "Jynx", Type = "Dog", Owner = "Dean" }
        };

И их владельцы:

Owner[] owners =
        {
            new Owner() { Name = "John", Country = "USA" },
            new Owner() { Name = "Mary", Country = "Switzerland" },
            new Owner() { Name = "Dean", Country = "Great Britain" }
        };

По этим 2-ум наборам я хочу определить, сколько питомцев каждого типа проживает в каждой стране. Т.е. мне нужен LINQ-запрос, эквивалентный такому SQL-запросу:

SELECT O.Country, P.Type, Count(P.Type) AS Amount
FROM owners AS O INNER JOIN pets AS P
ON O.Name = P.Owner
GROUP BY O.Country, P.Type;

Можно ли получить такое при помощи LINQ?

Answer 1

Такое выражение получилось

var el = from o in owners
         join p in pets
         on o.Name equals p.Owner
         group p by new {o.Country, p.Type}
         into grp 
             select new { 
                          Country = grp.Key.Country, 
                          Type = grp.Key.Type, 
                          Count = grp.Count() 
                        };

Тестируем тут - https://ideone.com/agkzWZ

Answer 2

Как-то так

 var result = owners.Join(pets, o => o.Name, p => p.Owner, (o,p) => new{o,p})
            .GroupBy(j => new { j.o.Country, j.p.Type})
            .Select(g => new {g.Key.Country, g.Key.Type, Amount = g.Count(), });