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 equi join

ISQL Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

 Using the "join" keyword and equals keyword we can do an equi join using a LINQ query,   LINQ query returns commons or matching records on which column equality comparison is performed.

Points to remember:

1.  Matches on the equality of two keys. 
2.  Comparisons such as "greater than" or "not equals" are not supported. 
3.  Join clause uses the equals keyword instead of the == operator. 
4.  The equals keyword can only be used in a join clause and it differs from the == operator in one important way.
5.  With equals, the left key consumes the outer source sequence, and the right key consumes the inner source. 
6.  The outer source is only in scope on the left side of equals and the inner source sequence is only in scope on the right side.


//Equi join linq query returns only matching records
//,i.e records with matching category ID
                var EquiJoinProducts = (from p in DbContext.Product
                                         join c in DbContext.Category
                                         on new { PID = p.CategoryID } equals new { PID = c.CategoryID }
                                         select p


How to perform equi join on multiple columns:

  var result = from x in entity or table1 entity
                         join y in entity2 or table2 entity
                         on new { X1 = x.field1, X2 = x.field2 } equals new { X1 = y.field1, X2 = y.field2 }
                         select new
                             /// Columns

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.


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,


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


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.


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