Having fun with the new LINQ to SharePoint on SharePoint 2010 (SP2010)

Introduction

This will be my first post on the new LINQ to SharePoint feature available in SharePoint 2010. I’ve decided to give it a try. I thought that it would be so much easier for me to retrieve SharePoint information using LINQ than creating CAML code since I’m very familiar with LINQ and used it a lot of times, especially LINQ to SQL and LINQ to Objects. But this previous knowledge on LINQ also played some tricks on me since that usually we tend to take a lot of things for granted when dealing with new technologies. So, here’s how I’ve started.

First I’ve watched one of the SharePoint 2010 Developer videos on Channel 9 related to LINQ to SharePoint. This was a good overview about the technology with a good demo. Then I’ve followed the walk-through on the SharePoint 2010 book that was distributed at the SharePoint Conference 2009. The walk-through is the one on page 162 called “Using LINQ to SharePoint from within a Visual Web Part”. That also gave me a better insight and a nice idea on how to use it in a Visual WebPart.

So, the first thing you need to do to use LINQ to SharePoint is to generate a mapping layer (same idea as LINQ to SQL) where you can have your SharePoint list definitions generated as C# (or VB) .NET classes. That will give you strongly-typed classes of your SharePoint schema. Again, just keep in mind that if you do change your SharePoint list definition you will probably need to re-generate this mapping layer. In order to generate this, you have to use a tool called SPMetal

Step 1 – Using SPMetal to generate a mapping layer

The tool SPMetal is installed as part of the SharePoint 2010 installation and it’s located by default at “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN”. If you open a command prompt, go to that folder and type spmetal, it will show you a description of its valid parameters. There’s also a good article on MSDN about SPMetal.

Basically the main parameters are the site URL, the namespace, the filename and the type of file (.cs or .vb). Here’s an example:

c:\>SPMetal /web:http://localserver/team /namespace:MyCompany.Data /code:TeamSite.cs

This command will basically generate a CSharp file where I can import into your Visual Studio 2010 project.

Step 2 – Using a parameters file to override the SPMetal default rules

There is also an additional option in SPMetal called parameters where you can specify a XML file that allows you to override the default rules to generate the mapping layer. So let’s say you want to specify which lists you want in your mapping file. You can use for example a file called myParameters.xml (it can be any filename as long as you specify the name right on the command-line).

c:\>SPMetal /web:http://localserver/team /namespace:MyCompany.Data /code:TeamSite.cs /parameters:myParameters.xml

The first time I’ve tried to use a parameters file, I’ve got a bunch of errors trying to generate the file. Some of these errors were related to a copying and paste problem from the MSDN article to my file. So make sure you use a valid XML editor instead of Notepad because the double quotes can play a trick on you. By using a XML editor you can guarantee that there are no malformed XML in your parameters file.

Also, make sure you understand the right schema for the parameters file. In the beginning I’ve also had problems because I was putting the XML elements in my file in an invalid order. Also, make sure that you understand what elements are parents and children of each other. It took me some trial and errors until I could figure it out, but it’d have saved me sometime if I had found earlier the really nice MSDN article describing the whole parameters file schema (see below)

These  two articles are a must read to be able to come up with a nice parameters  XML file

Overriding SPMetal Defaults with a Parameters XML File

SPMetal Parameters XML Schema

So let’s say you have a list in SharePoint in your site called “Customer”. Let’s assume that you are not using any custom Content Type on that list (assuming in this case it’s a pretty vanilla custom list). If you want to generate a mapping layer that will have only the Customer list and you want spmetal to generate fields for some out-of-the-box SharePoint fields such as the Author and Created you can (see below). There’s also a parameter called <ExcludeOtherLists> that tells SPMetal to generate classes for only the lists that you’ve specified. That can be pretty handy in case you don’t want to have a huge mapping layer file. So here’s an example:

myParameters.xml

  <?xml version="1.0" encoding="utf-8"?>
  <Web AccessModifier="Internal" xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal">
    <List Name="Customer">
      <ContentType Name="Item" Class="Customer" >
        <Column Name="Author" Member="Author" />
        <Column Name="Created" Member="Created" />
        <Column Name="Description" Member="Description" />
      </ContentType>
    </List>
    <ExcludeOtherLists />
  </Web>

Once the file is generated you can go to your project in Visual Studio 2010 (I’m assuming that you’ve already created one of the SharePoint 2010 project templates such as the Visual WebPart one) and add the file to your project by righ-clicking on your project name and selecting Add | Existing Item. Browse to the folder where you generated the file, select it and click Add.

Step 3 – Adding LINQ to SharePoint references

Rigth-click on your project again and select Add Reference. The Add Reference window will show up.Just select the Microsoft.SharePoint.Linq.dll

Once the Microsoft.SharePoint.Linq.dll is referenced then you need to add the following using statements on your code (if you didn’t generate the mapping layer with the same namespace as the class namespace where you will be using it, then you have to add this namespace too).

using System.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Linq;
using MyCompany.Data;  /* My mapping layer namespace */

Now that you have your mapping layer and the required references in place it’s now time to have some fun. Let’s get our hand dirty with some Linq code.

Step 4 – Retrieving SharePoint information using Linq

If you open your mapping file (in my case TeamSite.cs) you will see that SPMetal created a DataContext class for you and it should look like this

internal partial class TeamSiteDataContext : Microsoft.SharePoint.Linq.DataContext {}

You can also see that for the list that you’ve specified (in my case Customers) there was also a member created for that in this class, something like

[Microsoft.SharePoint.Linq.ListAttribute(Name="Customers")]
public Microsoft.SharePoint.Linq.EntityList<Customer> Customers{}

Now the fun part. In order to retrieve Customers from SharePoint I need to instantiate a DataContext class, load the list and iterate the items. You can use something like this code below in your WebPart code-behind or Visual WebPart User Control (ascx) code-behind.

try{
 using(TeamSiteDataContext ctx = new TeamSiteDataContext(SPContext.Current.Web.Url))
 {
  ctx.ObjectTrackingEnabled = false; //this is good for performance if you are only retrieving information
  EntityList<Customer> Customers = ctx.GetList<Customer>("Customers");
  var query = from c in Customers.ToList()
              select c;
  foreach(var cust in query)
  {
    //Do something here. The objects are strongly typed so I could do something like cust.Name for example
  }
 }

This MSDN link has some additional details on how to use this and have a few more examples.

Gotchas and Limitations

While “playing” with LINQ to SharePoint I’ve tried to do “wild” things that were pretty common in LINQ to SQL such as joins. So when I mentioned in the beginning of this post that you should never take things for granted, believe me this is one of this cases. There are some limitations in what you can do with LINQ to SharePoint and some LINQ constructs are not even possible. There are some that are possible but are called “inefficient”. Make sure you read this article first before trying to do “wild” things: Efficient, Inefficient, and Unsupported LINQ Queries

I’ve tried to do some joins in my LINQ based on two SharePoint lists linked by a lookup field but I’ve got a strange error message saying something about that Inneficient Queries were not allowed and I should change a property on my DataContext. Reading the article above (on Step 4) it says that you should set a property called AllowInefficientQueries() to true but I couldn’t find this property in my DataContext class. I’ve searched the web and found some people saying that as of Beta2 this is not there anymore, so I’m assuming this was in a previous beta but the documentation was not updated accordingly (but this needs to be confirmed).

Conclusion

LINQ to SharePoint is very promising and it can speed up things a lot if you need to retrieve and update information in SharePoint. It’s a great alternative to creating custom CAML code and it might leverage some of your existing knowledge in LINQ especially if you already done a bunch of them. If you haven’t then a good LINQ book (but doens’t cover LINQ to SharePoint) is Pro Linq from Apress.

Hope that helps and have fun coding with LINQ to SharePoint in SharePoint 2010 !

Cheers,

Andy Nogueira.