Click here to Skip to main content
Click here to Skip to main content

SharePoint 2010 – LINQ and SPMetal

, 29 Jun 2012
Rate this:
Please Sign up or sign in to vote.
Exploring LINQ and the SPMetal tool.

Introduction

In this article we explore some advanced programming areas:

  • LINQ to SharePoint
  • SPMetal

LINQ to SharePoint is a new feature of SharePoint 2010. LINQ stands for Language Integrated Query which is a part of .NET. The aim of LINQ is to support different data sources using the same Typed Query Syntax. Presently it supports Objects, Datasets, SQL, Entities, XML, etc.

Why do 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 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. SPMetal.exe is the tool which helps in generating Model classes. Although we can create Model classes manually, it will be a tedious job and error prone. Using SPMetal would be the right approach to model classes.

Activities

Following are the activities performed in this article:

  1. Manager List Creation
  2. Entity Creation
  3. Read using LINQ
  4. Insert Entity
  5. Update Entity
  6. Delete Entity

Experimenting with LINQ and SPMetal

To start with, create a custom list inheriting from Custom List and name it Manager. Add the following custom columns and data:

Generating the Entity Models

Now we can generate the Entity Model for the above List. You can get 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://YOURSITE /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.

Create Application

Create a new SharePoint > 2010 > Console Application (targeting the .NET 3.5 framework) and add the SiteEntities.cs file into it.

Add a reference to the following assembly:

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

Selecting an Item

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

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://appes-pc"))
{
    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 Expression to do the query. In the above example I have used Lambda.

On executing the application you can see the following results.

Inserting an Item

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

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

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

After executing the application, open the Manager list inside SharePoint as shown below:

Updating an Item

For updating an item inside SharePoint, use the following code:

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://appes-pc"))
{
    ManagerItem manager = context.Manager.Where(m =>  
                          string.IsNullOrEmpty(m.Title)).FirstOrDefault();
    if (manager != null)
        manager.Title = "New Title";

    context.SubmitChanges();
}

You can see the updated entity inside SharePoint:

Deleting an Item

For deleting an item inside SharePoint use the following code:

using (SiteEntitiesDataContext context = new SiteEntitiesDataContext("http://appes-pc"))
{
    ManagerItem manager = context.Manager.Where(m => m.Title.Length > 3).FirstOrDefault();
    if (manager != null)
        context.Manager.DeleteOnSubmit(manager);

    context.SubmitChanges();
}

You can see that the item is deleted inside SharePoint:

 

This concludes our Read, Insert, Update, Delete operations using LINQ to SharePoint. Hope the topics are understood by the reader.

Summary

In this article we have explored LINQ and the SPMetal tool. This information is necessary in real world programming scenarios. The attachment contains the source code we discussed.

References

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Jean Paul V.A
Architect
United States United States
Jean Paul is a Microsoft MVP and Architect with 12+ years of experience. He is very much passionate in programming and his core skills are SharePoint, ASP.NET & C#.
 
In the academic side he do hold a BS in Computer Science & MBA. In the certification side he holds MCPD & MCTS spanning from .Net Fundamentals to SQL Server.
 
Most of the free time he will be doing technical activities like researching solutions, writing articles, resolving forum problems etc. He believes quality & satisfaction goes hand in hand.
 
You can find some of his work over here. He blogs at http://jeanpaulva.com
Follow on   Twitter

Comments and Discussions

 
QuestionRunning from client machine Pinmembernishant231030-May-13 1:00 
AnswerRe: Running from client machine PinmvpJean Paul V.A30-May-13 1:45 
Questionrunning queries from a Client Machine Pinmembersdet_programmer22-May-13 9:02 
AnswerRe: running queries from a Client Machine PinmvpJean Paul V.A22-May-13 9:06 
GeneralRe: running queries from a Client Machine Pinmembersdet_programmer22-May-13 9:14 
GeneralRe: running queries from a Client Machine PinmvpJean Paul V.A22-May-13 10:07 
QuestionSPMetal is not generating code for custom list defintion based list PinmemberJayant Sharma28-Jun-12 18:57 
AnswerRe: SPMetal is not generating code for custom list defintion based list PinmemberJean Paul V.A29-Jun-12 3:32 
Hello Jayant,
 
I would request you to run the SPMETAL command again:
 
SPMetal.exe /web:http://YOURSITE /code:SiteEntities.cs
 
(please do not add any pace after web: or codeSmile | :)
 
Your file should be generated in the same folder named SiteEntities.cs
Jean.

GeneralRe: SPMetal is not generating code for custom list defintion based list PinmemberJayant Sharma1-Jul-12 17:57 
QuestionList Name PinmemberMikJr11-Jun-12 14:56 
AnswerRe: List Name PinmemberJean Paul V.A11-Jun-12 15:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 29 Jun 2012
Article Copyright 2012 by Jean Paul V.A
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid