Прямой 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(x => $"DELETE FROM {x} WHERE {x.Subtotal} = 0");
// ^ IntelliSense!
//----------
var old = DateTime.Today.AddDays(-30);
dc.Stroke(x => $"UPDATE {x} SET {x.IsActive} = 0 WHERE {x.RegisterDate} < {old}");
//----------
dc.Stroke- ((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(DataSpace.OSpace)
.FirstOrDefault(x => objectItemCollection.GetClrType(x) == t);
// ищем в метадате контейнер из концептуальной модели
var container = metadata
.GetItems(DataSpace.CSpace)
.Single()
.EntitySets
.Single(s => s.ElementType.Name == entityType.Name);
// вытаскиваем маппинги этого контейнера на свет б-жий
var mapping = metadata.GetItems(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()
.Single(m => m.Property.Name == propertyName)
.Column
.Name;
// быстро, не так ли?
return columnName;
}
Так, и вот тут я сразу и крупными буквами предупреждаю читателя: копаться в EF-метаданных — это медленно! Кроме шуток. Поэтому кэшируйте вообще всё, до чего дотянетесь. В статье есть ссылка на мой код — там я уже озаботился кэшированием — можете пользоваться. Но все равно держите в голове: реальные концептуальные модели EF — стозёвные чудища, хранящие в себе взводы и дивизии различных объектов. Если вам нужно только соотношение тип-имя таблицы и тип/свойство — имя колонки, то лучше один раз достаньте и закэшируйте (только не напоритесь там на утечку памяти — не храните ничего от DbContext
-а). В EF Core, говорят, с этим по-лучше.
Выражения
Самое скучное позади. Теперь — лямбда-выражения. Положим, мы хотим иметь метод Stroke
, чтобы вызывать его можно было вот таким макаром:
context.Stroke(x => $"UPDATE {x} WHERE {x.Age} > 10")
Сам метод Stroke
простой:
public static void Stroke(this DbContext s, Expression> 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 formatArgs = new List();
// а сюда - параметры запроса
List
Первое, что надо сделать — маленькая техническая особенность 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. За сим прощаюсь и желаю всяческих удач в разработке.
А, ну и опросик напоследок: