How to Union 2 Lists Using ClientContext?

Question

I have 2 lists which both contain identically-named fields that I'm trying to access using C#. Using ClientContext, how can I perform something that does the same thing as a SQL Union to give me one result set containing all the results from both lists (or a subset of both using a CAML query)? I know CAML doesn't support UNIONs natively, but if there's a snazzy trick to do this using ListItemCollections or something similar, I'd love to hear!

Thx

Answer 1

Create a defined class or an anonymous type that abstracts away the fact that the list items are from two different lists. This example combines the ID and Title fields from two lists, but you could expand it to include whatever fields you like of any type.

First of all, here's the non-interesting and very simple example init code:

var clientContext = new ClientContext("http://aasp2010:14502/");

var dogList = clientContext.Web.Lists.GetByTitle("Dogs");
var dogItems = dogList.GetItems(new CamlQuery());
clientContext.Load(dogItems);

var catList = clientContext.Web.Lists.GetByTitle("Cats");
var catItems = catList.GetItems(new CamlQuery());
clientContext.Load(catItems);

clientContext.ExecuteQuery();

Now, this is my interpretation of something snazzy. It uses an anonymous type to union the values from the two lists together:

var pets = (from dogItem in dogItems.AsEnumerable()
            select new
                    {
                        ID = (int) dogItem["ID"],
                        Title = dogItem["Title"] as string
                    })
    .Union(from catItem in catItems.AsEnumerable()
           select new
                    {
                        ID = (int) catItem["ID"],
                        Title = catItem["Title"] as string
                    });

(Note: The AsEnumerable() call is required or else LINQ will try to use Microsoft.SharePoint.Client.ClientQueryable.GetEnumerator() which throws a NotSupportedException.)

Just to show there's nothing up my sleeve:

foreach (var pet in pets)
{
    Console.WriteLine(pet.ID + ": " + pet.Title);
}