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.


Related Topics

A Beginner’s Tutorial on WPF in C#

Everything about Tuples in C# and When to Use?

Linear Search and Binary Search in C#

Creating Jagged Arrays in C#

Learning Indexers in C#

Understanding Method Parameter Modifiers in C#

Object Initializers in C#

Examples of Static Constructors in C#

When should We Use Private Constructors?

C# Practice Questions

C# Basic Examples

Private and Static Constructors in C#

Constructors in C#

C# Arrays

C# Examples

How to Create a C# Console Application

Creating Navigation Window Application Using WPF in C#

LINQ To SQL Examples

Understanding the Concept of Nested Classes in C#

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

Examples of Extension Methods in C#

Learning All Class Members in C#

Access Modifiers in C#

C# Root Class – Object

KeyValuePair and its Applications

IEnumerable and IEnumerator Interfaces

IEqualityComparer Interface

New Features in C# 9

Generic IList Interface and its Implementation in C#

Examples of Connected and Disconnected Approach in ADO.NET

How Data Binding Works in WPF

Language-Integrated Query (LINQ) in C#

Examples of Query Operations using LINQ in C#

Leave a Reply

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