3.  Advanced queries using LINQ to SharePoint

The LINQ-statements can be written like regular SQL-statements with some small adjustments. You might have noticed that the “from”-clause comes before the “select”-clause. This allows you to use the Visual Studio 2008’s Intellisense. Since it knows what table (in this case, what list) you wish to use; it can auto complete any properties or parameters you wish to set.  

Continuing on the LINQ to SharePoint example, I’m going to adjust the query so it will only select the books that have less than 500 pages and order the result by the number of pages in descending order.  

var res = from b in ctx.Books
          orderby b.NumberOfPages descending
          where b.NumberOfPages < 500
          select new { b.Title, b.Author, b.NumberOfPages}; 

Executing this query gives the following result:

{ Title = Design Patterns in C#, Author = Steven John Metsker, NumberOfPages = 456 }
{ Title = Common Language Runtime, Author = Steven Pratschner, NumberOfPages = 378 }  
 

You can add as many specifications to the “where”-clause as you wish by using the “&&”-operand: 

var res = from b in ctx.Books
          orderby b.NumberOfPages descending
          where b.NumberOfPages < 500 && b.Title.Contains("C#")
          select new { b.Title, b.Author, b.NumberOfPages};

output:

{ Title = Design Patterns in C#, Author = Steven John Metsker, NumberOfPages = 456 }

When sending a query to the SharePoint site, it is transformed into a language SharePoint understands, called CAML (Categorical Abstract Machine Language). This language is XML-based and is used to customize and build SharePoint-sites. You can view this CAML-code by using the “Log”-property in your project:

ctx.Log = Console.Out;

The CAML-code for the query above looks like this:

Query for books through the web services data provider...

<Query>
  <Where>
    <And>
      <Lt>
        <Value Type="Number">500</Value>
        <FieldRef Name="number_x0020_of_x0020_pages" />
      </Lt>
      <Contains>
        <FieldRef Name="Title" />
        <Value Type="Text">C#</Value>
      </Contains>
    </And>
  </Where>
  <OrderBy>
    <FieldRef Name="number_x0020_of_x0020_pages" Ascending="FALSE" />
  </OrderBy>
</Query>
<ViewFields>
  <FieldRef Name="Title" />
  <FieldRef Name="Book_x0020_Title" />
  <FieldRef Name="number_x0020_of_x0020_pages" />
</ViewFields>
 

Notice the ViewFields-tag which defines the columns you selected. This will also make sure only these are outputted. CAML’s Query Schema can be viewed  here (http://msdn2.microsoft.com/en-us/library/ms467521.aspx ). 
 

LINQ to SharePoint requires leaf-level conditions (i.e. conditions without Boolean operators) to written in a fixed format with only one reference to an entity type property. It's invalid to have more than one entity property reference in a leaf-level condition. “…where b.Title.Contains("C#")” is correct and “…where b.Title.Contains(b.Author)” is invalid.

Calculations should occur on the value side of the condition:

“…where b.NumberOfPages  < 250 * 2 ” is invalid.

The correct way is: “…where b.NumberOfPages / 2 < 250 ”
 

The condition itself can be in inverse order, so: “…where b.Author == “Scott Richter” “and “ “Scott Richter” == b.Author ” are both valid conditions in a LINQ-statement.
 

The following methods on System.String are supported in LINQ to SharePoint: StartsWith( string ), Contains( string ) and Equals( string ).Excessive ToString calls are stripped off automatically when using == or != comparisons:
 

b.Author.ToString().ToString() == "Jeffrey Richter" becomes b.Author == "Jeffrey Richter"
 

Entity properties that have been marked as Nullable because it aren't reference types and the field is not defined as required in the SharePoint list definition can be checked for null values in two ways:
 

u.Age.HasValue
u.Age != null
 

To reference the value of the nullable property, two approaches exist as well:
 

u.Age == 24
u.Age.Value == 24
 

Choice and MultiChoice fields are mapped on enum types by the SPMetal tool. Each CHOICE from SharePoint is mapped on a field in the target enumeration, possibly decorated with a ChoiceAttribute to indicate a different underlying name (for example [Choice("Laurel & Hardy")] will be applied on an enum field LaurelHardy). MultiChoice fields (represented by radio buttons in SharePoint) are mapped on a [Flags] enumeration where all values are powers of two to allow bitwise combination.
 

[Flags]
enum FavoriteFood
{
   Pizza = 1,
   Lasagna = 2,
   Hamburger = 4
}

enum MembershipType
{
   Gold,
   Silver,
   Bronze
}

Conditions on Choice fields should look like this:
 

u.MembershipType == MembershipType.Silver
u.MembershipType != MembershipType.Gold

and are translated into <Eq> or <Neq> CAML conditions. Comparison operators like <, <=, > and >= won't trigger compilation or runtime errors but shouldn't be used.
 

Conditions on MultiChoice fields should look like this:
 

u.FavoriteFood == FavoriteFood.Pizza
u.FavoriteFood != FavoriteFood.Pizza
u.FavoriteFood == (FavoriteFood.Pizza | FavoriteFood.Lasagna)
 

Warning: There's a semantic mismatch between LINQ queries for MultiChoice fields and what you normally expect in C#.
 

The first condition means that Pizza should be one of the choices applied for the list item. To that respect, it's equivalent to the (u.FavoriteFood & FavoriteFood.Pizza) == FavoriteFood.Pizza syntax normally used to check enumeration flags. This syntax isn't supported though. In a similar fashion, the second condition means that Pizza shouldn't be in the list of favorite foods of the list item; it doesn't restrict any other values though. The last condition is equivalent to u.FavoriteFood == FavoriteFood.Pizza || u.FavoriteFood == FavoriteFood.Lasagna but again it doesn't rule out the presence of other choices on the list item. If you want to use an absolute equality check, it should be written manually. For example, to find people who only like Pizza (and nothing but that), you'd have to write u.FavoriteFood == FavoriteFood.Pizza && u.FavoriteFood != FavoriteFood.Lasagna && u.FavoriteFood != FavoriteFood.Hamburger.
 

Fields with fill-in choices will have an additional mapping field of type string that can be null (no fill-in choice made) or set to some string. This mapping field is cross-linked from the original field using the OtherChoice property of the FieldAttribute mapping, like this:
 

    /// <summary>
    /// Favorite food
    /// </summary>
    [Field("Favorite food", FieldType.MultiChoice, Id = "c48610a1-098e-438c-9f77-6e65c6a392cb", OtherChoice = "FavoriteFoodOther")]
    public FavoriteFood? FavoriteFood { get; set; } 

    /// <summary>
    /// Favorite food 'Fill-in' value
    /// </summary>
    [Field("Favorite food", FieldType.Text, Id = "c48610a1-098e-438c-9f77-6e65c6a392cb")]
    public string FavoriteFoodOther { get; set; }
 

The fill-in choice entity property can used in queries too:
 

u.FavoriteFoodOther == "Steak"
u.FavoriteFoodOther != "Steak"
 

Again, the semantic mismatch applies and putting a condition on the fill-in choice doesn't say anything about the possible presence of other choices. Therefore, the first condition will retrieve all the people who like Steak but necessarily only Steak. The last condition retrieves everyone who doesn't like Steak.
 

In the current implementation, the fill-in choice field can also be used to put restrictions on known choice values. For example, you could rewrite u.FavoriteFood == FavoriteFood.Pizza with u.FavoriteFoodOther == "Pizza". To this respect, the 'Other' suffix on fill-in choice fields is a bit of a misnomer. This flexibility allows the list definition to be extended with new CHOICE values, without having to change the code. For example, u.FavoriteFoodOther == "Steak" will keep working even when Steak is added as a recognized pre-defined CHOICE value on the field.

Boolean negation isn't supported directly in CAML, but LINQ to SharePoint knows how to invert most of the supported comparison operators and implements De Morgan's laws to transform Boolean conditions with negations into a negation-less equivalent. A few examples: 

·         !(u.Age == 24) becomes u.Age != 24 (<Neq>...</Neq>)

·         !(u.Age <= 24) becomes u.Age > 24 (<Lt>...</Lt>)

·         !(u.Age > 24 && u.FirstName == "Bart") becomes u.Age <= 24 || u.FirstName != "Bart" (<Or><Leq>...</Leq><Neq>...</Neq></Or>)

·         !(u.Age >= 24 || !(u.FirstName == "Bart" && u.AccountBalance < 1234)) becomes u.Age < 24 && (u.FirstName == "Bart" && u.AccountBalance < 1234) (<And><Lt>...</Lt><And><Eq>...</Eq><Lt>...</Lt></And></And>)

Negation of the BeginsWith and Contains operators isn't supported though.