Как подружить Linq-to-Entities и Regex

    Entity Framework сильно облегчает разработку систем, использующих базы данных. Не будем сейчас спорить о достоинствах и недостатках этого фреймворка (коих, конечно, немало), а рассмотрим одну из практических задач, которую мне пришлось решать при разработке такой системы.

    Предположим, у нас есть база данных SQLite с довольно большим количеством записей, и эта база используется в нашем .NET приложении через System.Data.SQLite и Entity Framework 6.0. И вот приходит заказчик и сообщает, что ему нужна новая функция поиска записей в базе, да такая, чтобы можно было искать с использованием стандартных регулярных выражений.

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

    А в чём, собственно, проблема?


    А проблемы, вообще говоря, две.
    Во-первых, Linq-to-Entities по умолчанию не умеет переводить вызовы стандартных .NET методов регулярных выражений (классы Regex и Match) в SQL-запросы. Оно и понятно — не все СУБД умеют регулярные выражения вычислять. Можно было бы написать свой Linq-провайдер, но хочется всё же обойтись «малой кровью» (ведь в System.Data.SQLite уже есть готовый провайдер).

    Во-вторых, можно было бы вообще не углубляться в такие дебри и выполнять обработку локально, на стороне клиента. Но очевидно, что решение неудачное — для выполнения простой фильтрации придётся скачивать все данные из таблицы, чтобы в худшем случае потом отбросить все записи. А это — использование памяти и потеря производительности на стороне клиента.

    К счастью, SQLite нам поможет


    В SQLite есть специальный оператор REGEXP, который есть не что иное как синтаксический сахар для пользовательской функции regexp(). По умолчанию эта функция не реализована, и её вызов вызовет ошибку, так что пользователь обязан определить эту функцию, прежде чем использовать её в своих запросах.

    Именно от этого мы и будем отталкиваться. Задача сводится к следующему:
    • зарегистрировать функцию regexp() на SQLite-сервере;
    • заставить Linq-to-Entities переводить наш Linq-запрос таким образом, чтобы использовался оператор REGEXP или функция regexp().


    Итак, приступим.

    System.Data.SQLite уже имеет функционал для регистрации пользовательских функций, вот им-то мы и воспользуемся.
    Создадим класс, реализующий функцию:

    [SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)]
    public class RegExSQLiteFunction : SQLiteFunction
    {
        public static SQLiteFunctionAttribute GetAttribute()
        {
            return (SQLiteFunctionAttribute)typeof(RegExSQLiteFunction).GetCustomAttributes(typeof(SQLiteFunctionAttribute), false).Single();
        }
    
        public override object Invoke(object[] args)
        {
            try
            {
                // Обратите внимание, что аргументы передаются в обратном порядке
                return Regex.IsMatch((string)args[1], (string)args[0]);
            }
            catch (Exception ex)
            {
                // Согласно документации System.Data.SQLite, исключение нужно вернуть как результат
                return ex;
            }
        }
    }
    


    Чтобы зарегистрировать эту функцию, вызываем соответствующий метод у объекта подключения:
    var connection = new SQLiteConnection(connectionString);
    connection.Open();
    connection.BindFunction(RegExSQLiteFunction.GetAttribute(), new RegExSQLiteFunction());    
    


    Всё! Теперь при использовании оператора REGEXP в любом SQL-запросе, выполняемом сервером, будет вызываться наша реализация. Удобно, не правда ли?

    Как это вызвать через Linq в Entity Framework?


    Вот тут начинается «чёрная магия».
    При создании модели Entity Framework нам необходимо зарегистрировать в ней новое соглашение, описывающее нашу новую функцию. Для этого, например, можно в классе контекста данных при его создании добавить такой вызов метода:
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Здесь происходит настройка модели при использовании Code First
    
        // Добавляем соглашение в модель
        modelBuilder.Conventions.Add(new RegexpFunctionConvention());
    }
    


    А сам класс соглашения описывает нашу функцию и делает её «понятной» Linq-адаптеру System.Data.SQLite:
    public class RegexpFunctionConvention : IStoreModelConvention<EdmModel>
    {
        public void Apply(EdmModel item, DbModel model)
        {
            // Два входных строковых параметра
            var patternParameter = FunctionParameter.Create("pattern", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
            var inputParameter = FunctionParameter.Create("input", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
    
            // Возвращаемое значение            
            var returnValue = FunctionParameter.Create("result", this.GetStorePrimitiveType(model, PrimitiveTypeKind.Boolean), ParameterMode.ReturnValue);
    
            var function = this.CreateAndAddFunction(item, "regexp", new[] { patternParameter, inputParameter }, new[] { returnValue });
        }
    
        private EdmFunction CreateAndAddFunction(EdmModel item, string name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues)
        {
            EdmFunctionPayload payload = new EdmFunctionPayload 
            { 
                StoreFunctionName = name, 
                Parameters = parameters, 
                ReturnParameters = returnValues, 
                Schema = this.GetDefaultSchema(item),             
                IsBuiltIn = true  // Сообщаем, что эта функция известна СУБД
            };
                
            EdmFunction function = EdmFunction.Create(name, this.GetDefaultNamespace(item), item.DataSpace, payload, null);
            item.AddItem(function);
            return function;
        }
    
        // Часть методов опущена
    }
    


    И последнее, что нужно сделать, — это создать «метод-заглушку» для компилятора, чтобы на её основе Linq-провайдер генерировал уже настоящую функцию в SQL-запросе.

    internal static class R
    {
        [DbFunction("CodeFirstDatabaseSchema", "regexp")]
        public static bool Regexp(string pattern, string input)
        {
            // Этот метод никогда не будет вызываться
            throw new NotImplementedException();
        }
    }
    


    Живой пример


    Проделав всю подготовительную работу, мы, наконец, можем пожинать плоды и писать лаконичные Linq-запросы, которые к тому же будут выполняться на стороне сервера (правда, используя реализацию пользовательской функции на стороне клиента, но это отнюдь не то же самое, что обработка запроса с помощью Linq-to-Objects локально).

    // Найдём все entities типа Item, у которых значение свойства Name удовлетворяет регулярному выражению pattern
    using (MyDataContext context = new MyDataContext(myConnection))
    {
        var filteredItems = context.Items.Where(i => R.Regexp(pattern, i.Name));
    }
    


    Таким образом мы получаем IQueryable, выполняемый на сервере, со всеми его достоинствами и преимуществами!
    Буду рад, если моя небольшая статья поможет вам при решении похожих задач.
    • +22
    • 9,1k
    • 6
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 6
    • 0
      А что делать если SQL сервер не поддерживает нужный функционал? Например мне нужна функция которая бы считала Расстояние Левенштейна для 2 слов?
      • +2
        MSSQL поддерживает загрузку .NET-сборок, функции в которых можно мапить на хранимые процедуры самого SQL. За счёт этого можно сделать поддержку и регулярных выражений в MSSQL, и прокинуть её в тот же linq to sql примерно так же, как это показано в этой статье для EF.
      • +4
        Забавно читать «на стороне сервера», «на стороне клиента», когда речь идет о файловой БД :)
        • +1
          Согласен, читать забавно. Но по факту так оно и есть, просто клиент и сервер находятся в одном процессе.
        • +2
          >> происходит на стороне сервера, что позволяет ускорить его обработку
          Насколько ускорилась обработка?
          • +2
            Проделав всю подготовительную работу, мы, наконец, можем пожинать плоды и писать лаконичные Linq-запросы, которые к тому же будут выполняться на стороне сервера (правда, используя реализацию пользовательской функции на стороне клиента, но это отнюдь не то же самое, что обработка запроса с помощью Linq-to-Objects локально).

            Весьма провокационная фраза, если не знать, что SQLite — встраиваемая база и сервер и клиент — один и тот же процесс. А то ведь можно удивиться — неужели код передается с клиента на сервер. Думаю, это стоило бы пояснить.

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.