Sometimes, we need to combine two sets of data in our development. In LINQ, we join data by stitching the common content.
The syntax is
join A on B.content equals A.content
In this case, B.content equal A.content.
Data Source
Manufacturer Sample
Car Data Sample
As we can see, the two data sets share a common content, which is the manufacturer, such as BMW, Audi, etc.
Class Definition
Car Class
public class Car
{
public int Year { get; set; }
public string Manufacturer { get; set; }
public string Name { get; set; }
public double Displacement { get; set; }
public int Cylinders { get; set; }
public int City { get; set; }
public int Highway { get; set; }
public int Combined { get; set; }
}Manufacturer Class
public class Manufacturer
{
public string Name { get; set; }
public string Headquarters { get; set; }
public int Year { get; set; }
}Stitching
In this post, we are going to focus on joining data, so we ignore the data process. With that said, the method ProcessCars("fuel.csv"); returns car objects and the method ProcessManufacturers("manufacturers.csv"); returns manufacturer objects.
Query Syntax
static void Main(string[] args)
{
var cars = ProcessCars("fuel.csv");
var manufacturers = ProcessManufacturers("manufacturers.csv");
var query =
from car in cars
join manufacturer in manufacturers //Start stiching
on car.Manufacturer
equals manufacturer.Name //Finish stiching
orderby car.Combined descending, car.Name ascending // second sort
select new // Annoymous class
{
manufacturer.Headquarters,
car.Name,
car.Combined
};Method Syntax
var query2 = cars.Join(manufacturers,
c => c.Manufacturer,
m => m.Name, (c, m) => new
{
m.Headquarters,
c.Name,
c.Combined
})
.OrderByDescending(c => c.Combined)
.ThenBy(c => c.Name);As we can see from the above code, car.Manufacturer and manufacturer.Name share the same content, and we stitch cars and manufacturers together via this content.
Loop Through
Once we finish stitching the data, we can project a new data type; in this case, we use an anonymous type, which has three properties, manufacturer.Headquarters, car.Name and car.Combined.
foreach (var car in query.Take(10))
{ //car here is a new type
Console.WriteLine($"{car.Headquarters} : {car.Name} : {car.Combined}");
}Join Data via Multiple Common Items
Still in the project, we also can join data via multiple common items in LINQ. Let’s say we need to join car and manufacturer via the name of manufacturer and year.
The Query Syntax
static void Main(string[] args)
{
var cars = ProcessCars("fuel.csv");
var manufacturers = ProcessManufacturers("manufacturers.csv");
var query = from car in cars
join manufacturer in manufacturers //Start stiching
on new { car.Manufacturer, car.Year }
equals
new { Manufacturer = manufacturer.Name, manufacturer.Year } //Finish stiching
orderby car.Combined descending, car.Name ascending // second sort
select new // Annoymous class
{ manufacturer.Headquarters,
car.Name,
car.Combined
};
}Method Syntax
var query =
cars.Join(manufacturers,
c => c.Manufacturer,
m => m.Name, (c, m) => new
{
m.Headquarters,
c.Name,
c.Combined
})
.OrderByDescending(c => c.Combined)
.ThenBy(c => c.Name);