Programmingempire
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
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
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#
Programs to Find Armstrong Numbers in C#
One Dimensional and Two Dimensional Indexers in C#
Generic IList Interface and its Implementation in C#
Creating Navigation Window Application Using WPF in C#
Find Intersection Using Arrays
An array of Objects and Object Initializer
Performing Set Operations in LINQ
Data Binding Using BulletedList Control
Understanding the Quantifiers in 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#
Examples of Connected and Disconnected Approach in ADO.NET
IEnumerable and IEnumerator Interfaces
KeyValuePair and its Applications
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#
A Beginner’s Tutorial on WPF in C#
Explaining C# Records with Examples
Everything about Tuples in C# and When to Use?
Linear Search and Binary search in C#
Examples of Static Constructors in C#
When should We Use Private Constructors?
- Angular
- ASP.NET
- C
- C#
- C++
- CSS
- Dot Net Framework
- HTML
- IoT
- Java
- JavaScript
- Kotlin
- PHP
- Power Bi
- Python
- Scratch 3.0
- TypeScript
- VB.NET
