Inner Join Example in LINQ

Let see an example of using the Join method in LINQ and C#. The Join method performs an inner equijoin on two sequences, correlating the elements of these sequences based on matching keys. It is called equijoin, since we are testing for equality using the equals operator.

If you are familiar with relational databases, then in an inner join, each element of the first collection appears one time for every matching element in the second collection. If an element in the first collection has no matching elements, it does not appear in the join result set. The Join method in LINQ does the same.

We will using two classes Book and Order and use the Join operator on them and see the results. Here’s some sample data:

class Program{
    static void Main(string[] args)
    {
        List<Book> bookList = new List<Book>
        {
            new Book{BookID=1, BookNm="DevCurry.com Developer Tips"},
            new Book{BookID=2, BookNm=".NET and COM for Newbies"},
            new Book{BookID=3, BookNm="51 jQuery ASP.NET Recipes"},
            new Book{BookID=4, BookNm="Motivational Gurus"},
            new Book{BookID=5, BookNm="Spiritual Gurus"}
        };

        List<Order> bookOrders = new List<Order>{
            new Order{OrderID=1, BookID=1, PaymentMode="Cheque"},
            new Order{OrderID=2, BookID=5, PaymentMode="Credit"},
            new Order{OrderID=3, BookID=1, PaymentMode="Cash"},
            new Order{OrderID=4, BookID=3, PaymentMode="Cheque"},
            new Order{OrderID=5, BookID=3, PaymentMode="Cheque"},
            new Order{OrderID=6, BookID=4, PaymentMode="Cash"}
        };
    }
}

public class Book{
    public int BookID { get; set; }
    public string BookNm { get; set; }
}

public class Order{
    public int OrderID { get; set; }
    public int BookID { get; set; }
    public string PaymentMode { get; set; }
}

Let us apply a Join between Book and Order collection

var orderForBooks = from bk in bookList
            join ordr in bookOrders
            on bk.BookID equals ordr.BookID
            select new
            {
                bk.BookID,
                Name = bk.BookNm,
                ordr.PaymentMode
            };

foreach (var item in orderForBooks)
    Console.WriteLine(item);

Console.ReadLine();

In the code shown above, the query uses the join clause to match Book objects with Order objects testing it for equality using the equals operator.The select clause defines how the result will appear using anonymous types that consist of the BookID, Book Name and Order Payment Mode.

LINQ has a JOIN query operator that provides SQL JOIN like behavior and syntax. As you know, Inner join returns only those records or rows that match or exists in both the tables. The simple inner join example is given below:

DataContext context = new DataContext();
var q = (from pd in context.Products
 join od in context.Orders on pd.ProductID equals od.ProductID
 orderby od.OrderID
 select new
 {
 od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList();

Inner Join with AND condition

Sometimes, you need to apply inner join with and condition. To write query for inner join with and condition you need to make two anonymous types (one for left table and one for right table) by using new keyword and compare both the anonymous types as shown below:

DataContext context = new DataContext();
var q=from cust in context.tblCustomer
 join ord in context.tblOrder
// Both anonymous types should have exact same number of properties having same name and datatype
 on new {a=(int?)cust.CustID, cust.ContactNo} equals new {a=ord.CustomerID, ord.ContactNo}
 select new 
 {
 cust.Name,
 cust.Address,
 ord.OrderID,
 ord.Quantity
 };

// Generated SQL
SELECT [t0].[Name], [t0].[Address], [t1].[OrderID], [t1].[Quantity]
FROM [tblCustomer] AS [t0]
INNER JOIN [tblOrder] AS [t1] ON (([t0].[CustID]) = [t1].[CustomerID]) AND ([t0].[ContactNo] = [t1].[ContactNo])