Saturday, June 18, 2011

LINQ with SharePoint 2010

Querying SharePoint

When we consider accessing data from SharePoint, there are many data technologies that we can use
according to the given context. Those technologies are Server Object Model, Client Object Model, LINQ,
REST & Web Services API.

If we are using SharePoint 2007, our data querying capability is limited to CAML(Collaborative Application
Markup Language) queries. CAML queries does to work by using SPQuery object.

There are several limitations, we have to face if we stick in to CAML queries.

1] There are no strongly typed objects
2] There is no syntax checking in CAML API (We can see if the query is correct at runtime only)
3] We have to use 3rd party tool like CAML Query Builder
4] It has no IntelliSense support at design time


SharePoint LINQ (SPLINQ)

You can find more information regarding LINQ from here

In order to minimize issues with CAML queries, Microsoft has introduced LINQ capabilities with SharePoint 2010
release. The LINQ provider translates the SPLINQ queries in to CAML and execute that in the server.
There are several advantage of using the LINQ provider for SharePoint

1] Queries are strongly typed
2] Compile time syntax checking
3] IntelliSense support at design time
4] It is easier to join multiple lists

The following section of the article will provide you with a step by step guide of using LINQ with SharePoint.

Step 1 – Add a reference to SharePoint LINQ provider

Make reference to Microsoft.SharePoint.Linq

Step 2 – Generate entity classes with SPMetal

Entity classes will be used as an abstraction layer where we can query in object oriented manner.
We can create entity classes on SharePoint objects (eg: SharePoint lists and other content types) by hand or
generate them by using a command line tool called SPMetal.

To generate entity classes using SPMetal, open the SharePoint Management shell and put following command.

   1: SPMetal.exe /web:http://sitecollection/ namespace:Infosense /code:SPLinq.cs

image_thumb1

After that include that class in to your project in visual studio

image_thumb3

Then add the SPLinq.cs file will be added to your project
image_thumb8

A section of the generated SPLinq.cs class is listed below

   1: public partial class SPLinqDataContext : Microsoft.SharePoint.Linq.DataContext {
   2:         
   3:         #region Extensibility Method Definitions
   4:         partial void OnCreated();
   5:         #endregion
   6:         
   7:         public SPLinqDataContext(string requestUrl) : 
   8:                 base(requestUrl) {
   9:             this.OnCreated();
  10:         }
  11:  
  12: /// <summary>
  13:         /// List to store application page permisions
  14:         /// </summary>
  15:         [Microsoft.SharePoint.Linq.ListAttribute(Name="PagePermissions")]
  16:         public Microsoft.SharePoint.Linq.EntityList<PagePermissionsItem> PagePermissions {
  17:             get {
  18:                 return this.GetList<PagePermissionsItem>("PagePermissions");
  19:             }
  20:         }
  21:  
  22: /// <summary>
  23:     /// Create a new list item.
  24:     /// </summary>
  25:     [Microsoft.SharePoint.Linq.ContentTypeAttribute(Name="Item", Id="0x01", List="PagePermissions")]
  26:     public partial class PagePermissionsItem : Item {
  27:         
  28:         private string _groupName;
  29:         
  30:         private string _pageName;
  31:         
  32:         private System.Nullable<bool> _viewPermission;
  33:         
  34:         private System.Nullable<bool> _fullPermission;
  35:         
  36:         #region Extensibility Method Definitions
  37:         partial void OnLoaded();
  38:         partial void OnValidate();
  39:         partial void OnCreated();
  40:         #endregion
  41:         
  42:         public PagePermissionsItem() {
  43:             this.OnCreated();
  44:         }


Step 3 – Querying SharePoint objects using the DataContext object

To query objects in SharePoint you have to have an instance of the created DataContext object. In this example
the name of the DataContext is SPLinqDataContext.
image_thumb17

You have to create an instance of SPLinqDataContext and use its methods.

You can use a code like following to query a list and filter data by using different where clause

   1: public List<ApplicationPagePermission> GetPagePermissionLevel(SPUser user, string page, List<string> myGroups)
   2:         {
   3:             using (SPLinqDataContext data = new SPLinqDataContext(SPContext.Current.Web.Url))
   4:             {
   5:                 EntityList<PagePermissionsItem> pagePerm = data.GetList<PagePermissionsItem>("PagePermissions");
   6:                 List<ApplicationPagePermission> appPagePermissionList = (from pp in pagePerm
   7:                                                                          where pp.PageName == page &&
   8:                                                                          myGroups.Contains(pp.GroupName)
   9:                                                                          select new ApplicationPagePermission
  10:                                                                          {
  11:                                                                              FullPermission = (bool)pp.FullPermission,
  12:                                                                              GroupName = pp.GroupName,
  13:                                                                              PageName = pp.PageName,
  14:                                                                              ViewPermission = (bool)pp.ViewPermission
  15:                                                                          }).ToList();
  16:  
  17:                 return appPagePermissionList;
  18:             }
  19:         }

Working with SPLINQ is very easy and user friendly . Although CAML query support and the SPQuery objects are
still supported in SharePoint 2010 , I will stick in to SPLINQ for sure Smile

What is LINQ ?

LINQ stands for Language Integrated Query,which was introduced with .Net framework 3.5.It is used to query data sources as strongly typed queries.It defines a set of Standard Query Operators that helps it to get data from different data sources, Query expressions, Lambda expressions and anonymous types.

There are few types of usage


LINQ to Object
LINQ to Objects allows to perform queries against arrays and in-memory data collections.
It is the IEnumerable<T> API for the Standard Query Operations.

LINQ to XML

Is the LINQ API for manipulating XML. Operations are available in System.Xml.Linq namespace.

LINQ to DataSet

Is the LINQ API for DataSets.

LINQ to SQL

Is the IQueryable<T> API that allows LINQ queries to work with Microsoft SQL Server database.
System.Data.Linq namespace will be required

LINQ to Entities

Is the alternate LINQ API used to query a database. It decouples the entity object model from the
physical database by creating a logical mapping between the database and objects.