Posts Tagged ‘SPQuery’

SP 2010: LINQ to SharePoint – What CAML lies behind my query?

February 19th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

The two following questions are quite popular in my SharePoint 2010 developer training classes, so I ought to answer them right here.

  1. "What does LINQ to SharePoint really do?"
  2. "Can I see the CAML query generated by the LINQ to SharePoint query?"

The answer is simple: Yes, you can see the results of your generated LINQ query by using the Log property of your DataContext object.

What CAML lied behind my LINQ to SharePoint query?

In order to fetch the CAML query that lies behind your LINQ query, all you need is to work with the .Log object of your DataContext.

See this simple example, which simply outputs the CAML query to an XML-file for easy reading:

image

This will essentially generate the following content in the file C:MyEntitiesDataContextQuery.xml:

image

As you can see, the LINQ to SharePoint query is automatically turned into a CAML Query.

Summary

Yep, all you need is the .Log property to fetch the CAML query from your LINQ statement. In my sample I’m outputting it to a file called C:MyEntitiesDataContextQuery.xml.

You could of course output it in any other way you want – not just a physical file on the file system. The Log property is of the type TextWriter.

Enjoy!

SP 2010: List Joins & SPQuery enchancements!

January 4th, 2010 by Tobias Zimmergren

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

As per request by some of my readers who wanted information about how you can query SharePoint lists using joins in SharePoint 2010, here’s an introduction!

With the introduction of relational lists in SharePoint 2010, they also introduced a new set of investments in how we can interact with our lists.

We now have Projected fields, joins and relational integrity between parent and child lists.

Projected Fields & Relational Integrity will be covered in an upcoming blog series.

Joins

With our new relational lists in SharePoint 2010, we also have a better support for joins. Generally there’s the following ways of joining lists to pull out aggregated data:

  1. SharePoint API (SPQuery object)
  2. LINQ to SharePoint
  3. CAML
  4. SharePoint Designer 2010

In this article I will touch upon the SPQuery enhancements regarding joins and relational data, and will cover the rest in future articles.

SPQuery enhancements

Using the object model with SharePoint 2010, you now have the ability to use some new fancy properties of the SPQuery object:

  1. SPQuery.Joins
  2. SPQuery.ProjectedFields

SPQuery.Joins Property

With the new property called Joins on the SPQuery object, we can (using CAML, as usual..) define a join for our query like so:

<Joins>
    <Join Type=’LEFT ListAlias=’List1‘>
        <Eq>
            <FieldRef Name=’Field1′ RefType=’Id’ />
            <FieldRef Name=’ID’ List=’List1‘ />
        </Eq> 
    </Join> 

    <Join Type=’LEFT’ ListAlias=’List2‘>
        <Eq>
            <FieldRef List=’List1‘ Name=’SomeFieldName’ RefType=’Id’ />
            <FieldRef List=’List2‘ Name=’ID’ /> 
        </Eq>
    </Join>
</Joins>

A working sample of the SPQuery code could look like this:

SPQuery query = new SPQuery();
query.Query = "[YOUR CAML QUERY HERE]";
query.Joins = "[YOUR JOIN CAML HERE (See above for example)]";
query.ViewFields = "[SAME AS BEFORE]";

SPListItemCollection items = myList.GetItems(query);
foreach(SPListItem item in items)
    // Work with your items here like you’re used to, but now all joined up!

So the only thing you really need to do is to make sure you’ve got the join set up with the SPQuery.Joins property – and then shoot!

SPQuery.ProjectedFields Property

The SDK states the ProjectedFields property quite clearly:

…itemizes fields from joined foreign lists so that they can be referenced in the Where element and the ViewFields element

You should have a go at this link to learn more about projected fields, and how you can utilize them in your SPQuery objects:
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.projectedfields(office.14).aspx

Summary

It’s pretty easy to do joins using the SPQuery object in the SharePoint object model these days, and all you really need to do is have a quick look at the SPQuery class in order to grasp the news.

So, pretty easy huh?
- Well, even more easy is to use LINQ to SharePoint, which I will try to cover in an article later this week.

U2U CAML Query Builder – New version out

July 26th, 2008 by Tobias Zimmergren

I just read Karine Bosch’s blog where she posted some news on the U2U CAML Query Builder, so I just ought to get your attention in that direction.

Check the detailed updates out in Karine’s blog:
http://www.u2u.info/Blogs/karine/Lists/Posts/Post.aspx?List=d35935e0%2D8c0e%2D4176%2Da7e8%2D2ee90b3c8e5a&ID=30

Cheers

This post is a mere followup to my previous minor posts on the subject of SPQuery and SharePoint queries located here.

Basically this post will provide step-by-step instructions to construct your CAML (Collaborative Application Markup Language) queries using the U2U CALM Query Builder which you can find on the U2U Community Tools page.

Prerequisites

In order to be able to follow along with this step-by-step instructions you’ll need the following:

  • U2U CAML Query Builder 2007 or later
  • A site containing a Task List with some items
    • Some items with Status set to Completed
    • Some items with Status set to something else than Completed
Let’s get going!
  1. First of all, make sure you’ve got some items in your Task list:
    image
  2. Launch U2U CAML Query Builder
  3. Fill in the URL to your SharePoint site and choose weather you’d like to go in the Object Model-way or using SharePoint Web Services. I chose Connect via Object Model:
     image
  4. Select "Tasks" in the list that you’ll be presented with, and the right-hand side of the application will automatically be filled with the fields availible for that list, which will be the basis for our query:
    image

    As you can see here, there’s the option to:

    • Choose the field – the CAML Query Builder will automatically choose the InternalName
    • "Order By" – order the results being returned in any choose order
    • "Where" – Specify which results should be part of the returning resultset, this is the most vital tag if you ask me!
    • "Test Your CAML!"
      • "Editor": Enter any CAML here and hit the "Test" button in order to test the entered query directly against your List
      • "Parameters": Retrieves any parameters
      • "Result": The returning result of the query you test
        (tip: Always test your query before applying it to your code, no matter how simple it may be)

 

  1. Select the "Status" field
  2. Check "Where" and choose "Equal"  and choose the value "Completed" like this:
    image
  3. Hit the little arrow pointing to the right, and the query will be automatically created for you and you’ll get the CAML in the bottom-window like this:
    image
    IMPORTANT: When you construct your query in SharePoint later on, REMOVE the <Query> tags. See this post on the subject.
  4. Now that you’ve created the CAML you want, hit the "Test" button to the right and make sure you get the desired result!
    image
    Note: You can check the column called Status and make sure that all rows returned states "Completed"
  5. Now we’ve got our CAML Query ready to be implemented in our SharePoint code. Since we didn’t make anything complex or advanced it’ll be quite easy to implement this query, as I will show you now.
Prepare a Console Application for SharePoint Development

I will for simplicity create a Console Application to retrieve the desired SPListItem objects based on our SharePoint CAML query

  1. Create a new Console Application
  2. Add a reference to Microsoft.SharePoint.dll (Windows SharePoint Services) in order to be able to access the SharePoint object model
    image
  3. Add the following standard using-directive (Microsoft.SharePoint) in order to be able to use the classes in the SharePoint namespace:
    image
  4. Now we’re ready to roll – hang on to your camel
Implementing a normal list loop, without queries (not a good approach)

The following example is what it basically looks like at many implementations. A straight through loop that’ll basically loop EVERY item in the list and check EVERY object if it’s matching what we want – this is not a valid approach if you’ve got a couple of thousand (or even just a couple of hundred) items in the list. It’ll take way too long.

 

image 

The output is correct, but the time to walk through each item may even cause a timeout if you’ve got many thousand items in the list.
image

Implementing SharePoint (CAML) Queries using the SPQuery object (very good approach)

This will demonstrate how you achieve exactly the same result as previously demonstraded, but with less resource-hogging and much quicker.

Direct advantages:

  • Ordering, you can specify exactly in what order, sorted on any column that you want your resultset to be.
  • Filter out unwanted items (using the Where-tag you can simply choose ONLY the items you want returned)
  • SharePoint will dig right into the list and pick out the correct objects much faster than the previous example

Take a look at the following code, which will implement our previously created query:
image 
IMPORTANT: As you can see in the code above, I didn’t use the <Query> and </Query> tags provided by the U2U CAML Query Builder. If you do, your query will not work ;)

Conclusion

You may not acknowledge any performanceboost if you’ve got up to 100 items in your list, but when the list grows you’ll notice a huge difference immediately.

I did a test on my Forum Web Part where I had about 2500 posts (items in a list) and retrieved them using the first approach mentioned here (simply looping all items and using if-statements on each of them) – it resulted in timeouts all the time. When the page loaded, it took more than a minute per request, which isn’t appreciated by anyone ;)

When I made the comparsion with my SPQuery implementation to directly pick out the items I wanted, it resulted in a 1 second loadtime instead of more than a minute. This speaks a lot about how good queries are.

Yeah okay.. So what should I do now?

You should most definately refactor all your code to use Queries if you havn’t already!

Most people already do use this approach, but since I keep seeing implementations that doesn’t, I just thought I’d write this little piece up.

Hope someone can find it useful.

Introduction to SharePoint Queries

May 4th, 2008 by Tobias Zimmergren

Prephase

Recently I’ve been using SharePoint Queries more and more, not to say in my latest personal project: Zimmergren Forum WebPart. At times when I’ve been out at some clients to help them either refactor existing code or build new code, weather doing it myself of joining an existing SharePoint team – I always bump into things that could be solved using Queries instead of the way they’ve solved it using for- and foreach loops to go through entire lists with if-statements checking if that’s what they’re looking for.

Example: Code not using queries – Get all SPListItem objects that matches a value in a specified field

The following few lines of code is a sample of how you could fetch some SPListItems and check weather they’re what you’re looking for or not – without using SharePoint Queries (SPQuery). I’ve seen this kind of implementation one time too many and thought that it’d try to clarify the benefits with using SharePoint Queries.

Sample code without queries:

int counter = 0;
SPList myList = SPContext.Current.Web.Lists["ForumList"];
foreach (SPListItem forumItem in myList.Items)
{
    if (forumItem["ForumID"].ToString() == forumID.ToString())
        counter++;
}
return counter;

This code will work well if you don’t have too many items in your list. But if you’d have a couple of hundred or even thousand items in the list, it’d take way too long to loop through each of these items to see if the ForumID matches the item’s forumID.

Example: Code using queries – Get all SPListItem objects that matches a value in a specified field

To solve this issue, you should instead make use of the SPQuery (Microsoft.SharePoint.SPQuery) object in order to retreive only the items that actually contains the ForumID that you’re looking for – without the need for checking each and every item in the list. This will increase performance drastically if you’ve got a couple of thousand items in a list and making a lot of calls to the method that gathers listitems.

Sample code that makes use of SharePoint Queries (SPQuery)

SPList myList = SPContext.Current.Web.Lists["ForumList"];

SPQuery query = new SPQuery();
query.Query = string.Format(
    "<Where>"+
      "<Eq>"+
         "<FieldRef Name=’ForumID’ />"+
         "<Value Type=’Number’>{0}</Value>"+
      "</Eq>"+
   "</Where>", forumID); //forumID comes as a parameter to the method being called

SPListItemCollection listItems = myList.GetItems(query);

return listItems.Count;

The code above will significally increase performance of your code if there’s plenty of items in the list.

Conclusion

This post was not meant to teach the technique of using SharePoint queries, but rather to enlighten you on the fact that there’s a performance boost if you learn to use SPQuery instead of a bunch of if-statements to get specific ListItems.

I’ll cover the basics and more all-around techniques of using Queries in my next blogpost!

SPQuery returning all items

May 2nd, 2008 by Tobias Zimmergren

If you’ve ever used the SPQuery objects to get SPListItems from a SPList, and you didn’t get it to work because the query would return ALL items in the SPList, you might have used code similar to this:

SPQuery postsQuery = new SPQuery();
postsQuery.Query = string.Format(
    "<Query>"+
       "<OrderBy>"+
          "<FieldRef Name=’Created’ />" +
       "</OrderBy>"+
       "<Where>"+
          "<Eq>"+
             "<FieldRef Name=’ThreadID’ />"+
             "<Value Type=’Number’>{0}</Value>"+
          "</Eq>"+
       "</Where>"+
    "</Query>", threadID);

SPList allPosts = SpecificForumData.GetPostList(); // Custom method to get the SPList object
SPListItemCollection posts = allPosts.GetItems(postsQuery);

I’ve always built my queries by hand (what better way to learn than trial and error, right?), but when I tried the U2U CAML Query Builder to construct my SPQuery it suddenly all failed, and all items in the SPList was returned.

To solve this issue, just remove the <Query> and </Query> tags from the generated query that U2U CAML Query Builder produces and it should all work again.

So, replace the above code with this:

SPQuery postsQuery = new SPQuery();
postsQuery.Query = string.Format(
       "<OrderBy>"+
          "<FieldRef Name=’Created’ />" +
       "</OrderBy>"+
       "<Where>"+
          "<Eq>"+
             "<FieldRef Name=’ThreadID’ />"+
             "<Value Type=’Number’>{0}</Value>"+
          "</Eq>"+
       "</Where>", threadID);

SPList allPosts = SpecificForumData.GetPostList(); // Custom method to get the SPList object
SPListItemCollection posts = allPosts.GetItems(postsQuery);

If all is well, you should now be able to run this query to get better performance AND return only the selected SPListItem objects from the SPList

Edit: As a result of Peter’s comment, I’d also like to mention that the RowLimit of an SPQuery object is set to 100 per default, so if you do not alter the .RowLimit property you’ll end up with a result set of maximum 100 items. Thanks Peter

Hope it helps someone :)