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:
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.
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.