Microsoft Visual C# LINQ: Joining Records

	Joining Records in LINQ

Introduction

One of the most common operations you can perform on records consists of joining them based on a condition. Joining records consists of creating a list of records from more than one set of records. In order to do this, you specify the lists, you specify a value they have in common, and you define the joining relationship. To create a join, you must use some lists, like those we have created so far.

The primary formula to create a join is:

join JoinName
in ChildList
on Condition

The join condition is created before the select statement. You start with the join keyword followed by a name. The name can be used later or you can ignore it. Here is an example of starting a join:

var persons = from staffMembers
              in employees
              join std . . .
              select staffMembers;

var vdos = from videos
           in lstDirectors
           join vdo . . .
           select Something;

The join operation is followed by the in keyword. The ChildList specifies the list created from the child class. This can be the list created by the previous in statement. Here is an example:

var persons = from staffMembers
              in employees
              join std in employees . . .
              select staffMembers;

var vdos = from videos
           in lstDirectors
           join vdo in lstVideos . . .
           select Something;

Of course, you can write the in statement on its own line. The Condition holds a logical expression that specifies the relationship by which the records will be joined.

To support joins, the C# language provides a context-sensitive keyword named equals. Therefore, to create a join in LINQ, the values used to establish the link in the join statement should use the object of the from statement and that of the join clause. Having those values, you can access the joining property from each object and apply the equals operator on them. The formula would be:

ChildField equals ParentField

When you create a condition, the compiler (or rather the LINQ interpreter) would check each record of the child list that has a value for the corresponding property of the parent list.

Cross Joins

A cross join is a set that includes all records from two lists as follows: the first record from the first list is associated to each record from the second list. Then the second record from the first list is associated to each record from the second list, and so on. There are two ways you create a cross join in LINQ.

For a cross join, you can use one type of collection (such as a list of numbers, a list for videos, etc) and another type of list (such as a list of names, a list of music albums, etc). You can then create a common list that includes items from each list. To create a cross join, you can use a combination of from...in... statement for each sub-list. Here is an example:

var vdos = from videos in lstVideos
           from albums in lstMusic
           select . . .;

Then, in the select statement, provide a way to retrieve the members from each list. This can be done by using the new operator and creating a list of fields inside the curly brackets. This can be done as follows:

using System;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class Exercise : Form
{
    private Button btnShow;

    private ColumnHeader colVideoTitle;
    private ColumnHeader colDirector;
    private ColumnHeader colRating;
    private ColumnHeader colYearReleased;
    private ColumnHeader colAlbumTitle;
    private ColumnHeader colMusician;

    ListView lvwCollection;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnShow = new Button();
        btnShow.Location = new System.Drawing.Point(12, 12);
        btnShow.Width = 75;
        btnShow.Text = "Show";
        btnShow.Click += new System.EventHandler(this.btnShow_Click);

        lvwCollection = new ListView();
        lvwCollection.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
        lvwCollection.FullRowSelect = true;
        lvwCollection.GridLines = true;
        lvwCollection.Location = new Point(12, 40);
        lvwCollection.Size = new System.Drawing.Size(580, 192);
        lvwCollection.View = View.Details;

        colVideoTitle = new ColumnHeader();
        colVideoTitle.Text = "Video Title";
        colVideoTitle.Width = 160;
        lvwCollection.Columns.Add(colVideoTitle);

        colDirector = new ColumnHeader();
        colDirector.Text = "Director";
        colDirector.Width = 130;
        lvwCollection.Columns.Add(colDirector);

        colRating = new ColumnHeader();
        colRating.Text = "Rating";
        colRating.Width = 50;
        colRating.TextAlign = HorizontalAlignment.Center;
        lvwCollection.Columns.Add(colRating);

        colYearReleased = new ColumnHeader();
        colYearReleased.Text = "Year";
        colYearReleased.Width = 40;
        colYearReleased.TextAlign = HorizontalAlignment.Right;
        lvwCollection.Columns.Add(colYearReleased);

        colAlbumTitle = new ColumnHeader();
        colAlbumTitle.Text = "Album Title";
        colAlbumTitle.Width = 110;
        lvwCollection.Columns.Add(colAlbumTitle);

        colMusician = new ColumnHeader();
        colMusician.Text = "Musician";
        colMusician.Width = 80;
        lvwCollection.Columns.Add(colMusician);

        Size = new System.Drawing.Size(610, 270);
        Controls.Add(this.btnShow);
        Controls.Add(this.lvwCollection);
        Text = "Entertainment";
    }

    private void btnShow_Click(object sender, EventArgs e)
    {
        List<Video> lstVideos = new List<Video>();

        lstVideos.Add(new Video("Two for the Money", "D.J. Caruso", "R", "2008"));
        lstVideos.Add(new Video("Her Alibi", "Bruce Beresford", "PG-13", "1998"));
        lstVideos.Add(new Video("Distinguished Gentleman (The)", "Jonathan Lynn", "R", ""));
        lstVideos.Add(new Video("Memoirs of a Geisha", "Rob Marshall", "PG-13", "2006"));
        lstVideos.Add(new Video("Wall Street", "Oliver Stone", "R", "2000"));
        lstVideos.Add(new Video("Sneakers", "Phil Alden Robinson", "PG-13", "1992"));

        List<Music> lstAlbums = new List<Music>();
        lstAlbums.Add(new Music("WordSound&Power", "Christafari"));
        lstAlbums.Add(new Music("Tribute", "Yanni"));
        lstAlbums.Add(new Music("Mutuashi", "Tshala Muana"));

        var vdos = from videos in lstVideos
                   from albums in lstAlbums
                   select new
                   {
                       videos.VideoTitle,
                       videos.Director,
                       videos.Rating,
                       videos.YearReleased,
                       albums.AlbumTitle,
                       albums.Artist
                   };

        foreach (var item in vdos)
        {
            ListViewItem lviCollection = new ListViewItem(item.VideoTitle);

            lviCollection.SubItems.Add(item.Director);
            lviCollection.SubItems.Add(item.Rating);
            lviCollection.SubItems.Add(item.YearReleased);
            lviCollection.SubItems.Add(item.AlbumTitle);
            lviCollection.SubItems.Add(item.Artist);
            lvwCollection.Items.Add(lviCollection);
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

public class Video
{
    public string VideoTitle { get; set; }
    public string Director { get; set; }
    public string Rating { get; set; }
    public string YearReleased { get; set; }

    public Video(string title = "",
                 string dir = "",
                 string ratings = "",
                 string year = "")
    {
        VideoTitle = title;
        Director = dir;
        Rating = ratings;
        YearReleased = year;
    }
}

public class Music
{
    public string AlbumTitle { get; set; }
    public string Artist { get; set; }

    public Music(string ttl, string musician)
    {
        AlbumTitle = ttl;
        Artist = musician;
    }
}

This would produce:

Collection

Notice that each record from one list is associated to each record of the other list. To put a restriction in the list of values, you can add a where condition. Here is an example:

var vdos = from videos in lstVideos
           from albums in lstAlbums
           where videos.Rating.Equals("R")
           select new { videos.VideoTitle, videos.Director,
                        videos.Rating, videos.YearReleased,
                        albums.AlbumTitle, albums.Artist };

This would produce:

Collection

In the same way, you can create a where statement for each from list. Here is an example:

var vdos = from videos
           in lstVideos
           from albums
           in lstAlbums
           where videos.Rating.Equals("R")
           where albums.Artist.Equals("Yanni")
           select new
           {
               videos.VideoTitle,
               videos.Director,
               videos.Rating,
               videos.YearReleased,
               albums.AlbumTitle,
               albums.Artist
           };

This can also be written as follows:

var vdos = from videos
           in lstVideos
           where videos.Rating.Equals("R")
           from albums
           in lstAlbums
           where albums.Artist.Equals("Yanni")
           select new
           {
               videos.VideoTitle,
               videos.Director,
               videos.Rating,
               videos.YearReleased,
               albums.AlbumTitle,
               albums.Artist
           };

This would produce:

Collection

The second technique of creating a cross join involves two lists that have a relationship. You start with a class that can be used as the basis of a list. Here is an example of a class that would be used to create a list of movie (or video) directors:

public class Director
{
    public string DirectorID { get; set; }
    public string Name { get; set; }
}

Before creating the join, you should (must) have a member from each class so that these two members will be used to join the lists. These members should hold unique values. In the above class, that would be the role of the DirectorID property, where we would make sure that each record has a unique DirectorID value.

When creating the other class, you should (must) create a property (or field) that represents the unique-value provider of the first class. In our example, when creating a class that will be used to create a list of videos, we must create a property (or field) that represents the DirectorID of the first class. The class can be created as follows:

public class Video
{
    public string ShelfNumber { get; set; }
    public string VideoTitle { get; set; }
    public string DirectorID { get; set; }
    public string Length { get; set; }
    public string Rating { get; set; }
    public string YearReleased { get; set; }

    public Video(string number = "",
                 string title = "",
                 string dir = "",
                 string len = "",
                 string ratings = "",
                 string year = "")
    {
        ShelfNumber = number;
        Title = title;
        DirectorID = dir;
        Length = len;
        Rating = ratings;
        YearReleased = year;
    }
}

To create a cross join, apply the square brackets to the from and the join statements to indicate the index by which the records would be established (you can use the square brackets whether the lists were created as arrays or using the List<> class). Here is an example of creating a cross join (all properties were created as strings for illustration; this was made so that, when they don't have a value, they would be empty; if they used the int data type, their values would be set to 0):

using System;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class Exercise : Form
{
    private Button btnShow;

    private ColumnHeader colShelfNumber;
    private ColumnHeader colVideoTitle;
    private ColumnHeader colDirectorID;
    private ColumnHeader colLength;
    private ColumnHeader colRating;
    private ColumnHeader colYearReleased;

    ListView lvwCollection;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnShow = new Button();
        btnShow.Location = new System.Drawing.Point(12, 8);
        btnShow.Width = 75;
        btnShow.Text = "Show";
        btnShow.Click += new System.EventHandler(this.btnShow_Click);

        lvwCollection = new ListView();
        lvwCollection.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
        lvwCollection.FullRowSelect = true;
        lvwCollection.GridLines = true;
        lvwCollection.Location = new Point(12, 40);
        lvwCollection.Size = new System.Drawing.Size(520, 192);
        lvwCollection.View = View.Details;

        colShelfNumber = new ColumnHeader();
        colShelfNumber.Text = "Shelf #";
        lvwCollection.Columns.Add(colShelfNumber);

        colVideoTitle = new ColumnHeader();
        colVideoTitle.Text = "Video Title";
        colVideoTitle.Width = 160;
        lvwCollection.Columns.Add(colVideoTitle);

        colDirectorID = new ColumnHeader();
        colDirectorID.Text = "Director";
        colDirectorID.TextAlign = HorizontalAlignment.Center;
        colDirectorID.Width = 100;
        lvwCollection.Columns.Add(colDirectorID);

        colLength = new ColumnHeader();
        colLength.Text = "Length";
        colLength.Width = 80;
        lvwCollection.Columns.Add(colLength);

        colRating = new ColumnHeader();
        colRating.Text = "Rating";
        colRating.Width = 50;
        colRating.TextAlign = HorizontalAlignment.Center;
        lvwCollection.Columns.Add(colRating);

        colYearReleased = new ColumnHeader();
        colYearReleased.Text = "(c) Year";
        colYearReleased.TextAlign = HorizontalAlignment.Center;
        lvwCollection.Columns.Add(colYearReleased);

        Size = new System.Drawing.Size(550, 270);
        Controls.Add(this.btnShow);
        Controls.Add(this.lvwCollection);
        Text = "Entertainment";
    }

    private void btnShow_Click(object sender, EventArgs e)
    {
        Director[] lstDirectors = new Director[3];

        lstDirectors[0] = new Director();
        lstDirectors[0].DirectorID = 1;
        lstDirectors[0].Name = "Rob Marshall";
        lstDirectors[1] = new Director(2, "Oliver Stone");
        lstDirectors[2] = new Director(3, "Jonathan Lynn");

        Video[] lstVideos = new Video[]
        {
            new Video("927940", "Natural Born Killers", 2, "118 minutes", "R", "1994"),
            new Video("", "Platoon", 2, "120 minutes", "", "1986"),
            new Video("279472", "Outfoxed"),
            new Video("725917", "Distinguished Gentleman (The)", 3, "112 minutes", "R"),
            new Video("", "Memoirs of a Geisha", 1, "", "PG-13", "2006"),
            new Video("291705", "My Cousin Vinny", 3, "120 minutes", "", "1992"),
            new Video("248115", "", 0, "", "R"),
            new Video("", "Wall Street", 2, "", "R", "2000")
        };

        var vdos = from videos
                   in lstDirectors
                   join vdo in lstVideos on lstDirectors[1].DirectorID equals lstVideos[1].DirectorID
                   select new { Shelf = vdo.ShelfNumber,
                                Title = vdo.VideoTitle,
                                Manager = videos.Name,
                                Length = vdo.Length,
                                Classification = vdo.Rating,
                                Copyright = vdo.YearReleased };
        
        foreach (var item in vdos)
        {
            ListViewItem lviCollection = new ListViewItem(item.Shelf);

            lviCollection.SubItems.Add(item.Title);
            lviCollection.SubItems.Add(item.Manager.ToString());
            lviCollection.SubItems.Add(item.Length);
            lviCollection.SubItems.Add(item.Classification);
            lviCollection.SubItems.Add(item.Copyright);
            lvwCollection.Items.Add(lviCollection);
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

public class Director
{
    public int DirectorID { get; set; }
    public string Name { get; set; }

    public Director(int dirID = 0, string dir = "")
    {
        DirectorID = dirID;
        Name = dir;
    }
}

public class Video
{
    public string ShelfNumber { get; set; }
    public string VideoTitle { get; set; }
    public int DirectorID { get; set; }
    public string Length { get; set; }
    public string Rating { get; set; }
    public string YearReleased { get; set; }

    public Video(string number = "",
                 string title = "",
                 int dir = 0,
                 string len = "",
                 string rate = "",
                 string year = "")
    {
        ShelfNumber = number;
        VideoTitle = title;
        DirectorID = dir;
        Length = len;
        Rating = rate;
        YearReleased = year;
    }
}

This would produce:

Video Collection

Notice that each record of the parent class (Director) is associted to each record of the child class (Video).

Inner Joins

Consider the following list:

Video Collection

Notice that some records don't have a value for some fields (such as the DirectorID property); they are empty. You can make your final list include only records that have a value for the child property. This is called an inner join.

To create an inner join, you start with a normal join as done for a cross join. For the join formula, instead of joining the fields using an index, access the key from the value of the join clause and assign it to the corresponding key from the value of the from statement. Here is an example:

var vdos = from videos
           in lstDirectors
           join vdo in lstVideos on videos.DirectorID equals vdo.DirectorID
           select new { Shelf = vdo.ShelfNumber,
                        Title = vdo.VideoTitle,
                        Manager = videos.Name,
                        Length = vdo.Length,
                        Classification = vdo.Rating,
                        Copyright = vdo.YearReleased };

This would produce:

Video Collection

Outer Joins

Instead of showing only records that have entries in the child list, you may want your resulting list to include all records, including those that are empty. To get this, you would create an outer join.

To suport outer joins, the Enumerable class is equipped with a method named DefaultIfEmpty. Its syntax is:

public static IEnumerable DefaultIfEmpty(this IEnumerable source);

The source is the list that calls this method. Here is an example:

private void btnShow_Click(object sender, EventArgs e)
{
    Director[] lstDirectors = new Director[4];

    lstDirectors[0] = new Director(1, "Rob Marshall");
    lstDirectors[1] = new Director(2, "Oliver Stone");
    lstDirectors[2] = new Director(3, "Jonathan Lynn");
    lstDirectors[3] = new Director(4, "Robert Greenwald");

    Video[] lstVideos = new Video[]
    {
            new Video("927940", "Natural Born Killers", 2, "118 minutes", "R", "1994"),
            new Video("", "Platoon", 2, "120 minutes", "", "1986"),
            new Video("279472", "Outfoxed", 4),
            new Video("725917", "Distinguished Gentleman (The)", 3, "112 minutes", "R"),
            new Video("", "Memoirs of a Geisha", 1, "", "PG-13", "2006"),
            new Video("291705", "My Cousin Vinny", 3, "120 minutes", "", "1992"),
            new Video("", "Wall Street", 2, "", "R", "2000"),
            new Video("817264", "South of the Border", 2, "102 minutes")
    };

    var vdos = from videos
               in lstVideos
               join directors in lstDirectors  on videos.DirectorID equals directors.DirectorID into VdoGroup
               from views in VdoGroup.DefaultIfEmpty()
               select new
               {
                       Shelf = videos.ShelfNumber,
                       Title = videos.VideoTitle,
                       Manager = views.Name,
                       Length = videos.Length,
                       Classification = videos.Rating,
                       Copyright = videos.YearReleased
               };

    foreach (var item in vdos)
    {
            ListViewItem lviCollection = new ListViewItem(item.Shelf);

            lviCollection.SubItems.Add(item.Title);
            lviCollection.SubItems.Add(item.Manager.ToString());
            lviCollection.SubItems.Add(item.Length);
            lviCollection.SubItems.Add(item.Classification);
            lviCollection.SubItems.Add(item.Copyright);
            lvwCollection.Items.Add(lviCollection);
    }
}

This would produce:

Videos

To restrict the result, you can add a where condition to a join. Here an example:

var vdos = from videos
           in lstVideos
           join directors in lstDirectors  on videos.DirectorID equals directors.DirectorID into VdoGroup
           from views in VdoGroup.DefaultIfEmpty()
           where views.DirectorID == 2
           select new
           {
               Shelf = videos.ShelfNumber,
               Title = videos.VideoTitle,
               Manager = views.Name,
               Length = videos.Length,
               Classification = videos.Rating,
               Copyright = videos.YearReleased
           };

This would produce:

Students

In this case, when showing the result, since you know the category it includes, you can omit that category in the select statement. Here is an example:

var vdos = from videos
           in lstVideos
           join directors in lstDirectors  on videos.DirectorID equals directors.DirectorID into VdoGroup
           from views in VdoGroup.DefaultIfEmpty()
           where views.DirectorID == 2
           select new
           {
               Shelf = videos.ShelfNumber,
               Title = videos.VideoTitle,
               //Manager = views.Name,
               Length = videos.Length,
               Classification = videos.Rating,
               Copyright = videos.YearReleased
           };

foreach (var item in vdos)
{
    ListViewItem lviCollection = new ListViewItem(item.Shelf);

    lviCollection.SubItems.Add(item.Title);
    //lviCollection.SubItems.Add(item.Manager.ToString());
    lviCollection.SubItems.Add(item.Length);
    lviCollection.SubItems.Add(item.Classification);
    lviCollection.SubItems.Add(item.Copyright);
    lvwCollection.Items.Add(lviCollection);
}

This would produce:

Videos