C#

Join Operation using LINQ

This article will demonstrate the Join Operation using LINQ. Whenever you want to write an SQL-like join query using LINQ, you can use the LINQ join operator. In order to perform the join operation on two or more data sources, it is required that those data sources must have some common value.

Significantly, all the join operations that we can do with LINQ are equijoin operations and they use the equals operator. However, the join may be of any of the following types – inner join, group join, and the left outer join.

While the inner join establishes a relationship only if the two entities have a common value. In contrast, a group join will establish a relationship between the two entities even when the right side has no matching value. Basically, it links each element of the left side to one or more matching values on the right side. When there is no matching value on the right side, the group join creates an empty array for that value on the left side.

On the other hand, a Left Outer Join returns all values on the left side even if there is no matching value at the right side.

Example of Join Operation using LINQ

The following example shows the inner join on two or more data sources. Also, the data sources are the arrays of objects of the three classes namely Customer, Product, and Complaints.

Creating the Classes

As shown below, the example demonstrates an application where a customer purchases a software product from a company and also can submit a complaint.

class Customer
    {
        public int CustomerID { get; set; }
        public String Cname { get; set; }
        public string ProductID { get; set; }
        public override string ToString()
        {
            return $"Customer ID: {CustomerID}, Customer Name: {Cname}, Product ID: {ProductID}";
        }
    }
    class Product
    {
        public String P_Id { get; set; }
        public String Pname { get; set; }
        public override string ToString()
        {
            return $"Product ID: {P_Id}, Product Name: {Pname}";
        }
    }
    class Complaints
    {
        public int Complaint_Id { get; set; }
        public int Cust_Id { get; set; }
        public String P_Id { get; set; }
        public String Complaint_Description { get; set; }
        public override string ToString()
        {
            return $"Complaint ID: {Complaint_Id}, Customer ID: {Cust_Id}, Product ID: {P_Id}, Description: {Complaint_Description}";
        }

    }

Creating Collections

In order to create data sources, we need to create the corresponding collections. As shown below, we create the arrays of all the three entities that we have just created above.

Product[] arr1 = new Product[]
            {
                new Product{P_Id="p1", Pname="Online Backup Software"},
                new Product{P_Id="p2", Pname="ERP Software"},
                new Product{P_Id="p3", Pname="Attendance Software"},
                new Product{P_Id="p4", Pname="CRM Software"},
                new Product{P_Id="p5", Pname="Expense Management Software"},
                new Product{P_Id="p6", Pname="Accounting Software"},
            };

            Customer[] arr2 = new Customer[]
            {
                new Customer{CustomerID=1, Cname="A", ProductID="p1"},
                new Customer{CustomerID=1, Cname="A", ProductID="p4"},
                new Customer{CustomerID=2, Cname="B", ProductID="p2"},
                new Customer{CustomerID=2, Cname="B", ProductID="p3"},
                new Customer{CustomerID=2, Cname="B", ProductID="p5"},
                new Customer{CustomerID=3, Cname="C", ProductID="p5"},
                new Customer{CustomerID=4, Cname="D", ProductID="p1"},
                new Customer{CustomerID=4, Cname="D", ProductID="p2"},
                new Customer{CustomerID=4, Cname="D", ProductID="p6"},
                new Customer{CustomerID=4, Cname="D", ProductID="p4"},
            };
            Complaints[] arr3 = new Complaints[]
            {
                new Complaints{Complaint_Id=1, Cust_Id=1, P_Id="p1", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=2, Cust_Id=1, P_Id="p4", Complaint_Description="Software Too Complex"},
                new Complaints{Complaint_Id=3, Cust_Id=2, P_Id="p2", Complaint_Description="Insufficient Customer Satisfaction"},
                new Complaints{Complaint_Id=4, Cust_Id=2, P_Id="p5", Complaint_Description="Insufficient Customer Satisfaction"},
                new Complaints{Complaint_Id=5, Cust_Id=3, P_Id="p5", Complaint_Description="Very Slow"},
                new Complaints{Complaint_Id=6, Cust_Id=3, P_Id="p5", Complaint_Description="Updates Not Working"},
                new Complaints{Complaint_Id=7, Cust_Id=2, P_Id="p3", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=8, Cust_Id=2, P_Id="p2", Complaint_Description="Lack of updates"},
                new Complaints{Complaint_Id=9, Cust_Id=4, P_Id="p1", Complaint_Description="Software Too Complex"},
                new Complaints{Complaint_Id=10, Cust_Id=4, P_Id="p1", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=11, Cust_Id=4, P_Id="p6", Complaint_Description="Very Slow"},
                new Complaints{Complaint_Id=12, Cust_Id=4, P_Id="p4", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=13, Cust_Id=4, P_Id="p2", Complaint_Description="Difficult to Work"},
            };

Join Operation on Product and Customer Collections

After that, we write a query using the join clause on the Customers collection, and the Products collection. The matching field here is the P_Id, and the ProductID of the Products and Customers array respectively.

var q1 = (from r1 in arr1
          join r2 in arr2 on r1.P_Id equals r2.ProductID
          orderby r2.CustomerID
          select new { Customer = r2.Cname, Product = r1.Pname }).ToList();

Join Operation on Customer and Complaints Collections

In a similar manner, the next query performs the join operation on the Product collection and the Complaints collection.

var q2 = (from r1 in arr1
                     join r2 in arr3 on r1.P_Id equals r2.P_Id
                     orderby r2.Complaint_Id
                     select new { Complaint_ID = r2.Complaint_Id, Product = r1.Pname, Complaint = r2.Complaint_Description }).ToList();

Join Operation on Product, Customer and Complaints Collections

Finally, the last query performs the join operations on the three collections that we have created earlier.

var q3 = (from a1 in arr1
                     join a2 in arr2 on a1.P_Id equals a2.ProductID
                     join a3 in arr3 on new {p= a2.ProductID, c=a2.CustomerID} equals new{p=a3.P_Id,c= a3.Cust_Id }
                     orderby a2.Cname
                     select new { Complaint_ID = a3.Complaint_Id, Customer = a2.Cname, Product = a1.Pname, Complaint = a3.Complaint_Description }).ToList();

Complete Code

The following code shows all the inner join operations.

using System;
using System.Linq;
namespace JoinQueriesLINQ
{
    class Customer
    {
        public int CustomerID { get; set; }
        public String Cname { get; set; }
        public string ProductID { get; set; }
        public override string ToString()
        {
            return $"Customer ID: {CustomerID}, Customer Name: {Cname}, Product ID: {ProductID}";
        }
    }
    class Product
    {
        public String P_Id { get; set; }
        public String Pname { get; set; }
        public override string ToString()
        {
            return $"Product ID: {P_Id}, Product Name: {Pname}";
        }
    }
    class Complaints
    {
        public int Complaint_Id { get; set; }
        public int Cust_Id { get; set; }
        public String P_Id { get; set; }
        public String Complaint_Description { get; set; }
        public override string ToString()
        {
            return $"Complaint ID: {Complaint_Id}, Customer ID: {Cust_Id}, Product ID: {P_Id}, Description: {Complaint_Description}";
        }

    }
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Creating Data Sources....");
            Product[] arr1 = new Product[]
            {
                new Product{P_Id="p1", Pname="Online Backup Software"},
                new Product{P_Id="p2", Pname="ERP Software"},
                new Product{P_Id="p3", Pname="Attendance Software"},
                new Product{P_Id="p4", Pname="CRM Software"},
                new Product{P_Id="p5", Pname="Expense Management Software"},
                new Product{P_Id="p6", Pname="Accounting Software"},
            };

            Customer[] arr2 = new Customer[]
            {
                new Customer{CustomerID=1, Cname="A", ProductID="p1"},
                new Customer{CustomerID=1, Cname="A", ProductID="p4"},
                new Customer{CustomerID=2, Cname="B", ProductID="p2"},
                new Customer{CustomerID=2, Cname="B", ProductID="p3"},
                new Customer{CustomerID=2, Cname="B", ProductID="p5"},
                new Customer{CustomerID=3, Cname="C", ProductID="p5"},
                new Customer{CustomerID=4, Cname="D", ProductID="p1"},
                new Customer{CustomerID=4, Cname="D", ProductID="p2"},
                new Customer{CustomerID=4, Cname="D", ProductID="p6"},
                new Customer{CustomerID=4, Cname="D", ProductID="p4"},
            };
            Complaints[] arr3 = new Complaints[]
            {
                new Complaints{Complaint_Id=1, Cust_Id=1, P_Id="p1", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=2, Cust_Id=1, P_Id="p4", Complaint_Description="Software Too Complex"},
                new Complaints{Complaint_Id=3, Cust_Id=2, P_Id="p2", Complaint_Description="Insufficient Customer Satisfaction"},
                new Complaints{Complaint_Id=4, Cust_Id=2, P_Id="p5", Complaint_Description="Insufficient Customer Satisfaction"},
                new Complaints{Complaint_Id=5, Cust_Id=3, P_Id="p5", Complaint_Description="Very Slow"},
                new Complaints{Complaint_Id=6, Cust_Id=3, P_Id="p5", Complaint_Description="Updates Not Working"},
                new Complaints{Complaint_Id=7, Cust_Id=2, P_Id="p3", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=8, Cust_Id=2, P_Id="p2", Complaint_Description="Lack of updates"},
                new Complaints{Complaint_Id=9, Cust_Id=4, P_Id="p1", Complaint_Description="Software Too Complex"},
                new Complaints{Complaint_Id=10, Cust_Id=4, P_Id="p1", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=11, Cust_Id=4, P_Id="p6", Complaint_Description="Very Slow"},
                new Complaints{Complaint_Id=12, Cust_Id=4, P_Id="p4", Complaint_Description="Poor Support"},
                new Complaints{Complaint_Id=13, Cust_Id=4, P_Id="p2", Complaint_Description="Difficult to Work"},
            };
            Console.WriteLine();
            Console.WriteLine("Customers and Products Purchased...");
            var q1 = (from r1 in arr1
                      join r2 in arr2 on r1.P_Id equals r2.ProductID
                      orderby r2.CustomerID
                      select new { Customer = r2.Cname, Product = r1.Pname }).ToList();
            foreach (var ob in q1) Console.WriteLine(ob);

            Console.WriteLine();
            Console.WriteLine("Products and Complaints...");
            var q2 = (from r1 in arr1
                     join r2 in arr3 on r1.P_Id equals r2.P_Id
                     orderby r2.Complaint_Id
                     select new { Complaint_ID = r2.Complaint_Id, Product = r1.Pname, Complaint = r2.Complaint_Description }).ToList();
            foreach (var ob in q2) Console.WriteLine(ob);
            Console.WriteLine();

            Console.WriteLine("Customers, Products and Complaints...");
            var q3 = (from a1 in arr1
                     join a2 in arr2 on a1.P_Id equals a2.ProductID
                     join a3 in arr3 on new {p= a2.ProductID, c=a2.CustomerID} equals new{p=a3.P_Id,c= a3.Cust_Id }
                     orderby a2.Cname
                     select new { Complaint_ID = a3.Complaint_Id, Customer = a2.Cname, Product = a1.Pname, Complaint = a3.Complaint_Description }).ToList();
            foreach (var ob in q3) Console.WriteLine(ob);
        }
    }
}

Output

Join Operation using LINQ
Join Operation using LINQ

Summary

In this article, the Join Operation using LINQ has been explained. Evidently, the data sources used here are three arrays of objects that have certain fields in common. The examples demonstrate the inner join operations on two collections as well as on the three collections.


Further Reading

Selection Sort in C#

Insertion Sort in C#

Bubble Sort in C#

How to Create Instance Variables and Class Variables in Python

Comparing Rows of Two Tables with ADO.NET

Example of Label and Textbox Control in ASP.NET

One Dimensional and Two Dimensuonal Indexers in C#

Private and Static Constructors in C#

Methods of Array Class

Anonymous Functions in C#

Programs to Find Armstrong Numbers in C#

Matrix Multiplication in C#

One Dimensional and Two Dimensional Indexers in C#

Static Class Example in C#

Rotating an Array in C#

Generic IList Interface and its Implementation in C#

Recursive Binary search in C#

C# Practice Questions

Creating Navigation Window Application Using WPF in C#

Find Intersection Using Arrays

An array of Objects and Object Initializer

Performing Set Operations in LINQ

Using Quantifiers in LINQ

Data Binding Using BulletedList Control

Grouping Queries in LINQ

Generic Binary Search in C#

Understanding the Quantifiers in LINQ

Join Operation using LINQ

Deferred Query Execution and Immediate Query Execution in LINQ

Examples of Query Operations using LINQ in C#

An array of Objects and Object Initializer

Language-Integrated Query (LINQ) in C#

How Data Binding Works in WPF

Examples of Connected and Disconnected Approach in ADO.NET

New Features in C# 9

IEnumerable and IEnumerator Interfaces

KeyValuePair and its Applications

C# Root Class – Object

Access Modifiers in C#

Learning Properties in C#

Learning All Class Members in C#

Examples of Extension Methods in C#

How to Setup a Connection with SQL Server Database in Visual Studio

Understanding the Concept of Nested Classes in C#

LINQ To SQL Examples

A Beginner’s Tutorial on WPF in C#

Explaining C# Records with Examples

Everything about Tuples in C# and When to Use?

Creating Jagged Arrays in C#

Linear Search and Binary search in C#

Learning Indexers in C#

Object Initializers in C#

Examples of Static Constructors in C#

When should We Use Private Constructors?

C# Basic Examples

IEqualityComparer Interface

You may also like...

Leave a Reply

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