(C# Language) LINQ - Join

Create a Project

Create a C# console project for the latest version of .NET Core. Open the solution explorer and add a file called Models.cs. Next add two record classes - record class City and record class Population. A City record consists of a city id and city name. A Population record consists of a foreign key from the City record and the size of population. This is a common RDBMS scenario where data from one table is connected to the data in a second table through a primary and foreign keys.

// Models.cs file 
// CityID is a foreign key into Population 

internal record class City (Int32 CityID, String Name);

internal record class Population(Int32 CityIDFK, Int32 Size);

Video Explanation (see it happen!)

Please watch the following youtube video:

Program.cs file

Next let's open the Program.cs file and add code to this file.

First we create a list of City records. We have added three records to this list.

After this we add a list of Population records. Each item of this list contains a foreign key from the Cities list.

Next we call the Join extension method on the Cities list. This method performs an inner-equi join where only those elements are returned which have a corresponding entry in the other list.

There are various overloads of the Join method. You will have to refer to the documentation for further details.

// program.cs 

List<City> Cities     = new() { 
                                new(100, "London"), 
                                new(101, "Washington"), 
                                new(102, "Ottawa") 
                              };

List<Population> Pops = new() {
                                new(100, 22), 
                                new(101, 32), 
                                new(102, 41)
                              };

// inner equijoin 
// see the text or the linked video 
// for explanation of the arguments 
var joined = Cities.Join(
                          Pops, 
                          city => city.CityID, 
                          pop => pop.CityIDFK, 
                          (c, p) => new { 
                                          Name = c.Name, 
                                          Pop = p.Size
                                         }
                        );

foreach(var v in joined)
{
  Console.WriteLine($"{v.Name} - {v.Pop}");
}

We have passed four arguments to the Join method. The first argument is the list to be joined.

The second argument is a lambda that specifies the column of the first sequence, and the third argument is the column of the second sequence. Thus, we shall be joining CityID of the Cities sequence to the CityIDFK of the Populations sequence. The fourth argument is a lambda that can be used to specify the columns/properties to be returned in the final result after joining the two tables. In this case we shall return the name of the city with an alias of Name and the population of the city with an alias of Pop.

// output 

London - 22
Washington - 32
Ottawa - 41

Lastly, we have set a foreach loop to display the joined result. The Name property and the Pop property are displayed on the console.

We can run the project to verify that the name of the city and its population are displayed as expected. Thankyou!