Tuesday, 27 April 2010

Dealing with multi-valued managed properties with custom Sharepoint Search

I've been working with some code that uses Sharepoint's built-in search functionality to display a rolled-up view of data scattered across many site collections on the farm.  Some of my managed properties are defined with the flag 'Include values from all crawled properties mapped'. For the normal use case of search, this just allows you to search on both values, but since I want the detailed properties returned as well, it's getting in my way. In fact, for all the cases I've tried for my search targets, there is only a single value in the array that is returned (making me think that it becomes an array if *anything* in the farm has multiple values, not just anything in the result set).

So, I have multiple values for some fields, so what?  Well, I'm trying to bind them it to a SPGridView (actually a SPSmartGridView from my last blog post), and SPBoundField doesn't like the array – it shows 'System.Datetime []' – decidely unhelpful.  So, I used this little method to 'fix' my multi-valued columns.  Basically, it identifies all the multi-valued columns, moves each to another name, adds a single-valued column, fills it with the first value from the multi-valued column, and removes the multi-valued column.

/// <summary>
/// Converts all multi-valued (array) columns to single-value columns.
/// If there are one or more values, the first one is used.
/// If there is no value, null is used.
/// </summary>
private void FixMultiValuedColumns(DataTable tbl)
    // find the array columns to operate on
    var cols = tbl.Columns.Cast<DataColumn>()
        .Where(c => c.DataType.IsArray)
        .Select(c => new {
            Type = c.DataType.GetElementType() })
    // copy all columns we're working with to 'raw' versions
    cols.ForEach(c => tbl.Columns.Add(c.ColumnName + "_raw", typeof(object)));
    foreach (DataRow dr in tbl.Rows)
        cols.ForEach(c => dr[c.ColumnName + "_raw"] = dr[c.ColumnName]);

    // remove multi-valued column, replace with single-valued one
    cols.ForEach(c => tbl.Columns.Remove(c.ColumnName));
    cols.ForEach(c => tbl.Columns.Add(c.ColumnName, c.Type));
    // copy values from the 'raw' column to the new single-valued one
    foreach (DataRow dr in tbl.Rows)
        cols.ForEach(c => dr[c.ColumnName] =
            ((System.Collections.IEnumerable)dr[c.ColumnName + "_raw"])
    // remove 'raw' column
    cols.ForEach(c => tbl.Columns.Remove(c.ColumnName + "_raw"));

The code is downloadable from the link below.