Cooking ORM, without departing from the plate. We generate SQL - query based on binary expression trees
The article is a continuation of the first part . In the post, we will consider the construction of a SQL query using an object type model, in the form of a binary expression tree and the accompanying topics of SQL query parameterization, reflexion optimization. The topics of this article are very separate in themselves, so you can read without particularly paying attention to the first part. Once again, this decision is “it was in the evening - there was nothing to do” and does not claim the laurels of an industrial product.
Some lyrics or dynamic SQL parameterization
In general, dynamic SQL is a script that is sent from a client to execute a DBMS, which is not implemented as a stored procedure. The DBMS executes such scripts using the EXEC () and sp_executesql instructions.
It may seem unexpected for someone, but after version 6, SQL Server “can” cache dynamic queries. However, not all so simple. During the search in the cache of the stored procedure, SQL Server uses its name as the key, in the case of dynamic SQL there can be no name, therefore SQL uses all the text of the query, including parameters, as a search key. Yes, absolutely the entire text of the request, Karl! With spaces, case insensitive, with comments.
')
In addition, the server is looking for a query in the cache, according to its scheme. Therefore, it is important to specify the full name of the tables, according to the scheme.
Non parameterized query
// SQL Server - cmd.CommandText = "SELECT mycol FROM product WHERE col = " + value.ToString(); cmd.CommandText = "SELECT mycol FROM Product WHERE col = " + value.ToString(); Parameterized query
cmd.CommandText = "SELECT mycol FROM dbo.product WHERE col = @value"; cmd.Parameters.Add("@value", SqlDbType.Int); cmd.Parameters["@value"].Value = value; If there are a lot of non-parameterized requests “scattered” in the project, you should think about refactoring, using encapsulation in one function. Using ORM solves similar problems (in the case of ORM with manual SQL, you need to work independently on encapsulation).
Let's not forget that non-parameterized queries entail a lot of side effects, such as SQL injections, etc. Learn more about dynamic SQL caching issues here .
What do we want?
We define a repository method for retrieving data based on expression trees. The client should receive something like:
var repo = new ProfileRepository(); var profiles = repo.Get(x => x.Id == id && x.Rating > rate) Generating SQL based on expression trees has the following advantages:
- Eliminates the need for the user to remember the string column names in the database.
- The user can set the wrong column name, which will cause an exception or the name of the column with a space in another case, which will cause problems caching on the side of SQL Server
- The condition for filtering can be complex, which allows you to implement the .NET Expression class.
The disadvantage is the complexity and performance of generating SQL, with a recursive traversal of a binary tree.
A little reflexion
During the mapping of objects, it is necessary to obtain their properties and attributes dynamically, while, if possible, avoiding the slow reflection mechanism. How to optimize the performance of getting values and setting values for properties based on delegates is well described by Richter, we will not stop here in detail, but immediately implement a wrapper of the class PropertyInfo.
For dynamic work with type properties, we will need:
- Method of obtaining properties
- Property Setting Method
- Property name
- Property type
- Attributes of binding business property properties to fields in a table
Class PropWrapper
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(); } } // Gettr . - public Func<object, object> GetterMethod { get { return GetGetterMethod(); } } // Settr . - 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; } } Now we implement the class that encapsulates the entire type. Note that such things as attributes, property names, property types, depend only on the type, and not on the specific instance of the class. Therefore, it is convenient to immediately cache the type structure.
CacheTypeReflectionWrapper class implementation
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]; } } Cooking main course
Finally, you can start preparing the SQL mapper from the object model. I’ll note right away that the solution could have been implemented using LINQ providers of .NET, but I haven’t yet.
We calculate the body of the select query by the fields of the object. For what we need a type of business object and the name of the database in order to get the full name, according to the schema database.
CreateBody method
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); } Now the fun begins: - generation of SQL conditions after the word WHERE. For easy traversal of expression trees in .NET, there is an ExpressionVisitor class. But if to do bicycles, then in full! Therefore, do without the means out of the box.
Expression analysis will be carried out on the basis of binary expression trees .
A binary expression tree is a specific kind of binary tree used to represent expressions. A binary expression tree can be algebraic and logical values (unary and binary operators). Each node of a binary tree, and therefore a binary expression tree, has zero, one, or two children.
The expression tree can have vertices of various types: directly BinaryExpression,
MemberExpression, ConstantExpression, UnaryExpression and others.
It is important for us to get to the leaves, which in our case are of type MemberExpression, ConstantExpression. Vertices of type MemberExpression contain fields, and by the type of vertex you can get a conditional operand. Vertices of the ConstantExpression type contain directly the values of the operands themselves.
Let our expression be as follows:
repo.Get(x => x.RoleId == 2 && x.UserInfoId > 4 && x.Id < 6) To make it clearer, I will give the scheme of a binary expression tree for this case. The vertex values are taken from the debugger during the execution of the traversal algorithm.
In the picture, the {x.UserInfoId} field is a nullable type, because of this, such a vertex is UnaryExpression. Vertices of the UnaryExpression type do not contain two children, Left and Right. In this case, the operand value can be obtained by converting to the ConstantExpression type.
The code of the implementation implementation functional for the SQL condition on the expression tree, with detailed comments:
// 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); } As a result, by connecting the body and
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)); } All implementation details can be viewed on the githaba .
Source: https://habr.com/ru/post/319422/
All Articles