.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> SharePoint >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Querying SharePoint the smart way

Posted By:Manning       Posted Date: January 30, 2011    Points: 75    Category: SharePoint    URL: http://www.dotnetspark.com  

Many Web Parts built will query SharePoint for information from lists or sites. There are several ways to query SharePoint for information. Each query will have different performance implications. This is especially important when working with large lists
 

This article is taken from the book SharePoint 2010 Web Parts in Action. The author shows you different ways to query a standard Tasks list for all items that have the status set to Completed.


Get
40% off any version of SharePoint 2010 Web Parts in Action with the checkout code dnspark40. Offer is only valid through www.manning.com.


Many Web Parts built will query SharePoint for information from lists or sites. There are several ways to query SharePoint for information. Each query will have different performance implications. This is especially important when working with large lists. To illustrate the differences, I will show you how to query a standard Tasks list for all items that have the status set to Completed.
Using the object model to query

The easiest , but worst performing way to query a SharePoint list is to use the SharePoint object model and iterate over the objects and check the fields for the data. The following snippet shows how to iterate over the Tasks list items using a foreach statement:

SPWeb web = SPContext.Current.Web;
SPList tasks = web.Lists["Tasks"];
foreach (SPListItem task in tasks.Items) {
  if (task["Status"].ToString() == "Completed") {
  Label1.Text += task.Title;
  }
}

The Tasks list is retrieved from the current SPWeb object before iterating over all items. To get the correct items a string-comparison is done on the Status field.

For a very small list this works fine but as the list grows, this method will perform slowly. First, when iterating over the items in the list, the complete list item objects are retrieved from the database and read into memory. Second, the fields are not strongly typed, which requires that you must programmatically convert the fields into the correct type. This method will also make your code more error prone.

Querying using LINQ to SharePoint


SharePoint 2010 introduces a tool that can create strongly typed objects of your lists and list items called SPMetal. This tool's generated code allows you to use LINQ to query the SharePoint objects. As a result, queries are easier to write and you do not have to consider converting types.

SPMetal command line tool

SPMetal is a tool that generates entity classes to provide an object-oriented way to work with the objects within a SharePoint site. The tool, SPMetal.exe, is available in SharePoint Foundation and is located in {SharePoint Root}\BIN.

This snippet shows how the previous object model-oriented listing is done using the entity classes generated by SPMetal and LINQ queries.

using (SPMetalDataContext context = new SPMetalDataContext(
  SPContext.Current.Web.Url)) {

  var tasks = from task in context.GetList("Tasks")
  where task.TaskStatus == TaskStatus.Completed
  select task.Title;

  foreach (var title in tasks) {
  Label1.Text += title;
  }
}

A data context, which is created using the current web is needed to use the entity classes. A LINQ query is performed using the Tasks list, where all completed tasks are sorted and all titles of these tasks are returned.
 
Using the entity classes and LINQ, the query can use strongly typed properties, which reduces the number of errors in the code. Behind the scenes, LINQ converts the query to a Collaboration Markup Language (CAML) query that queries the database and returns only the items that match the query. This reduces the amount of items in the memory for this Web Part and the query is by magnitude faster than manually iterating the items. If the list contains a large number of items, this method is a better performer than the object model query.

The downside to using LINQ with SharePoint is that it requires that the LINQ runtime be loaded, which makes the first call slower compared to the succeeding ones. This method also requires that more objects and classes be loaded into memory. If you are working with anonymous users, for instance, for public facing web sites, you should know that LINQ to SharePoint does not currently support anonymous users.

Manually creating CAML queries


The best performing way to query lists is to create the CAML queries--the same query created by LINQ--manually. CAML can be hard to learn at first but, in high-performance scenarios, it's worth the trouble. Visual Studio lacks good tools and support for building CAML queries, so it's easy to make mistakes when writing them yourself.

Use the CAML generated by LINQ to SharePoint

The SharePoint DataContext object used in LINQ to SharePoint contains a property called Log. This property can be set to any TextWriter object such as the Console.Out or the return value from the File.CreateText method. All queries executed in the DataContext will then be written to the TextWriter as CAML. You can use this logged CAML and use in your manual CAML queries.
The following snippet shows how to create the same query as above using a manual CAML query.


SPQuery query = new SPQuery();
query.Query = @"  #1
  Completed";  #1

SPWeb web = SPContext.Current.Web;
SPList tasks = web.Lists["Tasks"];
SPListItemCollection items = tasks.GetItems(query);  #2

foreach (SPListItem item in items) {
  Label1.Text += item.Title;
}

#1 Create CAML query
#2 Get items using query

A CAML query is created using a SPQuery object and then setting the Query property to the CAML query to be used (#1). The query object is used to return the items from the list (#2).

This approach will perform faster than the previous ones. But it is likely that it takes longer time to develop the query and the chances to introduce errors in the code are higher than using the LINQ to SharePoint approach.
Take a look at the LINQ to SharePoint CAML!

Since the LINQ to SharePoint queries creates CAML behind the scenes, you can start your development using LINQ to SharePoint. When you are done, you can change your Web Part to use the SPQuery approach to get the performance gains. This is done by logging your LINQ to SharePoint generated CAML queries to a log file using the Log property of the LINQ to SharePoint DataContext.

Summary


SharePoint 2010 introduces new techniques for querying data such as LINQ to SharePoint. While this might improve the development experience and time, it is not always the way to go when programming for performance. There are different ways to query SharePoint of which all have its pros and cons.


SharePoint 2010 Web Parts in Action
EARLY ACCESS EDITION

Wictor Wilén
MEAP Release: March 2010
Softbound print: December 2010 (est.) | 375 pages
ISBN: 9781935182771


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend