C# LINQ Joins With SQL

There are Different Types of SQL Joins that are used to query data from more than one database table. In this article, you will learn about how to write SQL joins queries in LINQ using C#. LINQ has a JOIN query operator that gives you SQL JOIN-like behavior and syntax.

Types of LINQ Joins

Venn diagram for LINQ Joins

The JOIN query operator compares the specified properties/keys of two collections for equality by using the EQUALS keyword. By default, all join queries written by the JOIN keyword are treated as equijoins. Let's understand the LINQ Joins using Venn diagram.

LINQ Queries using LINQ PAD

I am a big fan of LINQ Pad since it allows us to run LINQ to SQL and LINQ to Entity Framework query and gives the query output. Whenever I need to write LINQ to SQL and LINQ to Entity Framework query then, I prefer to write and run the query on LINQ PAD. By using LINQ PAD, you can test and run your desired LINQ query and avoid the headache of testing LINQ queries within Visual Studio. You can download the LINQ Pad script used in this article by using a download link at the top.

In this article, I am using LINQ PAD for query data from the database. It is simple and useful. For more help about LINQ PAD refer the link. Now Suppose, we have three tables, and data in these three tables is shown in the given figure.

INNER JOIN

Inner join returns only those records or rows that match or exists in both tables.

C# Code

var q=(from pd in dataContext.tblProducts 
 join od in dataContext.tblOrders on pd.ProductID equals od.ProductID 
 orderby od.OrderID 
 select new { 
 od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList(); 
 

LINQ Pad Query

INNER JOIN Among More than Two Tables

Like SQL, we can also apply to join on multiple tables based on conditions as shown below.

C# Code

var q=(from pd in dataContext.tblProducts 
 join od in dataContext.tblOrders on pd.ProductID equals od.ProductID 
 join ct in dataContext.tblCustomers on od.CustomerID equals ct.CustID 
 orderby od.OrderID 
 select new { 
 od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 Customer=ct.Name //define anonymous type Customer
 }).ToList(); 
 

LINQ Pad Query

INNER JOIN On Multiple Conditions

Sometimes, we are required to apply to join on multiple conditions. In this case, we need to make two anonymous types (one for the left table and one for the right table) by using a new keyword then we compare both the anonymous types.

C# Code

var q=(from pd in dataContext.tblProducts 
 join od in dataContext.tblOrders on pd.ProductID equals od.ProductID 
 join ct in dataContext.tblCustomers 
 on new {a=od.CustomerID,b=od.ContactNo} equals new {a=ct.CustID,b=ct.ContactNo} 
 orderby od.OrderID 
 select new { 
 od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 Customer=ct.Name //define anonymous type Customer
 }).ToList();
 

LINQ Pad Query

NOTE

1. Always remember, that both the anonymous types should have the same number of properties with the same name and datatype otherwise you will get the compile-time error "Type inference failed in the call to Join".

2. Both the comparing fields should define either NULL or NOT NULL values.

3. If one of them is defined NULL and the other is defined NOT NULL then we need to do typecasting of a NOT NULL field to NULL data type as above fig.

LEFT JOIN or LEFT OUTER JOIN

LEFT JOIN returns all records or rows from the left table and from the right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

In LINQ to achieve LEFT JOIN behavior, it is mandatory to use the "INTO" keyword and the "DefaultIfEmpty()" method. We can apply LEFT JOIN in LINQ like as :

C# Code

var q=(from pd in dataContext.tblProducts 
 join od in dataContext.tblOrders on pd.ProductID equals od.ProductID 
 into t from rt in t.DefaultIfEmpty() 
 orderby pd.ProductID 
 select new { 
 //To handle null values do type casting as int?(NULL int) 
 //since OrderID is defined NOT NULL in tblOrders
 OrderID=(int?)rt.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 //no need to check for null since it is defined NULL in database
 rt.Quantity,
 rt.Price,
 }).ToList();
 

LINQ Pad Query

CROSS JOIN

Cross join is a cartesian join means a cartesian product of both tables. This join does not need any condition to join two tables. This join returns records or rows that are a multiplication of record numbers from both tables means each row on the left table will be related to each row on the right table.

In LINQ to achieve CROSS JOIN behavior, there is no need to use the Join clause and where clause. We will write the query as shown below.

C# Code

var q = from c in dataContext.Customers from o in dataContext.Orders 
 select new { 
 c.CustomerID,
 c.ContactName,
 a.OrderID,
 a.OrderDate
 }; 

LINQ Pad Query

GROUP JOIN

When a join clause uses an INTO expression, then it is called a group join. A group join produces a sequence of object arrays based on properties equivalence of left collection and right collection. If the right collection has no matching elements with left collection then an empty array will be produced.

C# Code

var q=(from pd in dataContext.tblProducts 
 join od in dataContext.tblOrders on pd.ProductID equals od.ProductID 
 into t orderby pd.ProductID
 select new{
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 Order=t
 }).ToList(); 

LINQ Pad Query

GROUP JOIN is like INNER-EQUIJOIN except that the result sequence is organized into groups.

GROUP JOIN As SubQuery

We can also use the result of a GROUP JOIN as a subquery like as:

C# Code

var q=(from pd in dataContext.tblProducts 
 join od in dataContext.tblOrders on pd.ProductID equals od.ProductID 
 into t from rt in t 
 where rt.Price>70000 
 orderby pd.ProductID 
 select new { 
 rt.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 rt.Quantity,
 rt.Price,
 }).ToList(); 

LINQ Pad Query

Summary:

I hope you will enjoy these valuable tricks while programming with LINQ to SQL. I would like to have feedback from my blog readers. Your valuable feedback, questions, or comments about this article are always welcome. Enjoy coding...!