Прямой SQL в EntityFramework. Теперь со строгой типизацией

Привет!

Сегодня мы немного поговорим про EntityFramework. Совсем чуть-чуть. Да, я знаю что к нему можно относиться по-разному, многие от него плюются, но за неимением лучшей альтернативы — продолжают использовать.

Так вот. Часто ли вы используете в своём C#-проекте с настроенным ORM-ом прямые SQL-запросы в базу? Ой, да бросьте, не отнекивайтесь. Используете. Иначе как бы вы реализовывали удаление/обновление сущностей пачками и оставались живы…

Что мы больше всего любим в прямом SQL? Скорость и простоту. Там, где "в лучших традициях ORM" надо выгрузить в память вагончик объектов и всем сделать context.Remove (ну или поманипулировать Attach-ем), можнo обойтись одним мааааленьким SQL-запросом.
Что мы больше всего не любим в прямом SQL? Правильно. Отсутствие типизации и взрывоопасность. Прямой SQL обычно делается через DbContext.Database.ExecuteSqlCommand, а оно на вход принимает только строку. Следовательно, Find Usages в студии никогда не покажет вам какие поля каких сущностей ваш прямой SQL затронул, ну и помимо прочего вам приходится полагаться на свою память в вопросе точных имён всех таблиц/колонок которые вы щупаете. А ещё молиться, что никакой лоботряс не покопается в вашей модели и не переименует всё в ходе рефакторинга или средствами EntityFramework, пока вы будете спать.

Так ликуйте же, адепты маленьких raw SQL-запросов! В этой статье я покажу вам как совместить их с EF, не потерять в майнтайнабильности и не наплодить детонаторов. Ныряйте же под кат скорее!

А чего конкретно хотим достичь?

Итак, в этой статье я покажу вам отличный подход, который раз и навсегда избавит вас от беспокойства о проблемах, которые обычно вызывает прямой SQL в тандеме с EntityFramework. Ваши запросы приобретут человеческий облик, будут находиться через Find Usages и станут устойчивы к рефакторингу (удалению/переименованию полей в сущностях), а ваши ноги потеплеют, язвы рассосутся, карма очистится.

Нам понадобится: C# 6.0 (ну, тот, в котором интерполяция строк реализована), лямбда-выражения и немножко прямых рук. Я назвал эту технику "SQL Stroke". В конечном счете мы напишем несколько extension-методов для DbContext, позволяющих отправлять в базу SQL со строго типизированными вставками. Для этого нам понадобится пообщаться с метаданными EntityFramework, попарсить лямбда-выражения и починить все возникающие по ходу баги и corner case-ы.

Вот как будет выглядеть ваш прямой SQL после прочтения этой статьи:

using (var dc = new MyDbContext())
{
    //----------
    dc.Stroke<Order>(x => $"DELETE FROM {x} WHERE {x.Subtotal} = 0");
    //                                              ^ IntelliSense!

    //----------
    var old = DateTime.Today.AddDays(-30);
    dc.Stroke<Customer>(x => $"UPDATE {x} SET {x.IsActive} = 0 WHERE {x.RegisterDate} < {old}");

    //----------
    dc.Stroke<Item, Order>((i, o) => $@"
UPDATE {i} SET {i.Name} = '[FREE] ' + {i.Name} 
FROM {i}
INNER JOIN {o} ON {i.OrderId} = {o.Id}
WHERE {o.Subtotal} = 0"
, true);

}

TL;DR: короче вот оно на гитхабе, там нагляднее

Здесь мы видим, что при вызове .Stroke тип-параметрами мы указываем сущности (замапленные на таблицы), с которыми будем работать. Они же становятся аргументами в последующем лямбда-выражении. Если кратко, то Stroke пропускает переданную ему лямбду через парсер, превращая {x} в таблицы, а {x.Property} в соответствующее имя колонки.

Как-то так. Теперь давайте просмакуем подробности.

Сопоставление классов и свойств с таблицами и колонками

Давайте освежим ваши знания Reflection-а: представьте что у вас есть класс (точнее Type) и у вас есть строка с именем проперти из этого класса. Так же имеется наследник EF-ного DbContext-а. Имея оные две вилки и тапок вам надобно добыть имя таблицы, на которую мапится ваш класс и имя колонки в БД, на которую мапится ваша проперть. Сразу же оговорюсь: решение этой задачи будет отличаться в EF Core, однако же на основную идею статьи это никак не влияет. Так что я предлагаю читателю самостоятельно реализовать/нагуглить решение этой задачи.

Итак, EF 6. Требуемое можно достать через весьма популярную магию приведения EF-ного контекста к IObjectContextAdapter:

public static void GetTableName(this DbContext context, Type t)
{
    // кастуем наш контекст к ObjectContext-у
    var objectContext = ((IObjectContextAdapter)context).ObjectContext;

    // достаем метаданные
    var metadata = objectContext.MetadataWorkspace;

    // из них извлекаем нашу коллекцию объектов из CLR-пространства
    var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));

    // и в оных ищем наш тип. Получаем EF-ный дескриптор нашего типа
    var entityType = metadata.GetItems<EntityType>(DataSpace.OSpace)
                .FirstOrDefault(x => objectItemCollection.GetClrType(x) == t);

    // ищем в метадате контейнер из концептуальной модели
    var container = metadata
                .GetItems<EntityContainer>(DataSpace.CSpace)
                .Single()
                .EntitySets
                .Single(s => s.ElementType.Name == entityType.Name);

    // вытаскиваем маппинги этого контейнера на свет б-жий
    var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
                .Single()
                .EntitySetMappings
                .Single(s => s.EntitySet == container);          

    // уплощаем, вытаскиваем данные об источнике данных (таблица)
    var tableEntitySet = mapping
                .EntityTypeMappings.Single()
                .Fragments.Single()
                .StoreEntitySet;

    // берем имя оной
    var tableName = tableEntitySet.MetadataProperties["Table"].Value ?? tableEntitySet.Name;

    // можно покурить
    return tableName;     
}

И, пожалуйста, не спрашивайте меня что же разработчики EntityFramework курили имели в виду, создавая такие лабиринты абстракций и что в нём означает каждый закоулочек. Честно признаюсь — я сам в этом лабиринте могу заблудиться и кусок выше я, не писал, а просто нашел и распотрошил.

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

public static void GetTableName(this DbContext context, Type t, string propertyName)
{
    // вот ровно тот же самый код, до var mappings = ...

    // только вытаскиваем мы из них проперть
    var columnName = mapping
            .EntityTypeMappings.Single()
            .Fragments.Single()
            .PropertyMappings
            .OfType<ScalarPropertyMapping>()
            .Single(m => m.Property.Name == propertyName)
            .Column
            .Name;

    // быстро, не так ли?
    return columnName;
}

Так, и вот тут я сразу и крупными буквами предупреждаю читателя: копаться в EF-метаданных — это медленно! Кроме шуток. Поэтому кэшируйте вообще всё, до чего дотянетесь. В статье есть ссылка на мой код — там я уже озаботился кэшированием — можете пользоваться. Но все равно держите в голове: реальные концептуальные модели EF — стозёвные чудища, хранящие в себе взводы и дивизии различных объектов. Если вам нужно только соотношение тип-имя таблицы и тип/свойство — имя колонки, то лучше один раз достаньте и закэшируйте (только не напоритесь там на утечку памяти — не храните ничего от DbContext-а). В EF Core, говорят, с этим по-лучше.

Выражения

Самое скучное позади. Теперь — лямбда-выражения. Положим, мы хотим иметь метод Stroke, чтобы вызывать его можно было вот таким макаром:

context.Stroke<MyEntity>(x => $"UPDATE {x} WHERE {x.Age} > 10")

Сам метод Stroke простой:

public static void Stroke<T>(this DbContext s, Expression<Func<T, string>> stroke)
{
    object[] pars = null;
    var sql = Parse(context, stroke, out pars);
    context.Database.ExecuteSqlCommand(sql, pars);
}

В его основе лежит метод Parse, который и делает всю основную работу. Как нетрудно догадаться, этот метод должен разбирать лямбда-выражение, полученное от интерполяции строки. Ни для кого не секрет, что шарповая интерполяция строк является синтаксическим сахаром для String.Format. Следовательно, когда вы пишете $"String containing {varA} and {varB}", то компилятор преобразует эту конструкцию в вызов String.Format("String containing {0} and {1}", varA, varB). Первым параметром у этого метода идёт строка формата. В ней мы невооруженным глазом наблюдаем плейсхолдеры — {0}, {1} и так далее. Format просто заменяет эти плейсхолдеры на то, что идет после строки формата, в порядке, обозначенном цифрами в плейсхолдерах. Если плейсхолдеров больше, чем 4 — то интерполированная строка компилируется в вызов перегрузки String.Format от двух параметров: самой строки формата и массива, в который пакуются все, страждущие попасть в результирующую строку параметры.

Таким образом, что мы сейчас сделаем в методе Parse? Мы клещами вытянем оригинальную строку формата, а аргументы форматирования пересчитаем, заменяя где надо на имена таблиц и колонок. После чего сами вызовем Format, чем и соберем оригинальную строку формата и обработанные аргументы в результирующую SQL-строку. Честное слово, это гораздо проще закодить чем объяснить :)

Итак, начнем:

public static string Parse(DbContext context, LambdaExpression query, out object[] parameters){

    // для начала отсечём совсем уж трешак
    const string err = "Плохая, негодная лямбда!";
    var bdy = query.Body as MethodCallExpression;

    // у нас точно вызов метода?
    if (bdy == null) throw new Exception(err);

    // и этот метод - точно String.Format?
    if (bdy.Method.DeclaringType != typeof(String) || bdy.Method.Name != "Format")
    {
        throw new Exception(err);
    }

Как вы знаете, лямбда-выражения в C# — в прямом смысле выражения. То есть всё, что идет после => должно быть одним и только одним выражением. В делегаты можно запихивать операторы и разделять их точкой с запятой. Но когда вы пишете Expression<> — всё. Отныне вы ограничиваете входные данные одним и только одним выражением. Так происходит в нашем методе Stroke. LambdaExpression же — это предок Expression<>, только без ненужных нам generic-ов. Следовательно, надо бы удостоверится, что единственное выражение, которое содержится в нашем query — это вызов string.Format и ничто иное, что мы и сделали. Теперь будем смотреть с какими аргументами его вызвали. Ну с первым аргументом всё ясно — это строка формата. Извлекаем её на радость всему честному народу:

    // берем самый первый аргумент
    var fmtExpr = bdy.Arguments[0] as ConstantExpression;
    if (fmtExpr == null) throw new Exception(err);
    // ...и достаём строку формата
    var format = fmtExpr.Value.ToString();

Дальше надо сделать небольшой финт ушами: как было сказано выше, если у интерполированной строки больше 4х плейсхолдеров, то она транслируется в вызов string.Format-а с двумя параметрами, второй из которых — массив (в форме new [] { ... }). Давайте же обработаем эту ситуацию:

    // стартовый индекс, с которого мы позже будем перебирать аргументы
    // 1 - потому что первый аргумент - строка формата
    int startingIndex = 1;

    // коллекция с аргументами
    var arguments = bdy.Arguments;
    bool longFormat = false;

    // если у нас всего два аргумента
    if (bdy.Arguments.Count == 2)
    {
        var secondArg = bdy.Arguments[1];
        // ...и второй из них - new[] {...}
        if (secondArg.NodeType == ExpressionType.NewArrayInit)
        {
            var array = secondArg as NewArrayExpression;
            // то подменяем нашу коллекцию с аргументами на этот массив
            arguments = array.Expressions;
            // сбрасываем индекс
            startingIndex = 0;
            // проставляем флаг, чтобы ниже по коду понять что происходит
            longFormat = true;
        }
    }

Теперь давайте пройдемся по образовавшейся коллекции arguments и, наконец, преобразуем каждый аргумент, который связан с параметрами нашей лямбды в имя таблицы/колонки, а всё, что не является отсылками к таблицам и колонкам — вычислим и закинем в список параметров запроса, оставив в параметрах формата {i}, где i — индекс соответствующего параметра. Ничего нового для опытных пользователей ExecuteSqlCommand.

    // сюда мы будем складывать преобразованные аргументы для
    // последующего вызова string.Format
    List<string> formatArgs = new List<string>();

    // а сюда - параметры запроса
    List<object> sqlParams = new List<object>();

Первое, что надо сделать — маленькая техническая особенность C#-повых лямбд: в виду строгой типиазции, когда вы пишете, например x => "a" + 10, компилятор оборачивает вашу десятку в Convert — приведение типа (очевидно, к строке). По существу всё правильно, но в ходе парсеринга лямбд это обстоятельство дюже мешается. Поэтому, тут мы сделаем маленький метод Unconvert, который проверит наш аргумент на предмет обёрнутости в Convert и при необходимости развернет:

private static Expression Unconvert(Expression ex)
{
    if (ex.NodeType == ExpressionType.Convert)
    {
        var cex = ex as UnaryExpression;
        ex = cex.Operand;
    }
    return ex;
}

Чудно. Далее нам потребуется понять имеет ли очередной аргумент отношение к параметрам выражения. Ну то есть имеет форму p.Field1.Field2..., где p — параметр нашего выражения (то, что ставится перед лямбда-оператором =>). Потому как если не имеет — то надобно этот аргумент просто вычислить, а результат запомнить как параметр SQL-запроса, для последующего скармливания EF-у. Самый простой и топорный способ определить обращаемся ли мы к полю какого-либо из параметров — это следующие два метода:

В первом мы просто перебираем цепочку обращений к членам, пока не дойдем до корня (я назвал его GetRootMember):

private static Expression GetRootMember(MemberExpression expr)
{
    var accessee = expr.Expression as MemberExpression;
    var current = expr.Expression;
    while (accessee != null)
    {
        accessee = accessee.Expression as MemberExpression;
        if (accessee != null) current = accessee.Expression;
    }
    return current;
}

Во втором — собственно проверяем требуемые нам условия:

private static bool IsScopedParameterAccess(Expression expr)
{
    // если это просто параметр - ну то есть {x}, то да, надо переводить
    if (expr.NodeType == ExpressionType.Parameter) return true;
    var ex = expr as MemberExpression;

    // если это не обращение к члену вообще - надо вычислять
    if (ex == null) return false;

    // достаем корень цепочки обращений
    var root = GetRootMember(ex);

    // да, такое тоже бывает
    if (root == null) return false;

    // если это не параметр - вычислим
    if (root.NodeType != ExpressionType.Parameter) return false;

    // ну и тут немного вариантов остаётся
    return true;
}

Готово. Возвращаемся к перебору аргументов:

    // поехали
    for (int i = startingIndex; i < arguments.Count; i++)
    {
        // убираем возможный Convert
        var cArg = Unconvert(arguments[i]);

        // если это НЕ доступ к параметру/полю 
        if (!IsScopedParameterAccess(cArg))
        {
            // собираем бесконтекстное лямбда-выражение
            var lex = Expression.Lambda(cArg);
            // компилим
            var compiled = lex.Compile();
            // вычисляем
            var result = compiled.DynamicInvoke();
            // в результирующей строке оставляем {i}, где i - номер параметра
            formatArgs.Add(string.Format("{{{0}}}", sqlParams.Count));
            // сохраняем полученный объект как SQL-параметр
            sqlParams.Add(result);
            // идем к следующему аргументу
            continue;
        }

Отлично. Мы отсекли все параметры, которые гарантированно не являются ссылками на наши таблицы/колонки. Список sqlParams потом вернётся через out-параметр — мы его наряду со строкой-результатом скормим context.Database.ExecuteSqlCommand вторым аргументом. Пока же обработаем ссылки на таблицы:

        // если встречаем {x}, то 
        if (cArg.NodeType == ExpressionType.Parameter)
        {
            // заменяем его на имя таблицы, из нашего контекста
            formatArgs.Add(string.Format("[{0}]", context.GetTableName(cArg.Type)))
            // и переходим к следующему аргументу
            continue;
        }

Тут нам придется отрезать возможность обращаться к агрегатам, ибо как это приведет к необходимости переколбашивать запрос JOIN-ами, чего мы технически сделать не можем. Так что — увы и ах. Если наш аргумент — это обращение к члену, но не к члену непосредственно параметра выражения — то звиняйте, ничем не можем помочь:

        var argProp = cArg as MemberExpression;

        if (argProp.Expression.NodeType != ExpressionType.Parameter)
        {
            var root = GetRootMember(argProp);
            throw new Exception(string.Format("Пожалуйста, не лезьте в душу {0}", root.Type));
        }

И вот, наконец, мы можем добыть наше имя колонки и добавить его в переработанный список формат-аргументов.

        var colId = string.Format("[{0}]", context.GetColumnName(argProp.Member.DeclaringType, argProp.Member.Name));        
        formatArgs.Add(colId);
        // и поехали к следующему формат-аргументу
    }

Теперь, когда все аргументы перебраны, мы можем наконец-таки сделать string.Format самостоятельно и получить SQL-строку и массив параметров, готовые к скармливанию ExecuteSqlCommand.

    var sqlString = string.Format(format, formatArgs.ToArray());
    parameters = sqlParams.ToArray();
    return sqlString;
}

Готово

Вот как-то так. Для статьи я намеренно упростил код. В частности, полная версия автоматически подставляет алиасы таблиц, нормально кэширует имена таблиц и колонок, а так же содержит перегрузки .Stroke до 8 параметров. С полным исходным кодом вы можете ознакомитья в моем github. За сим прощаюсь и желаю всяческих удач в разработке.

А, ну и опросик напоследок: