Entity Framework Code First — индексация полей и полнотекстовый поиск

image

По роду моей деятельности, мне часто приходится делать различные небольшие проекты, в основном, это сайты написанные на ASP.NET MVC. В любом современном проекте присутствуют данные, а значит и база данных, а значит с ней нужно как то работать.
Если отбросить все дискуссии про «за и против», то спешу сообщить, что мой выбор пал на Entity Framework Code First. Во время разработки проекта, я уделяю внимание исключительно бизнес-логике и не трачу время на проектирование базы данных и прочие шаблонные действия. Неприятным сюрпризом при использовании такого подхода для меня стало отсутствие возможности «из коробки» у Entity Framework возможности строить индекс по полям, а так же пользоваться удобным и современным механизмом полнотекстового поиска.

После многочасового гугления, опробовав десятки различных методов со StackOverflow и прочих подобных сайтов, я пришел к выводу, что очевидного и простого решения проблемы нет, поэтому решил сделать собственное, об этом и пойдет речь далее.
Реализация

Основным требованием к решению проблемы, является простота интеграции в любой новый (существующий) проект. В Code First принято все настраивать атрибутами, поэтому хорошо было бы сделать так:

public class SomeClass
  {
    public int Id { get; set; }

    [Index]
    public string Name { get; set; }

    [FullTextIndex]
    public string Description { get; set; }
  }

при этом, не хотелось бы переопределять DatabaseInitializer и делать прочие нетривиальные действия.

В своей работе я использую Visual Studio 2013 Ultimate. Создадим новый проект типа Class Library, сразу добавим в него Entity Framework 6 Beta 1 с помощью NuGet консоли (Package Manager Console):

PM> Install-Package EntityFramework -Pre

Создадим атрибуты Index и FullTextSearch, а так же перечисление для FullTextSearch:
public class IndexAttribute : Attribute { }
public class FullTextIndexAttribute : Attribute { }
public class FullTextIndex
 {
    public enum SearchAlgorithm
     {
        Contains,
        FreeText
     }
 }

Если Вы ранее работали с полнотекстовым поиском, то Вы наверняка поняли зачем нужен Contains и FreeText, если нет, то Вам сюда.
Далее, создадим абстрактный класс, унаследованный от DbContext:
public abstract class DbContextIndexed : DbContext
 {
    private static bool Complete;
    private int? language;

    public int Language
     {
        get
         {
          return language.HasValue ? language.Value : 1049; //1049 - русский язык
         }
       set
        {
         language = value;
        }
     }

    protected override void Dispose(bool disposing)
     {
      if (!Complete)
       {
        Complete = true;
        CalculateIndexes();
       }
       base.Dispose(disposing);
     }

    private void CalculateIndexes()
     {
      if (GetCompleteFlag()) return;

      //Получаем все сущности текущего DbContext
      foreach (var property in this.GetType()
                     .GetProperties()
                     .Where(f => f.PropertyType.BaseType != null && f.PropertyType.BaseType.Name == "DbQuery`1"))
       {
        var currentEntityType = property.PropertyType.GetGenericArguments().FirstOrDefault();
        if (currentEntityType == null || currentEntityType.BaseType.FullName != "System.Object")
            continue;

        //Получаем название таблицы в БД
        var tableAttribute = currentEntityType
                       .GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault() as TableAttribute;
        var tableName = tableAttribute != null ? tableAttribute.Name : property.Name;

        //Получаем у сущности свойства помеченые аттрибутом Index, создаем по ним индекс
        BuildingIndexes(tableName, currentEntityType.GetProperties()
                        .Where(f => f.GetCustomAttributes(typeof(IndexAttribute), false)
                        .Any()));

        //Получаем у сущности свойства помеченые атрибутом FullTextIndex, создаем по ним индекс
        BuildingFullTextIndexes(tableName, currentEntityType.GetProperties()
                        .Where(f => f.GetCustomAttributes(typeof(FullTextIndexAttribute), false)
                        .Any()));
     }
     CreateCompleteFlag();
    }

    private void BuildingIndexes(string tableName, IEnumerable<PropertyInfo> propertyes)
     {
     foreach (var property in propertyes)
         Database.ExecuteSqlCommand(String.Format("CREATE INDEX IX_{0} ON {1} ({0})", property.Name, tableName));
     }

    private void BuildingFullTextIndexes(string tableName, IEnumerable<PropertyInfo> propertyes)
     {
     var fullTextColumns = string.Empty;
     foreach (var property in propertyes)
           fullTextColumns += String.Format("{0}{1} language {2}", 
                          (string.IsNullOrWhiteSpace(fullTextColumns) ? null : ","),
                          property.Name, Language);

     //Создаем полнотекстовый индекс
     Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction,
               String.Format("IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('{1}') and name='IX_{2}') 
                                          CREATE UNIQUE INDEX IX_{2} ON {1} ({2});
                                          CREATE FULLTEXT CATALOG FTXC_{1} AS DEFAULT;
                                          CREATE FULLTEXT INDEX ON {1}({0}) KEY INDEX [IX_{2}] ON [FTXC_{1}]", 
                                          fullTextColumns, tableName, "Id"));
     }

    private void CreateCompleteFlag()
     {
     Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, 
                      "CREATE TABLE [dbo].[__IndexBuildingHistory](
                       [DataContext] [nvarchar](255) NOT NULL, 
                       [Complete] [bit] NOT NULL, 
                       CONSTRAINT [PK___IndexBuildingHistory] 
                       PRIMARY KEY CLUSTERED ([DataContext] ASC))");
     }

    private bool GetCompleteFlag()
     {
     var queryResult = Database.SqlQuery(typeof(string), 
            "IF OBJECT_ID('__IndexBuildingHistory', 'U') IS NOT NULL SELECT 
             'True' AS 'Result' ELSE SELECT 'False' AS 'Result'")
            .GetEnumerator();
     queryResult.MoveNext();
     return bool.Parse(queryResult.Current as string);
     }
 }

чтобы не раздувать пост, здесь намеренно убраны summary и некоторые комментарии, полная версия на GitHub'e. Если кратко пояснить, то EF создает модель при первичном обращении к DbContext'у, соответственно строить индексы на конструкторе мы не можем, остается самый простой вариант построить их после создания модели, при попытке уничтожить экземпляр DbContext. Далее, чтобы не нагружать БД каждый раз несколькими запросами и попыткой создания, в лучших традициях EF создадим в базе служебную таблицу __IndexBuildingHistory, наличие которой, будет свидетельствовать о наличии индексов. Остальное очевидно.

В целом, если уже сейчас создать модель, пометить ее атрибутами и запустить проект, то индексы будут успешно созданы, однако, нам еще нужно удобное использование полнотекстового индекса, для это создадим класс расширение (extension class):
public static class IQueryableExtension
 {
    public static IQueryable<T> FullTextSearch<T>(this DbSet<T> queryable,
                           Expression<Func<T, bool>> func,
                           FullTextIndex.SearchAlgorithm algorithm = FullTextIndex.SearchAlgorithm.FreeText) 
                           where T : class
    {
    var internalSet = queryable.AsQueryable()
                           .GetType()
                           .GetProperty("System.Data.Entity.Internal.Linq.IInternalSetAdapter.InternalSet", 
                                    BindingFlags.NonPublic | BindingFlags.Instance)
                           .GetValue(queryable.AsQueryable());
    var entitySet = (EntitySet)internalSet.GetType()
                           .GetProperty("EntitySet")
                           .GetValue(internalSet);

    var searchType = algorithm == FullTextIndex.SearchAlgorithm.Contains ? 
                                                           "CONTAINS" :
                                                           "FREETEXT";
    var columnName = ((MemberExpression)((BinaryExpression)func.Body).Left).Member.Name;
    var searchPattern = ((ConstantExpression)((BinaryExpression)func.Body).Right).Value;

    return queryable.SqlQuery(
                String.Format("SELECT * FROM {0} WHERE {1};", 
                entitySet.Name, 
                String.Format("{0}({1},'{2}')", 
                    searchType, 
                    columnName, 
                    searchPattern)))
                .AsQueryable();
    }
 }

Вот и все, казалось бы, такая популярная проблема как индексы и полнотекстовый поиск требует особого внимания со стороны создателей Entity Framework, однако, простого решения на сегодняшний день не было. Данная реализация с лихвой перекрывает мои требования к индексации, если Вам чего то не хватает (обработки ошибок, настроек — например, список стоп-слов и т.д.), Вы можете самостоятельно забрать проект с GitHub'a и доработать, либо написать мне. Статья была бы совсем скучной, если бы мы не попробовали как все это работает, поэтому переходим к использованию.

Использование

1. Создадим проект Console application
2. Добавим Entity Framework 6 beta через NuGet
3. Добавим ссылку на библиотеку (если Вы не читали про реализацию, то Вы можете скачать готовую библиотеку, ссылки в конце статьи)
4. Создадим простую сущность, без вложеностей и связей, для примера этого достаточно:
public class Animal
 {
    public int Id { get; set; }

    [Index]
    [StringLength(200)]
    public string Name { get; set; }

    [FullTextIndex]
    public string Description { get; set; }

    public int Family { get; set; }

    [FullTextIndex]
    public string AdditionalDescription { get; set; }
 }

Сущность животное, с названием (Name), по которому мы построим обычный индекс, описанием (Description) — построим полнотекстовый индекс и прочими полями для вида, мы не будем их использовать. Обратите внимание на строку [StringLength(200)], при создании индекса по строковым полям она обязательна, т.к. MSSQL позволяет строить индексы по полям, размер которых не превышает 900 байт — сколько это в символах, зависит от выбранной Вами кодировки базы данных.

5. Создадим контекст базы данных:
public class DataContext : DbContextIndexed
 {
    public DbSet<Animal> Animals { get; set; }
 }

единственная разница здесь в наследовании, обычно Вы наследуетесь от DbContext, а теперь от нашей DbContextIndexed

6. В Programm.cs добавим обращение к контексту, чтобы спровоцировать создание базы данных:
static void Main(string[] args)
 {
    using (var context = new DataContext())
     {
          var temp = context.Animals.ToList();
     }
 }

7. В config файле проекта пропишите строку подключения к базе данных с названием DataContext:
<configuration>
    <connectionStrings>
        <add name="DataContext" connectionString="Data Source=(local)\SQL; Initial Catalog=EFCF; 
         Integrated Security=true;" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>


8. Нажимаем F5, чтобы создать базу данных, когда программа завершится, с помощью Managment Studio можно убедится, что все работает, как мы запланировали:
image

9. Теперь, давайте попробуем добавить данные, чтобы опробовать поиск:
using (var context = new DataContext())
 {
    context.Animals.Add(new Animal { 
        Name = "Кот", 
        Description = "Относится к семейству кошачьих, очень любят вискас." });
    context.Animals.Add(new Animal { 
        Name = "Лев", 
        Description = "Лев по праву считается королем зверей, самый известный среди котов." });
    context.Animals.Add(new Animal { 
        Name = "Пантера", 
        Description = "Пантера - это маленькая черная кошка." });
    context.Animals.Add(new Animal { 
        Name = "Тигр", 
        Description = "Большой полосатый кот." });
    context.Animals.Add(new Animal { 
        Name = "Питбуль", 
        Description = "Хорошая бойцовая собака." });
     context.Animals.Add(new Animal { 
        Name = "Американский стафардширский терьер", 
        Description = "Произошел от питбуля, является примером отличной бойцовой собаки." });
    context.SaveChanges();
 }

запустим, чтобы данные записались в БД, теперь попробуем поискать:
using (var context = new DataContext())
 {
    foreach (var pet in context.Animals.FullTextSearch(f => f.Description == "коты"))
         Console.WriteLine("{0} - {1}", pet.Name, pet.Description);
 }

результат следующий:
image

У меня установлена версия MSSQL 2008R2, поэтому результат хороший, но не идеальный. Насколько я знаю в 2013-ой версии мы бы еще получили значение пантера, т.к. «кошка», тоже бы учлось.

Я считаю, что довольно простым, и самое главное, «стандартным» способом можно пользоваться полнотекстовым поиском и строить индексы по полям. Данной реализации достаточно для 95% маленьких проектов, но я искренне надеюсь, что создатели Entity Framework все таки реализуют данный функционал «в коробке».

Источники

Скачать готовую библиотеку:
в формате zip
в формате dll
Проект выложен на GitHub
Entity Framework 6 beta на сайте Nuget
Share post

Similar posts

Comments 24

    +1
    Увидев картинку из своего поста на минуту испытал когнитивный диссонанс, потом вчитался и понял что это другой пост))
    За статью спасибо.
      +1
      Извиняюсь, картинку искал в Google, просто она самая подходящая.
        0
        Так я же не в претензии )
        Главное, что статья хорошая, а рисунок не жалко )
      0
      Очень полезная и нужная статья. Спасибо, добавил в избранное.

      Но вот оформление кода это что-то… Растащите цепочку вызовов по разным строкам, пожалуйста
        +1
        Вечером сделаю, к сожалению с работы не могу править пост, прокси режет траффик.
        +1
        спасибо, познавательно.
        правда, честно говоря, мне даже не приходит в голову, как можно программировать какую-то бизнес-логику, не зная о схеме БД.
          0
          Ну в любом случае при бизнес-программировании сначала строится граф сущностей и связей между ними «на бумаге». Просто с EF Code First можно пропустить довольно скучный, медленный и рутинный процесс «набивания структуры БД» и написания классов-оберток над таблицами. Тут достаточно или написать новые классы или с помощью Fluent API отобразить существующие POCO.
          Хотя синтаксис Fluent API при всей простоте и понятности для сложных баз данных — это кошмар =)
            0
            Ну набивать структуру БД тоже не обязательно — Management Studio позволяет достаточно удобно визуально проектировать структуру, которую потом при помощи того же Entity Framework можно перенести в код.
              0
              Вопрос тут в том, что брать за основной источник метаданных — код или базу. В обоих случаях у тебя теоретически одинаковые возможности, но на практике детали отличаются. Ну, типа того что в code-first действительно нету индексов, и их нужно в миграции задавать. Зато если code first, то у тебя полный контроль над тем, что у тебя в классах, когда главная БД — приходится их частично генерировать.

              Код, как по мне, конкретно в EF удобнее всего получается с code first и встроенными миграциями. Чисто по совокупности плюсов/минусов
            0
            Как не зная? Модель контекста дает полную информацию о схеме базы при генерировании EnityModel.
              +1
              Пост не панацея при выборе метода подхода к проектированию БД, просто кто работает с Code First, сталкиваются с такими проблемами как индексация. Понятно, что для сложного проекта весьма проблематично использовать CF с таком виде, в каком он сейчас есть.
              +1
              С производительностью проблем нет (имею в виду EF в целом)? На какую в среднем нагрузку рассчитывается?
              p.s.
              картинка из русифицированной Managment Studio рвёт глаза.
                0
                Про производительность? Хм… вопрос интересный, проверка индексов происходит один раз за запуск приложения, так же как и сверяется модель, поэтому здесь Вы разницу вряд ли заметите, построение самих индексов происходит один раз (потребностей в миграция пока не возникает).
                Так же, все упирается в скорость работы ORM, меня для маленьких проектов Entity Framework вполне устраивает (~1-2 млн записей в самой большой таблице), на хорошем хостинге отрабатывает моментально (естественно с постраничным выводом и прочими «облегчающими» обстоятельствами).
                Про картинку согласен, к сожалению под рукой только русская Managment Studio и русская студия (писал не дома, а на работе куплены именно русские версии)
                  +1
                  Ну, по производительности от любой ORM есть прямой и коственный ущерб.

                  Прямой ущерб — это затраты на сам мэппинг, затраты на мэппинг LINQ в SQL, всякие принципиальные ограничения API. Тут в EF все более-менее нормально: сам по себе он много не жрет, есть способы подоптимизировать, есть способы обойти всякие ограничения.

                  Коственный ущерб — это что получается в среднем проекте на практике, из-за того что выбрана ORM-парадигма.

                  На практике с ORM получаются весьма крипожопые запросы, тут и там вылезает всякий lazy loading, тащащий по одному полю за 200 запросов, и т.п. Весьма типично увидеть в профайлере 500 запросов на страничку, и потратить прилично времени чтобы скостить хотя-бы до 50-ти.

                  Даже от прямого, по меркам ORM, кода, у многих базистов будет батхерт: то, что можно вытащить одной хранимкой в несколько resultset-ов, в ORM весьма типично выдирать в 5-10 SQL-запросов, с дикими join-ами и протаскиванием массивов id-шников с предыдущих запросов.

                  Впрочем, для очень многих проектов, плюсы ORM перевешивают минусы, а узкие места всегда можно переделать на те же хранимки и руками написанные запросы.
                    0
                    Все перечисленные вами минусы убиваются одним действием — пишете хранимую процедуру и вызываете ее из ORM
                  0
                  Если использовать миграции, то там есть хелпер-метод AddIndex. Ну или просто голым SQL можно все сделать как угодно.

                  А как жить без миграций (а точнее — с автоматическими миграциями), я не очень понимаю. Только если в самом начале проекта, когда еще нет данных на проде и всяких тестовых/staging-базах, и базу можно пересоздавать на каждый чих. И то — я бы не стал бы.
                    +2
                    Интересно другое — если это Code First, то скорее всего классы модели используются в BL. И зачем тогда BL эти знания? Зачем все это на модели?

                    Кроме того, это привязывает код к подробностям реализации SQL, что IMHO не правильно.

                    Такому коду (настройки БД и прочее) самое место в одной из реализаций IDatabaseInitializer или подобном.

                      0
                      Согласен с Вами, это удобно на одном двух проектах, но когда они небольшие и их десять-двадцать и не нужно инициализировать базу данных, то гораздо проще добавить пару атрибутов и унаследоваться. Плюс на первостепенном этапе часто бывает, что какие-то сущности уходят, какие то добавляются, лично мне так удобнее, возможно еще кому то по нраву сделать так же, правильнее безусловно сделать как написали Вы.
                        0
                        Небольшое замечание — БД инициализировать нужно всегда, ведь кто-то её должен создавать. Просто по умолчанию используется реализация CreateDatabaseIfNotExists. Но есть и другие варианты (DropCreateDatabaseAlways, DropCreateDatabaseIfModelChanges, MigrateDatabaseToLatestVersion). Более того, унаследовавшись от одного них и переопределив метод Seed() вы можете заполнять БД исходными данными при её создании (например забивать в неё словари и т.п.) А это и в мелких проектах бывает полезно.
                      0
                      IMHO, если нужен полнотекстовый поиск смотрите в сторону ElasticSearch и других подобных решений.

                      После перевода полнотекстового поиска на ElasticSearch вспоминаю о MS SQL как об ужасном сне.
                        0
                        Возможно так, не пробовал, обязательно возьму на заметку. Сложность заключается в том, что небольшие проекты — это как правило сайты, которые выставляются на «стандартный» хостинг, на который очень и очень сложно добавить что-то свое (я имею ввиду установку ElasticSearch и других подобных решений), а MSSQL со всеми его расширениями предоставляет 95% провайдеров. Опять же статья не призывает использовать такой метод, я лишь описал его, так как меня он полностью устраивает и возможно найдутся другие разработчики, которым это будет удобно.
                        +1
                        > В Code First принято все настраивать атрибутами

                        Я с вами тут не соглашусь. Я всегда предпочитал использовать Fluent API, так как сущность сама по себе не должна предоставлять никакой информации о своих индексах, primary key и т.п. Для этого должен существовать отдельный слой работы с данными.

                        А так статья хорошая, спасибо.
                          0
                          Да, есть и такой вариант, мне он показался слишком «заковыристым» что-ли, спасибо!
                          0
                          Вот эта строка выдаёт ошибку (невозможно преобразовать типы):
                          var columnName = ((MemberExpression)((BinaryExpression)func.Body).Left).Member.Name;

                          Only users with full accounts can post comments. Log in, please.