Статья является продолжением первой части. В посте рассмотрим построение SQL-запроса по объектной модели типа, в виде бинарного дерева выражений и сопутствующие темы параметризации SQL-запросов, оптимизации рефлексии. Темы этой статьи сами по себе весьма обособленны, поэтому можно читать особо не обращая внимание на первую часть. Еще раз отмечу, данное решение, является «дело было вечером — делать было нечего» и не претендует на лавры промышленного продукта.
Немного лирики или о параметризации динамического SQL
В общем случае, динамическим SQL называют скрипт, отправляемый с клиента на исполнение СУБД, который не реализован в виде хранимой процедуры. СУБД исполняет такие скрипты при помощи инструкций EXEC() и sp_executesql.
Может для кого-то покажется неожиданным, но после 6-й версии, SQL Server «умеет» кэшировать динамические запросы. Однако, не все так просто. Во время поиска в кэше хранимой процедуры, в качестве ключа SQL Server использует ее имя, в случае динамического SQL имени не может быть, поэтому SQL использует весь текст запроса, включая параметры, в виде ключа поиска. Да, абсолютно весь текст запроса, Карл! С пробелами, без учета регистра, с комментариями.
Кроме того, сервер ищет запрос в кэше, по его схеме. Поэтому так важно указывать полное имя таблиц, согласно схеме.
//В кэше планы исполнения этих непараметризованных запросов SQL Server расценит как разные из-за регистра
cmd.CommandText = "SELECT mycol FROM product WHERE col = " + value.ToString();
cmd.CommandText = "SELECT mycol FROM Product WHERE col = " + value.ToString();
cmd.CommandText = "SELECT mycol FROM dbo.product WHERE col = @value";
cmd.Parameters.Add("@value", SqlDbType.Int);
cmd.Parameters["@value"].Value = value;
Если в проекте множество «разбросанных» по проекту непараметризованных запросов, следует задуматься о рефакторинге, с помощью инкапсуляции в одну функцию. Использование ORM решает подобные проблемы(в случае ORM с ручным SQL, нужно самостоятельно потрудиться над инкапсуляцией).
Не будем забывать, что непараметризованные запросы влекут за собой множество побочных эффектов, таких как SQL-инъекции и т.д. Подробнее о проблемах кэширования динамического SQL тут.
Что мы хотим?
Определим метод репозитория для получения данных, на основе деревьев выражений. Клиент должен получить что-то вроде:
var repo = new ProfileRepository();
var profiles = repo.Get(x => x.Id == id && x.Rating > rate)
Генерация SQL на основе деревьев выражений имеет следующие преимущества:
- Избавляет от необходимости пользователю самому вспоминать строковые названия колонок в бд
- Пользователь может задать неправильное название колонки, что повлечет исключение или название колонки с пробелом, в другом регистре, что повлечет проблемы кеширования на стороне SQL Server
- Условие для фильтрации может быть составным, что и позволяет реализовать класс .NET Expression
Недостатком может является сложность и производительность генерации SQL, с рекурсивным обходом бинарного дерева.
Немного порефлексируем
Во время маппинга объектов, необходимо получать их свойства и атрибуты динамически, при этом, по возможности, избежав медленного механизма рефлексии. Как оптимизировать производительность получения значений и установки значений для свойств на основе делегатов, хорошо изложено у Рихтера, не будем тут подробно останавливаться, а сразу реализуем обертку класса PropertyInfo.
Для динамической работы со свойствами типа, нам будут нужны:
- Метод получения свойств
- Метод установки свойств
- Название свойства
- Тип свойства
- Атрибуты привязки свойств бизнес-объектов к полям в таблице
public class PropWrapper
{
private readonly PropertyInfo _property;
public Type Type
{
get { return _property.PropertyType; }
}
public string Name
{
get { return _property.Name; }
}
//атрибут связи бизнес-объекта с другими бизнес-объектами
public ICollection<RelatedEntityAttribute> RelatedEntityAttributes
{
get { return _property.GetCustomAttributes<RelatedEntityAttribute>().ToList(); }
}
//атрибут связи бизнес-объекта с объектами из бд
public ICollection<FieldNameAttribute> FieldNameAttributes
{
get { return _property.GetCustomAttributes<FieldNameAttribute>().ToList(); }
}
// свойство получающее Gettеr объекта. Аргумент делегата-экземляр конкретного объекта
public Func<object, object> GetterMethod
{
get { return GetGetterMethod(); }
}
// свойство получающее Settеr объекта. Аргумент делегата-экземляр конкретного объекта
public Action<object, object> SetterMethod
{
get { return GetSetterMethod(); }
}
public PropWrapper(PropertyInfo prop)
{
_property = prop;
}
private Func<object, object> GetGetterMethod()
{
if (_property == null)
throw new ArgumentNullException("property");
var getter = _property.GetGetMethod();
if (getter == null)
throw new ArgumentException("The specified property does not have a public accessor.");
var genericMethod = typeof (PropMethodsHelper).GetMethod("CreateGetterGeneric");
var r = _property.GetCustomAttributes<FieldNameAttribute>();
MethodInfo genericHelper = genericMethod.MakeGenericMethod(_property.DeclaringType, _property.PropertyType);
return (Func<object, object>) genericHelper.Invoke(null, new object[] {getter});
}
private static Func<object, object> CreateGetterGeneric<T, R>(MethodInfo getter) where T : class
{
Func<T, R> getterTypedDelegate = (Func<T, R>) Delegate.CreateDelegate(typeof (Func<T, R>), getter);
Func<object, object> getterDelegate =
(Func<object, object>) ((object instance) => getterTypedDelegate((T) instance));
return getterDelegate;
}
private Action<object, object> GetSetterMethod()
{
if (_property == null)
throw new ArgumentNullException("property");
var setter = _property.GetSetMethod();
if (setter == null)
throw new ArgumentException("The specified property does not have a public setter.");
var genericMethod = typeof (PropMethodsHelper).GetMethod("CreateSetterGeneric");
MethodInfo genericHelper = genericMethod.MakeGenericMethod(_property.DeclaringType, _property.PropertyType);
return (Action<object, object>) genericHelper.Invoke(null, new object[] {setter});
}
private static Action<object, object> CreateSetterGeneric<T, V>(MethodInfo setter) where T : class
{
Action<T, V> setterTypedDelegate = (Action<T, V>) Delegate.CreateDelegate(typeof (Action<T, V>), setter);
Action<object, object> setterDelegate =
(Action<object, object>)
((object instance, object value) => { setterTypedDelegate((T) instance, (V) value); });
return setterDelegate;
}
}
Теперь реализуем класс инкапсулирующий весь тип целиком. Заметим, что такие вещи как атрибуты, имена свойств, типы свойств, зависят только от типа, а не от конкретного экземляра класса. Поэтому удобно сразу кэшировать структуру типов.
internal static class CacheTypeReflectionWrapper
{
private static readonly Dictionary<Type, ICollection<PropWrapper>> TypesByProp =
new Dictionary<Type, ICollection<PropWrapper>>();
public static ICollection<PropWrapper> GetProps(Type type)
{
//проверяем есть ли тип к кэше
if (!TypesByProp.ContainsKey(type))
{
var props = type.GetProperties();
var propWrappers = props.Select(propertyInfo => new PropWrapper(propertyInfo)).ToList();
TypesByProp.Add(type, propWrappers);
}
return TypesByProp[type];
}
}
Готовим основное блюдо
Наконец можно приступать к приготовлению маппера SQL из объектной модели. Сразу отмечу, что решение можно было реализовать с помощью LINQ провайдеров .NET, но я пока не стал.
Вычислим тело select-запроса по полям объекта. Для чего нам необходим тип бизнес-объекта и имя бд, дабы получить полное имя, согласно схемы бд.
private static string CreateBody(string dbName, Type type)
{
//получаем имя таблицы в бд, согласно атрибутам в объявлении бизнес-объекта
var tableName = CommonCommandBuilder.GetTableName(type);
var cmdBulder = new StringBuilder();
//получаем все поля свойства объекта, у которых есть атрибуты привязки к таблицам в бд
foreach (var prop in CacheTypeReflectionWrapper.GetProps(type).Where(x => x.FieldNameAttributes.Any()))
{
var attrs = prop.FieldNameAttributes;
//в текущей реализации только один атрибут привязки к имени в бд является действительным
cmdBulder.Append(string.Format("[{0}].[{1}],", tableName, attrs.First().Value));
}
return string.Format("SELECT {0} FROM [{1}].[dbo].[{2}] ",
cmdBulder.ToString().Trim(','), dbName, tableName);
}
Теперь начинается самое веселое: — генерация SQL-условия после слова WHERE. Для удобного обхода деревьев выражений в .NET существует класс ExpressionVisitor. Но коль делать велосипеды, то по полной! Поэтому обойдемся без средств из коробки.
Разбор выражений будем осуществлять на основе бинарных деревьев выражений.
Бинарное дерево выражения представляет собой специфический вид бинарного дерева, используемого для представления выражений. Бинарное дерево выражений может представлять из себя алгебраические и логические значения(унарные и бинарные операторы). Каждый узел бинарного дерева, и, следовательно, бинарного дерева выражения, имеет ноль, один или двое детей.
Дерево выражений может иметь вершины различного типа: непосредственно BinaryExpression,
MemberExpression, ConstantExpression, UnaryExpression и другие.
Нам важно добраться до листьев, которые в нашем случае, имеют типы MemberExpression, ConstantExpression. В вершинах типа MemberExpression содержатся поля, а по типу вершины можно получить условный операнд. Вершины типа ConstantExpression содержат непосредственно сами значения операндов.
Пусть наше выражение имеет следующий вид:
repo.Get(x => x.RoleId == 2 && x.UserInfoId > 4 && x.Id < 6)
Чтобы было понятнее, приведу схему бинарного дерева выражений, для данного случая. Значения вершин взяты из отладчика во время выполнения алгоритма обхода.
На картинке, поле {x.UserInfoId} является nullable типом, из-за этого такая вершина UnaryExpression. Вершины типа UnaryExpression не содержат двух детей Left и Right. Получить значение операнда в этом случае можно преобразованием к типу ConstantExpression.
Код функционала реализации построения SQL-условия по дереву выражений, с подробными комментариями:
//в метод необходимо передать экземпляр IDbCommand, в который будут сформируются параметры и значения, передаваемые в динамический SQL
public static string BuildClauseByExpression(IDbCommand command, Type type, BinaryExpression exp)
{
var strBuilder = new StringBuilder();
//точка входа в главный метод
return BuildClauseByNode(command, type, exp, strBuilder);
}
//рекурсивный метод обхода дерева выражений
private static string BuildClauseByNode(IDbCommand command, Type type, BinaryExpression left, StringBuilder strBuilder)
{
var tableName = GetTableName(type);
if (left != null)
{
var parameter = command.CreateParameter();
var fieldName = string.Empty;
var expField = left.Left as MemberExpression;
if (expField == null)
{
if (left.Left is BinaryExpression)
{
//если вершина типа Binary - рекурсивный вызов
BuildClauseByNode(command, type, left.Left as BinaryExpression, strBuilder);
//ExpressionTypeToDbClause содержит словарь, где ключ-значение ExpressionType, а значение строковое выражение SQL операции:_instance[ExpressionType.AndAlso] = " AND "
strBuilder.Append(ExpressionTypeToDbClause.Instance[left.NodeType]);
}
}
else
{
//если вершина типа Member - формируем значения названий полей и часть SQL условия
var name = expField.Member.Name;
var prop = CacheTypeReflectionWrapper.GetProps(type)
.Where(x => x.FieldNameAttributes.Any()).First(x => x.Name.Equals(name));
var attrs = prop.FieldNameAttributes;
fieldName = attrs.First().Value;
strBuilder.Append(string.Format("[{0}].[{1}]", tableName, fieldName));
//ExpressionTypeToDbClause содержит словарь, где ключ-значение ExpressionType, а значение строковое выражение SQL операции:_instance[ExpressionType.AndAlso] = " AND "
var action = ExpressionTypeToDbClause.Instance[left.NodeType];
strBuilder.Append(action);
//TypeMap содержит словарь для поиска соответствия c# типов и бд типов
parameter.DbType = TypeMap[prop.Type];
}
var expValue = left.Right as ConstantExpression;
if (expValue == null)
{
var unaryNode = left.Right as UnaryExpression;
if (unaryNode != null)
{
//если вершина типа UnaryExpression необходимо воспользоватся свойством Operand и привести к //ConstantExpression
expValue = unaryNode.Operand as ConstantExpression;
if (expValue != null)
{
//метод формирует параметры для динамического SQL-запроса
InitParams(command, strBuilder, fieldName, parameter, expValue);
}
}
if (expValue == null)
{
if (left.Right is BinaryExpression)
{
//если вершина типа Binary - рекурсивный вызов
BuildClauseByNode(command, type, left.Right as BinaryExpression, strBuilder);
}
}
}
else
{
InitParams(command, strBuilder, fieldName, parameter, expValue);
}
}
return strBuilder.ToString();
}
//метод формирования параметров динамического SQL-запроса
private static void InitParams(IDbCommand command, StringBuilder strBuilder, string fieldName,
IDataParameter parameter, ConstantExpression expValue)
{
var valueFormat = GetParamsFormat(fieldName);
strBuilder.Append(valueFormat);
parameter.ParameterName = valueFormat;
parameter.Value = expValue.Value;
if (!command.Parameters.Contains(parameter.ParameterName))
command.Parameters.Add(parameter);
}
//служебный метод форматирования строки параметра динамического SQL-запроса
public static string GetParamsFormat(string fieldName)
{
return string.Format("@{0}", fieldName);
}
В итоге, соединив тело и душу условие запроса, получим следующую функцию:
public static string Create<T>(IDbCommand command, BinaryExpression exp)
where T : class, IEntity, new()
{
var type = typeof(T);
var selectBody = CreateBody(command.Connection.Database, type);
return string.Format("{0} WHERE {1}", selectBody, CommonCommandBuilder.BuildClauseByExpression(command, type, exp));
}
Все подробности реализации можно посмотреть на гитхабе.
Автор: eddHunter
