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>

WWF Workflow Activities

In this sample, we are going to develop a workflow with a custom code activity, this activity will read an xml file and return a list of Customers to be updated / inserted in our database

The first step is add a Sequence Activity in the designer. This activity enables you to construct a list of other activities, and when executed it will start with the first child activity and execute each child in turn

We have defined a class named Customer, with attributes corresponding to data, and three methods: one to check if exists in the database, one to update, and one to insert

Now we add a Code Activity to read our xml, and return a List of Customers. We call it ReadCustomers.cs

We also add a Code Activity named UpdateCustomer, and another one named InsertCustomer

The code for ReadCustomers activity is as follows

     
public class ReadCustomers : CodeActivity<List<Customer>>
{
    protected override List<Customer> Execute(CodeActivityContext context)
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.Load(@"c:\customers.xml");

        XmlNodeList customers = xDoc.GetElementsByTagName("Customer");

        List<Customer> listCustomers = new List<Customer>();
        foreach (XmlElement customer in customers)
        {
            listCustomers.Add(new Customer()
            {
                CustomerId = new Guid(customer.GetElementsByTagName("Id")[0].InnerText),
                FirstName = customer.GetElementsByTagName("Firstname")[0].InnerText,
                LastName = customer.GetElementsByTagName("Lastname")[0].InnerText,
                Telephone = customer.GetElementsByTagName("Telephone")[0].InnerText,
                Email = customer.GetElementsByTagName("Email")[0].InnerText
            });
        }

        return listCustomers;
    }
}

The next is the code for update

     
public sealed class UpdateCustomer : CodeActivity
{
    public InArgument<Customer> Customer { get; set; }

    protected override void Execute(CodeActivityContext context)
    {
        Customer customer = context.GetValue(this.Customer);
        customer.Update();
        Console.WriteLine("Customer " + customer.FirstName + " has been updated");
    }
}

And for insert

     
public sealed class InsertCustomer : CodeActivity
{
    public InArgument<Customer> Customer { get; set; }

    protected override void Execute(CodeActivityContext context)
    {
        Customer customer = context.GetValue(this.Customer);
        customer.Insert();
        Console.WriteLine("Customer " + customer.FirstName + " has been inserted");
    }
}

Now we have defined our Code Activities, we will proceed to complete our workflow diagram

First we drag the ReadCustomers activity into our sequence

Now we have to declare a variable with Sequence scope to take the return value of the activity. We select List type, and as element for the List we select our custom Customer type

At this point, we have implemented our activity to retrieve the list of Customers from the xml, so now we add the next elements to the diagram

- Foreach activity
- If activity
- Our custom activities in each branch of the if activity

The Foreach activity will iterates each Customer in the List, and the If activity will determines the Code activity to execute

With a correct execution, this is the result

<METHOD SOFTWARE © 2012>