LINQ Left Outer Join Example Using C# in ASP.Net

In LINQ, the left outer join operation works similar to relational database that produces a result containing each item from the first data source sequence for each related item available in the second data source. If an element in the first sequence finds no related item in the second sequence then it still appears in the result set. You can use the LINQ join clause to perform the left outer join operation over two data source sequences in which you can use the "equals" keyword to bind and compare the relational keys of both the data sources. To include the elements of first sequence having no correlated elements in the second source you can call the DefaultIfEmpty method on the grouped join result set that finally performs the left outer join. The following ASP.Net with C# example shows the use of group join with DefaultIfEmpty LINQ method to perform the left outer join.

Example for LINQ Left Outer Join Example Using C# in ASP.Net

// first Data Source
List<Category> Categories = new List<Category>()
{
    new Category(1, "Beverages"),
    new Category(2, "Condiments"),
    new Category(3, "Dairy Products"),
    new Category(4, "Confections"),
    new Category(5, "Seafood")
};

// second Data Source
List<Product> Products = new List<Product>()
{
    new Product(1, "Chai", 1),
    new Product(2, "Chang", 1),
    new Product(3, "Aniseed Syrup", 2),
    new Product(4, "Chef Anton's Gumbo Mix ", 2),
    new Product(5, "Teatime Chocolate Biscuits", 4),
};

// LINQ join query to perform left outer join
var query = from category in Categories
            join product in Products on category.CategoryID equals product.CategoryID into productGroup
            from prdGroup in productGroup.DefaultIfEmpty()
            select new { category.CategoryName, ProductName = (prdGroup == null ? "<i>-No product found-</i>" : prdGroup.ProductName) };

Response.Write("<pre>");
// loop over the query to get the result sequence
foreach (var items in query)
{
    Response.Output.Write("Category: {0, -20} Product: {1}<br />", items.CategoryName, items.ProductName);
}
Response.Write("</pre>");

// Output:
// Category: Beverages            Product: Chai
// Category: Beverages            Product: Chang
// Category: Condiments           Product: Aniseed Syrup 
// Category: Condiments           Product: Chef Anton's Gumbo Mix 
// Category: Dairy Products       Product: -No product found-
// Category: Confections          Product: Teatime Chocolate Biscuits
// Category: Seafood              Product: -No product found-

The Category Class:

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }

    public Category(int categoryID, string categoryName)
    {
        this.CategoryID = categoryID;
        this.CategoryName = categoryName;
    }
}

The Product Class:

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int CategoryID { get; set; }

    public Product(int productID, string productName, int categoryID)
    {
        this.ProductID = productID;
        this.ProductName = productName;
        this.CategoryID = categoryID;
    }
}

In the above C# example LINQ query expression has been used to perform the left outer join that will return the names of all the categories whether they contain the products or not.

Continue to next tutorial: LINQ Join by Composite Keys Example Using C# in ASP.Net to learn how to perform a join operation on two data sources using multiple keys to form a relational key between them.