Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.






SharePoint for Developers: 3.3 Hands-on - querying lists

The best way to get data from SharePoint lists is to query them. Using queries you guarantee that only list items you really need are retrieved from database and constructed to objects by SharePoint. Most of the time you don't need the full collection of list items, so it is not point to ask all the items and then use only few of these. Let's see how we can query SharePoint lists.


NB! This blog is moved to

Click here to go to article


Reggio NH said:

In our current project, we are using LINQ to query the Lists. It's going ok, but I don't really know whether it is considered a good practice or not. How is it compared to CAML in terms of performance and efficiency ?

# October 29, 2008 12:51 PM

DigiMortal said:

Can you give me some examples about how do you use LINQ on SharePoint lists? When talking about performance you should always use CAML queries.

# October 29, 2008 4:44 PM

Reggio NH said:

It looks similar to this:

var x = from d in SPContext.Current.Web.Lists["YOUR_LIST NAME"].Items.GetDataTable().AsEnumerable()

                           where d["ATTRIBUTE"].Equals(SomeValue)

                           select d;

it looks more like traditional SQL and you can even do joins.

# October 29, 2008 8:43 PM

DigiMortal said:

Reggio, this code has hard performance loss on larger lists. Just take reflector and check out what is going on inside Items property of SPList. You may be surprised. :)

# October 30, 2008 3:40 AM

opas said:

...if <Today Offset="1" /> does not work for you, try <Today OffsetDays="1" />

# July 16, 2009 8:24 AM

sweha said:


I am trying to get listitems using the same method. I have a order no field in the list and some other fields are Customers which is a lookup and date which is a calculated column and Agency which is a text.

I am querying by requestno

I get Agency as it is a simple text, but I can't seem to get Customer name and Date as one is a lookup and one is a date. it is my necessity that I query by order number only and for each order number, get the rest of the data for each listitem.

Can you share your ideas please?

# August 2, 2009 9:24 PM

Juan Trujillo said:

Hi Gunnar,

I'm trying to get this CAML to work. I'm doing this from within SPD and this is the selectcommand of a spdatasource. For some reason it doesn't do anything with the GroupBy tag. Is it even supported in this scenario ?

Also, I want to format the output of this query to something like "MM/yyyy". Does CAML support Date Formatting in any way ?

I would really appreciate your help on this.

# February 24, 2010 1:07 PM

DigiMortal said:

Hi Juan and thanks for your question. CAML has very strict syntax of dates. You have to format dates appropriately so CAML can parse them.

# February 27, 2010 4:02 PM

maru said:

Thank you so much.

# August 16, 2010 9:54 PM

PDavid said:

Very informative one. I learnt how to query in SP2010. you have explained all the merits and demerits of it. Very good and useful tips.

# December 31, 2010 4:32 AM

mad-tie said:

When you have



       <FieldRef Name="BirthDateTime" IncludeTimeValue="True" />

       <Value Type="DateTime">1977-12-13T23:00:00Z</Value>



it should be



       <FieldRef Name="BirthDateTime" />

       <Value Type="DateTime" IncludeTimeValue="True">1977-12-13T23:00:00Z</Value>



Caused me some problems before :)

# January 26, 2011 2:13 PM

Nagalakshmi said:


Thanks for the post. I have quick question I need to write a query like this but i donot have access to code behind How can I achieve  this with the SP designer 2007?

I have a list and I need to select distict value of a perticular column.



# August 11, 2011 6:06 PM

Howdy said:

Your article was excellent and eurtdie.

# September 26, 2011 7:17 AM