Linq to SharePoint. Part 4

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

All examples in this post are base on data model described in another one.

Linq to SharePoint and SPListItem

If in any solution there is any very big and very difficult CAML-query which retrieves data from many sites both and filters their. Or another some more case: there is SPListItem object into EventReceiver' code. In both cases it's necessary to have object from solution' data model instead of SPListItem.

For implementing this functionality add constructor to the base class. In this case the functionality is available in classes derived from it:

  1. public class ZhukDataItem : ITrackEntityState, ITrackOriginalValues,
  2.                             INotifyPropertyChanged, INotifyPropertyChanging
  3. {
  4.     public ZhukDataItem(SPItem item)
  5.     {
  6.     }
  7. }

To get values use ColumnAttribute of properties:

  1. public ZhukDataItem(SPItem item)
  2. {
  3.     if (item == nullreturn;
  4.     // Current type
  5.     var objType = GetType();
  6.     // Getting current object' properties
  7.     var properties = objType.GetProperties();
  8.     foreach (var property in properties)
  9.     {
  10.         // Getting ColumnAttribute attributes
  11.         var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), false);
  12.         foreach (ColumnAttribute att in attributes)
  13.         {
  14.             // Getting storage field
  15.             // from ColumnAttribute
  16.             var field = objType.GetField(att.Storage, 
  17.                 BindingFlags.NonPublic | BindingFlags.Instance);
  18.             // If there is no the field check out base class
  19.             while (field == null
  20.             {
  21.                 objType = objType.BaseType;
  22.                 if (objType == nullbreak;
  23.                 field = objType.GetField(att.Storage, 
  24.                     BindingFlags.NonPublic | BindingFlags.Instance);
  25.             }
  26.             if (field != null)
  27.             {
  28.                 // Parse Lookup field (LookupId;#LookupValue)
  29.                 if (att.FieldType == "Lookup")
  30.                 {
  31.                     try
  32.                     {
  33.                         var fv = new SPFieldLookupValue(
  34.                             (item[att.Name] ?? string.Empty).ToString());
  35.                         if (att.IsLookupId)
  36.                         {
  37.                             field.SetValue(this, fv.LookupId);
  38.                         }
  39.                         else
  40.                         {
  41.                             field.SetValue(this, fv.LookupValue);
  42.                         }
  43.                     }
  44.                     catch (ArgumentException) // Field is null
  45.                     {
  46.                         field.SetValue(this, item[att.Name]);
  47.                     }
  48.                 }
  49.                 else
  50.                 {
  51.                     // Set value as is
  52.                     field.SetValue(this, item[att.Name]);
  53.                 }
  54.             }
  55.         }
  56.     }
  57. }

Additionally, you can check object implements ICustomMapping interface and call MapFrom method if it does. Next step is attaching the object to data context. To do this use Attach method:

  1. using (var ctx = new ZhukDataContext(siteUrl))
  2. {
  3.     // Getting SPListItem
  4.     SPListItem employeeListItem = GetEmployeeListItem(employeeId);
  5.     // Initializing Employee instance
  6.     var entity = new Employee(employeeListItem);
  7.     // Attach object to the context
  8.     ctx.Employees.Attach(entity);
  9.     
  10.     // Проводим необходимые манипуляции
  11.  
  12.     // Save changes
  13.     ctx.SubmitChanges();
  14. }

Dynamic Linq to SharePoint

In the previous post I wrote Linq to SharePoint doesn't implement Contains method. There is a trick to bypass this restriction. To do this write extension-method for building expression for each value in an array and merge these (T-SQL IN operator analog):

  1. public static Expression<Func<T, bool>> EqualsAny<T, TValue>(this Expression<Func<T, TValue>> selector, 
  2.     IEnumerable<TValue> values)
  3. {
  4.     // If input  array is empty return x=> false expression
  5.     if (!values.Any()) return x => false;
  6.     // If parameters more then one return x=> false expression
  7.     if (selector.Parameters.Count != 1) return x => false;
  8.     // Retrieve selector' parameter
  9.     // It is needed to build expression
  10.     var p = selector.Parameters.First();
  11.     // Build expression for each value
  12.     var equals = values
  13.         .Select(v => (Expression)Expression.Equal(selector.Body, 
  14.                                  Expression.Constant(v, typeof(TValue))));
  15.     // Merge built expressions
  16.     var body = equals.Aggregate(Expression.Or);
  17.     // Return expression
  18.     return Expression.Lambda<Func<T, bool>>(body, p);
  19. }

Similarly you can make methods for text values (StartsWitAny, ContainsAny etc.). Merging lambda-function doesn't work case of Linq to SharePoint can't parse functions, just expression. For merging functions it's necessary to call Expression.Invoke method. After this SPLinqProvider throws the exception: Lambda Parameter not in scope. I couldn't create analogs of WhereAny and WhereAll method from Camlex.

Using this method is available even in the rather complex design:

  1. using (var ctx = new ZhukDataContext(siteUrl))
  2. {
  3.     var ids = new int?[] { 1, 3, 5, 7, 9, 11, 13, 15 };
  4.     var predicate = EqualsAny<Employee, int?>(emp => emp.Id, ids);
  5.     var employees = ctx.Employees
  6.         .ScopeToFolder(string.Empty, true)
  7.         .Where(emp => emp.ManagerId == 2)
  8.         .Where(predicate)
  9.         .Where(emp => emp.AccessLevel > 2)
  10.         .OrderBy(emp => emp.Title)
  11.         .Take(5)
  12.         .ToList();
  13.     //...
  14. }

The resul is a large CAML-query which retrieves only the data that are needed from a list:

  1. <View Scope='RecursiveAll'>
  2.   <Query>
  3.     <Where>
  4.       <And>
  5.         <And>
  6.           <And>
  7.             <BeginsWith>
  8.               <FieldRef Name="ContentTypeId" />
  9.               <Value Type="ContentTypeId">0x010078B0DD38574940478CF9E129FCD65E9B</Value>
  10.             </BeginsWith>
  11.             <Eq><FieldRef Name="Manager" LookupId="TRUE" /><Value Type="Lookup">2</Value></Eq>
  12.           </And>
  13.           <Or>
  14.             <Or>
  15.               <Or>
  16.                 <Or>
  17.                   <Or>
  18.                     <Or>
  19.                       <Or>
  20.                         <Eq><FieldRef Name="ID" /><Value Type="Counter">1</Value></Eq>
  21.                         <Eq><FieldRef Name="ID" /><Value Type="Counter">3</Value></Eq>
  22.                       </Or>
  23.                       <Eq><FieldRef Name="ID" /><Value Type="Counter">5</Value></Eq>
  24.                     </Or>
  25.                     <Eq><FieldRef Name="ID" /><Value Type="Counter">7</Value></Eq>
  26.                   </Or>
  27.                   <Eq><FieldRef Name="ID" /><Value Type="Counter">9</Value></Eq>
  28.                 </Or>
  29.                 <Eq><FieldRef Name="ID" /><Value Type="Counter">11</Value></Eq>
  30.               </Or>
  31.               <Eq><FieldRef Name="ID" /><Value Type="Counter">13</Value></Eq>
  32.             </Or>
  33.             <Eq><FieldRef Name="ID" /><Value Type="Counter">15</Value></Eq>
  34.           </Or>
  35.         </And>
  36.         <Gt><FieldRef Name="AccessLevel" /><Value Type="Integer">2</Value></Gt>
  37.       </And>
  38.     </Where>
  39.     <OrderBy Override="TRUE"><FieldRef Name="Title" /></OrderBy>
  40.   </Query>
  41.   <ViewFields>
  42.     <FieldRef Name="CellPhone" />
  43.     <FieldRef Name="AccessLevel" />
  44.     <FieldRef Name="Manager" />
  45.     <FieldRef Name="Department" />
  46.     <FieldRef Name="ID" />
  47.     <FieldRef Name="owshiddenversion" />
  48.     <FieldRef Name="FileDirRef" />
  49.     <FieldRef Name="Title" />
  50.     <FieldRef Name="Author" />
  51.     <FieldRef Name="Editor" />
  52.   </ViewFields>
  53.   <RowLimit Paged="TRUE">5</RowLimit>
  54. </View>

Share

Comments