Join Data in LINQ

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);

16 Replies to “Join Data in LINQ”

  1. What’s up, after reading this awesome post i am as well delighted to share my
    experience here with friends.

Leave a Reply

Your email address will not be published. Required fields are marked *