LINQ, SharePoint
LINQ to SharePoint: CRUD operation on SharePoint 2010 list using SPLinq
Posted by Dhananjay Kumar ⋅ November 10, 2010 ⋅ 1 Comment
Objective
In this article, we will see how to work with LINQ to SharePoint. I have tried to address all the common errors we encounter when first time we start using LINQ against SharePoint.
We will see ,
1. How to insert an item in SharePoint list using SPLinq
2. How to update an item in SharePoint list using SPLinq
3. How to delete an item in SharePoint list using SPLinq
4. How to fetch items from SharePoint list using SPLinq.
Advantage
1. SPLinq supports visual studio intellisense
2. SPLinq provides strong typecasting and data typing
3. SPLinq works without CAML query.
4. Using SPLinq , existing knowledge of LINQ could be apply to SharePoint development .
5. SPLinq provides completely language dependent development of SharePoint
6. Multiple list item insertion and deletion can be performed very easily with one syntax.
7. Join operation can be performed very easily on the related list
Using CAML when performing operation against SharePoint list was biggest challenge. CAML does not provide developer friendly syntaxes. SPLinq helps us to get rid of CAML.
SPLinq can be used with
1. Managed application , like windows or console or WPF
2. With SharePoint client object models.
3. With SharePoint webparts.
4. With SPGridView.
Challenge
The main challenge working with SPLInq is creation of entity class on command prompt. SPMetal creates a partial Entity class to apply Linq against that.
Assumption
We have a custom list
1. Name of the list is Test_Product.
2. Columns of the list is as below ,
clip_image002
3. There are two items in the list
clip_image004
4. URL of the SharePoint site is
“http://dhananjay-pc/my/personal/Test1
Now we need to fetch the list items in a managed console application using Linq to Sharepoint or SPLinq.
Follow the below steps,
Step1
Open the command prompt and change directory to
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
Type command CD C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
clip_image006
Step2
Now we need to create the class for corresponding list definitions.
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN> spme
tal.exe /web:http://dhananjay-pc/my/personal/Test1 /namespace:nwind /code:Product.cs
In above command we are passing few parameters to spmetal.exe, they are as below
1. /web:Url
Here we need to provide URL of SharePoint site
/web:http://dhananjay-pc/my/personal/Test1 /
://dhananjay-pc/my/personal/Test1 / is URL of SharePoint site, I created for myself. You need to provide your SharePoint site URL here.
2. /namespace:nwind
This would be the namespace under which class of the list will get created. In my case name of the namespace would be nwind.
3. /code:Product.cs
This is the file name of the generated class. Since we are giving name Product for the file then class generated will be ProductDataContext
Step3
Open visual studio and create a new project of type console. Right click on the Project and select Properties
clip_image007
Click on the Build tab and change Platform Target to Any CPU.
clip_image009
Click on the Application tab and change the Target framework type to .Net Framework 3.5
clip_image011
Step4
The class we created in Step2 will by default get saved in the same folder with SPMetal.exe. So to see where the class got created we need to navigate to folder
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
Add created class to the Project
Now add this class to the project. To do this, right click on the project and select Add existing item. Then browse to above path and select Product.cs
Add references to the Project
Microsoft.SharePoint
Microsoft.SharePoint.Linq
Right click on Reference and select Add Reference. To locate Microsoft.SharePoint and Microsoft.SharePoint.Linq dll browse to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI. All the SharePoint dll are here in this location.
Step5
Add the namespace
clip_image012
Nwind is the name of the namespace of the class we created in Step2.
Now console application project is ready for the CRUD operation on SharePoint 2010 list using SPLinq
Fetch the items from list using SPLinq
a. First we need to create instance of ProductContext class
clip_image014
Here we need to provide URL of the SharePoint site as parameter to constructor.
b. Now we can apply simple LINQ to access the list, like below.
clip_image016
c. There is one more way to access the list
clip_image018
Code for fetching list items
view source
print?
1 ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1");
2 var result = from r in context.Test1_Product select r;
3 foreach (var r in result)
4 {
5 Console.WriteLine(r.ProductId + ":" + r.ProductName + ":" + r.ProductPrice);
6 }
7 Console.ReadKey(true);
Output
clip_image020
Insert to SharePoint list using SPLinq
1. Create the instance of Data Context
clip_image022
2. Get the entity list where item would get inserted
clip_image024
3. Create instance of List item to be inserted
clip_image026
4. Call the InsertOnsubmit on the instance of Entity list.
clip_image027
5. Call sumitchange on the context
clip_image028
Code for inserting a list item
view source
print?
01 ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1");
02 EntityList
03 Test1_ProductItem itemToInsert = new Test1_ProductItem()
04 {
05 ProductId = "9",
06 ProductName = "Soccer Ball",
07 ProductPrice = 600
08 };
09 products.InsertOnSubmit(itemToInsert);
10 context.SubmitChanges();
Update a Particular list item in SharePoint list
1. Create instance of Data Context
clip_image030
2. Fetch the list item to be updated using SPLInq
clip_image032
3. Modify the list item property wished to be updated. I am updating Product name to Dhananjay of Product Id 1
clip_image033
4. Call the submit change on the context
clip_image034
Code for updating a list item
view source
print?
1 ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1");
2 var itemToUpdate = (from r in context.Test1_Product where r.ProductId == "1" select r).First();
3 itemToUpdate.ProductName = "Dhananjay";
4 context.SubmitChanges();
Delete a Particular item from the list
1. Create the instance of Data Context
clip_image022[1]
2. Get the entity list where item would get inserted
clip_image024[1]
3. Fetch the list item to be deleted using SPLInq
clip_image032[1]
4. Call deleteonsubmit to delete a particular item from SharePoint list
clip_image035
5. Call the submit change on the context
clip_image034[1]
Code for deleting a list item
view source
print?
1 ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1");
2 EntityList
3 var itemToDelete = (from r in context.Test1_Product where r.ProductId == "1" select r).First();
4 products.DeleteOnSubmit(itemToDelete);
5 context.SubmitChanges();
This Visual How To contains the following sections to demonstrate how to create and use a Web Part that uses an external content type:
Note: 
The Web Part is added to the page.
You can use SQL Server Management Studio to query the AdventureWorks database to verify that the Web Part operations via the external content type are working correctly.
This Visual How To provides steps and sample code that demonstrate how to use Visual Studio 2010 to create a Visual Web Part that uses a BCS external content type to read and write data to the AdventureWorks database. The sample code shows how to use the Create, Specific Finder, and Update methods of the external content type, which is based on the sample AdventureWorks web service that is provided with the SharePoint 2010 SDK.
