score:3

Accepted answer

If you have list of planes you can project each plane object into two anonymous objects - one for destination, and one for origin. Then group these anonymous objects by airport and calculate totals:

planes.SelectMany(p => new[] { 
         new { Airport = p.origin, IsOrigin = true },
         new { Airport = p.destination, IsOrigin = false }
       })
       .GroupBy(x => x.Airport)
       .Select(g => new {
           Airport = g.Key,
           AsOriginCount = g.Count(x => x.IsOrigin),
           AsDestinationCount = g.Count(x => !x.IsOrigin)
       })

For given planes:

var planes = new List<Plane> {
    new Plane { Origin = "Minsk", Destination = "London" },
    new Plane { Origin = "Barcelona", Destination = "Minsk" },
    new Plane { Origin = "Rome", Destination = "Minsk" },
    new Plane { Origin = "Barcelona", Destination = "London" },
    new Plane { Origin = "London", Destination = "Rome" },
};

Output will be:

[
  { "Airport": "Minsk", "AsOriginCount": 1, "AsDestinationCount": 2 },
  { "Airport": "London", "AsOriginCount": 1, "AsDestinationCount": 2 },
  { "Airport": "Barcelona", "AsOriginCount": 2, "AsDestinationCount": 0 },
  { "Airport": "Rome", "AsOriginCount": 1, "AsDestinationCount": 1 }
]

Update: This query will work with Entity Framework. Generated SQL will be big and scary.


Related Query

More Query from same tag