Query multiple lists in a provider hosted app

Question

I'm currenlty working on a provider hosted app that is hosted in Azure. In this app I need information with multiple lists in multiple sites, which are all in the same sitecollection.I'm basically looking for something like the SPSiteDataQuery. However for as far as I can see, it is only possible to query one list at a a time with CSOM. This would mean that I have to loop trough all sites and all lists to get my data. Is there any way to overcome this issue?

Answer 1

The CSOM does not provide a mechanism for querying data across multiple lists that are not associated by a lookup field. In other words, there is no client-side functional equivalent of the SPSiteDataQuery class.

Using LINQ for Objects

When you use the CSOM, you can write LINQ queries against client-side objects, such as lists and Webs, and then use the ClientContext class to submit these queries to the server.

Example:

using (var context = new ClientContext(url))
{
    //context.Credentials = credentials;

    var query = from web in context.Site.RootWeb.Webs
                where web.WebTemplate == "STS"
                select web;

    var result = context.LoadQuery(query);
    context.ExecuteQuery();


    foreach (var web in result)
    {
        var listQuery = from list in web.Lists
                    where list.BaseType == BaseType.DocumentLibrary
                    select list;
        var listresult = context.LoadQuery(listQuery);
        context.ExecuteQuery();

        foreach (var list in listresult)
        {
            Console.WriteLine("Web:{0} List:{1}",web.Title,list.Title);
        }
    }   
}

Search based approach

Search in SharePoint 2013 includes a client object model that enables access to search results for online, on-premises, and mobile development. The Search in SharePoint 2013 CSOM is built on the SharePoint 2013 CSOM.

Example:

using (var context = new ClientContext(url))
{
    var query = new KeywordQuery(context);
    query.QueryText = "ContentType:Document";
    query.RowLimit = 8;
    query.SelectProperties.Add("Title");
    var results = new SearchExecutor(context).ExecuteQuery(query);
    context.ExecuteQuery();
}

Reference: Using the SharePoint 2013 search Query APIs

Answer 2

Yes it is possible! I don't know of a CSOM equivalent to SPSiteDataQuery, unless you want to explore the Search capabilities of CSOM.

Below you'll find an example of how to do it. I'm sure there exist a more efficient way of doing this, but this is one way I could think of:

var context = new ClientContext("http://MyMagicalUrl");
var currentWeb = context.Web;

// Get all webs from currentWeb object
context.Load(currentWeb, website => website.Webs);
context.ExecuteQuery();

// Now you can loop through all the webs
foreach (Web web in currentWeb.Webs)
{
    // Remember, only get what you need
    var listCollection = context.Load(web.Lists, lists => lists.Include(list => list.Title,list => list.Id));
    context.ExecuteQuery();

    // Now you can do what you want using the list info.
}

Answer 3

I needed this information in a clientwebpart so I ended up with a setting to limited the sites I need to query and used the normal CSOM method. I still have to execute the query on multiple lists so it is still not optimal, but at least the performance is acceptable. Code looks like this.

string siteToQuery = "Global";
SharePointContext spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext.Current);
using (ClientContext ctx = spContext.CreateUserClientContextForSPHost())
{ 
    var webQuery = ctx.Site.RootWeb.Webs.Where(i => i.Title == siteToQuery).Include(i => i.Webs);
    var webQueryResult = ctx.LoadQuery(webQuery);
    ctx.ExecuteQuery();

    if (webQueryResult.Count() == 1)
    {
      Web parentWeb = webQueryResult.Single();

      CamlQuery camlQuery = new CamlQuery();
      camlQuery.ViewXml = this.GetQuery();

      foreach (Web web in parentWeb.Webs)
      {
        List list = web.Lists.GetByTitle("Pages");       

        Microsoft.SharePoint.Client.ListItemCollection items = list.GetItems(camlQuery);
        ctx.Load(items);
        ctx.ExecuteQuery();

        foreach (var item in items)
        {
          //do stuff
        }
      }
    }
}