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)