LINQ Join Operations

In this post we make a review of linq join operators, to perform different kind of join on two sets of related data

All data in this example is from Northwind database, distributed as example database by Microsoft, you can download it here: Northwind database for SQL Server

First, we add a set of LINQ to SQL Classes, with the next result

And we add a method to retrieve our sets of data

     
        using (NorthwindDataContext nwContext = new NorthwindDataContext())
        {
            customerList = (from cust in nwContext.Customers
                            select cust).ToList<Customer>();

            orderList = (from ord in nwContext.Orders
                            select ord).ToList<Order>();

            productList = (from prod in nwContext.Products
                            select prod).ToList<Product>();

            supplierList = (from supp in nwContext.Suppliers
                            select supp).ToList<Supplier>();

            categoryList = (from cat in nwContext.Categories
                            select cat).ToList<Category>();
        }

The first join operation we perform is a simple inner join of equivalence that produces a result ser of Customers that has a matching element in Orders

     
        var customer_orders = from cust in customerList
                              join ord in orderList 
                              on cust.CustomerID equals ord.CustomerID
                              orderby cust.ContactName ascending
                              select new { cust.ContactName, ord.OrderID };

        foreach (var cust_ord in customer_orders)
            Console.WriteLine("Customer = {0}, Order Id = {1}", 
                cust_ord.ContactName, cust_ord.OrderID);
                

The result of this query is the next

The second query is an inner join that produces a sequence of objects, each of which has a key and an inner sequence of all matching elements, we get this result set through the keyword into

     
        var customer_orders = from cust in customerList
                              join ord in orderList on cust.CustomerID equals ord.CustomerID
                              into cs
                              orderby cust.ContactName ascending
                              select new { Key = cust.CustomerID, Items = cs };

        foreach (var cust in customer_orders)
        {
            Console.WriteLine("Customer ID = {0} :", cust.Key);
            foreach (var order in cust.Items)
                Console.WriteLine(" - Order: {0}, {1}", order.OrderDate, order.OrderID);
        }
        

You can also select elements from the set produced by the into clause

     
        var prodByCategory = from cat in categoryList
                             join prod in productList 
                             on cat.CategoryID equals prod.CategoryID 
                             into ps
                             from p in ps
                             select new { CategoryName = cat.CategoryName, p.ProductName };

        foreach (var item in prodByCategory)
            Console.WriteLine("Category = {0}. Product Name = {1}", 
                item.CategoryName, item.ProductName);
                

With the next query, we perform a left outer join to produce a result set that includes all the left hand side elements at least once, even if they don't match any right side element

DefaultIfEmpty preserves left-hand elements that have no matches on the right side

     
        var productSupps = from prod in productList
                           join sup in supplierList on prod.SupplierID equals sup.SupplierID 
                           into ps
                           from p in ps.DefaultIfEmpty()
                           orderby prod.ProductName
                           select new
                           {
                               PName = prod.ProductName,
                               SName = p == null ? "--- No suppliers ---" : p.CompanyName
                           };

        foreach (var product in productSupps)
            Console.WriteLine("Product mame = {0} . Supplier name = {1}", 
                product.PName, product.SName);
                

<METHOD SOFTWARE © 2012>