Comparing two Fields of a SharePoint list

This topic is not something new in my blog, as I have blogged about this before too, thought not exclusively on this topic.

In many of my previous projects I had this task of retrieving list items from a SPList by comparing two columns in it. I found that CAML queries do not have the functionality to compare two columns. So, immediately I started to Google to find a solution :D.

I found that we can achieve this using LINQ Queries on SPListItemCollection as well as LINQ to SharePoint provider.

Let us first see a sample query using LINQ on SPListItemCollection:
Untitled

In the above LINQ query we are comparing 2 Number type columns of a SharePoint list by using the OfType filter on SPListItem. You can see that we are accessing the column values of a SPListItem in an usual conventional way by putting the column names in double quotes and also by converting them into a double.

Now let us see how to do the same thing using LINQ to SharePoint. I’m not going to explain how to use LINQ to SharePoint provider. There is a very good example provided here which clearly explains the process.

Here is a LINQ query formed using LINQ to SharePoint provider to compare two columns of a SharePoint list.

Untitled

As you can see in the above query we are comparing the two columns of a SharePoint list using strongly typed name of them because of which there won’t be any spelling mistakes on column names and also we need not do any typecasting as we did in the previous method.

But there is also a disadvantage of using LINQ to SharePoint provider. We do not have access to few default columns like Modified by, Created by, Author & so on.

SharePoint Caveats – 2: Generating CAML query from LINQ to SharePoint

I was always thinking about how to compare 2 fields(columns) of a SharePoint list using CAML queries. But got to know that it can be accomplished using LINQ to SharePoint but not CAML. I have also blogged about it over here. You can read this blog on how to use the LINQ to SharePoint feature to query SharePoint lists. But it is said that the LINQ query itself would get converted in to a CAML query at run time. I saw this blog which explains about how to write a CAML query generated from LINQ to SharePoint in to a text file.

So, I thought of writing a LINQ query to compare 2 fields(columns) and see what CAML query gets generated from it. I wrote the below program which compares 2 fields(columns) in an SP List called Spwitter using LINQ to SharePoint and logs the CAML query to an XML file.

using (var dataContext = new MySPDataContext("http://localhost:1446/"))
            {
                    TextWriter textWriter = new StreamWriter(@"c:\caml.xml", false);
                    dataContext.Log = textWriter;
                    var result = from c in dataContext.SpwitterList
                                 where  c.Title != c.Spweet
                                 select c;

                    foreach (var item in result)
                    {
                        System.Console.WriteLine("{0}----{1}", item.Title, item.Spweeter);
                    }
           }

I opened the caml.xml file with an excitement to see what CAML query has got generated for comparing 2 fields(columns) and saw the below query in it 😦 which does not have anything related to comparing 2 SharePoint list fields.

<View>
  <Query>
    <Where>
      <BeginsWith>
        <FieldRef Name="ContentTypeId" /><Value Type="ContentTypeId">0x0100</Value>
      </BeginsWith>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="Spweet" /><FieldRef Name="Spweeter" />
    <FieldRef Name="ImageURL" /><FieldRef Name="ID" />
    <FieldRef Name="owshiddenversion" />
    <FieldRef Name="FileDirRef" />
    <FieldRef Name="Title" />
  </ViewFields>
  <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>

Advantages and Disadvantages of LINQ to SharePoint over CAML Queries

The LINQ to SharePoint functionality which was available as a third party tool for free in codeplex for MOSS 2007 is available as an inbuilt functionality in SharePoint Server 2010.

I’m not going define what LINQ to SharePoint is as it is out of the scope of this post. I’m going to list out the advantages and the disadvantages of LINQ to SharePoint over CAML queries that I have been facing (though there are lots of blogs covering this topic). Let us see the advantages first.

Advantages:

1. As mentioned everywhere it provides strongly typed objects using which we get intellisense while coding. So, our code will be more bugs free unlike the CAML Queries where the result will be known only in the run time.

2. One more advantage not mentioned generally is that we can compare two columns (fields) of a SharePoint list in LINQ queries which is not possible in CAML queries.

3. We can also use LINQ to SharePoint to generate some complex CAML queries as shown in this blog

Disadvantages:

1. During the run time the LINQ query itself would get converted in to a CAML query which is an extra step ahead that takes some more time, which can be avoided if we straight away write a CAML query itself.

2.  Also, we generate a DataContext class using the SPMetal.exe. This class is the one which we use in our project to generate LINQ queries. This class does not get generated dynamically. So, if we do any changes in any of the lists or libraries in our site it does not get reflected in the DataContext class. So every time we have to generate a new class whenever we make any changes in the site.

3. LINQ to SharePoint has no use if we are going to access SharePoint data in Silverlight using Client Object Model unlike CAML queries.

4. Default fields like Created, CreatedBy, Modified and ModifiedBy of a SharePoint list are not created by SPMetal to be used in the LINQ queries.
Update: This article describes on how to achieve this.

5. LINQ to SharePoint cannot be implemented for an External list.