Прямой 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. За сим прощаюсь и желаю всяческих удач в разработке.
А, ну и опросик напоследок: