Silverlight 4 with WCF RIA Services – Joining Tables

.

It should be easier to join tables…

A simple Google search will show that it isn’t overly intuitive to join tables using the Entity Framework with Linq to SQL.  I’m not sure yet if I’m a fan of LINQ, have found myself making database views to get the data ready for presentation in some cases.

So far LINQ and data access in real-world situations (many-to-many relationship) has been the biggest challenge for me coming from PowerBuilder.

Using the Include Method

Joining tables in a Silverlight 4 project using WCF RIA can be handled with the Include method.  This is is applicable to Entity SQL and LINQ queries.  The Include method takes a string argument which identifies the entity or entities that should be included in the result set.

It is important to have the associations set up in the Entity Framework model.

Option 1:  Multiple entities in the Include method

This is a pretty clear example of joining three tables, however it joins across the (associative) or many-to-many table which can cause performance issues.

// orders and items for a specific contact.

Contact contact =
    context.Contacts.Include("SalesOrderHeaders.SalesOrderDetails")
    .FirstOrDefault();

// Execute query & display order info for first contact
foreach (SalesOrderHeader order in contact
    .SalesOrderHeaders)
{
    Console.WriteLine(String.Format("PO Number: {0}",
        order.PurchaseOrderNumber));
    Console.WriteLine(String.Format("Order Date: {0}",
        order.OrderDate.ToString()));
    Console.WriteLine("Order items:");
    foreach (SalesOrderDetail item in order.SalesOrderDetails)
    {
        Console.WriteLine(String.Format("Product: {0} "
            + "Quantity: {1}", item.ProductID.ToString(),
            item.OrderQty.ToString()));
    }
}

A second solution, using multiple Include Methods

You can use multiple Include methods this example selects the inner for better performance.

// billing and shipping addresses for each order.
ObjectQuery<SalesOrderHeader> query =
    context.SalesOrderHeaders.Include("SalesOrderDetails").Include("Address");
If you know of better ways to handle, or have comments please leave them.

Regards,
Rich (DisplacedGuy)