There are 2 data sets - pets:

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" } };

And their owners:

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

For these 2 sets, I want to determine how many pets of each type live in each country. Those. I need a LINQ -query equivalent to the following SQL -query:

 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;

Is it possible to get this with LINQ ?

    2 answers 2

    This expression turned out

     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() };

    Testing here - https://ideone.com/agkzWZ

      Something like this

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