Linq to SharePoint. Part 2

February 06, 2012

Part 1. First()/FirstOrDefault(), T-SQL IN, Path
Part 2. Count(), Take(), Skip(), JOIN, ObjectTracking
Part 3. Anonymous access, Resolve list by URL
Part 4. Dynamic LINQ, Convert SPListItem to data context object
Part 5. Choice and MultiChoice fields

For demonstration i've created a simple project which contains four content types (Employee, Department, Branch and Company), fields for them, lists and classes representing these entities:

Tracing

First of all for testing building of CAML queries it's necessary a tracer. Here is one used by myself:

public class ZhukBlogTracer : TextWriter
{
  public override Encoding Encoding
  {
    get { return Encoding.UTF8; }
  }
 
  public override void WriteLine(string value)
  {
    Debug.WriteLine(string.Empty);
    Debug.WriteLine("---------------------------------------------------------");
    Debug.WriteLine(value);
    Debug.WriteLine("---------------------------------------------------------");
 
  }
 
  public override void Flush()
  {
    Debug.Flush();
  }
}

Is just writes CAML query to the Output window of Visual Studio. It's enough for testing. To use it's enough to set Log property of data context to instance of ZhukBlogTracer class.

Count()

Linq to SharePoint knows nothing about this extension method and so it'll retrieve all items from list and then Count method will be executed. Bu it'll be Linq to Object, not Linq to SharePoint. If you need to count then number of items it must be limit selected fields. All we need is just ID field of item:

var branchQnt = ctx.Branches
  .Where(b => b.City != "Moscow")
  .Count(); // LINQ to Objects !
var branchQntQuick = ctx.Branches
  .Where(b => b.City != "Moscow")
  .Select(b => b.Id) // ViewFields
  .Count();

Following CAML query will be built as a result of it:

<View>
 <Query>
  <Where>
   <And>
    <BeginsWith>
     <FieldRef Name="ContentTypeId" />
     <Value Type="ContentTypeId">0x01007087DA17F20149E3A4602E517E6E8EEF</Value>
    </BeginsWith>
    <Neq>
     <FieldRef Name="City" />
     <Value Type="Text">Moscow</Value>
    </Neq>
   </And>
  </Where>
 </Query>
 <ViewFields>
  <FieldRef Name="ID" />
 </ViewFields>
 <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>

Take(n), Skip(n)

It's not a trivial case too. CAML-query has RowLimit parameter, which limits count of items and following code sets RowLimit property to 10:

var branchTake = ctx.Branches
  .Where(b => b.City != "Moscow")
  .Take(10)
  .Count();

As for Skip method, Linq to SharePoint retrieves all items before executing of it. The usual sequential call of Skip and Take mothods - it's not Linq to SharePoint:

var branchSkipTake = ctx.Branches
  .Where(b => b.City != "Moscow")
  .Skip(10) // LINQ to Objects !
  .Take(10); // LINQ to Objects !

If paging is necessary on a project the only way to do this is replace Skip method with Take one. If page size is 10 items retriving for N'th page is equal 10*N.

// pageIndex - index of the current page
// pageSize - page size
var branchPaged = ctx.Branches
  .Where(b => b.City != "Moscow")
  .Take((pageIndex + 1) * pageSize)
  .Skip(pageIndex * pageSize); // LINQ to Objects !

JOINs

Join of lists appering in SharePoint 2010 based on EntityRef properties of objects representing these lists (or document libraries). There is no way to use this functionality be calling Join extension method. In addition to this limitation there is another one: only Many-to-One relationships are supported for parent-child queries. Here is an example:

var bigJoin = ctx.Employees
  .Where(x => x.Department.Title.Contains("IT"))
  .Where(x => x.Title.Contains("Zhukov"))
  .ToList();

And here is a long-awaited JOIN in the CAML query:

<View>
 <Query>
  <Where>
   <And>
    <And>
     <BeginsWith>
      <FieldRef Name="ContentTypeId" />
      <Value Type="ContentTypeId">0x010078B0DD38574940478CF9E129FCD65E9B</Value>
     </BeginsWith>
     <Contains>
      <FieldRef Name="DepartmentTitle" />
      <Value Type="Lookup">IT</Value>
     </Contains>
    </And>
    <Contains>
     <FieldRef Name="Title" />
     <Value Type="Text">Zhukov</Value>
    </Contains>
   </And>
  </Where>
 </Query>
 <ViewFields>
  <FieldRef Name="CellPhone" />
  <FieldRef Name="AccessLevel" />
  <FieldRef Name="Manager" />
  <FieldRef Name="Department" />
  <FieldRef Name="ID" />
  <FieldRef Name="owshiddenversion" />
  <FieldRef Name="FileDirRef" />
  <FieldRef Name="Title" />
  <FieldRef Name="Author" />
  <FieldRef Name="Editor" />
 </ViewFields>
 <ProjectedFields>
  <Field Name="DepartmentTitle" Type="Lookup" List="Department" ShowField="Title" />
 </ProjectedFields>
 <Joins>
  <Join Type="LEFT" ListAlias="Department">
   <!--List Name: Departments-->
   <Eq>
    <FieldRef Name="Department" RefType="ID" />
    <FieldRef List="Department" Name="ID" />
   </Eq>
  </Join>
 </Joins>
 <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>

ObjectTrackingEnabled

The last thing is a trick with ObjectTrackingEnabled property of a data context. This boolean proerty shows that tracking of objects is enabled or not. In the case of using Linq to SharePoint in readonly mode set this property to false for more performance.

Source code is available here.