LINQ Join Example C#

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.

LINQ INNER JOIN example

Joining two tables or lists

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();
Joining Multiple Tables or Lists

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.

Read from anonymous type

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));
}
Linq join on multiple columns and multiple conditions

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                            
    }
LINQ LEFT OUTER JOIN

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();
LINQ CROSS JOIN

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
                        };
LINQ Group Join

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

 
LINQ Joins Example
LINQ (language integrated query) allow you to write query on database objects like ado.net, entity framework etc, LINQ is type safe, easy to convert database object to list objects and business objects in secure and scalable way.
linq Interview Questions Answers
LINQ C# examples | Join Asp.net MVC Course