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….
1.
Approach 1 : Using ICustomMapping interface
Code Sample:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Linq;
using Microsoft.SharePoint;
public partial
class Item
: ICustomMapping
{
public
DateTime Modified { get;
set; }
public
DateTime Created { get;
set; }
public
string CreatedBy { get;
set; }
public
string ModifiedBy { get;
set; }
[CustomMapping(Columns
= new String[]
{ "Modified", "Created",
"Editor", "Author"
})]
public
void MapFrom(object listItem)
{
SPListItem
item = (SPListItem)listItem;
this.Modified
= (DateTime)item[SPBuiltInFieldId.Modified];
this.Created
= (DateTime)item[SPBuiltInFieldId.Created];
this.CreatedBy
= (string)item[SPBuiltInFieldId.Author];
this.ModifiedBy
= (string)item[SPBuiltInFieldId.Editor];
}
public
void MapTo(object listItem)
{
SPListItem
item = (SPListItem)listItem;
item[SPBuiltInFieldId.Modified]
= this.Modified;
item[SPBuiltInFieldId.Created]
= this.Created;
item[SPBuiltInFieldId.Author]
= this.CreatedBy;
item[SPBuiltInFieldId.Editor]
= this.ModifiedBy;
}
public
void Resolve(RefreshMode mode, object originalListItem, object
databaseObject)
{
SPListItem
originalItem = (SPListItem)originalListItem;
SPListItem
databaseItem = (SPListItem)databaseObject;
DateTime
originalModifiedValue = (DateTime)originalItem[SPBuiltInFieldId.Modified];
DateTime
dbModifiedValue = (DateTime)databaseItem[SPBuiltInFieldId.Modified];
DateTime
originalCreatedValue = (DateTime)originalItem[SPBuiltInFieldId.Created];
DateTime
dbCreatedValue = (DateTime)databaseItem[SPBuiltInFieldId.Created];
string
originalCreatedByValue = (string)originalItem[SPBuiltInFieldId.Author];
string
dbCreatedByValue = (string)databaseItem[SPBuiltInFieldId.Author];
string
originalModifiedByValue = (string)originalItem[SPBuiltInFieldId.Editor];
string
dbModifiedByValue = (string)databaseItem[SPBuiltInFieldId.Editor];
if
(mode == RefreshMode.OverwriteCurrentValues)
{
this.Modified = dbModifiedValue;
this.Created = dbCreatedValue;
this.CreatedBy = dbCreatedByValue;
this.ModifiedBy = dbModifiedByValue;
}
else if (mode
== RefreshMode.KeepCurrentValues)
{
databaseItem[SPBuiltInFieldId.Modified] =
this.Modified;
databaseItem[SPBuiltInFieldId.Modified] =
this.Created;
databaseItem[SPBuiltInFieldId.Modified] =
this.CreatedBy;
databaseItem[SPBuiltInFieldId.Modified] =
this.ModifiedBy;
}
else
if (mode == RefreshMode.KeepChanges)
{
if (this.Modified != originalModifiedValue)
{
databaseItem[SPBuiltInFieldId.Modified]
= this.Modified;
}
else if (this.Modified
== originalModifiedValue && this.Modified
!= dbModifiedValue)
{
this.Modified = dbModifiedValue;
}
if (this.Created != originalCreatedValue)
{
databaseItem[SPBuiltInFieldId.Created] =
this.Created;
}
else if (this.Created
== originalCreatedValue && this.Created
!= dbCreatedValue)
{
this.Created = dbCreatedValue;
}
if (this.CreatedBy != originalCreatedByValue)
{
databaseItem[SPBuiltInFieldId.Author] =
this.CreatedBy;
}
else if (this.CreatedBy
== originalCreatedByValue && this.CreatedBy
!= dbCreatedByValue)
{
this.CreatedBy = dbCreatedByValue;
}
if (this.ModifiedBy != originalModifiedByValue)
{
databaseItem[SPBuiltInFieldId.Editor] =
this.ModifiedBy;
}
else if (this.ModifiedBy
== originalModifiedByValue && this.ModifiedBy
!= dbModifiedByValue)
{
this.ModifiedBy = dbModifiedByValue;
}
}
}
}
|
2.
Approach 2: Native style
In this approach, properties are implemented similar to the native properties generated
by SPMetal in a separate partial class.
Code Sample:
public partial
class Item
: Microsoft.SharePoint.Linq.ITrackEntityState,
Microsoft.SharePoint.Linq.ITrackOriginalValues,
System.ComponentModel.INotifyPropertyChanged,
System.ComponentModel.INotifyPropertyChanging
{
string _createdBy;
[Microsoft.SharePoint.Linq.ColumnAttribute(Name
= "Author", Storage =
"_createdBy", ReadOnly = true, FieldType
= "User", IsLookupValue =
true)]
public
string CreatedBy
{
get
{
return this._createdBy;
}
set
{
if ((value != this._createdBy))
{
this.OnPropertyChanging("CreatedBy",
this._createdBy);
this._createdBy =
value;
this.OnPropertyChanged("CreatedBy");
}
}
}
string _modifiedBy;
[Microsoft.SharePoint.Linq.ColumnAttribute(Name
= "Editor", Storage =
"_modifiedBy", ReadOnly = true, FieldType
= "User", IsLookupValue =
true)]
public
string ModifiedBy
{
get
{
return this._modifiedBy;
}
set
{
if ((value != this._modifiedBy))
{
this.OnPropertyChanging("ModifiedBy",
this._modifiedBy);
this._modifiedBy =
value;
this.OnPropertyChanged("ModifiedBy");
}
}
}
DateTime _Created;
[Microsoft.SharePoint.Linq.ColumnAttribute(Name
= "Created", Storage =
"_Created", ReadOnly = true, FieldType
= "DateTime", IsLookupValue =
false)]
public
DateTime Created
{
get
{
return this._Created;
}
set
{
if ((value != this._Created))
{
this.OnPropertyChanging("Created",
this._Created);
this._Created = value;
this.OnPropertyChanged("Created");
}
}
}
DateTime _Modified;
[Microsoft.SharePoint.Linq.ColumnAttribute(Name
= "Modified", Storage =
"_Modified", ReadOnly = true, FieldType
= "DateTime", IsLookupValue =
false)]
public
DateTime Modified
{
get
{
return this._Modified;
}
set
{
if ((value != this._Modified))
{
this.OnPropertyChanging("Modified",
this._Modified);
this._Modified =
value;
this.OnPropertyChanged("Modified");
}
}
}
}
|
In my case, I used this approach to get the DocIcon url like this...
public partial
class Document
: Microsoft.SharePoint.Linq.ITrackEntityState,
Microsoft.SharePoint.Linq.ITrackOriginalValues,
System.ComponentModel.INotifyPropertyChanged,
System.ComponentModel.INotifyPropertyChanging
{
string _docIcon;
[Microsoft.SharePoint.Linq.ColumnAttribute(Name
= "DocIcon", Storage =
"_docIcon", ReadOnly = true, FieldType
= "Computed", IsLookupValue =
false)]
public
string DocIcon
{
get
{
return this._docIcon;
}
}
public
string DocIconFullUrl
{
get
{
string docType =
this._docIcon;
SPWeb web = SPContext.Current.Web;
string docIcon = SPUtility.MapToIcon(web,
docType, string.Empty,
IconSize.Size16);
string iconUrl = string.Format("{0}/_layouts/images/{1}",
web.Url, docIcon);
return iconUrl;
}
}
}
|
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.
In addition, I used this for DocIcon column but same approach can be used for all OOB columns which are ignored by SPMetal utility.
public SPUser
CreatedByUser
{
get
{
return
new SPFieldUserValue(SPContext.Current.Web, this._createdBy).User;
}
}
public SPUser
ModifiedByUser
{
get
{
return
new SPFieldUserValue(SPContext.Current.Web, this._modifiedBy).User;
}
}
|
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>
<FieldRef Name="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>
<RowLimit Paged="TRUE">2147483647</RowLimit>
</View>
|
<View>
<Query>
<Where>
<And>
<BeginsWith>
<FieldRef Name="ContentTypeId" />
<Value
Type="ContentTypeId">0x010400</Value>
</BeginsWith>
<Gt>
<FieldRef Name="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>
<RowLimit Paged="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.
Choice column space issue
Simple solution:
DateTime columns as treated as DateOnly…
I will end this post by suggesting to always have a look at CAML generated by the each LINQ queries to avoid performance issues and unexpected list throttling exceptions.
Most useful article I have found on extending the SPMetal generated classes. The recommended solution (2) works great.
ReplyDelete