Linq to SharePoint. Part 5. Choice and MultiChoice fields

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

Today's post is about Linq to SharePoint again. This time I'll show how to work with Choice and MultiChoice field.

Environment

First of all i need to say all examples are based on data model from my second post about Linq to SharePoint. There are two new fields in the Employee content type for this post. First of these - Sex (SPFieldChoice) and second one is Hobbies (SPFieldMultiChoice):

<Field ID="{68c1ee4a-5a25-4ccb-82ca-f5ff17e2016f}" Name="Sex" DisplayName="Sex" Type="Choice" Format="RadioButtons">
  <CHOICES>
    <CHOICE>Male</CHOICE>
    <CHOICE>Female</CHOICE>
  </CHOICES>
  <Default>Male</Default>
</Field>
<Field ID="{BD1898B4-0869-41F2-9DB0-B0F1B8F139D3}" Name="Hobbies" DisplayName="Hobbies" Type="MultiChoice" Mult="TRUE">
  <CHOICES>
    <CHOICE>Chess</CHOICE>
    <CHOICE>Football</CHOICE>
    <CHOICE>Basketball</CHOICE>
  </CHOICES>
  <Default>Chess</Default>
</Field>

Now after deploying this solution we can generate code using SPMetal tool. After this, we have two enumerators for these fields. One of these has Flags attribute (multichoice field):

public enum EmployeeSex
{
    None = 0,
    Invalid = 1,
    [Choice(Value = "Male")]
    Male = 2,
    [Choice(Value = "Female")]
    Female = 4
}
 
[FlagsAttribute]
public enum EmployeeHobby
{
    None = 0,
    Invalid = 1,
    [Choice(Value = "Chess")]
    Chess = 2,
    [Choice(Value = "Football")]
    Football = 4,
    [Choice(Value = "Basketball")]
    Basketball = 8
}

Single Choice

Let's begin from simple cases when a field can have only one value. SPMetal generates code which we can just use. If we need to select male employees we use code like following:

using (var ctx = new ZhukDataContext(siteUrl))
{
    var employees = ctx.Employees
        .Where(emp => emp.Sex == EmployeeSex.Male)
        .ToList();
    // ...
}

But in this case SPLinqProvider can't convert this lambda function to CAML query. Before filtering data SPLinqProvider retrieves all data from a list (or document library) and after this, it'll be filtering using Linq to Object. The reason is this: values of Choice and MultiChoice fields are stored in a content database as text values (separated by ";#" in a multichoice case).

To work around this limitation I use this trick: I change the type of property from enumerator to string. For support enumerators you can create additional calculated property without ColumnAttribute attribute:

public EmployeeSex? Sex
{
    get
    {
        var res = Enum.Parse(typeof (EmployeeSex), _sexValue);
        return res is EmployeeSex ? (EmployeeSex) res : EmployeeSex.Invalid;
    }
}
 
[Column(Name = "Sex", Storage = "_sexValue", FieldType = "Choice")]
public string SexValue
{
    get
    {
        return _sexValue;
    }
    set
    {
        if ((value == _sexValue)) return;
        var vals = Enum.GetValues(typeof(EmployeeSex));
        foreach (EmployeeSex val in vals)
        {
            if (!string.Equals(Enum.GetName(typeof(EmployeeSex), val), value,
                                StringComparison.InvariantCultureIgnoreCase)) continue;
            OnPropertyChanging("SexValue", _sexValue);
            _sexValue = value;
            OnPropertyChanged("SexValue");
        }
    }
}

Now filtering by text value (SexValue property) of field is available for us. And the result will be the right CAML-query. To escape texts in your code you may use extension method for enums, which returns a value from ChoiceAttribute of enumerator' member:

public static string GetChoiceValue(this Enum enumerator)
{
    // Get the type
    var type = enumerator.GetType();
    // Get the name of field
    var fieldName = Enum.GetName(enumerator.GetType(), enumerator);
    // Get the field
    var field = type.GetField(fieldName, 
                BindingFlags.Static | BindingFlags.GetField | BindingFlags.Public);
    // Get attributes of the field
    var attributes = field.GetCustomAttributes(typeof (ChoiceAttribute), true);
    var attribute = attributes.FirstOrDefault();
    // If the attribute is null return empty text
    // In other case return Value property of it
    return attribute == null
                ? string.Empty
                : ((ChoiceAttribute) attribute).Value;
}

Now everything works properly and looks quite nice:

using (var ctx = new ZhukDataContext(siteUrl))
{
    var employees = ctx.Employees
        .Where(emp => emp.SexValue == EmployeeSex.Male.GetChoiceValue())
        .ToList();
}

CAML query generated in both cases I'll not give there - it needs a lot of space. You can try this by taking the demo project.

Multiple Choice

If a field can have multiple values this trick doesn't work (at least I can't work this around). I think the key to this trouble is this: values of MultiChoice field stored in content database as separated by delimeter (;#):

One of solution is converting enumerators to string. You can use for this extension method like EqualsAny, desribed by myself in 4th part. Th new method checks on the contents of value in row:

public static Expression<Func<T, bool>> ContainsAny<T>(this Expression<Func<T, string>> selector,
    IEnumerable<string> values)
{
    // Return x=> false expression if value is empty
    if (!values.Any()) return x => false;
    // Do the same in case count of parameters doesn't equal one
    if (selector.Parameters.Count != 1) return x => false;
    var p = selector.Parameters.First();
    // Get the reference to Contains method
    var method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    // Build expression containing String.Contains method for each value
    var equals = values
        .Select(v => (Expression)Expression.Call(
            selector.Body, method, Expression.Constant(v, typeof(string))));
    // Aggregate expressions
    var body = equals.Aggregate(Expression.Or);
    // Return the expression
    return Expression.Lambda<Func<T, bool>>(body, p);
}

Source code of demo project for this post is available here

I hope my posts about using Linq to SharePoint will be helpful.