Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

LINQ Cross Join

In SQL server a cross joins that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

With LINQ to achieve cross join, no need to use join keyword, using two from clause we can achieve cross join behavior.

Example:

var CrossJoinProducts =        (from p in DbContext.Product
                                from c in DbContext.Category
                                select new
                                {
                                      ProductID = p.ProductID,
                                      ProductName =p.ProductName,
                                      Description =p.Description,
                                      ImagePath = p.ImagePath,
                                      UnitPrice = p.UnitPrice,
                                      CategoryID = (p.CategoryID == null)? 0:p.CategoryID,
                                      CategoryName=c.CategoryName
                                }
 
                     ).ToList();

 

Above example returns product of the tables that are involved in the join (product and categories).

LINQ RIGHT JOIN/ RIGHT OUTER JOIN

In SQL RIGHT JOIN keyword returns all rows from the right table with the matching rows in the left table. The result is NULL in the left side when there is no match.

 

A right outer join is not possible with LINQ. LINQ only supports left outer joins. If we swap the tables and do a left outer join then we can get the behavior of a right outer join.
 
In LINQ RIGHT JOIN keyword is not available you can achieve right join using "INTO" keyword and "DefaultIfEmpty()" method.


Example:

using (ApplicationServices DbContext = new ApplicationServices())
            {
                var categories = (from c in DbContext.Category join p in DbContext.Product
                                on new { PID = c.CategoryID } equals new { PID = p.CategoryID }
                                into ProductCatInfo
                                  from productsCatData in ProductCatInfo.DefaultIfEmpty()
                                select new
                                {
                                       CategoryID= productsCatData.CategoryID,
                                       CategoryName = (c.CategoryName == null) ? null : c.CategoryName,
                                       Description = (c.Description == null) ? null : productsCatData.Description,
                                       CreatedDate = (c.CreatedDate == null) ? null : c.CreatedDate,
                                      UpdatedDate = (c.UpdatedDate == null)? null:c.UpdatedDate
                                                                        
                                }
 
                     ).ToList();
 

LINQ Left outer join

In SQL LEFT JOIN keyword returns all rows from the left table (Products), with the matching rows in the right table .The result is NULL in the right side when there is no match.

In LINQ to achieve LEFT JOIN on tables/collections data, your require to use "INTO" keyword and "DefaultIfEmpty()" method.

 

 

 


Example:

using (ApplicationServices DbContext = new ApplicationServices())
            {
                var products = (from p in DbContext.Product
                                join c in DbContext.Category
                                on new { PID = p.CategoryID } equals new { PID = c.CategoryID }
                                into ProductInfo
                                from  productsData in ProductInfo.DefaultIfEmpty()
                                select new
                                {
                                      ProductID = p.ProductID,
                                      ProductName = (p.ProductName == null)? null:p.ProductName,
                                      Description =  (p.Description == null)? null:p.Description,
                                      ImagePath = (p.ImagePath == null)? null:p.ImagePath,
                                      UnitPrice = (p.UnitPrice == null)? 0:p.UnitPrice,
                                      CategoryID = (p.CategoryID == null)? 0:p.CategoryID
                                   
                                }
 
                     ).ToList();