Procedures and tricks to use LINQ work with SharePoint 2010 list data

LINQ is a very simple and powerful way of querying data in the .Net programming language. It allows the .Net language to query data natively against strongly typed classes. Yes against class with objects through DataContext generated by SPMetal.exe tool. SharePoint will convert the LINQ to CAML during the runtime. As a result, LINQ is an easy way comparing to older CAML to work with list data. Here is example to use LINQ to SharePoint 2010 with tricks and limitations.

1. Create a list named “City” with two columns “City” and “State” on site http://sbx08/sites/Harry. You will see the list as in the screen shot.

2. Use SPMetal.exe located in ..\14\BIN to generate DataContext so you could use the objects in LINQ. The command you could use is:

spmetal /web:http://sbx08/sites/Harry/ /namespace:HarrySite /code:HarrySite.cs /language:csharp /useremoteapieapi

Please note we are using option  /useremoteapi if you are going across the wire from a non-SharePoint machine. It will add default credentials to the api call.

3. Create a console test application and add the following items to avoid compilation error.

• Change platform target to "x64"
• Change target framework to ".Net Framework 3.5"
• Add existing item HarrySite.cs generated form SPMetal.exe to main class
• Add Microsoft.SharePoint.Linq.dll reference
• Add using HarrySite and using Microsoft.SharePoint.Kinq in main class

4. Use DataContext to query the "City" list data and insert one new City Chicago, Illinois to the list. Here is the code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

//using TeamSite100; 2 instances will cause issue

using HarrySite;

using Microsoft.SharePoint.Linq;

namespace SharePointLinq

{

    class Program

    {

        static void Main(string[] args)

        {

DataContext ctx = new DataContext("http://sbx08/sites/Harry/");

            // or HarryDataContext ctx = new HarryDataContext("http://sbx08/sites/Harry/");

            EntityList<CityItem> all = ctx.GetList<CityItem>("City");

            // var cities = from c in ctx.CityItem works also

            var cities = from c in all

                         where c.State == "Washington"

                         orderby c.State

                         select c;

               // Add an item to City list

               CityItem ct = new CityItem() { Title = "44", City = "Chicago", State = "Illilois" };

               all.InsertOnSubmit(ct);

               ctx.SubmitChanges();

            // Get each item from City list

            foreach (var city in cities)

            {

                Console.WriteLine(city.City + "," + city.State);

            }

            Console.ReadLine();

        }

    }

}

5. If you run the program, you will see one city added to the list and program will display the follow city.

Everything looks good and easy. There are some good bog you could refer to utilize LINQ.

•  Compare CAML and Linq forjoin lists
• LINQ CRUD operations 
• Handles tools for LINQ toSahrePoint 

However, there are some tricks you need to be aware of when you use LINQ to SharePoint. Here are the top three you need to keep in mind.

1. Any list definition change such as column renamed, deleted, or type changed will require SPMetal tool to rebuild the DataContext class. Your implementation may require to change also.
2. LINQ can not retrieve any managed metadata column values or look-up columns we will explain in next blog.
3. SPMetal will not generate reverse lookup associations for any of the relationships based on the lookup column IF a site lookup column is used by more than one list.
4. There is no UpdateOnSubmit() function so it is difficult to update the list item through LINQ. The functions InsertOnSubmit() and DeleteOnSubmit() could be used to add and delete list items.

Now w you understand the limitations of the LINQ, you should use this to work with the list that has no managed metadata columns or look-up columns and columns will maintain unchanged.