Entity Framework 6 with Full-Text Search via LINQ

    Хочу поделиться своим костылем в решении довольно банальной проблемы: как подружить полнотекстовый поиск MSSQL c Entity Framework. Тема очень узкоспециальная, но как мне кажется, актуальна на сегодняшний день. Интересующихся прошу под кат.


    Все началось с боли


    Я разрабатываю проекты на C# (ASP.NET) и иногда пишу микросервисы. В большинстве случаев для работы с данными я использую базу данных MSSQL. В качестве связующего звена между базой данных и моим проектом используется Entity Framework. С EF я получаю широкие возможности для работы с данными, формирования правильных запросов, регулирования нагрузки на сервер. Волшебный механизм LINQ просто очаровывает своими возможностями. Спустя годы я уже и не представляю более быстрые и удобные способы по работе с базой данных. Но как и практически любая ORM, EF имеет ряд недостатков. Во первых это производительность, но это тема отдельной статьи. А во вторых — это покрытие возможностей самой базы данных.


    В MSSQL есть встроенный полнотекстовый поиск который работает “из коробки”. Для выполнения полнотекстовых запросов можно воспользоваться встроенными предикатами (CONTAINS и FREETEXT) или функциями (CONTAINSTABLE и FREETEXTTABLE). Есть только одна проблема: EF не поддерживает полнотекстовые запросы, от слова совсем!


    Приведу пример из реального опыта. Допустим у меня есть таблица статей — Article, и я создаю для нее класс описывающий эту таблицу:


    /// c#
    public partial class Article
    {
        public int Id { get; set; }
        public System.DateTime Date { get; set; }
        public string Text { get; set; }
        public bool Active { get; set; }
    }

    Потом мне надо сделать выборку из этих статей, скажем, вывести последние 10 опубликованных статей:


    /// c#
    dbEntities db = new dbEntities();
    var articles = db.Article
        .Where(n => n.Active)
        .OrderByDescending(n => n.Date)
        .Take(10)
        .ToArray();

    Все очень красиво пока не появляется задача добавить полнотекстовый поиск. Поскольку поддержки полнотекстовых функций выборки в EF нет (в .NET core 2.1 уже частично есть) то остается либо использовать какую-то стороннюю библиотеку, либо написать запрос на чистом SQL.


    SQL запрос из примера выше не такой уж и сложный:


    SELECT TOP (10) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Date] AS [Date], 
    [Extent1].[Text] AS [Text], 
    [Extent1].[Active] AS [Active]
    FROM [dbo].[Article] AS [Extent1]
    WHERE [Extent1].[Active] = 1
    ORDER BY [Extent1].[Date] DESC

    В реальных проектах все обстоит не так просто. Запросы к базе данных на порядок сложнее и поддерживать их в ручную сложно и долго. В результате первое время я писал запрос с помощью LINQ, потом доставал сгенерированный текст SQL запроса к БД, и уже в него внедрял полнотекстовые условия выборки данных. Далее отправлял это в db.Database.SqlQuery и получал нужные мне данные. Это все конечно хорошо пока на запрос не нужно навешать десяток различных фильтров со сложными join-нами и условиями.


    Итак — у меня есть конкретная боль. Надо ее решать!


    В поисках решения


    В очередной раз сидя в своем любимом поиске в надежде отыскать хоть какое-то решение я наткнулся на этот репозиторий. С помощью этого решения можно внедрить в LINQ поддержку предикатов (CONTAINS и FREETEXT). Благодаря поддержки EF 6 специального интерфейса IDbCommandInterceptor, позволяющего делать перехват готового запроса SQL, перед отправкой его в БД и было реализовано данное решение. В поле Contains подставляется специальная сгенерированная строка маркер, а потом после генерации запроса это место заменяется на предикат Пример:


    /// c#
    var text = FullTextSearchModelUtil.Contains("code");
        db.Tables.Where(c=>c.Fullname.Contains(text));

    Однако если выборку данных нужно отсортировать по рангу совпадений, то это решение уже не подойдет и придется писать SQL запрос вручную. По сути, это решение, подменяет обычный LIKE на выборку по предикату.


    Итак, на этом этапе у меня встал вопрос: можно ли реализовать реальный полнотекстовый поиск с помощью встроенных функций MS SQL (CONTAINSTABLE и FREETEXTTABLE) чтобы все это генерировалось через LINQ да еще и с поддержкой сортировки запроса по рангу совпадений? Как оказалось, можно!


    Реализация


    Для начала нужно было разработать логику написания самого запроса с помощью LINQ. Поскольку в реальных SQL запросах с полнотекстовыми выборками чаще всего используют JOIN для присоединения виртуальной таблицы с рангами, я решил пойти по этому же пути и в LINQ запросе.


    Вот пример такого LINQ запроса:


    /// c#
    var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText));
    
    var query = db.Article
        .Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new
        {
            article.Id,
            article.Text,
            fts.Key,
            fts.Rank,
        })
        .OrderByDescending(n => n.Rank);

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


    Дополнительный класс FTS_Int используемый в данном запрос:


    /// c#
    public partial class FTS_Int
    {
        public int Key { get; set; }
        public int Rank { get; set; }
        public string Query { get; set; }
    }

    Название было выбрано не случайно, так как ключевой столбец в этом классе должен совпадать по тику с ключевым столбцом в таблице поиска (в моем примере с [Article].[Id] тип int). В случае если нужно делать запросы по другим таблицам с другими типами ключевых столбцов, я предполагал просто скопировать подобный класс и создать его Key того типа который нужен.


    Само условие для формирование полнотекстового запроса предполагалось передавать в переменной queryText. Для формирование текста этой переменной была реализована отдельная функция:


    /// c#
    string queryText = FtsSearch.Query(
        dbContext: db, // текущий контекст БД, нужен для формирования правильных имен таблиц
        ftsEnum: FtsEnum.CONTAINS, // тип запроса: CONTAINS или FREETEXT
        tableQuery: typeof(News), // тип таблицы по которой делается запрос
        tableFts: typeof(FTS_Int), // тип вспомогательного класс
        search: "text"); // условие полнотекстового поиска

    Выполнение готового запроса и получение данных:


    /// c#
    var result = FtsSearch.Execute(() => query.ToList());

    Последняя функция FtsSearch.Execute обертка используется для временного подключения интерфейса IDbCommandInterceptor. В примере приведенном по ссылке выше автор предпочел использовать алгоритм подмены запросов постоянно для всех запросов. В результате после подключения механизма замены запросов в каждом запросе ищется необходимая комбинация для замены. Мне такой вариант показался расточительным, поэтому выполнение самого запроса данных выполняется в передаваемой функции, которая перед вызовом подключает автозамену запроса а после вызова — отключает.


    Применение


    Я использую автогенерацию классов моделей данных из БД с помощью файла edmx. Поскольку просто созданный класс FTS_Int использовать в EF нельзя по причине отсутствия необходимых метаданных в DbContext, я создал реальную таблицу по его модели (может кто знает способ получше, буду рад вашей помощи в комментариях):


    Скриншот таблице созданной в файле edmx



    CREATE TABLE [dbo].[FTS_Int] (
        [Key]   INT          NOT NULL,
        [Rank]  INT          NOT NULL,
        [Query] NVARCHAR (1) NOT NULL,
        CONSTRAINT [PK_FTS_Int] PRIMARY KEY CLUSTERED ([Key] ASC)
    );

    После этого при обновлении файла edmx из БД добавляем созданную таблицу и получаем ее сгенерированный класс:


    /// c#
    public partial class FTS_Int
    {
        public int Key { get; set; }
        public int Rank { get; set; }
        public string Query { get; set; }
    }

    Запросы к этой таблице вестись не будут, она лишь нужна, чтобы правильно сформировались метаданные для создания запроса. Финальный пример использования полнотекстовых запрос к БД:


    /// c#
    string queryText = FtsSearch.Query(
        dbContext: db,
        ftsEnum: FtsEnum.CONTAINS,
        tableQuery: typeof(Article),
        tableFts: typeof(FTS_Int),
        search: "text");
    
    var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText));
    
    var query = db.Article
        .Where(n => n.Active)
        .Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new
        {
            article,
            fts.Rank,
        })
        .OrderByDescending(n => n.Rank)
        .Take(10)
        .Select(n => n.article);
    
    var result = FtsSearch.Execute(() => query.ToList());

    Также есть поддержка асинхронных запросов:


    /// c#
    var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());

    SQL запрос сформированный до автозамены:


    SELECT TOP (10) 
        [Project1].[Id] AS [Id], 
        [Project1].[Date] AS [Date], 
        [Project1].[Text] AS [Text], 
        [Project1].[Active] AS [Active]
        FROM ( SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Date] AS [Date], 
            [Extent1].[Text] AS [Text], 
            [Extent1].[Active] AS [Active], 
            [Extent2].[Rank] AS [Rank]
            FROM  [dbo].[Article] AS [Extent1]
            INNER JOIN [dbo].[FTS_Int] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key]
            WHERE ([Extent1].[Active] = 1) AND ([Extent2].[Query] LIKE @p__linq__0 ESCAPE N'~')
        )  AS [Project1]
        ORDER BY [Project1].[Rank] DESC

    SQL запрос сформированный после автозамены:


    SELECT TOP (10) 
        [Project1].[Id] AS [Id], 
        [Project1].[Date] AS [Date], 
        [Project1].[Text] AS [Text], 
        [Project1].[Active] AS [Active]
        FROM ( SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Date] AS [Date], 
            [Extent1].[Text] AS [Text], 
            [Extent1].[Active] AS [Active], 
            [Extent2].[Rank] AS [Rank]
            FROM  [dbo].[Article] AS [Extent1]
            INNER JOIN CONTAINSTABLE([dbo].[Article],(*),'text') AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key]
            WHERE ([Extent1].[Active] = 1) AND (1=1)
        )  AS [Project1]
        ORDER BY [Project1].[Rank] DESC

    По умолчанию полнотекстовый поиск работает по всем столбцам таблицы:


    CONTAINSTABLE([dbo].[Article],(*),'text')

    Если нужно сделать выборку только по некоторым полям, то их можно указать в параметре fields функции FtsSearch.Query.


    Итого


    Результат — поддержка полнотекстового поиска в LINQ.


    Нюансы данного подхода.


    1. Параметр search в функции FtsSearch.Query не использует каких либо проверок или оберток для защиты от SQL инъекций. Значение этой переменной передается как есть в текст запроса. Если есть какие то идеи по этому поводу пишите в комментариях. Я же использовал обычное регулярное выражение которое просто убирает все символы отличных от букв и цифр.


    2. Также нужно учитывать особенности построения выражений для полнотекстовых запросов. Параметр в функцию


      /* Запрос с ошибкой */
      CONTAINSTABLE([dbo].[News],(*),'Новое исследование')

      имеет недопустимый формат так как MS SQL требует разделения слов логическими литералами. Чтобы запрос был выполнен успешно нужно исправить его так:


      /* Корректный запрос */
      CONTAINSTABLE([dbo].[News],(*),'Новое and исследование')

      или изменить функцию выборки данных


      /* Корректный запрос */
      FREETEXTTABLE([dbo].[News],(*),'Новое исследование')

      За более подробной информацией об особенностях создания запросов лучше обратиться к официальной документации.


    3. Стандартное логирование с таким решением работает некорректно. Для этого был добавлен специальный логгер:


      /// c#
      db.Database.Log = (val) => Console.WriteLine(val);

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



    В ходе тестирования я проверял и на более сложных запросах со множественными выборками из разных таблиц и здесь не возникло никаких проблем.


    Исходники на GitHub

    • +13
    • 4.4k
    • 5
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 5

    • UFO just landed and posted this here
        +1
        как сейчас mssql2016 дружит с морфологией русского языка?
          0
          Ну каких то особенностей не замечал, все работает как часы, поддержка именно русского из коробки есть. Стоп слова тоже имеются.
          +1
          Еще такой вариант был на хабре habr.com/ru/post/189806
            0
            Я этот вариант пропустил. За ссылку спасибо. В моей реализации основная цель была именно в сортировке по рангу полученных значений. Подобных реализаций я еще не встречал нигде, поэтому и решил написать пост.

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