I. Introduction▲
At the time of writing, SharePoint 2010 Beta has just been made available. You can therefore easily download it and start working with it.
This article focuses on the new aspects related to the data model. In the previous release of SharePoint, you had to use either the API, either CAML queries to read and update list data. The CAML complexity and limitations could sometimes discourage some "classical" dotnet developers when learning SharePoint.
At the end of this article, you can download a full sample program demonstrating the basic operations such as read, join, inserting, updating, deleting and handling change conflicts via LINQ. All the methods developed in this program are explained throughout this article. Here is a screenshot illustrating the program:
Just double-click the option of your choice and the program will execute the related method interactively or not depending on the chosen option.
Three lists are used by the various examples. A user solution (.wsp) containing the data structure is also available for download. It contains the lists Cities, Customers and Orders. The Customers list contains a lookup field pointing to the Cities list to specify in which city a customer lives. The Orders list contains a lookup field to the Customers list to specify who's the customer of a given order.
II. New data model▲
One of the most frequent problem one could meet in the previous version of SharePoint was that it was not possible to apply relational integrity between lists. The relationships could be made through lookup columns but were only logical. To apply a kind of integrity and to avoid unauthorized deletions, you had to develop event handlers or use external databases.
This missing functionality is now delivered OOTB in SharePoint 2010.
II-A. A few pictures to illustrate the relationships▲
When you create a relationship between two lists through a lookup column, it is now possible to enforce referential integrity as illustrated below:
You can chose one of the following options :
- Restrict Delete : prevent the deletion of a source element which is referenced by another element
- Cascade Delete : deletes automatically the elements referencing the source element
On top of the referential integrity, it's now possible to define columns with unique values:
II-B. API changes related to these new possibilities▲
II-B-1. Enumerating list columns that are related to other lists▲
This code :
using (SPSite TargetSite = new SPSite(TargetSiteUrl))
{
using (SPWeb TargetWeb = TargetSite.OpenWeb())
{
SPRelatedFieldCollection RelatedFields = TargetWeb.Lists["Cities"].GetRelatedFields();
foreach (SPRelatedField RelatedField in RelatedFields)
{
Console.WriteLine("Field <{0}>\r\nbound to <{1}>\r\nlookup on <{2}>\r\nbehavior <{3}>\r\nWeb <{4}>",
RelatedField.FieldId.ToString(),
TargetWeb.Lists[RelatedField.ListId].Title,
RelatedField.LookupList,
RelatedField.RelationshipDeleteBehavior,
RelatedField.WebId);
}
}
}allows you to list all the columns that are bound to the list Cities. This example returns this:
which is the relationship between Cities and Customers for which the referential integrity is enforced and is of type Restrict. This type of relationship prevents a city from being deleted while it is referenced by one or more customers.
The objects SPRelatedField and SPRelatedFieldCollection allow us to manage the relationships.
II-B-2. Adding a relationship▲
This code sample does the following:
- Creates a source list
- Creates a target list
- Adds a lookup column to the target list that points to the source list
- Enforces the referential integrity
- Creates a source element
- Creates a target element that references a source element
- Tries to remove the source element => the execution raises an exception because of the referential integrity
static void CreateRelationShip()
{
string NewLookupFieldTitle = "New Field";
using (SPSite Site = new SPSite("http://win-a8m4b9i5u4w/test"))
{
using (SPWeb Web = Site.OpenWeb())
{
try
{
SPListCollection Lists = Web.Lists;
Guid SourceListId = Lists.Add("Source List",
"",
SPListTemplateType.GenericList);
Console.WriteLine("Source List Created");
Guid TargetListId = Lists.Add("Target List",
"",
SPListTemplateType.GenericList);
Console.WriteLine("Target List Created");
SPList SourceList = Lists[SourceListId];
SPList TargetList = Lists[TargetListId];
SPFieldCollection Fields = TargetList.Fields;
Fields.AddLookup(NewLookupFieldTitle, SourceList.ID, true);
Console.WriteLine("Lookup Field Created");
SPFieldLookup NewLookupField = Fields[NewLookupFieldTitle] as SPFieldLookup;
NewLookupField.Indexed = true;
NewLookupField.LookupField = "Title";
NewLookupField.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
NewLookupField.Update();
Console.WriteLine("Lookup Field Integrity enfored");
SPListItem NewSourceItem = SourceList.Items.Add();
NewSourceItem["Title"] = "Source Data";
NewSourceItem.Update();
Console.WriteLine("Source List Item Created");
SPListItem NewTargetItem = TargetList.Items.Add();
NewTargetItem["Title"] = "Target Data";
NewTargetItem[NewLookupFieldTitle] = new SPFieldLookupValue(1, "Source Data");
NewTargetItem.Update();
Console.WriteLine("Source List Item Created");
TargetList.Update();
SourceList.Update();
Console.WriteLine("Trying to delete the referenced source item...");
NewSourceItem.Delete();
}
catch (SPException Ex)
{
Console.WriteLine(Ex.Message);
}
}
}
}The result is:
However, two lists were created with one element in each:
II-B-3. Handling unique columns▲
Now that the columns can be marked as unique, let's have a look at the involved properties. To specify that a column must be unique, you can use the following code:
using (SPSite TargetSite = new SPSite(TargetSiteUrl))
{
using (SPWeb TargetWeb = TargetSite.OpenWeb())
{
SPRelatedFieldCollection RelatedFields = TargetWeb.Lists["Cities"].GetRelatedFields();
SPField TheField = TargetWeb.Lists["Customers"].Fields.GetFieldByInternalName("Title");
TheField.Indexed = true;
TheField.AllowDuplicateValues = false;
TheField.Update();
}
}You first need to specify that the column is indexed, then set the property AllowDuplicateValues to false.
III. Acquiring SharePoint data using LINQ▲
An interesting thing to know is that the SharePoint LINQ API generates CAMl code to perform the queries. Because of that some queries are considered efficient or inefficient (see below for more info). In order to let SPLINQ generate the CAML code for you, you first need to instanciate a datacontext object that's been created by SPMETAL.
III-A. Acquiring a LINQ context▲
III-A-1. SPMetal▲
SPMetal is a new command-line tool located in 14\BIN that allows you to generate the LINQ datacontext containing references to your entities. In order to create that class, you need to execute this command on an existing site the following way:
spmetal /web:<url> /namespace:<namespace> /code:<codefile.cs>Let's have a look now at what SPMetal does. It's important to understand what it does because you might be brought to modify the generated class in case some structural elements are not automatically taken into account by SPMetal.
Let's see what SPMetal did to generate the CustomersItem entity representing a Customers list item. The CustomersItem contains properties (metadata) such as Title, Age, City (lookup column storing the ID of the city where the customer lives) and OrdersItem which is a reference from Orders to Customers.
The code generated by SPMetal for the CustomersItem entity is:
[Microsoft.SharePoint.Linq.ListAttribute(Name="Customers")]
public Microsoft.SharePoint.Linq.EntityList<CustomersItem> Customers
{
get
{
return this.GetList<CustomersItem>("Customers");
}
}This returns a handle to the Customers list of the SharePoint site that you can call from the datacontext object.
The code representing the entity is:
[Microsoft.SharePoint.Linq.ContentTypeAttribute(Name="Item", Id="0x01", List="Customers")]
public partial class CustomersItem : Item {
private System.Nullable<double> _age;
private Microsoft.SharePoint.Linq.EntityRef<CitiesItem> _city;
private Microsoft.SharePoint.Linq.EntitySet<OrdersItem> _ordersItem;
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate();
partial void OnCreated();
#endregion
public CustomersItem() {
this._city = new Microsoft.SharePoint.Linq.EntityRef<CitiesItem>();
this._city.OnSync += new System.EventHandler<Microsoft.SharePoint.Linq.AssociationChangedEventArgs<CitiesItem>>(this.OnCitySync);
this._city.OnChanged += new System.EventHandler(this.OnCityChanged);
this._city.OnChanging += new System.EventHandler(this.OnCityChanging);
this._ordersItem = new Microsoft.SharePoint.Linq.EntitySet<OrdersItem>();
this._ordersItem.OnSync += new System.EventHandler<Microsoft.SharePoint.Linq.AssociationChangedEventArgs<OrdersItem>>(this.OnOrdersItemSync);
this._ordersItem.OnChanged += new System.EventHandler(this.OnOrdersItemChanged);
this._ordersItem.OnChanging += new System.EventHandler(this.OnOrdersItemChanging);
this.OnCreated();
}
[Microsoft.SharePoint.Linq.ColumnAttribute(Name = "Age", Storage = "_age", FieldType = "Number")]
public System.Nullable<double> Age
{
get
{
return this._age;
}
set
{
if ((value != this._age))
{
this.OnPropertyChanging("Age", this._age);
this._age = value;
this.OnPropertyChanged("Age");
}
}
}
[Microsoft.SharePoint.Linq.AssociationAttribute(Name="City", Storage="_city", MultivalueType=Microsoft.SharePoint.Linq.AssociationType.Single, List="Cities")]
public CitiesItem City {
get {
return this._city.GetEntity();
}
set {
this._city.SetEntity(value);
}
}
[Microsoft.SharePoint.Linq.AssociationAttribute(Name = "Customer", Storage = "_ordersItem", MultivalueType = Microsoft.SharePoint.Linq.AssociationType.Backward, List = "Orders")]
public Microsoft.SharePoint.Linq.EntitySet<OrdersItem> OrdersItem
{
get
{
return this._ordersItem;
}
set
{
this._ordersItem.Assign(value);
}
}
private void OnCityChanging(object sender, System.EventArgs e) {
this.OnPropertyChanging("City", this._city.Clone());
}
private void OnCityChanged(object sender, System.EventArgs e) {
this.OnPropertyChanged("City");
}
private void OnCitySync(object sender, Microsoft.SharePoint.Linq.AssociationChangedEventArgs<CitiesItem> e) {
if ((Microsoft.SharePoint.Linq.AssociationChangedState.Added == e.State)) {
e.Item.CustomersItem.Add(this);
}
else {
e.Item.CustomersItem.Remove(this);
}
}
private void OnOrdersItemChanging(object sender, System.EventArgs e)
{
this.OnPropertyChanging("OrdersItem", this._ordersItem.Clone());
}
private void OnOrdersItemChanged(object sender, System.EventArgs e)
{
this.OnPropertyChanged("OrdersItem");
}
private void OnOrdersItemSync(object sender, Microsoft.SharePoint.Linq.AssociationChangedEventArgs<OrdersItem> e)
{
if ((Microsoft.SharePoint.Linq.AssociationChangedState.Added == e.State))
{
e.Item.Customer = this;
}
else
{
e.Item.Customer = null;
}
}
}Mainly getters and setters allowing you to manipulate the metadata in read/write mode. As you can see, the setter of the properties City and OrdersItem is implemented differently. The City setter calls the method SetEntity() while the other calls the method Assign().
To limit the code listing, I've not included the implementation of the other entities but you'll see it if you download the solution.
III-A-2. Using the class generated by SPMetal▲
When a datacontext has been generated by SPMetal, you can add it to your project and start working with it. If for instance, your datacontext is named TeamSite, you can use it the following way:
TeamSiteDataContext Ctx = new TeamSiteDataContext("http://siteurl");or
TeamSiteDataContext Ctx = new TeamSiteDataContext(SPContext.Current.Web.Url);if SPContext.Current is not null.
III-B. Simples queries▲
As stated in the introduction, all the examples are based on three lists, here is a screenshot of them:
With LINQ, performing queries is very simple :
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers
select Customer;
foreach (var CustomerItem in CustomerItems)
{
Console.WriteLine(string.Format("Customer <{0}> aged <{1}> lives in <{2}> - <{3}>",
(CustomerItem.Title != null) ? CustomerItem.Title : "",
(CustomerItem.Age != null) ? CustomerItem.Age.ToString() : "-",
(CustomerItem.City != null && CustomerItem.City.Title != null) ? CustomerItem.City.Title : "",
(CustomerItem.City != null && CustomerItem.City.Country != null) ? CustomerItem.City.Country : "")
);
}In this example,Ctx is the datacontext (as explained in the previous section). This query allows you to retrieve data from several lists, in this case, the lists Customers and Cities. You need to instanciate an entity list of type CustomersItem, make it point to Customers and code your query.
The query returns information related to the both the customers and the cities since the country of the city is also displayed. Here is a screenshot showing the result of the query:
You can easily add some criteria to the query:
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers
where Customer.City.Title == "Los Angeles" && Customer.Age > 30
select Customer;
foreach (var CustomerItem in CustomerItems)
{
Console.WriteLine(string.Format("Customer <{0}> aged <{1}> lives in <{2}> - <{3}>",
(CustomerItem.Title != null) ? CustomerItem.Title : "",
(CustomerItem.Age != null) ? CustomerItem.Age.ToString() : "-",
(CustomerItem.City != null && CustomerItem.City.Title != null) ? CustomerItem.City.Title : "",
(CustomerItem.City != null && CustomerItem.City.Country != null) ? CustomerItem.City.Country : "")
);
}which will return customers living in LA and aged > 30 years old. As you can see, it's very easy to specify some criteria and retrieve data from several lists which was already much more work in CAML.
III-C. Efficient and Inefficient queries▲
SPLINQ generates CAML code behind the scenes to return the expected results. Any LINQ query that can be "translated" directly into CAML code is considered Efficient. Any LINQ query that requires an extra step not supported by CAML such as grouping/aggregating data is considered Inefficient.
Before Beta2, it was possible to set AllowInefficientQueries to true/false in order to allow inefficient queries usage. SPLINQ was able to generate the extra plumbing required to perform queries that couldn't be returned using only CAML, he made the job for us. Since Beta2, this is not supported at all anymore. The property AllowInefficientQueries has been marked internal which make it not usable by object instances.
The list of inefficient LINQ operators can be found on MSDN, http://msdn.microsoft.com/en-us/library/ee536585(office.14).aspx
This means that if you still wish to run that kind of queries, you need to perform the extra steps yourself. The examples shown so far were efficient because they didn't include any unsupported operator.
Whenever your code tries to perform an inefficient query, the following exception is thrown:
it's confusing since it states that you must set AllowInefficientQueries to True although this flag isn't available anymore. Don't lose time and forget about that flag! The buggy error message should be fixed in the next version of SharePoint! (unless AllowInefficientQueries is made available again...).
III-C-1. A few examples of inefficient queries▲
The following query tries to return the number of customers who are less than 35 per city:
IEnumerable<IGrouping<CitiesItem, CustomersItem>> CustomersByCity = Ctx.Customers.Where(c => c.Age < 35).GroupBy(c => c.City);
foreach (var CustomerCity in CustomersByCity)
{
CitiesItem CityGroup = CustomerCity.Key as CitiesItem;
Console.WriteLine(string.Format("Number of customers aged < 35 living in {0} is {1}",
CityGroup.Title, CustomerCity.Count()));
}This query is inefficient because it requires SPLINQ to group the on the city column which isn't possible to do only with CAML. In order to achive the expected result, you need to perform the grouping operation using LINQ to objects as follows:
IEnumerable<IGrouping<CitiesItem, CustomersItem>> CustomersByCity = Ctx.Customers.Where(c => c.Age < 35).ToList().GroupBy(c => c.City);
foreach (var CustomerCity in CustomersByCity)
{
CitiesItem CityGroup = CustomerCity.Key as CitiesItem;
Console.WriteLine(string.Format("Number of customers aged < 35 living in {0} is {1}",
CityGroup.Title, CustomerCity.Count()));
}The call to the method .ToList() makes the grouping operation be performed by LINQ to objects and not by SPLINQ anymore. What SPLINQ does is only to return the list of customers who are less than 35 years old.
Multiple joins are also considered inefficient:
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
EntityList<OrdersItem> Orders = Ctx.GetList<OrdersItem>("Orders");
var QueryResults = from Customer in Customers
join City in Cities on Customer.City.Id equals City.Id
join OrderItem in Orders on Customer.Id equals OrderItem.Customer.Id
select new { CityName = City.Title, City.Country, Customer.Title, OrderTitle = OrderItem.Title };As for the previous example, you need to use LINQ to Objects to perform multiple joins. Here is the equivalent by using both SPLINQ and LINQ to objects:
List<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers").Where(c=>c.City!=null).ToList();
List<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities").ToList();
List<OrdersItem> Orders = Ctx.GetList<OrdersItem>("Orders").Where(o => o.Customer != null).ToList();
var QueryResults = from Customer in Customers
join City in Cities on Customer.City.Id equals City.Id
join OrderItem in Orders on Customer.Id equals OrderItem.Customer.Id
select new { CityName = City.Title, City.Country, Customer.Title, OrderTitle = OrderItem.Title };Again, we call the ToList() method to retrieve List<> objects on which we apply the joins afterwards. We also make sure that SPLINQ only returns customers referencing a city and orders referencing customers. Although this step isn't required, it's better to avoid retrieving unrelated items.
III-D. Performing joins▲
Only Implicit joins are supported by SPLINQ Beta 2...However combining implicit joins and where clause is already considered inefficient... Here is a tolerated example if we consider that a Lookup column was created to make a relationship between Customers and Cities:
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
var QueryResults = from Customer in Customers
join City in Cities on Customer.City.Id equals City.Id
select new { CityName=City.Title, City.Country, Customer.Title };
var Results = QueryResults.ToList();
if (Results.Count > 0)
{
Results.ForEach(cc => Console.WriteLine("Customer <{0}> lives in <{1}> - <{2}>",
(cc.Title != null) ? cc.Title : "-",
(cc.CityName != null) ? cc.CityName : "-",
(cc.Country != null) ? cc.Country : "-"));
}
else
{
Console.WriteLine("No data found!");
}The join keyword symbolizes the relationship. In the resulting anonymous object, we use CityName as an alias. This is necessary to distinguish the title column from cities and customers. The result of the previous query may look like this:
Meaning, all the customers living in a city where a correspondance has been found in the Cities list.
III-D-1. SPLINQ joins alternatives▲
As we saw previously, most of the SPLINQ joins are considered inefficient forcing your to use either LINQ to Objects or intermediate classes that would potentially generate the CAML themselves instead of letting SPLINQ do the work. For those reasons, it's always good to know how to work directly in CAML.
Here are a few examples using joins in CAML:
III-D-1-a. Simple CAML join▲
SPList CustomerList = Web.Lists["Customers"];
SPQuery CustomerCityQuery = new SPQuery();
CustomerCityQuery.Joins =
"<Join Type='INNER' ListAlias='Cities'>" +
"<Eq>" +
"<FieldRef Name='City' RefType='Id' />" +
"<FieldRef List='Cities' Name='ID' />" +
"</Eq>" +
"</Join>";
StringBuilder ProjectedFields = new StringBuilder();
ProjectedFields.Append("<Field Name='CityTitle' Type='Lookup' List='Cities' ShowField='Title' />");
ProjectedFields.Append("<Field Name='CityCountry' Type='Lookup' List='Cities' ShowField='Country' />");
CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();
SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);
foreach (SPListItem Result in Results)
{
SPFieldLookupValue CityTitle = new SPFieldLookupValue(Result["CityTitle"].ToString());
SPFieldLookupValue CityCountry = new SPFieldLookupValue(Result["CityCountry"].ToString());
Console.WriteLine(string.Format("Customer {0} lives in {1} - {2}",
Result.Title,
CityTitle.LookupValue,
CityCountry.LookupValue));
}The SPQuery objects has the new property Joins allowing you to assign one or more join declaration. In order to be able to work with the joined list columns, you must specify the value of the ProjectedFields property. In this example, this simple join operates on Customers and Cities and we want to retrieve the name of the city and the name of the country coming from the "remote" list Cities. They are symbolized by the aliases CityTitle and CityCountry.
Note that the join type can be either INNER either LEFT.
III-D-1-b. Multiple joins in CAML▲
SPList CustomerList = Web.Lists["Orders"];
SPQuery CustomerCityQuery = new SPQuery();
CustomerCityQuery.Joins =
"<Join Type='INNER' ListAlias='Customers'>" +
"<Eq>" +
"<FieldRef Name='Customer' RefType='Id' />" +
"<FieldRef List='Customers' Name='ID' />" +
"</Eq>" +
"</Join>" +
"<Join Type='INNER' ListAlias='Cities'>" +
"<Eq>" +
"<FieldRef List='Customers' Name='City' RefType='Id' />" +
"<FieldRef List='Cities' Name='ID' /> " +
"</Eq>" +
"</Join>";
StringBuilder ProjectedFields = new StringBuilder();
ProjectedFields.Append("<Field Name='CityTitle' Type='Lookup' List='Cities' ShowField='Title' />");
ProjectedFields.Append("<Field Name='CityCountry' Type='Lookup' List='Cities' ShowField='Country' />");
ProjectedFields.Append("<Field Name='CustomerTitle' Type='Lookup' List='Customers' ShowField='Title' />");
ProjectedFields.Append("<Field Name='CustomerAge' Type='Lookup' List='Customers' ShowField='Age' />");
CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();
SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);
foreach (SPListItem Result in Results)
{
SPFieldLookupValue CityTitle =
new SPFieldLookupValue((Result["CityTitle"] != null) ? Result["CityTitle"].ToString() : "");
SPFieldLookupValue CityCountry =
new SPFieldLookupValue((Result["CityCountry"] != null) ? Result["CityCountry"].ToString() : "");
SPFieldLookupValue CustomerTitle =
new SPFieldLookupValue((Result["CustomerTitle"] != null) ? Result["CustomerTitle"].ToString() : "");
SPFieldLookupValue CustomerAge =
new SPFieldLookupValue((Result["CustomerAge"] != null) ? Result["CustomerAge"].ToString() : "");
Console.WriteLine(string.Format("Customer {0} living in {1} - {2} has ordered #{3}",
CustomerTitle.LookupValue,
CityTitle.LookupValue,
CityCountry.LookupValue,
Result.Title));
}Following the same principle than the previous example, you need to specify the value of .Joins and of ProjectedFields.
Note: interistingly, although multiple joins are supported in CAML and could thus be "translated" directLY SPLINQ with no extra step, SPLINQ still considers those joins as inefficient. The reasons still remain quite obscure to me.
IV. SPLINQ and updating data▲
IV-A. Inserting new elements▲
The LINQ API allows not only to read items but also to update them. For instance, to add a new customer, you can perform:
static void AddCustomer(string CustomerName)
{
try
{
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
CustomersItem NewCustomer = new CustomersItem();
NewCustomer.Title = CustomerName;
Customers.InsertOnSubmit(NewCustomer);
Ctx.SubmitChanges();
Console.WriteLine("Customer added");
}
catch (SPDuplicateValuesFoundException)
{
Console.WriteLine("The customer was not added because it would have created a duplicate entry");
}
catch (ChangeConflictException ConflictException)
{
Console.WriteLine("The customer was not added because a conflict occured:" + ConflictException.Message);
}
catch (Exception Ex)
{
Console.WriteLine("The customer was not added because the following error occured:" + Ex.Message);
}
}As for reading data, you need to instanciate an entity and then, just assign values to the properties of your choice. The method SubmitChanges of the data context will effectively apply the modifications.
It's possible to trap the exceptions that could be potentially be raised by an update. Among them, the most frequent are:
- SPDuplicateValuesFoundException : raised when a field is marked as "no duplicate" and when your update violates this rule
- ChangeConflictException : raised when another process has updated the data you're trying to update after you acquired it
- Exception : generic exception...
You can combine several types of updates in a single piece of code and call SubmitChanges() only once. For instance:
try
{
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
for(int i=0;i<10;i++)
{
CustomersItem NewCustomer = new CustomersItem();
NewCustomer.Title = String.Format("Customer {0}",i.ToString());
Customers.InsertOnSubmit(NewCustomer);
}
Ctx.SubmitChanges();
Console.WriteLine("Customer added");
}
catch (SPDuplicateValuesFoundException)
{
Console.WriteLine("The customer was not added because it would have created a duplicate entry");
}
catch (ChangeConflictException ConflictException)
{
Console.WriteLine("The customer was not added because a conflict occured:" + ConflictException.Message);
}
catch (Exception Ex)
{
Console.WriteLine("The customer was not added because the following error occured:" + Ex.Message);
}adds 10 customers at once with only one invocation of SubmitChanges no matter whether you work on a single entity or more.
IV-B. Updating items▲
static void UpdateCustomer(int CustomerId, string CityName)
{
EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
var CitiesItm = from City in Cities
where City.Title == CityName
select City;
CitiesItem CityItem = null;
foreach (var Cit in CitiesItm)
CityItem = Cit;
if (CityItem == null)
{
Console.WriteLine("City not found");
Environment.Exit(0);
}
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers
where Customer.Id == CustomerId
select Customer;
List<CustomersItem> Results = CustomerItems.ToList();
if (Results.Count > 0)
{
try
{
Results.ForEach(CustomerItem => CustomerItem.City = CityItem);
Ctx.SubmitChanges();
Console.WriteLine("Customer {0} updated with City {1}!",
CustomerId.ToString(), CityName);
}
catch (ChangeConflictException ConflictException)
{
Console.WriteLine("The customer was not updated because a conflict occured:" + ConflictException.Message);
}
catch (Exception Ex)
{
Console.WriteLine("The customer was not updated because the following error occured:" + Ex.Message);
}
}
else
{
Console.WriteLine("Customer {0} not found!", CustomerId.ToString());
}
}Similarly, you notice how easy it is to update an item. Just get it, change its properties and call SubmitChanges
IV-C. Removing items▲
static void DeleteCustomer(int CustomerId)
{
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var QueryResults = from Customer in Customers
where Customer.Id == CustomerId
select Customer;
List<CustomersItem> ReturnedCustomers = QueryResults.ToList();
if (ReturnedCustomers.Count > 0)
{
try
{
Customers.DeleteOnSubmit(ReturnedCustomers[0]);
Ctx.SubmitChanges();
}
catch (ChangeConflictException ConflictException)
{
Console.WriteLine("The customers were not updated because a conflict occured:" + ConflictException.Message);
}
catch (Exception Ex)
{
Console.WriteLine("The customers were not updated because the following error occured:" + Ex.Message);
}
}
else
{
Console.WriteLine("Customer {0} not found", CustomerId.ToString());
}
}This time, you call DeleteOnSubmit and then SubmitChanges
IV-D. Massive updates▲
A massive update consists in modifying/inserting/deleting several items in one shot. We could consider that a process updating >=100 list items is a massive update.
In regular CAML and in the previous version of SharePoint, the best option was to create a batch and to call SPWeb.ProcessBatchData to avoid performance problems. With LINQ, it's still difficult to say what's the best option, we should wait the RTM to see if it's still the recommended way or if LINQ is also suitable for that kind of operations.
While waiting for the answer, here is an example of an update operation that could be considered as massive since it updates all the customers stored in the Customers list to set their city property to CityName given in parameter:
static void UpdateAllCustomers(string CityName)
{
EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
var CitiesItm = from City in Cities
where City.Title == CityName
select City;
CitiesItem CityItem = null;
foreach (var Cit in CitiesItm)
CityItem = Cit;
if (CityItem == null)
{
Console.WriteLine("City not found");
Environment.Exit(0);
}
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers
select Customer;
List<CustomersItem> Results = CustomerItems.ToList();
if (Results.Count > 0)
{
try
{
Results.ForEach(CustomerItem => CustomerItem.City = CityItem);
Ctx.SubmitChanges();
Console.WriteLine("All the customer were updated with City {0}!",
CityName);
}
catch (ChangeConflictException ConflictException)
{
Console.WriteLine("The customers were not updated because a conflict occured:" + ConflictException.Message);
}
catch (Exception Ex)
{
Console.WriteLine("The customers were not updated because the following error occured:" + Ex.Message);
}
}
else
{
Console.WriteLine("No customer currently exist");
}
}IV-E. Handling change conflicts▲
Here is a schema illustrating what's a change conflict:
To simulate this situation, we need two parallel instances. The application available for download does this by using multi-threading. Here is exactly how the application generates the conflicts:
You specify the value of Age and the target customer. Next, you double-click on the option of your choice.
Whatever your choice is, the mechanism remains the same. For instance, for the List conflicts option, the threads are started like this:
Thread t1 = new Thread (new ParameterizedThreadStart(ListConflicts));
t1.Start(string.Format("{0};{1};{2}", 2000, NewAge.Text, CustomerList.SelectedValue));
Thread t2 = new Thread (new ParameterizedThreadStart(ListConflicts));
t2.Start(string.Format("{0};{1};{2}", 0, ComputedAge, CustomerList.SelectedValue));The parameters are: the number of milliseconds to sleep, the value of Age and the target customer. As you can see, the first thread gets started first but sleeps during 2 seconds while the second thread won't sleep at all. The value of ComputedAge is Age+1.
This way, the first thread will be the first to get the target item but the second thread will update the same item before the first thread. Therefore, when the first thread will try to update the item, a change conflict will occur. To get all the pieces of the puzzle, Here is the implementation of ListConflicts:
void ListConflicts(object data)
{
string [] Args = data.ToString().Split(';');
SPLINQ ThreadedLinqExample = new SPLINQ(UrlValue.Text);
ThreadedLinqExample.TestChangeConflictExceptionListConflicts(
Convert.ToInt16(Args[0]),
Convert.ToDouble(Args[1]),
Convert.ToInt16(Args[2]));
}The method calls TestChangeConflictExceptionListConflicts whose the implementation is described later with the right parameters The same principle is applied to the other options.
IV-E-1. Detecting conflicts▲
Each started thread will call this method whose the purpose is to modify the target customer with the age given in arguments:
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers
where Customer.Id == CustomerId
select Customer;
foreach (var CustomerItem in CustomerItems)
{
CustomerItem.Age = Age;
}
System.Threading.Thread.Sleep(SleepTime);
try
{
Ctx.SubmitChanges(ConflictMode.FailOnFirstConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict UpdatedListItem in Ctx.ChangeConflicts)
{
foreach (MemberChangeConflict UpdatedField in UpdatedListItem.MemberConflicts)
{
MessageBox.Show(string.Format(
"Conflict detected on <{0}>, Original value <{1}>, Database Value <{2}> Trying to set <{3}>",
UpdatedField.Member.Name,
UpdatedField.OriginalValue,
UpdatedField.DatabaseValue,
UpdatedField.CurrentValue));
}
}
}When thread 1 will try to update the customer, he'll branch the Catch block and each conflict (in this case only 1) is retrieved from the ChangeConflicts collection of the datacontext object which is Ctx. This method just shows a message for each conflict.
In this case, it looks like:
The database value is already 23, meaning Age + 1 => ComputedAge. Thread 2 has modified the customer item sucessfully. Thread 1 still tries to put the value 22 which isn't applied since we don't take any action against the conflict. The default behavior of SharePoint is to cancel changes in case a conflict occurs. Thus, the final result in this case will be 23, the value set by the second thread.
IV-E-3. Resolving conflicts - Confirming changes▲
The principle being the same than for the previous example, here is how to confirm your changes in case of conflicts:
try
{
Ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
MessageBox.Show ("Detected change conflict exception, forcing changes");
Ctx.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
Ctx.SubmitChanges();
}By calling ResolveAll with RefreshMode.KeepChanges, the failing updates are confirmed and applied. In this case, values from thread 2 are replaced by those of thread 1.
IV-E-4. Resolving conflicts - Confirming/Cancelling changes▲
If you encounter multiple change conflicts, you can decide whether you want ton confirm/cancel a change according to some business criteria. In the following example, by casting the ObjectChangeConflict to a CustomersItem, the process decides to confirm the change if the customer is "customer 1" otherwise, the failing changes are cancelled.
try
{
Ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict UpdatedListItem in Ctx.ChangeConflicts)
{
CustomersItem CurrentObj = UpdatedListItem.Object as CustomersItem;
MessageBox.Show(string.Format("Detected conflict for customer <{0}>",
CurrentObj.Id));
foreach (MemberChangeConflict UpdatedField in UpdatedListItem.MemberConflicts)
{
MessageBox.Show(string.Format(
"Conflict detected on <{0}>, Original value <{1}>, Database Value <{2}> Trying to set <{3}>",
UpdatedField.Member.Name,
UpdatedField.OriginalValue,
UpdatedField.DatabaseValue,
UpdatedField.CurrentValue));
if (CurrentObj.Id == 1 &&
UpdatedField.Member.Name == "Age") // in this case it's always age but it's just to show an example
{
MessageBox.Show("Forcing changes for customer <1>");
UpdatedField.Resolve(RefreshMode.KeepCurrentValues);
}
else
{
MessageBox.Show(string.Format("Cancelling changes for customer <{0}>",
CurrentObj.Id));
UpdatedField.Resolve(RefreshMode.OverwriteCurrentValues);
}
}
}
Ctx.SubmitChanges();
}V. What about CAML?▲
V-A. Backward compatibility▲
The CAML queries are 100% compatible with the previous version. As stated already, the LINQ API makes use of CAML to perform the queries.
V-B. Is it still useful to use CAML queries?▲
At the time of writing, it's still very difficult to answer that question because we're still only in beta version...In the past, it has always been advised to use objects such as SPQuery, SPSiteDataQuery and CrossListQueryInfo to deal with big amount of data rather than using directly the API for performance reasons.
It was also advised to use ProcessBatchData and to create batch updates (INSERT, UPDATE, DELETE) to process massive updates.
The new LINQ API offers answers to all those needs, however, while an RTM is not released, it is very difficult to estimate the performance overhead implied by this new layer. What we can already state is that development productity should gain a lot since there are much more possibilities and a better control on update processes.
Whatever the overhead will be, small or big, this API has certainly a great upcoming future and will probably be very welcomed by developers.
VI. Download▲
You can download the console program and the user solution here
Once downloaded, please follow these instructions:
- Extract the archive
- Upload the solution SPLINQ.wsp in the solution galery of a root site and activate it
- Create a site based on the uploaded template
- Start playing with the program and specify the URL of the site you've created.
