Export (0) Print
Expand All

Unsupported LINQ Queries and Two-stage Queries

SharePoint 2010

Last modified: September 22, 2011

Applies to: SharePoint Foundation 2010

In this article
Unsupported Queries
Two-Stage Queries
When the Distinction Does Not Matter

This topic describes certain kinds of Language Integrated Query (LINQ) queries that are not supported by the LINQ to SharePoint provider. It also describes how a two-stage process is used for queries that use LINQ operators that cannot be translated by the LINQ to SharePoint provider into Collaborative Application Markup Language (CAML).

Some kinds of queries that are made available in the LINQ syntax of C# and Visual Basic and that work just fine in some contexts for certain kinds of data sources create unacceptably poor performance when used against the data in a Microsoft SharePoint Foundation Web site. Such queries are not supported by the LINQ to SharePoint provider. Specifically, these include any query that requires more than one separate query to the content database. For example, if a query to table A effectively requires a separate query to Table B for every row of Table A, then the query to table A is not supported. Similarly, an attempt to use the LINQ union keyword as shown in the following example is not supported and would throw an exception because it requires separate queries to two lists.


DataContext data = new DataContext("http://ContosoServer");            DataContext juniorData = new DataContext("http://ContosoServer/JuniorTeamSite");

EntityList<Announcement> Announcements = data.GetList<Announcement>("Announcements");
EntityList<Announcement> JuniorAnnouncements = juniorData.GetList<Announcement>("Announcements");

var seniorAnns = from ann in Announcements
                 select ann;

var juniorAnns = from ann in JuniorAnnouncements
                 select ann;

IEnumerable<Announcement> mergedAnns = seniorAnns.union(juniorAnns);

foreach (Announcement ann in mergedAnns)
{
    Console.WriteLine(ann.Title);
}

One implication of this rule is that queries that assume an implicit join between two SharePoint Foundation lists are supported if and only if the joining field is configured as a lookup field.

Some LINQ queries cannot be completely translated into CAML. However, however such queries can, in principle, run correctly because they can be executed in two stages. First, the LINQ to SharePoint provider translates as much of the query into CAML as it can and executes that query. This produces a superset of the data which is passed from the content database back to the front-end Web server as an IEnumerable<T> object. Second, the remaining parts of the query are recomposed as a LINQ to Objects query against the IEnumerable<T> object. (The first stage, however, must be executable with a single query to the content database or the whole query throws an exception.

As an example of a one-stage query, consider a LINQ select clause such as "select new { c.Name, c.ZipCode }". This can be translated into CAML as a <ViewFields> tag with two <FieldRef> child elements. But the clause "select new { c.Price*2, c.Orders, c.Customer }" contains a mathematical function which is not supported in CAML. So, the query is translated by LINQ to SharePoint and executed, but only up to the select clause. Then the results that are received from the CAML query are sent to the front-end Web server as an IEnumerable<T> object. A new LINQ query on the front-end Web server then executes the select clause projection on the object by using the Enumerable.Select() method of LINQ to Objects.

There is no simple way to specify LINQ operators that are unsupported or that require two stages. For example, the Union() operator, as noted above, is not supported if both of the data sources that are being merged are lists from the content database. But if only one of them is a SharePoint Foundation list and the other is, say, a table that is already in memory, then the Union() can be used.

For the most part, query methods that require mathematical operations require two stages, but there are exceptions when LINQ to SharePoint can get a value from the SharePoint Foundation object model. For example, the Count() and LongCount() methods do not require splitting the query into two stages.

The following operators usually require two stages and normally work, provided that the first stage requires only one query of the content database.

If you are writing the output of the CAML-translatable parts of the query into an IEnumerable<T> object anyway, by means of the ToList<TSource>(IEnumerable<TSource>), ToDictionary(), or ToArray<TSource>(IEnumerable<TSource>) methods, then the portion of your query that comes after the call of one of these methods is using the query operators from the Enumerable class, rather than the Queryable class. In that case, the distinction between two-stage and one-stage queries does not matter.

For example, contrast the following two queries. Both would require two stages because the mathematical operator "-" is used; but the second query makes this explicit because LINQ to SharePoint is used only in the from line, which reads the entire list "orders" into an IList<T>.


var ordersInArrears = from order in orders
                      where order.Price – order.Paid > 0
                      select order;

var ordersInArrears = from order in orders.ToList()
                      where order.Price – order.Paid > 0
                      select order;

Manually splitting your queries into a LINQ to SharePoint part and a LINQ to Objects part can also give you a way to use LINQ keywords that would otherwise be unsupported. For example, the following version of the attempt to merge two sets of announcements would work, because the LINQ to SharePoint queries are enumerated separately into two IEnumerable<T> objects. The union keyword being used is the LINQ to Objects version of Union().


DataContext data = new DataContext("http://ContosoServer");            DataContext juniorData = new DataContext("http://ContosoServer/JuniorTeamSite");

EntityList<Announcement> Announcements = data.GetList<Announcement>("Announcements");
EntityList<Announcement> JuniorAnnouncements = juniorData.GetList<Announcement>("Announcements");
List<Announcement> seniorAnns = (from ann in Announcement
                                 select ann).ToList();

List<Announcement> juniorAnns = (from ann in JuniorAnnouncements
                                 select ann).ToList();

IEnumerable<Announcement> mergedAnns = seniorAnns.union(juniorAnns);

foreach (Announcement ann in mergedAnns)
{
    Console.WriteLine(ann.Title);
}

Reference

Select

Other Resources

Show:
© 2014 Microsoft