In this article, I m going to give an overview of LINQ Joins with c#.
If you are having any experience with databases like SQL, Oracle, MySQL, etc .then you must be familiar with SQL Joins. LINQ joins are not different. they are also used to fetch data from multiple data sources sharing some common keys.
Before starting with joins let’s first see some basic definition of LINQ and what are join operations in LINQ and why we need to perform joins and what are different joins LINQ provide.
What is LINQ?
LINQ is Language integrated query.
It is a uniform syntax in c# and VB.net to retrieve data from the different data sources with different conditions in different formats.
What is LINQ joins?
As per the Microsoft documentation “A join of two data sources is the association of objects in one data source with objects that share a common attribute in another data source”.
If we simply then “Join Operations are used to fetch data from multiple data sources with some common attribute sharing between data source”.
Why do we need to Join Operations?
let’s understand why we need to perform join operation with an example. Here we are having 3 DataSource (Customer, Product, and Order)
We have created 3 C# Class Customer, Product, Order with the above data as below.
Customer Class
public class Customer
{
public int? CustId { get; set; }
public string CustName { get; set; }
public string Contact { get; set; }
public static List<Customer> GetAllCustomer()
{
return new List<Customer>()
{
new Customer { CustId = 1, CustName = "Priyanka", Contact = "9856325874" },
new Customer { CustId = 2, CustName = "Raj", Contact = "7958425632" },
new Customer { CustId = 3, CustName = "Rahul", Contact = "8236589654" },
new Customer { CustId = 4, CustName = "Sam", Contact = "7995826352" },
new Customer { CustId = 5, CustName = "John", Contact = "7995826352" }
};
}
}
Product Class
public class Product
{
public int? ProductId { get; set; }
public string ProductName { get; set; }
public int? Price { get; set; }
public static List<Product> GetAllProcuct()
{
return new List<Product>()
{
new Product { ProductId = 1, ProductName = "KeyBoard", Price = 500 },
new Product { ProductId = 2, ProductName = "Mouse", Price = 100 },
new Product { ProductId = 3, ProductName = "Monitor", Price = 7000 },
new Product { ProductId = 4, ProductName = "CPU", Price = 12000},
new Product { ProductId = 5, ProductName = "Printer", Price = 9000}
};
}
}
Order Class
public class Order
{
public int? OrderId { get; set; }
public int? ProductId { get; set; }
public int? CustId { get; set; }
public int? Quantity { get; set; }
public int? Price { get; set; }
public static List<Order> GetAllOrder()
{
return new List<Order>()
{
new Order { OrderId = 1, ProductId = 1 ,CustId=1,Quantity=5,Price=2500},
new Order { OrderId = 2, ProductId = 2 ,CustId=2,Quantity=5,Price=500},
new Order { OrderId = 3, ProductId = 2 ,CustId=3,Quantity=7,Price=700},
new Order { OrderId = 4, ProductId = 4 ,CustId=4,Quantity=2,Price=24000}
};
}
}
we need to fetch data from the above data source as shown below.
As we can see we are having data from all dataSource in the above result. So for this, the most important point that we need to understand is that for performing join we need the common property.
Here Product and Customer is the master table and the Order table joins both data sources. The common property between Customer and order is CustId and ProductId is common property for order and Product.
So to get data from this type of scenario we need a join operation that helps us to fetch the data from multiple data sources.
What are the different joins LINQ provides?
We can perform different types of joins such as Inner Join, Left Join, Full Join, and Cross Join in Linq.
let us see all these types in Detail with the above data sources.
Inner Join
Inner join returns only the matching elements from both data sources while non-matching elements are removed from the result set.
var innerJoin = (from prod in Product.GetAllProcuct()
join order in Order.GetAllOrder()
on prod.ProductId equals order.ProductId
select new
{
ProductName = prod.ProductName,
Price = prod.Price,
OrderId = order.OrderId,
Quantity = order.Quantity,
TotalPrice = order.Price
}).ToList();
In the above code, we have performed inner join on Product and Order Datasouce having a common element ProductId.
As result, we can see Inner join returns only the records that match in Product and order, and all the unmatched data are removed.
Left Join or Left Outer Join
The Left join or Left outer join will return the matching data as well as all non-matching data from the left data source.
var leftJoin = (from prod in Product.GetAllProcuct()
join od in Order.GetAllOrder() on prod.ProductId equals od.ProductId into t
from rt in t.DefaultIfEmpty()
select new
{
ProductName = prod.ProductName,
Price = prod.Price,
OrderId = rt?.OrderId,
Quantity = rt?.Quantity,
TotalPrice = rt?.Price
}).ToList();
In the above code, we have performed inner join on Product and Order Datasouce having a common element ProductId.
As result, we can see Left join returns only the records that match in Product and order, and also the remaining records of Product.
Full Join or Full Outer Join
Linq not exactly provides Full join. But we can derive the result of Full Join by union two different results.
Full Joins All the matching and non-matching records from both data sources.
NOTE:- For Union in LINQ both the result must have the same data type at the same position.
var result1 = from prod in Product.GetAllProcuct()
join od in Order.GetAllOrder() on prod.ProductId equals od.ProductId into ordertable
from rt in ordertable.DefaultIfEmpty()
join cust in Customer.GetAllCustomer() on rt?.CustId equals cust.CustId into custtable
from ct in custtable.DefaultIfEmpty()
select new
{
CustomerName=ct?.CustName,
ProductName = prod.ProductName,
Price = prod.Price,
OrderId = rt?.OrderId,
Quantity = rt?.Quantity,
TotalPrice = rt?.Price
};
var result2 = from cust in Customer.GetAllCustomer()
join od in Order.GetAllOrder() on cust.CustId equals od.CustId into ordertable
from rt in ordertable.DefaultIfEmpty()
join prod in Product.GetAllProcuct() on rt?.ProductId equals prod.ProductId into prodTable
from pr in prodTable.DefaultIfEmpty()
select new
{
CustomerName = cust.CustName,
ProductName = pr?.ProductName,
Price = pr?.Price,
OrderId = rt?.OrderId,
Quantity = rt?.Quantity,
TotalPrice = rt?.Price
};
result1 = result1.Union(result2);
In the above code, we have derived two different results, First will have all the records of Product and all matching records from Order and the Second will have all the records from Order and all the matching records from Product.
And we have union both the result to get the result of Full join.
As a result, we are having all the records from Product, Customer, and Order.
Cross Join
In Cross join we do not need any condition to join two or more data sources and it will result in the multiplication of record numbers from both data sources.
This means each data of the first data source will relate to each data of the second data source.
This result is also known as Cartesian Product.
var crossJoin = (from prod in Product.GetAllProcuct()
from order in Order.GetAllOrder()
select new
{
ProductName = prod.ProductName,
Price = prod?.Price,
OrderId = order.OrderId,
Quantity = order.Quantity,
TotalPrice = order.Price
}).ToList();
As a result, we are having all the records from Product associated with all the records of Order.
Join with more than Two DataSource.
we can also join more than two data sources in LINQ join and can perform all the above joins . Here I m performing Inner Join with all three data sources.
var multipleDataJoin = (from cust in Customer.GetAllCustomer()
join order in Order.GetAllOrder() on cust.CustId equals order.CustId
join prod in Product.GetAllProcuct() on order.ProductId equals prod.ProductId
select new
{
CustomerName = cust.CustName,
ProductName = prod.ProductName,
Price = prod?.Price,
OrderId = order.OrderId,
Quantity = order.Quantity,
TotalPrice = order.Price
}).ToList();
In the above code, we have joined Order, Product, and Customer
As a result, we get all the matching data from all three data sources.
Note:- LINQ does not provide Right join or Right Outer join we can get the result by swapping Datasource in left join or left outer join.
You can find the Demo Project of LINQ joins Here.
Hope this article helps you guys.
