Готовим ORM, не отходя от плиты. Генерируем SQL — запрос на основе бинарных деревьев выражений

Статья является продолжением первой части ^[1]. В посте рассмотрим построение 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 тут ^[2].

Что мы хотим?

Определим метод репозитория для получения данных, на основе деревьев выражений. Клиент должен получить что-то вроде:

 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 ^[3]. Но коль делать велосипеды, то по полной! Поэтому обойдемся без средств из коробки.
Разбор выражений будем осуществлять на основе бинарных деревьев выражений ^[4].
Бинарное дерево выражения представляет собой специфический вид бинарного дерева, используемого для представления выражений. Бинарное дерево выражений может представлять из себя алгебраические и логические значения(унарные и бинарные операторы). Каждый узел бинарного дерева, и, следовательно, бинарного дерева выражения, имеет ноль, один или двое детей.

Дерево выражений может иметь вершины различного типа: непосредственно 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));
        }

Все подробности реализации можно посмотреть на гитхабе ^[5].

Автор: eddHunter

Источник ^[6]