C# LINQ Joins With Query Structure

LINQ uses an SQL-like syntax to make query expressions well beyond the capabilities of embedded SQL, as implemented in programming languages.

Idea behind LINQ

LINQ (Language Integrated Query) was introduced in .NET Framework 3.5. Suppose, we are working on SQL Server database and we know SQL Server syntax and SQL related ADO.NET object. Now, suppose after some time, the project requirement needs us to switch to another database like Oracle. Now, we need to learn the syntax of Oracle database and Oracle related ADO.NET objects to work with C#.

 

This problem arises with number of technologies. Learning all languages is neither feasible nor possible. So, basically to avoid this, Microsoft introduced LINQ for querying any database MySQL, SQL, Oracle, etc.

Now, in this article, I would like to share how joins work in LINQ with syntax expression comparison with SQL syntax expression.

Kinds of joins in Linq

1. INNER JOIN
2. LEFT OUTER JOIN
3. CROSS JOIN
4. GROUP JOIN

We have the following three tables (Customer, Product and Order), and the data in these three tables is shown in figure.

INNER JOIN

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

Suppose we want to use inner join in Customer and Product table.

var q = (from cust in dataContext.tblCustomer join product in dataContext.tblProduct on cust.CustomerId equals product.CustomerId orderby cust.CustomerId select new {
    cust.CustomerId,
        cust.Name,
        product.ProductName,
        product.Price,
}).ToList();

NOTE

dataContext, I used in the previous post

INNER JOIN among more than two tables.

We can apply join on multiple table on condition base.

var q = (from product in dataContext.Products join order in dataContext.Orders on product.ProductId equals order.ProductId join cust in dataContext.Customers on order.CustomerId equals cust.CustomerId orderby order.OrderId select new {
    order.OrderId,
        product.ProductId,
        product.ProductName,
        product.Price,
        order.Quantity,
        Customer = cust.Name
}).ToList();

INNER JOIN With Multiple Conditions

Sometimes, we required to apply join with multiple coditions. So in this case, we need to make two anonymous types (one for left table and one for right table) by using new keyword then we compare both the anonymous types. You can see custid,pid below

var q = (from product in dataContext.Products join order in dataContext.Orders on product.ProductId equals order.ProductId join cust in dataContext.Customers on new {
        custid = order.CustomerId, pid = order.ProductId
    }
    equals new {
        custid = cust.CustomerId, pid = cust.ProductId
    }
    orderby order.OrderId select new {
        order.OrderId,
            product.ProductId,
            product.ProductName,
            product.Price,
            order.Quantity,
            Customer = cust.Name
    }).ToList();

NOTE

1. Always remember, both the anonymous types should have exact same number of properties with same name and datatype other wise you will get the compile time error.
   
   
2. If one of them is defined NULL and other is defined NOT NULL then we need to do typecasting of NOT NULL field to NULL data type.

LEFT JOIN or LEFT OUTER JOIN

Left Join returns all rows from left table and from right table returns only matched records. If there are no columns matching in the right table crosponding to left table records, it returns NULL values.

In Left Join it is mandatory to use "INTO" keyword and "DefaultIfEmpty()" method

var q = (from cust in dataContext.Customers join product in dataContext.Products on cust.CustomerId equals product.CustomerId into rd from rt in rd.DefaultIfEmpty() orderby cust.CustomerId select new {
    cust.CustomerId,
        cust.Name,
        //You can handle null values do type casting like int?(NULL int)
        rt.(int ? ) ProductId,
        rt.ProductName,
        rt.Price,
}).ToList();

NOTE

1. Always remember; for Right join, just swap the table. In LINQ, there is pre-defined method for right join.

CROSS JOIN

Cross join returns cartesian product of both the tables that means multiplication of number records from both the tables -  each row on left table will be related to each row of right table

var q = (from cust in dataContext.Customers from product in dataContext.Products select new {
    cust.CustomerId,
        cust.Name,
        product.ProductId,
        product.ProductName,
}).ToList();

NOTE

1. In Cross Join, there is no need to use Join clause and where clause.

GROUP JOIN

A group join produces a sequence of object arrays based on properties equivalence of left collection and right collection.

var q = (from cust in dataContext.Customers join product in dataContext.Products on cust.CustomerId equals product.CustomerId into rd orderby cust.CustomerId select new {
    cust.CustomerId,
        cust.Name,
        Product = rd,
}).ToList();

NOTE

1. If right collection has no matching elements with left collection, then an empty array will be produced.
2. We can also use the result of a GROUP JOIN as a subquery.

I hope you will enjoy the valuable code. Happy Coding !!!