Получение уникальных значений поля списка

15 марта, 2012

Сегодня пост об использовании не совсем правильного подхода при решении очень часто встречающейся проблемы при разработке решений на базе SharePoint: получение набора уникальных значений поля списка. Описанный мною метод относится к спискам/библиотекам, содержащим более 5000 элементов.

Принцип решения

При получении данных из списка SharePoint есть одно досадное ограничение: он не поддерживает получение уникальных значений, т.к. в любом случае выбирает такие уникальные поля как ID, UniqueId и прочие. Поэтому для решения описанной выше задачи приходится обходить объектную модель SharePoint и выбирать данные напрямую из базы данных содержимого.

База данных содержимого

Все данные списков/библиотек документов в SharePoint хранятся в одной единственной таблице dbo.AllUserData базы данных содержимого. Для начала, вот так выглядит в SharePoint иерархия содержимого сверху-вниз:

У приложения (SPWebApplication) может быть одна или более база содержимого. И при этом у коллекции сайтов (SPSite) и всех нижестоящих элементов иерархии база содержимого одна. Но мы будем "подниматься" по этой иерархии снизу и выбирать нам не придется.

// Путь к строке подключения от поля списка
SPFieldObject.ParentList.ParentWeb.Site.ContentDatabase.DatabaseConnectionString

SchemaXml

Фильтровать данные в таблице dbo.AlllUserData мы будем по следующим полям

tp_ListId - Id списка, из которого мы будем выбирать данные;
tp_IsCurrent - флаг, указывающий на текущую версию;
tp_RowOrdinal - номер строки. Он используется если полей в таблице не хватает для хранения значений полей. По умолчанию, максимальное кол-во строк, для одного элемента списка/библиотеки документов равно 6;
tp_DeleteTransactionId - Id транзакции удаления. Если элемент не удален, то значение этого поля будет равно 0x

И последний параметр - само поле, где хранится значение. Все эти параметры мы будем брать из схемы поля, представленного свойством SchemaXml:

<Field ID="{0eb872f2-94a8-455b-9c0f-529dd0688a41}" Name="AccessLevel"
    DisplayName="AccessLevel" Type="Integer"
    ColName="int4"
    StaticName="AccessLevel"
    SourceID="{7D206E84-E96D-4D2D-A503-88E4702D7409}"
    Indexed="TRUE" Version="1" RowOrdinal="0">
</Field>

И SQL-запрос будет выглядеть примерно так:

select top 10
    int4
from
    dbo.AllUserData (nolock)
where
    tp_ListId = '7D206E84-E96D-4D2D-A503-88E4702D7409'
    and 
    tp_IsCurrent = 1
    and                    
    tp_RowOrdinal = 0
    and
    tp_DeleteTransactionId = 0x
order by
    1 asc

Решение

Решение будет представлять из себя метод-расширитель для объектов типа SPField. Для начала "болванка" для построение SQL-запроса:

public static string FieldDistinctValues =
        @"select distinct
            %SqlColName%
        from
            dbo.AllUserData
        where
            tp_ListId = @ListId
            and tp_IsCurrent = 1
            and
            tp_RowOrdinal = @RowOrdinal
            and
            tp_DeleteTransactionId = 0x
        order by
            1 asc";

Здесь %SqlColName% мы будем подменять названием колонки из атрибута ColName. Остальные @Параметры будем подставлять, используя стандартный функционал:

/// <summary>
/// Получение уникальных значений поля списка
/// </summary>
/// <typeparam name="TValue">Тип поля</typeparam>
/// <param name="field">Поле списка</param>
/// <returns></returns>
public static IEnumerable<TValue> DistinctValues<TValue>(this SPField field)
{
    var connectionString = string.Empty;
    var res = new List<TValue>();
    SPSecurity.RunWithElevatedPrivileges(
        () =>
        {
            //Получаем ID сайта
            var siteId = field.ParentList.ParentWeb.Site.ID;
            // Инициализируем новый сайт с правами учетной записи пула приложения
            using (var site = new SPSite(siteId))
            {
                connectionString = site.ContentDatabase.DatabaseConnectionString;
            }
            // Получаем значение атрибута ColName
            var colName = field.AttributeValue("ColName");
            var ordinal = field.AttributeValueAsInteger("RowOrdinal");
            var listId = field.ParentList.ID;
            // Создаем комманду
            using (var cmd = new SqlCommand(
                    FieldDistinctValues.Replace("%SqlColName%", colName))
                    {
                        CommandType = CommandType.Text
                    })
            {
                cmd.Parameters.Add(new SqlParameter("@ListId", listId));
                cmd.Parameters.Add(new SqlParameter("@RowOrdinal", ordinal));
                // Инициализируем подключение к базе данных
                using (var con = new SqlConnection(connectionString))
                {
                    cmd.Connection = con;
                    con.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader != null && reader.Read())
                    {
                        // Заполняем полученные значения
                        res.Add(reader[0] is TValue 
                            ? (TValue) reader[0] 
                            : default(TValue));
                    }
                }
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                }
            }
        });
    return res;
}

Выбирать данные из базы данных, минуя объектную модель как минимум некрасиво, т.к. её структура может измениться, но во-первых эта структура (таблица dbo.AllUserData) остается со времен Windows SharePoint Service 2.0, а во-вторых, получаемый прирост производительности оправдывает такое решение.

Производительность

Производительность я измерял, используя список с 10.000 элементами и производил выборку уникальных значений трех полей. Самих уникальных значений в этих полях было 2, 5 и 2045. Каждый запрос я выполнял десять раз и измерял среднее время исполнения. Вот, что у меня получилось:

Сравнение производительности прямого SQL-запроса и объектной модели

Теперь я добавил ещё один момент к выборке данных: подсчет количества элементов в списке. В случае с SQL-запросом надо просто добавить group by. А в случае с объектной моделью группировку придется выполнять в памяти.

Сравнение производительности прямого SQL-запроса и объектной модели при использовании группировки

Результат мало чем отличается от предыдущего. Разница в производительности здесь объясняется очень просто: использую объектную модель SharePoint все равно приходится выбирать все данные из списка. В случае, когда уникальных значений в списке всего два, а самих элементов 10000, придется "вытянуть" 10000 значений из списка SharePoint и только потом в памяти искать уникальные значения.

Можно еще усложнить запрос, добавив к нему предикат для предварительной фильтрации данных, например для реализации быстрого автокомплита в поле.

As is

В заключение еще раз напомню, что выбирать данные напрямую из базы данных, миную объектную модель - это плохо и очень плохо. Поэтому используйте описанный выше метод на свой страх и риск.