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 post is about Linq to SharePoint again. This time I'll show how to work with Choice and MultiChoice field.

enum for Choice 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):

  1. <Field ID="{68c1ee4a-5a25-4ccb-82ca-f5ff17e2016f}" Name="Sex" DisplayName="Sex" Type="Choice" Format="RadioButtons">
  2.   <CHOICES>
  3.     <CHOICE>Male</CHOICE>
  4.     <CHOICE>Female</CHOICE>
  5.   </CHOICES>
  6.   <Default>Male</Default>
  7. </Field>
  8. <Field ID="{BD1898B4-0869-41F2-9DB0-B0F1B8F139D3}" Name="Hobbies" DisplayName="Hobbies" Type="MultiChoice" Mult="TRUE">
  9.   <CHOICES>
  10.     <CHOICE>Chess</CHOICE>
  11.     <CHOICE>Football</CHOICE>
  12.     <CHOICE>Basketball</CHOICE>
  13.   </CHOICES>
  14.   <Default>Chess</Default>
  15. </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 (multichoise field):

  1. public enum EmployeeSex
  2. {
  3.     None = 0,
  4.     Invalid = 1,
  5.     [Choice(Value = "Male")]
  6.     Male = 2,
  7.     [Choice(Value = "Female")]
  8.     Female = 4
  9. }
  10.  
  11. [FlagsAttribute]
  12. public enum EmployeeHobby
  13. {
  14.     None = 0,
  15.     Invalid = 1,
  16.     [Choice(Value = "Chess")]
  17.     Chess = 2,
  18.     [Choice(Value = "Football")]
  19.     Football = 4,
  20.     [Choice(Value = "Basketball")]
  21.     Basketball = 8
  22. }

Single Choice

Let's begin from simple case 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:

  1. using (var ctx = new ZhukDataContext(siteUrl))
  2. {
  3.     var employees = ctx.Employees
  4.         .Where(emp => emp.Sex == EmployeeSex.Male)
  5.         .ToList();
  6.     // ...
  7. }

But in this case SPLinqProvider can't convert this labda 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 multichoise case).

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

  1. public EmployeeSex? Sex
  2. {
  3.     get
  4.     {
  5.         var res = Enum.Parse(typeof (EmployeeSex), _sexValue);
  6.         return res is EmployeeSex ? (EmployeeSex) res : EmployeeSex.Invalid;
  7.     }
  8. }
  9.  
  10. [Column(Name = "Sex", Storage = "_sexValue", FieldType = "Choice")]
  11. public string SexValue
  12. {
  13.     get
  14.     {
  15.         return _sexValue;
  16.     }
  17.     set
  18.     {
  19.         if ((value == _sexValue)) return;
  20.         var vals = Enum.GetValues(typeof(EmployeeSex));
  21.         foreach (EmployeeSex val in vals)
  22.         {
  23.             if (!string.Equals(Enum.GetName(typeof(EmployeeSex), val), value,
  24.                                 StringComparison.InvariantCultureIgnoreCase)) continue;
  25.             OnPropertyChanging("SexValue", _sexValue);
  26.             _sexValue = value;
  27.             OnPropertyChanged("SexValue");
  28.         }
  29.     }
  30. }

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

  1. public static string GetChoiceValue(this Enum enumerator)
  2. {
  3.     // Get the type
  4.     var type = enumerator.GetType();
  5.     // Get the name of field
  6.     var fieldName = Enum.GetName(enumerator.GetType(), enumerator);
  7.     // Get the field
  8.     var field = type.GetField(fieldName, 
  9.                 BindingFlags.Static | BindingFlags.GetField | BindingFlags.Public);
  10.     // Get attributes of the field
  11.     var attributes = field.GetCustomAttributes(typeof (ChoiceAttribute), true);
  12.     var attribute = attributes.FirstOrDefault();
  13.     // If the attribute is null return empty text
  14.     // In other case return Value property of it
  15.     return attribute == null
  16.                 ? string.Empty
  17.                 : ((ChoiceAttribute) attribute).Value;
  18. }

Now everything works properly and looks quite nice:

  1. using (var ctx = new ZhukDataContext(siteUrl))
  2. {
  3.     var employees = ctx.Employees
  4.         .Where(emp => emp.SexValue == EmployeeSex.Male.GetChoiceValue())
  5.         .ToList();
  6. }

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

Multiple Choice

If a field can has multiple value 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:

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

Source code of demo project for this post is available here.

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


Share

Comments