LINQ to SharePoint

In this article we can explore some of the advanced programming areas:

·                     LINQ to SharePoint

·                     SPMetal

 One of the most significant new features of Microsoft SharePoint 2010 is the support for

LINQ on the server side. This is a satisfying alternative to the classic object model

“Server Object Model.” This will begin with a quick overview of

LINQ, just in case you’re not familiar with it, and then show you how to work with it. Next,

you’ll learn about LINQ to SharePoint as a LINQ query provider implementation, which is

useful for querying and managing items in SharePoint lists using the LINQ data access model.

LINQ stands for Language Integrated Query, which is a programming model that introduces

queries as a first-class concept into any Microsoft .NET language. Complete support for LINQ,

however, requires some extensions in the language that you are using. These extensions

boost developer productivity, thereby providing a shorter, more meaningful, and expressive

syntax with which to manipulate data.

LINQ provides a methodology that simplifies and unifies the implementation of any kind of

data access. LINQ does not force you to use a specific architecture; it facilitates the implementation

of several existing architectures for accessing data, such as the following:

·                     RAD/prototype

·                     Client/server

·                     N-tier

·                     Smart client

Why we need LINQ?

You might have noted that the previous List Programming examples did not use proper column name access. LINQ allows us to access the List in a Typed manner. Adding more clarity, we can access the list items based on the column names which we usually do with the databases.

Example: var result = from c in Citizen where c.Name == "John" select c;

What is SPMetal?

As we will be creating custom lists having custom column names, we need to generate the Entity Model. The SPMetal.exe is the tool which helps in generating the Model classes.

Although we can create the Model classes manually, it will be a tedious job and prone to error. Using SPMetal would be the right approach to model classes.

Activities

·                     LINQ to Objects This is used to query in-memory data and object graphs.

·                     LINQ to SQL This was specifically designed to query and manage data stored in a Microsoft SQL Server database, using a lightweight, simplified Object-Relational Mapper (O/RM) that maps entities to tables with a one-to-one relationship.

·                     LINQ to Entities The first class O/RM offered by Microsoft to design solutions basedon the domain model, with a real abstraction from the underlying persistence storage.

·                     LINQ to DataSet This is a LINQ implementation targeting old-style ADO.NET DataSet and DataTable types. It is mainly offered for backward compatibility reasons.

·                      LINQ to XML This is a LINQ implementation targeting XML contents, useful to query,manage and navigate across XML nodes.

Modeling with SPMetal.EXE

The first and main task when developing solutions that make use of LINQ to SharePoint is

to model the typed entities. You can define these manually, but it is general more useful to

use a specific tool, named SPMetal.EXE, which can automatically generate entities for you.

You can find the SPMetal.EXE utility in the SharePoint14_Root\BIN folder. SPMetal.EXE is a

command-line tool that accepts a wide range of arguments, That You Can Provide to SPMetal.EXE

Argument Description

/web:<url> Specifies the absolute URL of the target website. Host address can be

local, in which case, the tool uses the Server Object Model to connect

to the server.

/useremoteapi Specifies that the website URL is remote. You might not use this option

if any of the lists on the website contain lookup fields. Secondary

lookups are not supported by the Client Object Model.

/user:<name> Specifies the logon username (or domain).

/password:<password> Specifies the logon password.

/parameters:<file> Specifies an XML file with code generation parameters.

Argument Description

/code:<file> Specifies the output location for generated code (default: console).

/language:<language> Specifies the source code language. Valid options are csharp and vb

(default: inferred from source code file name extension).

/namespace:<namespace> Specifies a namespace used for auto-generated code (default: nonamespace).

/serialization:<type> Specifies a serialization type. Valid options are none and unidirectional

(default: none). The entities serialization topic will be discussed in the

“Disconnected Entities” section.

Note that the default behavior of SPMetal.EXE is to output auto-generated code to the console.

That’s not terribly useful except for testing, so you should generally provide a /code

argument to instruct the tool to generate a code file, instead. Next, you need to provide the

target website URL by using the /web argument, and then instruct the tool to use the client

object model (/useremoteapi), if the site is remote. It’s common to also provide a namespace

by using the /namespace argument. Here’s a typical command-line invocation of the tool:

SPMETAL.EXE /web:http://sureshnvSP.com/ /code:SiteEntities.cs

/namespace:sureshsp.nv

As you will see when you execute SPMetal.EXE, by default it creates a full model for the

target site, defining a class for almost every supported content type, and a list for every

list instance, except for hidden lists. The tool will also create a class named [WebSiteName]

DataContext, where [WebSiteName] is the name of the target website without spaces, in case

the site name has spaces in its content. This class represents the entry point for using LINQ to

SharePoint, and it inherits from the Microsoft.SharePoint.Linq.DataContext base class. Quite

often, you do not really need to model each and every content type and list instance of the

target site. Usually, you need to model only some custom data structures that you plan to

query and manage with LINQ to SharePoint. The /parameters command line argument is

provided for this purpose. In fact, using this argument you can provide SPMetal.EXE with an

XML file that instructs the tool about what to skip and what to include in the auto-generated

model. Listing 4-1 shows a sample XML parameters file that excludes all the common “Team

Site” default contents, while it includes all other content-types and lists.

 A sample XML parameters file suitable for SPMetal.EXE.

<?xml version="1.0" encoding="utf-8"?>

<Web AccessModifier="Internal"

xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal">

<ExcludeList Name="Announcements"/>

<ExcludeList Name="Calendar"/>

<ExcludeList Name="Customized Reports"/>

<ExcludeList Name="Form Templates"/>

<ExcludeList Name="Links"/>

Modeling with SPMetal.EXE 109

<ExcludeList Name="Shared Documents"/>

<ExcludeList Name="Site Assets"/>

<ExcludeList Name="Site Pages"/>

<ExcludeList Name="Style Library"/>

<ExcludeList Name="Tasks"/>

<ExcludeList Name="Team Discussion"/>

</Web>

Generating the Entity Models

Now we can generate the Entity Model for the above List.

You can get the SPMetal.exe inside the following folder:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

Open a command prompt and go to the specified folder:

Now run the following command: 

SPMetal.exe /web: http://sureshnvSP /code: SiteEntities.cs

Wait for a while and you will be ready with the new file. Open the file SiteEntities and you can see the Manager class is contained inside.

You can try with the following operations: Read, Insert, Update and Delete using LINQ to SharePoint.

Selecting an Item

Now we are trying to select the managers with the country "USA":

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://sureshnvSP /"))
{
    var result = context.Manager.Where(m => m.Country == "USA");

    foreach (ManagerItem manager in result)
    {
        Console.WriteLine(manager.Name);
    }
}

Note: You can use LINQ or Lambda Expressions to do the query. In the above example I have used Lambda.

Inserting an Item
For inserting a new item into the Manager list, use the following code:

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://sureshnvSP/"))
{
    ManagerItem manager = new ManagerItem();
    manager.Name = "New Manager";
    manager.Address = "New Address";
    manager.Country = "New Country";

    context.Manager.InsertOnSubmit(manager);
    context.SubmitChanges();
}

Updating an Item

For updating an item inside SharePoint use the following code:
 

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://sureshnvSP/"))

{

    ManagerItem manager = context.Manager.Where(m => 

                          string.IsNullOrEmpty(m.Title)).FirstOrDefault();

    if (manager != null)

        manager.Title = "New Title";

    context.SubmitChanges();

}

Deleting an Item

For deleting an item inside SharePoint use the following code:

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://sureshnvSP/"))

{

    ManagerItem manager = context.Manager.Where(m => m.Title.Length > 3).FirstOrDefault();

    if (manager != null)

        context.Manager.DeleteOnSubmit(manager);

    context.SubmitChanges();

}

Hope this will help you