C# Joining 3 Lists with Linq

Question

I want to join 3 Lists that have the following format:

List1:
  CountryID | CountryData     | regionUID
  ===================================
         12 | Has good gras   | 4567
         12 | nice weather    | 6789
         16 | stormy weather  | 1234

List2:
  CountryID | CountryName
  =======================
         12 | green hill
         16 | stormy mountain

List3:
  regionUID | regionName
  =======================
       4567 | above 1000feet
       6789 | on the ground
       1234 | on the hill

Output should look like:

CountryName      | CountryData      | regionName
==============================================
 green hill      | Has good gras    | above 1000feet
 green hill      | nice weather     | on the ground
 stormy mountain | stormy weather   | on the hill

where region is unique and country can fit to multiple lines

If you're simply interested in the syntax of a LINQ Join, see http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — George Johnston, May 21 '13 at 19:25

score 17 · Accepted Answer · answered May 21 '13 at 20:44

You want something like this:

var list1 = new []{
    new { CountryID = 12, CountryData = "Has good gras", regionUID = 4567  },
    new { CountryID = 12, CountryData = "nice weather", regionUID = 6789  },
    new { CountryID = 16, CountryData = "stormy weather", regionUID = 1234 }};

var list2 = new []{
    new { CountryID = 12, CountryName = "green hill"},
    new { CountryID = 16, CountryName = "stormy mountain"}
};

var list3 = new []{
    new {regionUID = 4567, regionName = "above 1000feet"},
    new {regionUID = 6789, regionName = "on the ground"},
    new {regionUID = 1234, regionName = "on the hill"}
};

var result = from m1 in list1
    join m2 in list2 on m1.CountryID equals m2.CountryID
    select new { m2.CountryName, m1.CountryData, m1.regionUID } into intermediate
    join m3 in list3 on intermediate.regionUID equals m3.regionUID
    select new { intermediate.CountryName, intermediate.CountryData, m3.regionName};

RW

C# Joining 3 Lists with Linq

1 Answers1

Linked