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.


Introduction


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.

Prepare for Developing LINQ to SharePoint


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

Entity Classes


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");
}
}
}

Join the Lists – Web Part

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>"));




Summary


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.

Download


EntityClasses.cs

OrderDetails.cs web part

SPMetal batch file

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

By Alon Havivi   Popularity  (17389 Views)
Picture
Biography - Alon Havivi

Alon Havivi is a Microsoft Certified Technology Specialist, working as SharePoint Consultant / Developer at e-office. Specialized in SharePoint 2007/2010 and SharePoint Online (Office 365). With more than 10 years of experience in analysis, design and development complex Internet and Intranet portals using the latest Microsoft tools and practices, such as C# .NET 4.0, Silverlight and Windows Azure platform. Besides professional work, I write articles/blog and publish open source projects on CodePlex
View Alon Havivi's professional profile on LinkedIn. View Alon Havivi's projects on CodePlex. View Alon Havivi's articels on Eggheadcafe. Follow Alon Havivi on Twitter Connect with Alon Havivi via Facebook View Alon Havivi's Blog Subscribe to Alon Havivi RSS Feed