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 that retrieves data from many sites both and filters there. Or another some more case: there is SPListItem object into EventReceiver' code. In both cases, it's necessary to have an object from the solution' data model instead of SPListItem.

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

public class ZhukDataItem : ITrackEntityState, ITrackOriginalValues,
                            INotifyPropertyChanged, INotifyPropertyChanging
{
    public ZhukDataItem(SPItem item)
    {
    }
}

To get values use ColumnAttribute of properties:

public ZhukDataItem(SPItem item)
{
    if (item == null) return;
    // Current type
    var objType = GetType();
    // Getting current object' properties
    var properties = objType.GetProperties();
    foreach (var property in properties)
    {
        // Getting ColumnAttribute attributes
        var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), false);
        foreach (ColumnAttribute att in attributes)
        {
            // Getting storage field
            // from ColumnAttribute
            var field = objType.GetField(att.Storage, 
                BindingFlags.NonPublic | BindingFlags.Instance);
            // If there is no the field check out base class
            while (field == null) 
            {
                objType = objType.BaseType;
                if (objType == null) break;
                field = objType.GetField(att.Storage, 
                    BindingFlags.NonPublic | BindingFlags.Instance);
            }
            if (field != null)
            {
                // Parse Lookup field (LookupId;#LookupValue)
                if (att.FieldType == "Lookup")
                {
                    try
                    {
                        var fv = new SPFieldLookupValue(
                            (item[att.Name] ?? string.Empty).ToString());
                        if (att.IsLookupId)
                        {
                            field.SetValue(this, fv.LookupId);
                        }
                        else
                        {
                            field.SetValue(this, fv.LookupValue);
                        }
                    }
                    catch (ArgumentException) // Field is null
                    {
                        field.SetValue(this, item[att.Name]);
                    }
                }
                else
                {
                    // Set value as is
                    field.SetValue(this, item[att.Name]);
                }
            }
        }
    }
}

Additionally, you can check the 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:

using (var ctx = new ZhukDataContext(siteUrl))
{
    // Getting SPListItem
    SPListItem employeeListItem = GetEmployeeListItem(employeeId);
    // Initializing Employee instance
    var entity = new Employee(employeeListItem);
    // Attach object to the context
    ctx.Employees.Attach(entity);
    
    // Проводим необходимые манипуляции
 
    // Save changes
    ctx.SubmitChanges();
}

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):

public static Expression<Func<T, bool>> EqualsAny<T, TValue>(this Expression<Func<T, TValue>> selector, 
    IEnumerable<TValue> values)
{
    // If input  array is empty return x=> false expression
    if (!values.Any()) return x => false;
    // If parameters more then one return x=> false expression
    if (selector.Parameters.Count != 1) return x => false;
    // Retrieve selector' parameter
    // It is needed to build expression
    var p = selector.Parameters.First();
    // Build expression for each value
    var equals = values
        .Select(v => (Expression)Expression.Equal(selector.Body, 
                                 Expression.Constant(v, typeof(TValue))));
    // Merge built expressions
    var body = equals.Aggregate(Expression.Or);
    // Return expression
    return Expression.Lambda<Func<T, bool>>(body, p);
}

Similarly, you can make methods for text values (StartsWitAny, ContainsAny, etc.). Merging lambda-function doesn't work in 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:

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

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

<View Scope='RecursiveAll'>
  <Query>
    <Where>
      <And>
        <And>
          <And>
            <BeginsWith>
              <FieldRef Name="ContentTypeId" />
              <Value Type="ContentTypeId">0x010078B0DD38574940478CF9E129FCD65E9B</Value>
            </BeginsWith>
            <Eq><FieldRef Name="Manager" LookupId="TRUE" /><Value Type="Lookup">2</Value></Eq>
          </And>
          <Or>
            <Or>
              <Or>
                <Or>
                  <Or>
                    <Or>
                      <Or>
                        <Eq><FieldRef Name="ID" /><Value Type="Counter">1</Value></Eq>
                        <Eq><FieldRef Name="ID" /><Value Type="Counter">3</Value></Eq>
                      </Or>
                      <Eq><FieldRef Name="ID" /><Value Type="Counter">5</Value></Eq>
                    </Or>
                    <Eq><FieldRef Name="ID" /><Value Type="Counter">7</Value></Eq>
                  </Or>
                  <Eq><FieldRef Name="ID" /><Value Type="Counter">9</Value></Eq>
                </Or>
                <Eq><FieldRef Name="ID" /><Value Type="Counter">11</Value></Eq>
              </Or>
              <Eq><FieldRef Name="ID" /><Value Type="Counter">13</Value></Eq>
            </Or>
            <Eq><FieldRef Name="ID" /><Value Type="Counter">15</Value></Eq>
          </Or>
        </And>
        <Gt><FieldRef Name="AccessLevel" /><Value Type="Integer">2</Value></Gt>
      </And>
    </Where>
    <OrderBy Override="TRUE"><FieldRef Name="Title" /></OrderBy>
  </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>
  <RowLimit Paged="TRUE">5</RowLimit>
</View>
Vitaly Zhukov

Vitaly Zhukov

Tech Lead, Architect, Developer, Technical Trainer, Microsoft MVP. Over 20 years of experience in system integration and software development. I specialize in designing and implementing scalable, high-performance software solutions across various industries.

You May Also Like

Call Dataverse API from SPFx Web Part

Call Dataverse API from SPFx Web Part

Provision Lists and Libraries with SPFx solution

Provision Lists and Libraries with SPFx solution

SharePoint. Drag-and-Drop File Uploading

SharePoint. Drag-and-Drop File Uploading

CSOM. Upload document

CSOM. Upload document

SharePoint List REST API. Part 2

SharePoint List REST API. Part 2

SharePoint Framework. Create Angular WebPart

SharePoint Framework. Create Angular WebPart

SharePoint List REST API. Part 1

SharePoint List REST API. Part 1

Project Server. CSOM + Custom Fields

Project Server. CSOM + Custom Fields

SharePoint 2010. Long time operation with updatable status

SharePoint 2010. Long time operation with updatable status

Linq to SharePoint. Cross site collection queries

Linq to SharePoint. Cross site collection queries

SharePoint. Getting Document Icon URL

SharePoint. Getting Document Icon URL

Linq to SharePoint. Repository pattern

Linq to SharePoint. Repository pattern

Linq to SharePoint vs Camlex.NET Performance Comparison

Linq to SharePoint vs Camlex.NET Performance Comparison

Linq to SharePoint. Part 5. Choice and MultiChoice fields

Linq to SharePoint. Part 5. Choice and MultiChoice fields

Linq to SharePoint. Part 3

Linq to SharePoint. Part 3

Linq to SharePoint. Part 2

Linq to SharePoint. Part 2

Linq to Sharepoint. Part 1

Linq to Sharepoint. Part 1