LINQ to SharePoint cross list query

In SharePoint 2010 we already have the LINQ to SharePoint support that is a great improvement over the traditional SPListItem + CAML approach for exposing and modifying SharePoint list data. The built-in LINQ to SharePoint implementation is a complete ORM framework which provides full support for CRUD operations.

But in SharePoint there is one other method for retrieving list data – this is the cross list query support that is available through the SPWeb.GetSiteData method. The cross list query provides means to fetch list data from many lists in the entire site collection simultaneously and can be useful in many scenarios; it is also internally used by the standard Content Query web part (CQWP). Still there is no out-of-the-box support for LINQ to cross list query in SharePoint 2010, so I decided to create a small framework with a light-weight and simple implementation of a LINQ query provider for cross list query CAML. Note here that this solution is focused mainly on the lambda expression to CAML translation and doesn’t use entities that are mapped to SharePoint lists or content types – in this sense it is not an ORM system (you will see that from the demonstration below). Another important note – since the object model support for the cross list query hasn’t changed much in SharePoint 2010, this solution works in both SharePoint 2007 and SharePoint 2010. You can download the code of the custom LINQ framework from here.

And now, let’s jump directly to a small code sample:

using (SPSite site = new SPSite("http://testserver/sites/testsite"))

{

    SPWeb web = site.OpenWeb();

    IQueryable<DataRow> result = web.GetSiteData()

           .Webs(WebScope.SiteCollection)

           .Lists(BaseListType.GenericList)

           .ViewFields(Fields.ID, Fields.Text1.Nullable, Fields.Date1.Nullable, Fields.Bool1.Nullable, Fields.Look1.Nullable, Fields.Numb1.Nullable)

           .CamlWhere(r =>

                Fields.Text1.Value.Contains ("test")

                && (Fields.Look1.LookupId == 1 || Fields.Look1.Value == "test")

                && (Fields.Bool1.Value != true || Fields.Date1.Value > DateTime.Today.AddDays(-2))

            )

           .CamlOrderBy(Fields.Date1.Descending, Fields.Text1)

           .RowLimit(10);

 

    foreach (DataRow row in result)

    {

        Console.WriteLine(Fields.ID[row]);

        Console.WriteLine(Fields.Text1[row]);

        Console.WriteLine(Fields.Numb1[row]);

        Console.WriteLine(Fields.Bool1[row]);

        if (Fields.Look1[row] != null)

            Console.WriteLine(Fields.Look1[row].LookupValue);

    }

}

As you can see the code snippet starts with opening an SPWeb instance and then there is a sequence of extension method calls the end result of which is an IQueryable of DataRow. So there are several important things to note here before going into detail:

• unlike the standard LINQ to SharePoint implementation there are no entity classes here, so instead of IQueryable<SomeEntity> the custom query provider returns always IQueryable<DataRow> (keeping things much closer to the SPWeb.GetSiteData method which returns a DataTable)
• the standard IQueryable extension methods Select, Where, First, FirstOrDefault, etc are not used and are actually not supported. Instead there is a small set of custom extension methods which map directly to the properties of the SharePoint SPSiteDataQuery class (I’ll explain how these can be used below).
• instead of entity classes that you normally generate with the SPMetal utility, a static class is used here (class Fields) which exposes a number of static variables that represent SharePoint site and list fields. A small wrapper class is used for these static variables (whose name is, you guessed it - Field) and it is used in all cases that in the CAML you would use the “FieldRef” element. So this is actually the greatest difference between the standard LINQ to SharePoint and this custom implementation – there are no wrapping classes on the entity level (content type or list level in SharePoint terms) but instead – on the column/field level. This has the drawback of not having your data neatly packed into dedicated classes but on the other hand can give you more flexibility over the static wrappers in terms of the ease with which you can modify the meta-data (the fields that you want to retrieve or use to filter or sort on) of your queries without it being necessary to re-generate the entity classes every time.

And here is the definition of the sample “Fields” class that is used in the first code snippet:

public static class Fields

{

    public static readonly Field<int, int> ID =

        new Field<int, int>("ID", "Integer", SimpleCamlValueConvertor.Instance);

 

    public static readonly Field<string, string> Text1 =

        new Field<string, string>("Text1", "TEXT", SimpleCamlValueConvertor.Instance);

 

    public static readonly DateField<DateTime, DateTime> Date1 =

        new DateField<DateTime, DateTime>("Date1", "DateTime", SimpleCamlValueConvertor.Instance);

 

    public static readonly Field<double, double> Numb1 =

        new Field<double, double>("Numb1", "Number", SimpleCamlValueConvertor.Instance);

 

    public static readonly Field<bool, bool> Bool1 =

        new Field<bool, bool>("Bool1", "Boolean", SimpleCamlValueConvertor.Instance);

 

    public static readonly LookupField<string, SPFieldLookupValue> Look1 =

        new LookupField<string, SPFieldLookupValue>("Look1", "Lookup", SimpleCamlValueConvertor.Instance);

}

So, this is basically sorts of container or placeholder class – it can be named with any suitable name and the important part is the set of static member variables (these can be defined as member properties as well) that represent the SharePoint fields that you want to use in your site data queries. The type of the member variables is the generic Field<TValue, TParsedValue> class, which as I mentioned is used as a small wrapping class for SharePoint list and/or site fields. You can also see in the code sample that two other generic types are used – DateField and LookupField – they actually inherit the Field class (these two are the only inheritors) and provide specific support for the SharePoint date and lookup field types. At this point I haven’t implemented a utility that automatically generates the container class with the mapped “Field” member variables but as you can see it is pretty easy to create one very quickly by hand – it is basically one line of code per field.

And this is the declaration of the generic Field class (this is only the declaration of the class as it appears in the meta-data code view in Visual Studio):

public class Field<TValue, TParsedValue> : Field

{

    public Field(string name, string type, ICamlValueConvertor convertor);

    public TValue Value { get; }

    public virtual TParsedValue this[DataRow item] { get; }

}

as you see, it inherits a non-generic class also named “Field” (which is also an abstract class):

public abstract class Field

{

    public Field(string name, string type, ICamlValueConvertor convertor);

    public Field Descending { get; }

    public Field Nullable { get; }

}

The two classes are very simple with just a couple of properties, a constructor and a custom indexer. As for the separation in two classes – a base one and an inheritor (which is also a generic class) – you will see the logic behind this design solution in a while.

So, let me now give you a brief explanation about the members of the “Field” classes:

• first – the constructor – it takes two string parameters in which you should provide the name (the internal name actually) and CAML type (as it appears in the Type attribute of the CAML “Value” element) of the SharePoint field, and a third parameter of type ICamlValueConverter – this is a custom interface type defined in this custom LINQ framework. You don’t have to know details about this interface, since there is already a class that implements it which you can use directly (I will briefly give you some details about the interface below). And here is a sample usage:
  
  new Field<string, string>("Text1", "TEXT", SimpleCamlValueConvertor.Instance);
  
  the field in this case is a single line text field - “Text1” is its internal name and “TEXT” is the CAML type that should be used for it. For the third parameter you can see that the SimpleCamlValueConvertor class is used (this is a singleton class, available through its “Instance” static property).
• the generic “Value” property – this property has one purpose only – it should be used only in the custom CamlWhere extension method, inside its lambda expression parameter. It is this expression tree actually that will be translated to the CAML “Where” element. If you have for example this comparison expression with the Value property:
  

  
  Fields.Bool1.Value != true

  it will get translated to this CAML:
  
  

  <Neq>

    <FieldRef Name="Bool1" />

    <Value Type="Boolean">1</Value>

  </Neq>

  The exact .NET type of the “Value” property is determined by the first generic type parameter of the Field<TValue, TParsedValue> class. Basically only five .NET types can be used for it – string, bool, int, double and DateTime, depending on the underlying SharePoint field type. For all SharePoint field types whose field value type is not a primitive .NET type, the System.String type should be used – including the lookup, multi lookup, multi column, multi choice, etc. field types – this is because of the limitations of the CAML syntax which doesn’t allow more complex comparison operations.
• the generic indexer that can be used to extract typed values from the DataRow instances returned when the IQueryable gets enumerated (see above in the first code snippet). The exact .NET type of the return value is determined by the second generic type parameter of the Field<TValue, TParsedValue> class. You can provide the corresponding field value type here which depending on the field type of the underlying SharePoint field can be SPFieldLookupValue, SPFieldLookupValueCollection, SPFieldMultiColumnValue, etc, as well as just a simple .NET value type like int, double, bool. The converting of the DataRow column value to the exact .NET type is handled normally by the SimpleCamlValueConvertor class, which you provide as a parameter to the Field class constructor. You can use a custom class here too that implements the ICamlValueConvertor interface if you want to have some different converting logic. Note that the SimpleCamlValueConvertor class doesn’t support “nullable” types like Nullable<int>, Nullable<double>, etc (or with the short-hand notation: int?, double?, etc), and for fields whose value type is a .NET value type when the underlying list item contains a null value in the corresponding column, the indexer will return the default value for that .NET value type (DateTime.MinValue for System.DateTime, false for System.Boolean, 0 for System.Int32 and 0.0 for System.Double).
• “Descending” property – this property whose type is the abstract Field class should be used in the custom CamlOrderBy extension method only:
  
  

  CamlOrderBy(Fields.Date1.Descending, Fields.Text1)

  
  the idea is that when you provide only a Field member variable only (like Fields.Text1) the sorting will be on that column ascending and if you use the “Descending” property of the Field member variable (Fields.Date1.Descending) then the sorting will be descending. The above method call will be translated into this CAML:
  
  

  <OrderBy>

    <FieldRef Name="Date1" Ascending="FALSE" />

    <FieldRef Name="Text1" Ascending="TRUE" />

  </OrderBy>

• “Nullable” property – this is similar to the “Descending” property, its type is again the abstract Field class and it is intended for use only in the “ViewFields” custom extension method:
  
  

  ViewFields(Fields.ID, Fields.Text1.Nullable);

  
  and this method call will be translated to this CAML:
  
  

  <ViewFields>

    <FieldRef Name="ID" Nullable="FALSE" />

    <FieldRef Name="Text1" Nullable="TRUE" />

  </ViewFields>

And a quick look at the LookupField and DateField classes which inherit the Field<TValue, TParsedValue> class and each of which adds one extra member property:

• the “LookupId” property of the LookupField class – this is an integer property that can be used only in the lambda expression parameter of the custom CamlWhere extension method (like the “Value” member property of the Field class):
  
  

  .CamlWhere(r =>

     Fields.Look1.LookupId == 1 || Fields.Look1.Value == "test")

  which will translate to:
  
  

  <Where>

    <Or>

      <Eq>

        <FieldRef Name="Look1" LookupId="TRUE" />

        <Value Type="Lookup">1</Value>

      </Eq>

      <Eq>

        <FieldRef Name="Look1" />

        <Value Type="Lookup">test</Value>

      </Eq>

    </Or>

  </Where>

  as you can see, you can use both the “Value” and the “LookupId” properties of the LookupField class inside the lambda expression but they have different semantics – when you use the “LookupId” property the generated CAML contains the extra “LookupId” attribute in the “FieldRef” element. This means that when you use the “Value” property the comparison will be on the value of the lookup field (show field) in the lookup list and when you use the “LookupId” property the comparison will be on the ID of the lookup list item (the referenced item in the lookup list).
• the “DateValue” property of the DateField class – this is again a property that can be used only in the lambda expression parameter of the custom CamlWhere extension method. The difference between the “Value” and “DateValue” properties of the DateField class is that when you use the “Value” property the comparison will be on the full date-time value, while for the “DateValue” property the comparison will be on the date part only of the DateTime argument (the CAML will contain the extra “IncludeTimeValue” attribute when you use the “Value” property).

And here are the declarations of the custom extension methods:

public static class SPWebExtensions

{

    public static IQueryable<DataRow> GetSiteData(this Microsoft.SharePoint.SPWeb web);

}

public static class SiteDataExtensions

{

    public static IQueryable<DataRow> CamlOrderBy(this IQueryable<DataRow> items, params Field[] fields);

    public static IQueryable<DataRow> CamlWhere(this IQueryable<DataRow> source, Expression<Func<DataRow, bool>> predicate);

    public static IQueryable<DataRow> Lists(this IQueryable<DataRow> items, BaseListType listType);

    public static IQueryable<DataRow> Lists(this IQueryable<DataRow> items, params Guid[] listIDs);

    public static IQueryable<DataRow> Lists(this IQueryable<DataRow> items, string serverTemplate);

    public static IQueryable<DataRow> Lists(this IQueryable<DataRow> items, BaseListType listType, bool hidden, int maxListLimit);

    public static IQueryable<DataRow> Lists(this IQueryable<DataRow> items, bool hidden, int maxListLimit, params Guid[] listIDs);

    public static IQueryable<DataRow> Lists(this IQueryable<DataRow> items, string serverTemplate, bool hidden, int maxListLimit);

    public static IQueryable<DataRow> RowLimit(this IQueryable<DataRow> items, uint rowLimit);

    public static IQueryable<DataRow> ViewFields(this IQueryable<DataRow> items, params Field[] viewFields);

    public static IQueryable<DataRow> Webs(this IQueryable<DataRow> items, WebScope webScope);

}

• SPWebExtensions.GetSiteData – this is the method that you will use first to get a reference of IQueryable<DateRow> that you can then use with the extension methods defined in the SiteDataExtensions class. With the standard SharePoint 2010 LINQ support we have the “context” class which exposes public properties that return IQueryable for the different entities that you have generated. And with this custom LINQ implementation you see that it is much simpler – just one extension method that you use on a SPWeb instance. And actually this method has the same name as the standard SPWeb.GetSiteData method (which accepts a SPSiteDataQuery parameter), but this one is parameterless. After you have the IQueryable<DateRow> instance you can then call the extension methods defined in SiteDataExtension which directly map to the properties of the standard SPSiteDataQuery class, but are much easier to use, because you don’t have to use the cumbersome CAML syntax:
• SiteDataExtensions.CamlOrderBy – this method generates the “OrderBy” element of the CAML that is provided to the SPSiteDataQuery.Query property. It accepts a “params” array parameter of the abstract “Field” class which you can provide as “Field” static member variables of your “container” class (class Fields in the sample above) or their “Descending” properties if you want to use descending sorting (for sample code see the paragraph about the Field.Descending property).
• SiteDataExtensions.CamlWhere - this method generates the “Where” element of the CAML that is provided to the SPSiteDataQuery.Query property. Note that it has the same notation as the standard Queryable.Where extension method (I could have just as well used the latter instead, but with all other custom extension methods, I decided to have all of these custom). It is actually the expression tree that the .NET framework generates from its lambda expression parameter that gets translated to the “Where” CAML. Note that since the resultant CAML is with very limited comparison expression capabilities the custom Queryable provider of this implementation supports very few .NET methods and operators:
  • the binary operators “&&” (AndAlso) and “||” (OrElse) (that map to CAML “And” and “Or”)
  • the binary comparison operators “==” (Equal), “!=” (NotEqual), “>” (GreaterThan), “>=” (GreaterThanOrEqual), “<” (LessThan), “<=” (LessThanOrEqual) (these map to CAML “Eq”, “Neq”, “Gt”, “Geq”, “Lt”, “Leq” respectively, for equality/inequality to null: “== null” and “!= null” the corresponding CAML elements are “IsNull” and “IsNotNull”)
  • .NET methods – only two methods are supported – String.StartsWith and String.Contains (these map to CAML “BeginsWith” and “Contains”):
    
    

    Fields.Text1.Value.Contains ("test")

  • No other operators and methods are supported (the unary negation (unary “!”) is not supported either)
  Note also the following restrictions for the expression tree from the lambda expression of the CamlWhere method:
  • one of the operands of the binary comparison operators (no matter whether left or right operand) should always be a “Field” static member variable/property of your container class (class “Fields” from the sample) using its “Value”, “LookupId” or “DateValue” properties (see the sample code above)
  • the other operand of the binary comparison operators should be a constant expression or an expression that doesn’t reference “Field” member variables/properties and that evaluates to the same .NET type as the “Value” property of the “Field” instance from the other operand (note that it should evaluate to the exactly same .NET type otherwise you have to explicitly use type casting for that operand). Note also that you can use null equality/inequality comparison even for .NET value types (e.g. int, double, DateTime) – these will be translated to CAML “IsNull” and “IsNotNull”:
    
    

    Fields.Date1.Value == null

    The operand can contain any type of expressions and methods – the restrictions mentioned above for the allowed operators/methods don’t apply here, for instance you can have some more complex calculations and/or call other custom methods:
    
    

    Fields.Date1.Value > DateTime.Today.AddDays(-2)

  • For “Field” members with Boolean “Value” property you should explicitly use the equality/inequality operator (even though it may seem more economical to omit it):
    
    

    Fields.Bool1.Value == true
    

  It is important to note here that if you violate one of the above mentioned restrictions you will get various exceptions (of the custom ParseLinqException type) during run-time. Note also that the delegate type of the lambda expression parameter accepts a DataRow parameter but this one is not used (and shouldn’t be used) anywhere in the lambda expression’s body.
• SiteDataExtensions.Lists – this method generates the CAML that is provided to the SPSiteDataQuery.Lists property. The method comes with six overloads that provide handy support for the different list modes of the cross list query – either using the base list type, or the list server template or providing an array of SharePoint list IDs.
• SiteDataExtensions.RowLimit – this methods maps to the RowLimit property of the SPSiteDataQuery – it accepts an unsigned integer parameter with which you specify how many list items the query should return.
• SiteDataExtensions.ViewFields – this method generates the CAML that is provided to the SPSiteDataQuery.ViewFields property. It accepts a “params” array parameter of the abstract “Field” class which you can provide as “Field” static member variables of your “container” class (class Fields in the sample above) or their “Nullable” properties if you want the generated “FieldRef” elements to have the “Nullable” attribute added (see the first code sample).
• SiteDataExtensions.Webs – this method generates the CAML that is provided to the SPSiteDataQuery.Webs property.

Quick start with the custom LINQ framework:

And lastly several words of how to use the custom LINQ framework inside your SharePoint projects.
The solution comes in a single assembly - Stefan.SiteData.Linq (you can use it as a separate assembly or to incorporate the code in a custom assembly of yours). The steps to use it are as follows:

1. If you use it as a separate assembly you need to have an assembly reference of it in your SharePoint project.
2. You then need to create the static “container” class and create a static member variable (or property) for every SharePoint field that you will use in your cross list queries (“FieldRef” elements inside “ViewFields”, “Where” and “OrderBy” CAML elements) – these should be of the Field<TValue, TParsedValue>, LookupField<TValue, TParsedValue> or DateField<TValue, TParsedValue> classes type (check the definition of the sample “Fields” class above).
3. You can proceed with your list data retrieving code – after you have an SPWeb instance, you can start with calling the extension SPWebExtensions.GetSiteData extension method to create the IQueryable<DataRow> and then start calling the extension methods of the SiteDataExtensions class so that you specify custom filtering, sorting and view fields that should be included in the result-set (check the first code snippet at the top).
4. Lastly when you start iterating the IQueryable<DataRow> you can make use of the “Field” class indexer so that you get typed values from your SharePoint fields (you can as well directly access the values from the DataRow instances and use them as simple strings). As I mentioned above the conversion logic for the DataRow column string value to specific SharePoint field value .NET types is in the custom SimpleCamlValueConvertor class (you can check its code, it is really simple). You can provide your custom conversion implementation if you create a class implementing the ICamlValueConverter interface:
   
   

       public interface ICamlValueConvertor

       {

           string ConvertToString<T>(T value);

           T ConvertFromString<T>(string value);

       }

   It is the ConvertFromString generic method that is used for the conversion and its generic parameter determines the return type of the method (this is also the .NET type of the indexer of the “Field” class). You can also inherit the SimpleCamlValueConvertor class and override its ConvertFromString method (it is defined as a virtual method) – then you won’t have to implement the ICamlValueConvertor.ConvertToString method.
5. When you debug your code you will notice that when you hover over the IQueryable<DataRow> variable or add a debug watch for it you will be able to see the full query CAML generated by the custom IQueryable provider which can be very handy for issue troubleshooting.