Friday, May 14, 2010

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<SPListItem>()
                where p["ProductName"].ToString().Contains("monitor")
                orderby Convert.ToInt32(p["AvaialableQuantity"])
                select p;
 

An Exception you may get:

You may find the following exception when you try to run an application in SharePoint server. At first glance you may think the site is not accessible or you have misspelled the site name. But the real fact is that if you try to run your application in 32 bit mode then you may get the exception.

The Web application at http://localhost could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application.

Handy tools for Linq to SharePoint

SharePoint 2010 is not out there for too long. But there are much activities to help developers to easy development/deployment. I have found few codeplex projects aimed at easing development,deployment and more. I have found few Visual Studio extensions for SharePoint in MSDN Visual Studio gallery. The major problem we face in SharePoint development is that we need to deploy our code to test in development environment. VS 2010 has added nice extension already for developer to ease and fast development/deployment. But few VS extensions developed by others may add extra value to SharePoint developer’s life. Here are few useful links SharePoint developers may find handy.

Linq to SharePoint DSL Extension

CKS For SharePoint Server

SharePoint Developer Tool

Linq to SharePoint limitations

With Linq to SharePoint you can’t run all kinds of queries. There some kind of queries which is inefficient. For example if you join two lists in such a way that for each item in first list, you need to go to database for finding matching items in second list then the query is inefficient and not supported in Linq to SharePoint. You can find more details on this on the following link:

http://msdn.microsoft.com/en-us/library/ee536585%28office.14%29.aspx

4 comments:

  1. Nice informative blog.
    SharePoint Development helps the developers to develop, configure, and customize Internet, Extranet, and Internet portal based on Microsoft SharePoint.

    william

    ReplyDelete
  2. Great article but I have a question on InsertOnSubmit.

    What if you have a Lookup field on Product which is for example ProductType that looksup on ProductType List how would I do this now?

    var item = new ProductItem();
    item.AvaialableQuantity = 100;
    item.LaunchDate = DateTime.Now;
    item.ProductDescription = "this is computer monitor";
    item.ProductName = "Monitor";
    item.ProductType = ?????????????
    context.Product.InsertOnSubmit(item);
    context.SubmitChanges();

    as ProductType will be or ProductTypeItem type.

    ReplyDelete
  3. @Raymund, use the following code block:

    FieldLookupValue prodcutType=new FieldLookupValue();
    prodcutType.Id=10;//say your product type id is 10.


    Here the FieldLookupValue is the key.

    ReplyDelete
  4. Not sure if Sohel's solution works, but that is not the most appropriate way.

    Query the datacontext for the ProductType, then you can set item.ProductType to the retrieved ProductType-object.

    Examplecode (in conjunction with your own example):
    ProductType ptScreen = context.ProductType.Where(pt => pt.Title == "Screen").FirstOrDefault();

    item.ProductType = ptScreen;

    The reason I'm first using .Where, then .FirstOrDefault, is in LINQ to SP's limitations. Using FirstOrDefault directly with the lambda in it will not be processed serverside; it will immediately retrieve the whole list's content to memory, then query it in there -> slow performance on large(r) lists! Also see the MSDN-link on it's limitations in the blogpost for more info.

    ReplyDelete

Note: Only a member of this blog may post a comment.