Linq to SharePoint

Linq to SharePoint

SharePoint 2010 has added new Linq extension called Linq-to-SharePoint similar like Linq-to-Sql. In Linq to Sql, when you Visual Studio generate classes based on your database schema, under the hood a tool called SqlMetal is used. Though VS generates classes for you, you can use SqlMetal outside of VS for your own purpose. Linq to SharePoint is new extension which allows to generate DataContext (having classes representing lists) based on your sharepoint lists using SPMetal and you can use the DataContext for manipulating SharePoint list in an object-oriented fashion. So you don’t need to worry about CAML or SharePoint Object Model. The steps to work with Linq-to-SharePoint is easy. Let’s dig it deeper.

1. Generate Linq-to-SharePoint DataContext class: To use Linq to SharePoint you need to use SPMetal to generate Linq-To-SharePoint class. You can find the SPMetal in the location “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN”. You can run the command from command prompt. For example the following command will generate Linq-to-SharePoint DataContext class for site ‘http://localhost’ and put the code in C:\MyClass.cs file with namespace MyNameSpace.

spmetal /web:http://localhost /code:c:\myclass.cs /namespace:mynamespace

One thing to point here is that there are Visual Studio extension availa to enable developers to generate DataContext from Visual Studio. One such tool is Linq to SharePoint DSL Extension.
2. Add DataContext class to your Visual Studio Project: The file you have generated at step 1 need to add in your Visual Studio Project. To do so you need to add reference to Microsoft.SharePoint.Linq.dll from Visual Studio’s Add Reference windows’ ‘.NET’ tab.
3. Code Using DataContext Class: Now you can use the DataContext class for manipulating SharePoint lists. For example in the following code I have used the DataContext class generated at step 1 to add a new product in the product list.

using (var context = new MyclassDataContext(http://mysite))
{
var item = new ProductItem();
item.AvaialableQuantity = 100;
item.LaunchDate = DateTime.Now;
item.ProductDescription = "this is computer monitor";
item.ProductName = "Monitor";
context.Product.InsertOnSubmit(item);
context.SubmitChanges();
}

4. Suggestion: If you use SPMetal to generate DataContext class then you’ll find that all classes are placed in a single file (in my case MyClass.cs file). This is very difficult to manage and modify. My suggestion will be to modify the file to move classes in individual pages. You can use some Refactoring tools like Resharper.

CAML and Linq side-by-side

You may think CAML is dead now as we can do all operations using Linq to SharePoint. Wait, here few point to notice. Firstly, when you use Linq to SharePoint, under the hood, CAML is used. the Linq to SharePoint convert you expression in CAML. However, you can still use CAML for retriving data from database, then you can use Linq to do operations on returned results. For example, you can use CAML to find products. Then you can run linq query against the result set to perform operations like orderby, group, join etc. As shown in the example below, I have used Linq to SharePoint to query Product list to find items whose name contains monitor and also ordered by available quantity.

using (var context = new MyclassDataContext("http://mysite"))
{
var proudcts = from p in context.Product
where p.ProductName.Contains("monitor")
orderby p.AvaialableQuantity
select p;
}

In the following example, I have used CAML query to perform the same operation I have performed above. In the above example I have used Linq to SharePoint extension fully. But in the following example I have used CAML query first to filter data from database. Then I have run Linq query to order data. The following example doesn’t use Linq to SharePoint. Rather it uses the conventional CAML and C# Linq.

SPList productList = SPContext.Current.Web.Lists["Product"];
SPQuery camlQuery=new SPQuery();
camlQuery.Query = "your CAMl query";

var products = productList.GetItems(camlQuery);
var orderProducts = from p in products.Cast()
where p["ProductName"].ToString().Contains("monitor")
orderby Convert.ToInt32(p["AvaialableQuantity"])
select p;