XYY.Core/1.Command/XYY.Core.Standard/Data/Infrastructure/QueryTranslator.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Diagnostics;

namespace XYY.Core.Standard.Data.Infrastructure
{
    public class QueryTranslator
    {
        internal class SplitExpression
        {
            public Expression Expression { get; set; }

            public string InnerLink { get; set; }

            public string OutLink { get; set; }

            public string Flag { get; set; }

        }

        public string SqlWhere;
        /// <summary>
        /// 根据条件生成对应的sql查询操作符
        /// </summary>
        /// <param name="expressiontype"></param>
        /// <returns></returns>
        private string GetOperator(ExpressionType expressiontype)
        {
            switch (expressiontype)
            {
                case ExpressionType.And:
                    return "and";
                case ExpressionType.AndAlso:
                    return "and";
                case ExpressionType.Or:
                    return "or";
                case ExpressionType.OrElse:
                    return "or";
                case ExpressionType.Equal:
                    return "=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                default:
                    throw new Exception(string.Format("不支持{0}此种运算符查找！" + expressiontype));
            }
        }

        public string Translate(Expression expression)
        {
            List<SplitExpression> splitExpression = new List<SplitExpression>();
            Resolve(expression, expression.NodeType, ref splitExpression);
            this.SqlWhere = AnalyzeExpression(splitExpression);
            return SqlWhere;
        }

        private void Resolve(Expression expression, ExpressionType link, ref List<SplitExpression> list)
        {
            if (expression is LambdaExpression)
            {
                LambdaExpression lambda = expression as LambdaExpression;
                Resolve(lambda.Body, lambda.NodeType, ref list);
            }
            string guid = Guid.NewGuid().ToString();
            if (expression.NodeType == ExpressionType.AndAlso || expression.NodeType == ExpressionType.OrElse)
            {
                if (expression is BinaryExpression)
                {
                    var binary = (expression as BinaryExpression);
                    if (binary.Left.NodeType == ExpressionType.AndAlso || binary.Left.NodeType == ExpressionType.OrElse)
                    {
                        Resolve(binary.Left, expression.NodeType, ref list);
                    }
                    else
                    {
                        list.Add(new SplitExpression()
                        {
                            Expression = binary.Left,
                            InnerLink = binary.NodeType.ToString(),
                            Flag = guid,
                            OutLink = link.ToString()
                        });
                    }
                    if (binary.Right.NodeType == ExpressionType.AndAlso || binary.Right.NodeType == ExpressionType.OrElse)
                    {
                        Resolve(binary.Right, expression.NodeType, ref list);
                    }
                    else
                    {
                        list.Add(new SplitExpression()
                        {
                            Expression = binary.Right,
                            InnerLink = binary.NodeType.ToString(),
                            Flag = guid,
                            OutLink = link.ToString()
                        });
                    }
                }
            }
            else
            {
                if (expression is BinaryExpression)
                {
                    var binary = (expression as BinaryExpression);
                    if (binary != null)
                    {
                        list.Add(new SplitExpression()
                        {
                            Expression = binary,
                            Flag = guid
                        });
                    }
                }
                else if (expression is MemberExpression)
                {
                    var member = (expression as MemberExpression);
                    if (member != null)
                    {
                        list.Add(new SplitExpression()
                        {
                            Expression = member,
                            Flag = guid
                        });
                    }
                }
                else if (expression is MethodCallExpression)
                {
                    var callMethod = (expression as MethodCallExpression);
                    list.Add(new SplitExpression()
                    {
                        Expression = callMethod,
                        Flag = guid
                    });
                }


            }
        }

        private string AnalyzeExpression(List<SplitExpression> expression)
        {
            string where = string.Empty;
            var group = expression.GroupBy(x => x.Flag).Select(x => x.Key);
            foreach (var flag in group)
            {
                var splitExpression = expression.Where(x => x.Flag == flag);
                where += " (";
                string innerWhere = "";
                string link = "";
                foreach (var item in splitExpression)
                {
                    item.OutLink = item.OutLink ?? "";
                    item.InnerLink = item.InnerLink ?? "";
                    link = item.OutLink;
#if DEBUG
                    Trace.WriteLine("OutLink:" + link);
#endif
                    var express = item.Expression;
                    if (express is UnaryExpression)
                    {
                        UnaryExpression unary = express as UnaryExpression;
                        if (unary.Operand is MethodCallExpression)//解析!x=>x.Name.Contains("xxx")或!array.Contains(x.Name)这类
                            return ResolveLinqToObject(unary.Operand, false);
                        if (unary.Operand is MemberExpression && unary.NodeType == ExpressionType.Not)//解析x=>!x.isDeletion这样的 
                        {
                            ConstantExpression constant = Expression.Constant(false);
                            innerWhere += ResolveFunc(unary.Operand, constant, ExpressionType.Equal) + (item.InnerLink == "AndAlso" ? " AND " : " OR ");
                        }
                    }
                    else if (express is MethodCallExpression)
                    {
                        MethodCallExpression methodcall = express as MethodCallExpression;

                        innerWhere += ResolveLinqToObject(methodcall, true) + (item.InnerLink == "AndAlso" ? " AND " : " OR ");
#if DEBUG
                        Trace.WriteLine("where:" + innerWhere + "; InnerLink:" + item.InnerLink);
#endif
                    }
                    else if (express is BinaryExpression)
                    {
                        BinaryExpression binary = express as BinaryExpression;
                        dynamic value = null;
                        if ((binary.Right is MemberExpression && binary.Right.NodeType == ExpressionType.MemberAccess)
                            ||
                            (binary.Right is MethodCallExpression && binary.Right.NodeType == ExpressionType.Call)
                            ||
                            (binary.Right is UnaryExpression && binary.Right.NodeType == ExpressionType.Convert)
                            )
                        {
                            LambdaExpression lambda = Expression.Lambda(binary.Right);
                            Delegate fn = lambda.Compile();

                            value = Expression.Constant(fn.DynamicInvoke(null), binary.Right.Type);

                        }
                        else
                        {
                            value = binary.Right;
                        }
                        string property = GetPropertyName(binary);
                        string opr = GetOperator(binary.NodeType);
                        string condition;
                        if (value.Value != null)
                        {
                            string conditionString = "{0} {1} N'{2}'";
                            if (binary.Right.Type.Equals(typeof(DateTime)) || binary.Right.Type.Equals(typeof(Nullable<DateTime>)))
                            {
                                property = "Convert(varchar(10),{0},23)".Formater(property);
                                value = string.Format("Convert(varchar,'{0}',23)", value.Value.ToString("yyyy-MM-dd hh:mm:ss"));
                                conditionString = "{0} {1} {2}";
                            }
                            else
                            {
                                value = value.Value.ToString();
                            }
                            condition = string.Format(conditionString, property, opr, value) + (item.InnerLink == "AndAlso" ? " AND " : " OR ");
                        }
                        else
                        {
                            condition = string.Format("{0} is NULL{1}", property, item.InnerLink == "AndAlso" ? " AND " : " OR ");
                        }

                        innerWhere += condition;
                    }
                    else if (express is MemberExpression)
                    {
                        MemberExpression member = express as MemberExpression;
                        ConstantExpression constant = Expression.Constant(true);
                        innerWhere += ResolveFunc(member, constant, ExpressionType.Equal) + (item.InnerLink == "AndAlso" ? " AND " : " OR ");
                    }
                }
                innerWhere = innerWhere.Substring(0, innerWhere.Length - 4);
                where += innerWhere;
                where += string.Format(") {0}", link == "AndAlso" ? " AND " : " OR ");
            }
            return where.Substring(0, where.Length - 4);
        }

        private string ResolveFunc(Expression left, Expression right, ExpressionType expressiontype)
        {
            var Name = (left as MemberExpression).Member.Name;
            var Value = (right as ConstantExpression).Value;
            var Operator = GetOperator(expressiontype);
            //string CompName = SetArgument(Name, Value.ToString());
            string Result = string.Format("({0} {1} N'{2}')", Name, Operator, Value);
            return Result;
        }

        private string ResolveLinqToObject(Expression expression, object value, ExpressionType? expressiontype = null)
        {
            var MethodCall = expression as MethodCallExpression;
            var MethodName = MethodCall.Method.Name;
            switch (MethodName)//这里其实还可以改成反射调用，不用写switch
            {
                case "Contains":
                    if (MethodCall.Object != null && !MethodCall.Object.Type.IsGenericType)
                        return Like(MethodCall);
                    return In(MethodCall, value);
                case "Count":
                    return Len(MethodCall, value, expressiontype.Value);
                case "LongCount":
                    return Len(MethodCall, value, expressiontype.Value);
                case "ToLower":
                    return ToLower(MethodCall, value, expressiontype.Value);
                case "In":
                    return InCall(MethodCall, value);
                case "StartsWith":
                    if (MethodCall.Object != null && !MethodCall.Object.Type.IsGenericType)
                        return Like(MethodCall, true);
                    return In(MethodCall, value);
                default:
                    throw new Exception(string.Format("不支持{0}方法的查找！", MethodName));
            }
        }

        private string InCall(MethodCallExpression expression, object isTrue)
        {
            string field = "";
            if ((expression.Arguments[0] as MemberExpression) != null)
            {
                field = (expression.Arguments[0] as MemberExpression).Member.Name;
            }
            else
            {
                field = (((expression.Arguments[0] as MethodCallExpression).Arguments[0] as UnaryExpression).Operand as MemberExpression).Member.Name;
            }

            var member = (expression.Arguments[1] as MemberExpression);
            var values = ((ConstantExpression)member.Expression).Value;
            var list = values.GetType().GetField(member.Member.Name).GetValue(values) as IEnumerable<int>;

            //var values = ((expression.Arguments[1] as MemberExpression).Expression as ConstantExpression).Value;
            //var list = values.GetType().GetFields()[0].GetValue(values) as IEnumerable<int>;
            List<string> inElement = new List<string>();
            foreach (var item in list)
            {
                inElement.Add(item.ToString());
            }
            string _operator = Convert.ToBoolean(isTrue) ? "IN" : " NOT IN";
            return "{0} {1} ({2})".Formater(field, _operator, string.Join(",", inElement));
        }

        private string In(MethodCallExpression expression, object isTrue)
        {
            var Argument1 = (expression.Arguments[0] as MemberExpression).Expression as ConstantExpression;
            var Argument2 = expression.Arguments[1] as MemberExpression;
            var Field_Array = Argument1.Value.GetType().GetFields().First();
            var fieldVal = Field_Array.GetValue(Argument1.Value);
            object[] Array = fieldVal as object[];
            if (Array == null)
            {
                if (fieldVal is IEnumerable<string>)
                {
                    Array = (fieldVal as IEnumerable<string>).Select(i => $"'{i}'").ToArray();
                }
            }
            List<string> SetInPara = new List<string>();
            for (int i = 0; i < Array.Length; i++)
            {
                //string Name_para = "InParameter" + i;
                string Value = Array[i].ToString();
                //string Key = SetArgument(Name_para, Value);
                SetInPara.Add(Value);
            }
            string Name = Argument2.Member.Name;
            string Operator = Convert.ToBoolean(isTrue) ? "in" : " not in";
            string CompName = string.Join(",", SetInPara);
            string Result = string.Format("{0} {1} ({2})", Name, Operator, CompName);
            return Result;
        }

        private string Like(MethodCallExpression expression,bool isStartWith=false)
        {
            object Temp_Vale = null;
            if ((expression.Arguments[0] is ConstantExpression))
            {
                Temp_Vale = (expression.Arguments[0] as ConstantExpression).Value;
            }
            else if (expression.Arguments[0] is MemberExpression)
            {
                var arg = expression.Arguments[0] as MemberExpression;
                var argVal = Expression.Lambda(arg).Compile().DynamicInvoke();
                Temp_Vale = argVal;
            }

            string Value = isStartWith ? string.Format("'{0}%'", Temp_Vale) : string.Format("'%{0}%'", Temp_Vale);
            string Name = (expression.Object as MemberExpression).Member.Name;
            // string CompName = SetArgument(Name, Value);
            string Result = string.Format("{0} LIKE {1}", Name, Value);
            return Result;
        }

        private string Len(MethodCallExpression expression, object value, ExpressionType expressiontype)
        {
            object Name = (expression.Arguments[0] as MemberExpression).Member.Name;
            string Operator = GetOperator(expressiontype);
            //string CompName = SetArgument(Name.ToString(), value.ToString());
            string Result = string.Format("len({0}){1}{2}", Name, Operator, value);
            return Result;
        }

        private string ToLower(MethodCallExpression expression, object value, ExpressionType expressiontype)
        {
            return (expression.Arguments[0] as MemberExpression).Member.Name.ToLower();
        }

        private static string GetPropertyName(BinaryExpression body)
        {
            string leftStr = body.Left.ToString();
            if(body.Left.NodeType==ExpressionType.Convert)
            {
                var t = (body.Left as UnaryExpression)?.Operand.ToString();
                leftStr = t;
            }
            string propertyName = leftStr.Split(new char[] { '.' })[1];

            if (body.Left.NodeType == ExpressionType.Convert)
            {
                // hack to remove the trailing ) when convering.
                propertyName = propertyName.Replace(")", string.Empty);
            }

            return propertyName;
        }


    }
}