Sharepoint : LINQ Examples

In this post I am going to show some of the LINQ to SharePoint examples which will be useful in performing CRUD operations.

Before that lets think about SQL, CAML and LINQ

In SharePoint all the data will be stored in SQL databases. SharePoint List will be an front end for doing CRUD operation. Since the users does not have DB access List will be used as an interface to perform CRUD operation.

Suppose if we want to get few records from SharePoint list based on some condition then we need to write CAML query to get the records.

Writing CAML query is little bit tedious even though we have excellent CAML Query builder. To overcome this problem we have LINQ for SharePoint which will internally run CAML to perform CRUD operations.

So LINQ is a front end for CAML, internally CAML is front end to SQL.

Here I am showing how to use LINQ to SharePoint using SPMetal.exe

Lets create a SharePoint List called EmployeeDetails with following fields.

EmployeeID - Integer FirstName - Single Line Text LastName - Single Line Text DOB - Date field Phone - Number Email - Single Line Text

Create some dummy records in the employee list.

Requirement:

Lets assume we have one timer job which will send an EMail to the employee whose birthday is today :).

1) Open SPMetal.exe from CommondPrompt

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



Run the following command from window.

c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN>SPMETAL.exe /web:http://sharepoint2010 /namespace:SharePointConsoleApplication1 /code:ListEntities.cs (This will create the entity class with the namespace.



or

c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN>SPMETAL.exe /web:http://sharepoint2010 /code:ListEntities.cs (This will create Entity class without namespace)



Add ListEntities.cs file from 14 hive folder to the project and verify the namespace(make sure Microsoft.Sharepoint.Linq dll reference has been added in the project)

The structure of the Project should be as below after adding the entity class into the project.



Read Operation:

I have pasted below code which will display Employee Name whose birthday is today. (Its like an Select statement in SQL)

using System;
using System.Linq;
using Microsoft.SharePoint;
namespace SharePointConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//Open the List Entities Data context file and pass the Site url as parameter
using (ListEntitiesDataContext list = new ListEntitiesDataContext("http://sharepoint2010"))
{
//Once data context is ready its simple Linq statement to fetch the records based on the condition
var spItems = from a in list.EmployeeDetails where a.DOB == System.DateTime.Now select a;
//Loop through all the items
foreach (var item in spItems)
{
//Here we can write our business logic just displaying the employess first name
Console.WriteLine(item.FirstName);
}
}
}
}
}

Will continue :)