LEFT OUTER JOIN in LINQ to objects

Question

Consider the following code.
City and CitPlace are joined by CityCode.
What I want to do is perform a LEFT OUTER JOIN between CityPlace and City.

City[] cities = new City[]{
 new City{CityCode="0771",CityName="Raipur",CityPopulation="BIG"},
 new City{CityCode="0751",CityName="Gwalior",CityPopulation="MEDIUM"},
 new City{CityCode="0755",CityName="Bhopal",CityPopulation="BIG"},
 new City{CityCode="022",CityName="Mumbai",CityPopulation="BIG"},
};


CityPlace[] places = new CityPlace[]{
 new CityPlace{CityCode="0771",Place="Shankar Nagar"},
 new CityPlace{CityCode="0771",Place="Pandari"},
 new CityPlace{CityCode="0771",Place="Energy Park"},

 new CityPlace{CityCode="0751",Place="Baadaa"},
 new CityPlace{CityCode="0751",Place="Nai Sadak"},
 new CityPlace{CityCode="0751",Place="Jayendraganj"},
 new CityPlace{CityCode="0751",Place="Vinay Nagar"},

 new CityPlace{CityCode="0755",Place="Idgah Hills"},

 new CityPlace{CityCode="022",Place="Parel"},
 new CityPlace{CityCode="022",Place="Haaji Ali"},
 new CityPlace{CityCode="022",Place="Girgaon Beach"},

 new CityPlace{CityCode="0783",Place="Railway Station"}};

What I did is

var res = places.GroupJoin(cities,
                           p1=>p1.CityCode,
                           c1=>c1.CityCode,
                           (p2,c2s)=>new {Place=p2.Place,
                                CityName=c2s.Count()==0 ? "NO NAME" 
                                           : c2s.First().CityName });

foreach(var v in res)
 Console.WriteLine(v);

Is this the standard or it is quicky and dirty solution?

Answer 1

Here is a linq query version

var noCity = new City {CityName = "NO NAME"};
var anotherway = from p in places
                 join c in cities on p.CityCode equals c.CityCode into merge
                 from c in merge.DefaultIfEmpty(noCity)
                 select new { p.Place, c.CityName };

I think the use of DefaultIfEmpty() makes it a little more clear.

All in all, I find outer joins in linq pretty darn confusing. It's one of the few places where I find SQL queries dramatically superior.

Answer 2

In your case, you are not grouping the records so don't use your solution. you can use the solution from ScottS or use the query below.

var res = from p in places
                       select new
                       {
                           Place = p.Place,
                           CityName = (from c in cities where p.CityCode == c.CityCode select c.CityName).DefaultIfEmpty("NO NAME").ElementAtOrDefault(0)
                       };