January 6, 2014

Joins in C# Linq

There are four types of sql joins which are using in LINQ query for querying the data. In this post, I'm going to show you the working JOIN operator in LINQ which behaves as SQL JOIN. Normally we have four types of Joins.

==============
1. INNER JOIN
==============

Returns only those records which are exists in both tables.

var innerJoin = (from product in _dataContext.Products
                join order in _dataContext.Orders 
                     on product.ProductId equals order.ProductId
                order by order.OrderDate
                select new
                {
                   order.OrderId,
                   product.ProductId,
                   product.ProductName,
                   product.UnitPrice,
                   order.Quantity,
                   order.Price
                }).ToList();

===================
2. LEFT OUTER JOIN
===================

Returns all records from left table and only matches records from right table.

var leftJoin = (from product in _dataContext.Products
                join order in _dataContext.Orders 
                     on product.ProductId equals order.ProductId
                     into query from joinedTable in query.DefaultIfEmpty()
                order by product.ProductName
                select new
                {
                   OrderId = (int?)joinedTable.OrderId,
                   product.ProductId,
                   product.ProductName,
                   product.UnitPrice,
                   Quantity = (int?)joinedTable.Quantity,
                   Price = (decimal?)joinedTable.Price
                }).ToList();

==============
3. CROSS JOIN
==============

Returns the records that are the multiplication of rows from table A to table B.

var crossJoin = from product in _dataContext.Products
                from order in _dataContext.Orders
                select new
                {
                   order.OrderId,
                   product.ProductId,
                   product.ProductName,
                   order.OrderDate
                };

==============
4. GROUP JOIN
==============

It groupes the collection of objects into seprate collections and then matches the collection with each other. Returns an empty array if right collection doesn't match any element with left collection.

var groupJoin = (from product in _dataContext.Products
                join order in _dataContext.Orders 
                     on product.ProductId equals order.ProductId into query
                order by order.OrderDate
                select new
                {
                   Order = query,
                   product.ProductId,
                   product.ProductName,
                   product.UnitPrice,
                   order.Quantity,
                   order.Price
                }).ToList();

Hope you understand now the different SQL JOINs and how to use them in your programming areas.