Like sql query, we also can write join in linq, which helps creating customised data view fetching from different data objects, just like we create view in sql database, but in linq, the way we write query syntax is slightly different than sql query.
Here you learn all type of joins in LINQ query, how to join two collection object and read them into a table format.
Different type of joins in LINQ: Using join in LINQ is very useful and powerful way to work efficiently on different situation; you don’t have to depend on querying on different data sources.
this is an example of how to join two tables in linq and select columns from different tables, also using order by clause.
var q = (from order in GetOrderList() join cust in GetCustomerList() on order.CustomerId equals cust.CustomerId orderby cust.CustomerId select new { order.OrderId, order.Price, order.Quantity, order.OrderDate, cust.CustomerId, cust.CustomerName, }).ToList();
LINQ INNER JOIN example : Below is an example of joining multiple tables in linq, notice we have joined based on two columns, customer id and product id, after joining the result set is stored into variable which is anonymous type.
var OrderList = GetOrderList(); var CustomerList = GetCustomerList(); var ProductList = GetProductList(); var q1 = (from order in OrderList join cust in CustomerList on order.CustomerId equals cust.CustomerId join p in ProductList on order.ItemId equals p.ProductId orderby cust.CustomerId select new { order.OrderId, order.Price, order.Quantity, order.OrderDate, cust.CustomerId, cust.CustomerName, p.ProductId, p.ProductName }).ToList();
After joining multiple tables we are storing the result set into a Anonymous type variable.
Now we have to read values from anonymous type, this is how you can extract values from that variable.
foreach (var item in q1) { Console.WriteLine(string.Format("{0} - {1} - {2}", item.ProductName, item.Price, item.CustomerName)); }
You also can make join on multiple conditions, suppose you want data to be matched between more than one column, in that case you can write join on multiple columns and conditions.
on new { CutomerId = order.CustomerId // column 2 } equals new { CutomerId = cust.CustomerId // condition 2 }
Now in this example I have two entities with name Student and Registration, where Registration.StuId = Student.StuId
LEFT JOIN will return all the rows from left table and only matched records from right table. If there are no matching columns in the right table, it returns NULL values
var q = (from s in context.Students join r in context.Registration on s.StuId equals r.StuId into t from rt in t.DefaultIfEmpty() orderby s.StuId select new { StudentId = rt.StuId, s.Firstname, s.Lastname, s.ContactNumber, rt.RegDate, rt.CourseId, }).ToList();
This is an example of cross join, there is no condition, each row on left table will relate to each row of right table.
var q = from c in context.Students from r in context.Registration select new { c.StuId, c.Firstname, c.Lastname, c.ContactNumber, r.RegDate, r.CourseId };
join clause use an INTO expression, If right table has no matching rows with left table then an empty array will be returned.
var q = (from s in context.Students join od in context.Registration on s.StuId equals od.StuId into t orderby s.StuId select new { s.StuId, s.Firstname, s.ContactNumber, Reg = t }).ToList();
You may be interested in following posts