Join Lists with LINQ - SharePoint 2010

One common request for LINQ to SharePoint is support for joins across Lists. This article will show you how to use the Join() operator in efficient way. Shown here by an example that joins two lists, a Customer List and an Orders List, based on a relationship between certain columns.

Before we start it is important to know that LINQ to SharePoint is not (yet) mature enough like the LINQ to SQL. That means that some queries are unsupported queries or inefficient, specifically queries that involve more than one list. On MSDN, the Join() operator mark as inefficient query and it will be possible only with LookUp fields. To avoid inefficient LINQ queries in SharePoint we can use LINQ to SharePoint provider and LINQ to Objects provider. The combination of these two providers can help us to deal with inefficient LINQ queries type.

• SharePoint Lists - For this example I used two simple lists, joined with a regular CustomerID Text field Column.

Customer List:


Order List:

To keep it simple, the columns in this example are all Single line of text fields

• To use the LINQ to SharePoint Provider you need add this references Microsoft.SharePoint.Linq.dll and add the following using statement at the top of your class Microsoft.SharePoint.Linq using System.Linq;

• To use LINQ to Objects add this reference System.Core.dll and add the following using statement at the top of your class using System.Linq;

• To be able to run queries against SharePoint Lists you need to generate Entity Classes. The easiest way to do it is to use SPMetal tool. I used this batch file code:

set SPMETAL="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN\SPMETAL.EXE"

%SPMETAL% /web:http://sharepoint/ /namespace:SharePointLINQ.JoinLists /code: EntityClasses.cs pause

Finally copy the generated EntityClasses.csto your project

First, let’s take a look at the generated EntityClasses.cs class; you can see that SPMetal generate a DataContext Class provides access to the lists in your site.

public partial class EntityClassesDataContext : Microsoft.SharePoint.Linq.DataContext
{
#region Extensibility Method Definitions
partial void OnCreated();
#endregion
public EntityClassesDataContext(string requestUrl) :
base(requestUrl)
{
this.OnCreated();
}

[Microsoft.SharePoint.Linq.ListAttribute(Name = "Customer")]
public Microsoft.SharePoint.Linq.EntityList<CustomerItem> Customer
{
get
{
return this.GetList<CustomerItem>("Customer");
}
}

[Microsoft.SharePoint.Linq.ListAttribute(Name = "Order")]
public Microsoft.SharePoint.Linq.EntityList<OrderItem> Order
{
get
{
return this.GetList<OrderItem>("Order");
}
}
}

Now that our solution is ready for LINQ, let's add a web part to display the Lists join. You can use the SharePoint project templates to build standard Web Part. I called my Web Part an Order Details.

Step 1 - Creating a DataContext object

EntityClassesDataContext site = new EntityClassesDataContext("http://sharepoint");
site.ObjectTrackingEnabled = false;

In this example we only run Read Only queries (no Edit, Add or Delete). When we set ObjectTrackingEnabled to false we improve the performance.

Step 2 - Getting the Lists Reference

EntityList<CustomerItem> customersItems = site.GetList<CustomerItem>("Customer");
EntityList<OrderItem> ordersItems = site.GetList<OrderItem>("Order");

Step 3 – Use the LINQ to SharePoint Provider

With this query we retrieve all orders items from the Order List

var queryOrders = from order in ordersItems
select order;

Step 4 – Create Order Class

Like I wrote before, to run a Join operator in efficient way, we will use LINQ to Object. We create a new Class to represent one of our Lists, in our case it will be the Order List.

public class Order
{
public string OrderID { get; set; }
public string CustomerID { get; set; }
public string OrderDate { get; set; }
public string ShipCity { get; set; }
public string ShipCountry { get; set; }
public Order(string orderID, string customerID, string orderDate, string shipCity, string shipCountry)
{
OrderID = orderID;
CustomerID = customerID;
OrderDate = orderDate;
ShipCity = shipCity;
ShipCountry = shipCountry;
}
}

Step 5 – Convert LINQ to SharePoint in LINQ to Objects

Now, we build the Order list collection using the class defined previously. Then we enumerate the queryOrders result and add each order to the order list collection

var orders = new List<Order>();
foreach (var order in queryOrders)
{
orders.Add(new Order(order.OrderDate,
order.CustomerID,
order.OrderDate,
order.ShipCity,
order.ShipCountry));
}

Step 6 – Run the JOIN query

var orderDetails = from order in orders
join customer in customersItems on order.CustomerID equals customer.CustomerID
select new
{
customer.ContactName,
customer.CompanyName,
order.OrderDate,
order.ShipCity,
order.ShipCountry
};

As you can see in this, we can run our query against customersItems (entity list) and orders (Generic List Collection). In this why we can use the JOIN operator in more efficient way and we can join lists with any types of column (not only LookUp columns).

Step 7 – Display the Join Result

Render a simple HTML table:

this.Controls.Add(new LiteralControl("<Table border=1>"));
this.Controls.Add(new LiteralControl("<tr><td><b>Customer</b></td>" +
"<td><b>Company Name</b></td>" +
"<td><b>Order Date</b></td>" +
"<td><b>Ship City</b></td>" +
"<td><b>Ship Country</b></td>" +
"</tr>"));
foreach (var order in orderDetails)
{
this.Controls.Add(new LiteralControl("<tr><td>" + order.ContactName + "</td>" +
"<td>" + order.CompanyName + "</td>" +
"<td>" + order.OrderDate + "</td>" +
"<td>" + order.ShipCity + "</td>" +
"<td>" + order.ShipCountry + "</td>" +
"</tr>"));
}
this.Controls.Add(new LiteralControl("</Table>"));

When we need to use inefficient operators like JOIN() it is better to convert our List Entity Class to a Generic Collection, that means that we can use LINQ to Objects and query objects in a collection.

EntityClasses.cs

OrderDetails.cs web part

SPMetal batch file

Download the complete code. (Visual Studio 2010 beta 2)