Second Life of a Hungarian SharePoint Geek

February 9, 2011

How to query external lists on the client side using CAML?

Filed under: BCS, CAML, Managed Client OM, SP 2010 — Tags: , , , — Peter Holpar @ 08:05

One of the great features of SharePoint 2010 BCS External List concept is that it enables you to publish and access external data similar way as in the case of standard SharePoint list (I wrote similar, because there are significant exceptions as well). It includes the programmatic access either.

Despite of the similarities, I have found forums questions that show it is not always trivial to achieve your goals. In this post I show you a code example that illustrates how one can filter the external data using managed client object model through a  CAML query. For this example I’ve created an external list on my server that connects to the Customers table of the well-known Northwind database. You can find several posts about how to create the external content type (ECT) for you external list, for example here.

Once you have your external list created (called Northwind Customers in my case), you can try this code from your client project:

  1. String siteUrl = "http://sp2010";
  2.  
  3. ClientContext clientContext = new ClientContext(siteUrl);
  4.  
  5. List list = clientContext.Web.Lists
  6.     .GetByTitle("Northwind Customers");
  7. CamlQuery camlQuery = new CamlQuery();
  8. // When using a CAML query for external list
  9. // one should include all referenced fields in ViewFields
  10. // otherwise a "The given key was not present in the dictionary" exception is thrown
  11. // RowLimit seems to have no effect            
  12. camlQuery.ViewXml =
  13.     @"<View>
  14.         <Query>
  15.           <Where>
  16.             <Eq>
  17.               <FieldRef Name='City'/>
  18.               <Value Type='Text'>London</Value>
  19.             </Eq>
  20.           </Where>
  21.           <OrderBy>
  22.             <FieldRef Name='ContactName'/>
  23.           </OrderBy>
  24.         </Query>
  25.         <ViewFields>
  26.           <FieldRef Name='CustomerID'/>
  27.           <FieldRef Name='ContactName'/>
  28.           <FieldRef Name='CompanyName'/>
  29.           <FieldRef Name='City'/>
  30.         </ViewFields>
  31.         <RowLimit>1</RowLimit>
  32.     </View>";
  33.  
  34. // include referenced field here as well
  35. ListItemCollection listItems = list.GetItems(camlQuery);
  36. clientContext.Load(
  37.      listItems,
  38.      items => items
  39.          .Include(
  40.             item => item["CustomerID"],
  41.             item => item["CompanyName"],
  42.             item => item["ContactName"],
  43.             item => item["City"]));
  44. clientContext.ExecuteQuery();
  45.  
  46. // display the result
  47. foreach (ListItem listItem in listItems)
  48. {
  49.     Console.WriteLine("CustomerID: '{0}', CompanyName: '{1}', ContactName: '{2}'",
  50.         listItem.FieldValues["CustomerID"],
  51.         listItem.FieldValues["CompanyName"],
  52.         listItem.FieldValues["ContactName"]);
  53. }
  54.  
  55. Console.Write("Press Enter to continue!");
  56. Console.ReadLine();

The code filters customers that have London specified in the City field and order them by the value of the ContactName field.

Important things to note:

  • You must include all fields you use either to filter, order in CAML query, or to display later in your code into both the ViewFields node of CAML query as FieldRef and load into the ClientContext as illustrated above. Otherwise you will receive a "The given key was not present in the dictionary" exception when referencing the field later.
  • Specifying the RowLimit seems to have no effect, all of the matching items are returned by the query (I found similar info in this forum thread). That is not very nice when working with external lists having large item count.
Advertisements

6 Comments »

  1. Do you know of any javascript to accomplish this same outcome?

    Comment by Chris — August 8, 2011 @ 18:22

    • Chris, you should be able to achieve the same using the JavaScript client object model, but I don’t have a ready example to illustrate how to do that. If my time allows that I try to put one together.

      Comment by Peter Holpar — August 10, 2011 @ 21:30

  2. Thanks for the info. Does working in Silverlight and executing query async make any difference? I can’t seem to get the clientContext.Load(listItems,
    items => items
    .Include(
    item => item[“CustomerID”],…)
    as it does not recognize the second parameter of the query with an error
    “Cannot convert lambda expression to type ‘System.Linq.Expressions.Expression<System.Func>[]’ because it is not a delegate type”

    Comment by boris — August 19, 2011 @ 10:14

  3. Hi Peter,

    I tried your solution, but it still doesn’t seem to work when I have Filters enabled for my External Content Type.

    The moment filters are enabled for the External Content Type, SharePoint seem to ignore whatever query is passed to it in the CAML query and simply return the results that are filtered by the default filter set up in SharePoint.

    Have you encountered this before?

    Comment by JD — April 5, 2012 @ 01:56

  4. Hii, Greetings for the day,
    Can i apply join between sharepoint list and external list?

    Comment by Swapnill — September 24, 2012 @ 13:44


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: