Linq joins:
In this article I will explain in
details with example all possible linq joins.
We can achieve sql joins by calling
linq provided methods with proper parameters.
Before we continue with examples, we
will list the types of the different JOINs you can use:
1. INNER
JOIN
2. LEFT
JOIN
3. RIGHT
JOIN
4. FULL
JOIN
5. CROSS
JOIN
6. FULL
JOIN
We can achieve more joins like group
join, linq non equi join,linq equi join for more details
http://codechef4u.com/post/2016/01/14/linq-non-equi-join
http://codechef4u.com/post/2015/05/30/LINQ-Group-joins
linq join with multiple conditions:
Linq inner join:
In SQL server inner join
returns common or matching records/rows from both the database tables (left
table and right table).
Using the "join"
keyword we can do an inner join using a LINQ query, join returns common or
matching records/rows from both tables/collections.
Example:
/// <summary>
/// Method
returns only matching or common records from product and categories
table, for inner join used linq query
/// </summary>
public List<Product>
GetProductCatInnerJoin()
{ {
List<Product>
ProductCategories = new List<Product>();
using (ApplicationServices DbContext
= new ApplicationServices())
{ {
//inner
join linq query returns only matching records
var InnerJoinProducts
= (from p in DbContext.Product
join c in DbContext.Category
on new {
PID = p.CategoryID } equals new { PID = c.CategoryID }
select p
).ToList();
).ToList();
foreach (var p in InnerJoinProducts)
{ {
Product product
= new Product();
product.ProductID = p.ProductID;
product.ProductName = p.ProductName;
product.Description = p.Description;
product.ImagePath = p.ImagePath;
product.UnitPrice = p.UnitPrice;
product.CategoryID = p.CategoryID;
ProductCategories.Add(product);
} }
product.ProductID = p.ProductID;
product.ProductName = p.ProductName;
product.Description = p.Description;
product.ImagePath = p.ImagePath;
product.UnitPrice = p.UnitPrice;
product.CategoryID = p.CategoryID;
ProductCategories.Add(product);
} }
return ProductCategories;
}} }}
Inner join with multiple tables Example
http://codechef4u.com/post/2015/05/24/LINQ-inner-join
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 ==
Description = (p.Description == null)? null:p.Description,
ImagePath = (p.ImagePath ==
ImagePath = (p.ImagePath == null)? null:p.ImagePath,
UnitPrice = (p.UnitPrice ==
UnitPrice = (p.UnitPrice == null)? 0:p.UnitPrice,
CategoryID = (p.CategoryID ==
CategoryID = (p.CategoryID == null)? 0:p.CategoryID
}
).ToList();
}
).ToList();
Linq 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 ==
Description = (c.Description == null) ? null :
productsCatData.Description,
CreatedDate = (c.CreatedDate ==
CreatedDate = (c.CreatedDate == null) ? null : c.CreatedDate,
UpdatedDate = (c.UpdatedDate ==
UpdatedDate = (c.UpdatedDate == null)? null:c.UpdatedDate
}
).ToList();
}
).ToList();
LINQ Full outer join:
In SQL Full outer join
returns all the rows from both tables whether it has been matched or not.
To achieve full outer join in
LINQ we require performing logical union of a left outer join and a right outer
join result. LINQ does not support full outer joins directly, the same as
right outer joins.
Example:
public List<Product> GetAllproductsUsingLinqFullOuter()
{
{
var productsAll = new List<Product>();
using (ApplicationServices DbContext = new ApplicationServices())
{
{
//left join
//all records from left
table only matching records from right
var productsLeft = (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??0,
ProductName
= p.ProductName ?? null,
Description = p.Description ??
Description = p.Description ?? null,
ImagePath = p.ImagePath ??
ImagePath = p.ImagePath ?? null,
UnitPrice = p.UnitPrice ?? 0,
CategoryID = productsData.CategoryID ?? 0,
CategoryName = productsData.CategoryName
UnitPrice = p.UnitPrice ?? 0,
CategoryID = productsData.CategoryID ?? 0,
CategoryName = productsData.CategoryName //from right table
}
).ToList();
//right join
//all data from right
table, only matching records from left table
var productsRight = (from c in DbContext.Category
join p in DbContext.Product
on new { PID = c.CategoryID
} equals new { PID =
p.CategoryID}
into ProductInfoRight
from productsRightData in ProductInfoRight.DefaultIfEmpty()
select new
{
ProductID = productsRightData.ProductID??0,
ProductName = productsRightData.ProductName ?? null,
Description = productsRightData.Description ??
Description = productsRightData.Description ?? null,
ImagePath = productsRightData.ImagePath ??
ImagePath = productsRightData.ImagePath ?? null,
UnitPrice = productsRightData.UnitPrice ?? 0,
CategoryID = c.CategoryID??0,
CategoryName = c.CategoryName
UnitPrice = productsRightData.UnitPrice ?? 0,
CategoryID = c.CategoryID??0,
CategoryName = c.CategoryName //from left table
}
).ToList();
//union
//union of left and right
join data
var allProducts =
productsLeft.Union(productsRight).ToList();
} }
return productsAll;
}
}
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();
CategoryName=c.CategoryName
}
).ToList();
Above example returns product of the tables
that are involved in the join (product and categories).
LINQ self join
In SQL self join is
basically when a table is joined to itself. The way you should visualize a self
join for a given table is by imagining that a join is performed between two
identical copies of that table.
Using the "join"
keyword we can do an self join using a LINQ query and table aliases, join
returns common or matching records/rows from two identical copies of that
table.Check following linq self join example .
Example(Linq Inner self
join ):
/// <summary>
///
/// Method returns user detail with managers from same table
using linq self join query
/// </summary>
public List<UserAndManagers> GetUserAndManagers()
{
{
List<UserAndManagers> UserManagers = new List<UserAndManagers>();
using (ApplicationServices DbContext = new ApplicationServices())
{
{
//linq self join
query,here two identical copies created u1 and u2 for table USER
var allUsers = (from u1 in DbContext.Users
join u2 in DbContext.Users
on new { PID = u1.ManagerId
} equals new { PID = u2.ID }
select new
{
EmplyeeId=u1.ID,
Emplyeename= u1.LoginName,
Manager=u2.LoginName
}
).ToList();
foreach (var u in allUsers)
{
{
UserAndManagers userDetail = new UserAndManagers();
userDetail.userId = u.EmplyeeId;
userDetail.UserName = u.Emplyeename;
userDetail.ManagerName = u.Manager;
UserManagers.Add(userDetail);
}
userDetail.userId = u.EmplyeeId;
userDetail.UserName = u.Emplyeename;
userDetail.ManagerName = u.Manager;
UserManagers.Add(userDetail);
}
return UserManagers;
}
}
}
}