Хабрахабр

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

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

Показать больше

Похожие публикации

Кнопка «Наверх»