Sunday, 5 August 2012

SharePoint 2010: Extend LINQ to SharePoint Provider

One of the cool features of SharePoint 2010 is LINQ to SharePoint Provider which
can be used to perform CRUD operations to SharePoint Data with strongly typed objects.

LINQ to SharePoint provide simplifies access to SharePoint data and the code also
looks very neat. However, at the same time we also need to keep in mind its pitfalls/shortcomings.
To me, it is getting matured. Possibly…Microsoft will enhance and fix the issues
with it in SharePoint 2013/ SharePoint 15. I would like to keep LINQ to SharePoint provider's pitfalls beyond the scope of this article.

In one of my projects, one of the my tasks was to display documents in asp.net TreeView
control in accordion style. Each document should show the DocIcon before it. But
the problem was SPMetal utility does not generate any property for it. While googling
I found couple of ways to extend the LINQ entity classes which I am putting together
at one place in this post….

Side note: If you notice, I have used
string as a data type for Created By
and Modified by user type in both the
approaches….that can be easily converted to return SPUser type in first approach.
In second approach, add below extra properties to do the same.

In addition, I used
this for DocIcon column but same approach can be used for all OOB columns which
are ignored by SPMetal utility.

Let us see the CAML generated for below LINQ query to understand the performance
in both the approaches… (refer this link to understand how to see the CAML generated by LINQ
queries)

var announcements =
from a in dtContext.Announcements

where a.Created >
DateTime.Now.AddDays(-10)

select a;

Approach 1 CAML

Approach 2 CAML

<View>

<Query>

<Where>

<BeginsWith>

<FieldRefName="ContentTypeId" />

<Value
Type="ContentTypeId">0x010400</Value>

</BeginsWith>

</Where>

</Query>

<ViewFields>

<FieldRef
Name="FillInChoiceField" />

<FieldRef
Name="Body" />

<FieldRef
Name="Expires" />

<FieldRef
Name="ID" />

<FieldRef
Name="owshiddenversion" />

<FieldRef
Name="FileDirRef" />

<FieldRef
Name="Title" />

<FieldRef
Name="Modified" />

<FieldRef
Name="Created" />

<FieldRef
Name="Editor" />

<FieldRef
Name="Author" />

</ViewFields>

<RowLimitPaged="TRUE">2147483647</RowLimit>

</View>

<View>

<Query>

<Where>

<And>

<BeginsWith>

<FieldRefName="ContentTypeId" />

<Value
Type="ContentTypeId">0x010400</Value>

</BeginsWith>

<Gt>

<FieldRefName="Created"IncludeTimeValue="TRUE"
/>

<Value
Type="DateTime">2012-07-26T14:23:50Z</Value>

</Gt>

</And>

</Where>

</Query>

<ViewFields>

<FieldRef
Name="Body" />

<FieldRef
Name="Expires" />

<FieldRef
Name="Author" />

<FieldRef
Name="Editor" />

<FieldRef
Name="Created" />

<FieldRef
Name="Modified" />

<FieldRef
Name="ID" />

<FieldRef
Name="owshiddenversion" />

<FieldRef
Name="FileDirRef" />

<FieldRef
Name="Title" />

</ViewFields>

<RowLimitPaged="TRUE">2147483647</RowLimit>

</View>

Approach 1 is not having Created datetime filter in where condition…hence it is
very clear that it fetches all the data into the memory first and then applies filtering
to the in-memory objects. This is the reason why it is slower than approach 2 which
does include Created datetime filtering into CAML. In addition, first approach will
throw list throttling exceptions when items in the list exceed list throttling limits.
Hence I strongly recommend you to go with second approach.

If you are using the LINQ to SharePoint provider then keep a close eye on Choice,
DateTime queries. Choice columns have problems with spaces and DateTime ignores
time component in the date.