Join Operators

Cross Join

This sample shows how to perform a simple inner equijoin of two sequences to produce a flat result set that consists of each element in suppliers that has a matching element in customers.

Code:

            public void Linq102()
            {

                List customers = GetCustomerList();
                List suppliers = GetSupplierList();

                var custSupJoin = suppliers.Join(customers, sup => sup.Country, cust => cust.Country, (sup, cust) => new {
                                       Country = sup.Country, 
                                       SupplierName = sup.SupplierName, 
                                       CustomerName = cust.CompanyName});

                foreach (var item in custSupJoin)
                {
                    Log.WriteLine("Country = {0}, Supplier = {1}, Customer = {2}", item.Country, item.SupplierName, item.CustomerName);
                }
            }

Result:

Group Join

A group join produces a hierarchical sequence. The following 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.

Code:

            public void Linq103()
            {


                List customers = GetCustomerList();
                List suppliers = GetSupplierList();

                var custSupQuery = suppliers.GroupJoin(customers, sup => sup.Country, cust => cust.Country, (sup, cs) => new {Key = sup.Country, Items = cs});
                
                foreach (var item in custSupQuery)
                {
                    Log.WriteLine(item.Key + ":");
                    foreach (var element in item.Items)
                    {
                        Log.WriteLine("   " + element.CompanyName);
                    }
                }
            }

Result:

Cross Join with Group Join

The group join operator is more general than join, as this slightly more verbose version of the cross join sample shows.

Code:

            public void Linq104()
            {
                string[] categories = new string[]{ 
                "Beverages", 
                "Condiments", 
                "Vegetables", 
                "Dairy Products", 
                "Seafood" };

                List products = GetProductList();

                var prodByCategory =
                    categories.GroupJoin(products, cat => cat, prod => prod.Category, (cat, ps) => new {cat, ps})
                              .SelectMany(@t => @t.ps, (@t, p) => new {Category = @t.cat, p.ProductName});

                foreach (var item in prodByCategory)
                {
                    Log.WriteLine(item.ProductName + ": " + item.Category);
                }
            }

Result:

Left Outer Join

A left outer join produces a result set that includes all the left hand side elements at least once, even if they don't match any right hand side elements.

Code:

            public void Linq105()
            {
                List customers = GetCustomerList();
                List suppliers = GetSupplierList();

                var supplierCusts =
                    suppliers.GroupJoin(customers, sup => sup.Country, cust => cust.Country, (sup, cs) => new {sup, cs})
                             .SelectMany(@t => @t.cs.DefaultIfEmpty(),(@t, c) => new {@t, c})
                             .OrderBy(@t => @t.@t.sup.SupplierName)
                             .Select(@t => new{
                                 Country = @t.@t.sup.Country,
                                 CompanyName = @t.c == null? "(No customers)": @t.c.CompanyName,
                                 SupplierName = @t.@t.sup.SupplierName});

                foreach (var item in supplierCusts)
                {
                    Log.WriteLine("{0} ({1}): {2}", item.SupplierName, item.Country, item.CompanyName);
                }
            }

Result:

For each customer in the table of customers, this query returns all the suppliers from that same country, or else a string indicating that no suppliers from that country were found.

Code:

            public void Linq106()
            {

                List customers = GetCustomerList();
                List suppliers = GetSupplierList();

                var custSuppliers =
                    customers.GroupJoin(suppliers, cust => cust.Country, sup => sup.Country, (cust, ss) => new {cust, ss})
                             .SelectMany(@t => @t.ss.DefaultIfEmpty(),(@t, s) => new {@t, s})
                             .OrderBy(@t => @t.@t.cust.CompanyName)
                             .Select(@t => new{
                                Country =@t.@t.cust.Country, 
                                CompanyName = @t.@t.cust.CompanyName,
                                SupplierName = @t.s == null? "(No suppliers)": @t.s.SupplierName});

                foreach (var item in custSuppliers)
                {
                    Log.WriteLine("{0} ({1}): {2}", item.CompanyName, item.Country, item.SupplierName);
                }
            }

Result:

For each supplier in the table of suppliers, this query returns all the customers from the same city and country, or else a string indicating that no customers from that city/country were found. Note the use of anonymous types to encapsulate the multiple key values.

Code:

            public void Linq107()
            {
                List customers = GetCustomerList();
                List suppliers = GetSupplierList();

                var supplierCusts =
                    suppliers.GroupJoin(customers, sup => new {sup.City, sup.Country}, cust => new {cust.City, cust.Country}, (sup, cs) => new {sup, cs})
                             .SelectMany(@t => @t.cs.DefaultIfEmpty(), (@t, c) => new {@t, c})
                             .OrderBy   (@t => @t.@t.sup.SupplierName)
                                .Select(@t => new {
                                    Country = @t.@t.sup.Country,
                                    City = @t.@t.sup.City,
                                    SupplierName = @t.@t.sup.SupplierName,
                                    CompanyName = @t.c == null ? "(No customers)" : @t.c.CompanyName});

                foreach (var item in supplierCusts)
                {
                    Log.WriteLine("{0} ({1}, {2}): {3}", item.SupplierName, item.City, item.Country, item.CompanyName);
                }
            }

Result: