Joining Records in LINQ
Joining Records in LINQ
LINQ Junctions
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 list 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 join-name in child-list 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 child-list 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 values from each list and apply the equals operator on them. The formula would be:
child-field equals parent-field
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 value of the parent list.
A cross join is a collection 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 list (such as a list of numbers, a list of 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 a new operator and creating a list of fields inside the curly brackets. This can be done as follows:
namespace MediaCollection
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(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.ToString());
lviCollection.SubItems.Add(item.AlbumTitle);
lviCollection.SubItems.Add(item.Artist);
lvwCollection.Items.Add(lviCollection);
}
}
}
internal readonly record struct Video
{
internal string VideoTitle { get; init; }
internal string Director { get; init; }
internal string Rating { get; init; }
internal int YearReleased { get; init; }
public Video(string title, string dir,
string rating, int year = 1000)
{
(VideoTitle, Director, Rating, YearReleased) =
(title, dir, rating, year);
}
}
internal readonly record struct Music
{
internal string AlbumTitle { get; init; }
internal string Artist { get; init; }
public Music(string ttl, string musician)
{
(AlbumTitle, Artist) = (ttl, musician);
}
}
}
This would produce:
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:
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:
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:
internal readonly record struct 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:
internal readonly record struct Director
{
internal string DirectorId { get; init; }
internal string Name { get; init; }
}
internal readonly record struct Video
{
internal string VideoTitle { get; init; }
internal string DirectorId { get; init; }
internal string Rating { get; init; }
internal int YearReleased { get; init; }
}
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):
namespace Numerotation
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnShow_Click(object sender, EventArgs e)
{
Director[] lstDirectors = new Director[3];
lstDirectors[0] = new Director(1, "Rob Marshall");
lstDirectors[1] = new() { Name = "Oliver Stone", DirectorId = 2, };
lstDirectors[2] = new(dirId : 3, dir : "Jonathan Lynn");
Video[] lstVideos = new Video[]
{
new Video() { ShelfNumber = "927940", VideoTitle = "Natural Born Killers", DirectorId = 2, Length = "118 minutes", Rating = "R", YearReleased = "1994" },
new Video() { DirectorId = 2, Length = "120 minutes", YearReleased = "1986", VideoTitle = "Platoon" },
new Video() { ShelfNumber = "279472", VideoTitle = "Outfoxed" },
new Video() { VideoTitle = "Distinguished Gentleman (The)", Rating = "R", DirectorId = 3, ShelfNumber = "725917", Length = "112 minutes" },
new Video() { YearReleased = "2006", Rating = "PG-13", DirectorId = 1, VideoTitle = "Memoirs of a Geisha" },
new Video() { Length = "120 minutes", ShelfNumber = "291705", DirectorId = 3, YearReleased = "1992", VideoTitle = "My Cousin Vinny" },
new Video() { Rating = "R", ShelfNumber = "248115", DirectorId = 0 },
new Video() { VideoTitle = "Wall Street", DirectorId = 2, Rating = "R", Length = "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);
}
}
}
internal readonly record struct Director
{
internal int DirectorId { get; init; }
internal string Name { get; init; }
public Director(int dirId = 0, string dir = "")
{
DirectorId = dirId;
Name = dir;
}
}
internal readonly record struct Video
{
internal string ShelfNumber { get; init; }
internal string VideoTitle { get; init; }
internal int DirectorId { get; init; }
internal string Length { get; init; }
internal string Rating { get; init; }
internal string YearReleased { get; init; }
}
}
This would produce:
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:
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:
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 IEnumerableDefaultIfEmpty (this IEnumerable source);
The source is the list that calls this method. Here is an example:
namespace VideoCollection
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
}
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() { ShelfNumber = "927940", VideoTitle = "Natural Born Killers", DirectorId = 2, Length = "118 minutes", Rating = "R", YearReleased = "1994" },
new Video() { DirectorId = 2, Length = "120 minutes", YearReleased = "1986", VideoTitle = "Platoon" },
new Video() { ShelfNumber = "279472", VideoTitle = "Outfoxed", DirectorId = 4 },
new Video() { VideoTitle = "Distinguished Gentleman (The)", Rating = "R", DirectorId = 3, ShelfNumber = "725917", Length = "112 minutes" },
new Video() { YearReleased = "2006", Rating = "PG-13", DirectorId = 1, VideoTitle = "Memoirs of a Geisha" },
new Video() { Length = "120 minutes", ShelfNumber = "291705", DirectorId = 3, YearReleased = "1992", VideoTitle = "My Cousin Vinny" },
new Video() { VideoTitle = "Wall Street", DirectorId = 2, Rating = "R", Length = "2000" },
new Video() { ShelfNumber = "817264", Length = "102 minutes", VideoTitle = "South of the Border", DirectorId = 2 }
};
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);
}
}
}
internal readonly record struct Director
{
internal int DirectorId { get; init; }
internal string Name { get; init; }
internal Director(int dirId = 0, string dir = "")
{
DirectorId = dirId;
Name = dir;
}
}
internal readonly record struct Video
{
internal string ShelfNumber { get; init; }
internal string VideoTitle { get; init; }
internal int DirectorId { get; init; }
internal string Length { get; init; }
internal string Rating { get; init; }
internal string YearReleased { get; init; }
}
}
This would produce:
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:
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:
| Previous | Copyright © 2008-2023, FunctionX, Inc. | Wednesday 15 September 2021 | Next |