Working with Joins in LINQ

While working with data most of the time we have to work with relation between different lists of data. Many a times we want to fetch data from both the list at once. This requires us to make different kind of joins between the lists of data.

LINQ support different kinds of join

Inner Join

    List<Customer> customers = GetCustomerList();

    List<Supplier> suppliers = GetSupplierList();

    var custSupJoin =

        from sup in suppliers

        join cust in customers on sup.Country equals cust.Country

        select new { Country = sup.Country, SupplierName = sup.SupplierName, CustomerName = cust.CompanyName };

Group Join – where By the joined dataset is also grouped.

    List<Customer> customers = GetCustomerList();

    List<Supplier> suppliers = GetSupplierList();

    var custSupQuery =

        from sup in suppliers

        join cust in customers on sup.Country equals cust.Country into cs

        select new { Key = sup.Country, Items = cs };

We can also work with the Left outer join in LINQ like this.

    List<Customer> customers = GetCustomerList();

    List<Supplier> suppliers = GetSupplierList();

    var supplierCusts =

        from sup in suppliers

        join cust in customers on sup.Country equals cust.Country into cs

        from c in cs.DefaultIfEmpty()  // DefaultIfEmpty preserves left-hand elements that have no matches on the right side

        orderby sup.SupplierName

        select new { Country = sup.Country, CompanyName = c == null ? "(No customers)" : c.CompanyName,

                     SupplierName = sup.SupplierName};