/// <summary>
        /// Contains
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        private static void Contains(MethodCallExpression expression, SqlWrapper sqlWrapper)
        {
            if (expression.Object != null)
            {
                if (typeof(IList).IsAssignableFrom(expression.Object.Type))
                {
                    SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper);
                    sqlWrapper += " IN ";
                    SqlExpressionProvider.In(expression.Object, sqlWrapper);
                }
                else
                {
                    SqlExpressionProvider.Where(expression.Object, sqlWrapper);
                    switch (sqlWrapper.DatabaseType)
                    {
                    case DatabaseType.SqlServer:
                        sqlWrapper += " LIKE '%' + ";
                        break;

                    case DatabaseType.MySql:
                    case DatabaseType.PostgreSql:
                        sqlWrapper += " LIKE CONCAT('%',";
                        break;

                    case DatabaseType.Oracle:
                    case DatabaseType.Sqlite:
                        sqlWrapper += " LIKE '%' || ";
                        break;

                    default:
                        break;
                    }
                    SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper);
                    switch (sqlWrapper.DatabaseType)
                    {
                    case DatabaseType.SqlServer:
                        sqlWrapper += " + '%'";
                        break;

                    case DatabaseType.MySql:
                    case DatabaseType.PostgreSql:
                        sqlWrapper += ",'%')";
                        break;

                    case DatabaseType.Oracle:
                    case DatabaseType.Sqlite:
                        sqlWrapper += " || '%'";
                        break;

                    default:
                        break;
                    }
                }
            }
            else if (expression.Arguments.Count > 1 && expression.Arguments[1] is MemberExpression memberExpression)
            {
                SqlExpressionProvider.Where(memberExpression, sqlWrapper);
                sqlWrapper += " IN ";
                SqlExpressionProvider.In(expression.Arguments[0], sqlWrapper);
            }
        }
 /// <summary>
 /// Not In
 /// </summary>
 /// <param name="expression">表达式树</param>
 /// <param name="sqlWrapper">sql打包对象</param>
 private static void NotIn(MethodCallExpression expression, SqlWrapper sqlWrapper)
 {
     SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper);
     sqlWrapper += " NOT IN ";
     SqlExpressionProvider.In(expression.Arguments[1], sqlWrapper);
 }
        /// <summary>
        /// Select
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper Select(NewExpression expression, SqlWrapper sqlWrapper)
        {
            if (expression.Members != null)
            {
                for (var i = 0; i < expression.Members.Count; i++)
                {
                    var argument = expression.Arguments[i];
                    var member   = expression.Members[i];
                    SqlExpressionProvider.Select(argument, sqlWrapper);

                    var fieldName = sqlWrapper
                                    .SelectFields[sqlWrapper.FieldCount - 1]
                                    .Split('.')
                                    .LastOrDefault()?
                                    .Replace("[", "")
                                    .Replace("]", "")
                                    .Replace("\"", "")
                                    .Replace("`", "");

                    //添加字段别名
                    if (argument is MemberExpression memberExpression)
                    {
                        var agrumentName = memberExpression.Member.Name;

                        if (agrumentName != member.Name)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(member.Name)}";
                        }

                        else if (agrumentName != fieldName)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(agrumentName)}";
                        }
                    }
                    else if (argument is ConstantExpression constantExpression)
                    {
                        var agrumentName = constantExpression.Value?.ToString();

                        if (agrumentName != member.Name)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(member.Name)}";
                        }

                        else if (agrumentName != fieldName)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(agrumentName)}";
                        }
                    }
                    else if (argument is MethodCallExpression methodCallExpression)
                    {
                        var agrumentName = methodCallExpression.ToObject()?.ToString();

                        if (agrumentName != member.Name)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(member.Name)}";
                        }

                        else if (agrumentName != fieldName)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(agrumentName)}";
                        }
                    }
                    else if (argument is BinaryExpression binaryExpression)
                    {
                        var agrumentName = binaryExpression.ToObject()?.ToString();

                        if (agrumentName != member.Name)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(member.Name)}";
                        }

                        else if (agrumentName != fieldName)
                        {
                            sqlWrapper.SelectFields[sqlWrapper.FieldCount - 1] += $" AS {sqlWrapper.GetFormatName(agrumentName)}";
                        }
                    }
                }
            }
            else
            {
                sqlWrapper.AddField("*");
            }

            return(sqlWrapper);
        }
예제 #4
0
        /// <summary>
        /// Expression嵌套解析
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql包装器</param>
        /// <param name="method">方法名,可选值:Join、Having、Where</param>
        public static int ExpressionNestedResolver(BinaryExpression expression, SqlWrapper sqlWrapper, string method)
        {
            //左侧嵌套
            var lExpr = expression.Left as BinaryExpression;

            var llIsBinaryExpr = lExpr?.Left is BinaryExpression;
            var llIsBoolMethod = typeof(bool) == (lExpr?.Left as MethodCallExpression)?.Method.ReturnType;

            var lrIsBinaryExpr = lExpr?.Right is BinaryExpression;
            var lrIsBoolMethod = typeof(bool) == (lExpr?.Right as MethodCallExpression)?.Method.ReturnType;

            var lNested = (llIsBinaryExpr || llIsBoolMethod) && (lrIsBinaryExpr || lrIsBoolMethod);

            if (lNested)
            {
                sqlWrapper.Append("(");
            }

            if (method.EqualIgnoreCase(nameof(Join)))
            {
                SqlExpressionProvider.Join(expression.Left, sqlWrapper);
            }

            else if (method.EqualIgnoreCase(nameof(Having)))
            {
                SqlExpressionProvider.Having(expression.Left, sqlWrapper);
            }

            else
            {
                SqlExpressionProvider.Where(expression.Left, sqlWrapper);
            }

            if (lNested)
            {
                sqlWrapper.Append(")");
            }

            var operatorIndex = sqlWrapper.Length;

            //右侧嵌套
            var rExpr = expression.Right as BinaryExpression;

            var rlIsBinaryExpr = rExpr?.Left is BinaryExpression;
            var rlIsBoolMethod = typeof(bool) == (rExpr?.Left as MethodCallExpression)?.Method.ReturnType;

            var rrIsBinaryExpr = rExpr?.Right is BinaryExpression;
            var rrIsBoolMethod = typeof(bool) == (rExpr?.Right as MethodCallExpression)?.Method.ReturnType;

            var rNested = (rlIsBinaryExpr || rlIsBoolMethod) && (rrIsBinaryExpr || rrIsBoolMethod);

            if (rNested)
            {
                sqlWrapper.Append("(");
            }

            if (method.EqualIgnoreCase(nameof(Having)))
            {
                SqlExpressionProvider.Having(expression.Right, sqlWrapper);
            }

            else
            {
                SqlExpressionProvider.Where(expression.Right, sqlWrapper);
            }

            if (rNested)
            {
                sqlWrapper.Append(")");
            }

            return(operatorIndex);
        }
        /// <summary>
        /// GroupBy
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper GroupBy(MemberExpression expression, SqlWrapper sqlWrapper)
        {
            var tableAlias = string.Empty;
            var tableName  = string.Empty;

            if (expression.Expression.NodeType == ExpressionType.Parameter)
            {
                var type = expression.Expression.Type != expression.Member.DeclaringType ?
                           expression.Expression.Type :
                           expression.Member.DeclaringType;
                tableName  = sqlWrapper.GetTableName(type);
                tableAlias = (expression.Expression as ParameterExpression)?.Name;
            }

            if (expression.Expression.NodeType == ExpressionType.Constant)
            {
                tableName = sqlWrapper.GetTableName(sqlWrapper.DefaultType);
            }

            tableAlias = sqlWrapper.GetTableAlias(tableName, tableAlias);

            if (!tableAlias.IsNullOrEmpty())
            {
                tableAlias += ".";
            }

            if (expression.Expression.NodeType == ExpressionType.Parameter)
            {
                sqlWrapper += tableAlias + sqlWrapper.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName;
            }

            if (expression.Expression.NodeType == ExpressionType.Constant)
            {
                var obj = expression.ToObject();
                if (obj != null)
                {
                    var type = obj.GetType().Name;

                    if (type == "String[]" && obj is string[] array)
                    {
                        foreach (var item in array)
                        {
                            SqlExpressionProvider.GroupBy(Expression.Constant(item, item.GetType()), sqlWrapper);
                        }
                        sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
                    }

                    if (type == "List`1" && obj is List <string> list)
                    {
                        foreach (var item in list)
                        {
                            SqlExpressionProvider.GroupBy(Expression.Constant(item, item.GetType()), sqlWrapper);
                        }
                        sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
                    }

                    if (type == "String" && obj is string str)
                    {
                        SqlExpressionProvider.GroupBy(Expression.Constant(str, str.GetType()), sqlWrapper);
                        sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
                    }
                }
            }

            return(sqlWrapper);
        }
        /// <summary>
        /// OrderBy
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <param name="orders">排序方式</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper OrderBy(MemberExpression expression, SqlWrapper sqlWrapper, params OrderType[] orders)
        {
            var tableAlias = string.Empty;
            var tableName  = string.Empty;

            if (expression.Expression.NodeType == ExpressionType.Parameter)
            {
                var type = expression.Expression.Type != expression.Member.DeclaringType ?
                           expression.Expression.Type :
                           expression.Member.DeclaringType;
                tableName = sqlWrapper.GetTableName(type);
            }

            if (expression.Expression.NodeType == ExpressionType.Constant)
            {
                tableName = sqlWrapper.GetTableName(sqlWrapper.DefaultType);
            }

            tableAlias = sqlWrapper.GetTableAlias(tableName, tableAlias);

            if (!tableAlias.IsNullOrEmpty())
            {
                tableAlias += ".";
            }

            if (expression.Expression.NodeType == ExpressionType.Parameter)
            {
                sqlWrapper += tableAlias + sqlWrapper.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName;
                if (orders?.Length > 0)
                {
                    sqlWrapper += $" { (orders[0] == OrderType.Descending ? "DESC" : "ASC")}";
                }
            }

            if (expression.Expression.NodeType == ExpressionType.Constant)
            {
                var obj = expression.ToObject();
                if (obj != null)
                {
                    var type = obj.GetType().Name;

                    if (type == "String[]" && obj is string[] array)
                    {
                        for (var i = 0; i < array.Length; i++)
                        {
                            SqlExpressionProvider.OrderBy(Expression.Constant(array[i], array[i].GetType()), sqlWrapper);

                            if (i <= orders.Length - 1)
                            {
                                sqlWrapper += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},";
                            }
                            else if (!array[i].ToUpper().Contains("ASC") && !array[i].ToUpper().Contains("DESC"))
                            {
                                sqlWrapper += " ASC,";
                            }
                            else
                            {
                                sqlWrapper += ",";
                            }
                        }
                        sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
                    }

                    if (type == "List`1" && obj is List <string> list)
                    {
                        for (var i = 0; i < list.Count; i++)
                        {
                            SqlExpressionProvider.OrderBy(Expression.Constant(list[i], list[i].GetType()), sqlWrapper);

                            if (i <= orders.Length - 1)
                            {
                                sqlWrapper += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},";
                            }
                            else if (!list[i].ToUpper().Contains("ASC") && !list[i].ToUpper().Contains("DESC"))
                            {
                                sqlWrapper += " ASC,";
                            }
                            else
                            {
                                sqlWrapper += ",";
                            }
                        }
                        sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
                    }

                    if (type == "String" && obj is string str)
                    {
                        SqlExpressionProvider.OrderBy(Expression.Constant(str, str.GetType()), sqlWrapper);
                        str = str.ToUpper();
                        if (!str.Contains("ASC") && !str.Contains("DESC"))
                        {
                            if (orders.Length >= 1)
                            {
                                sqlWrapper += $" { (orders[0] == OrderType.Descending ? "DESC" : "ASC")},";
                            }
                            else
                            {
                                sqlWrapper += " ASC,";
                            }

                            sqlWrapper.Remove(sqlWrapper.Length - 1, 1);
                        }
                    }
                }
            }

            return(sqlWrapper);
        }
예제 #7
0
        /// <summary>
        /// Where
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper Where(UnaryExpression expression, SqlWrapper sqlWrapper)
        {
            var startIndex = sqlWrapper.Length;

            SqlExpressionProvider.Where(expression.Operand, sqlWrapper);

            if (expression.NodeType == ExpressionType.Not)
            {
                var subString = sqlWrapper.Substring(startIndex, sqlWrapper.Length - startIndex);

                //IS NOT、IS
                if (subString.Contains("IS NOT"))
                {
                    var index = sqlWrapper.LastIndexOf("IS NOT");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("IS NOT", "IS", index, 6);
                    }
                }
                if (subString.Contains("IS") && subString.LastIndexOf("IS") != subString.LastIndexOf("IS NOT"))
                {
                    var index = sqlWrapper.LastIndexOf("IS");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("IS", "IS NOT", index, 2);
                    }
                }

                //NOT LIKE、LIKE
                if (subString.Contains("NOT LIKE"))
                {
                    var index = sqlWrapper.LastIndexOf("NOT LIKE");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("NOT LIKE", "LIKE", index, 8);
                    }
                }
                if (subString.Contains("LIKE") && subString.LastIndexOf("LIKE") != (subString.LastIndexOf("NOT LIKE") + 4))
                {
                    var index = sqlWrapper.LastIndexOf("LIKE");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("LIKE", "NOT LIKE", index, 4);
                    }
                }

                //NOT IN、IN
                if (subString.Contains("NOT IN"))
                {
                    var index = sqlWrapper.LastIndexOf("NOT IN");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("NOT IN", "IN", index, 6);
                    }
                }
                if (subString.Contains("IN") && subString.LastIndexOf("IN") != (subString.LastIndexOf("NOT IN") + 4))
                {
                    var index = sqlWrapper.LastIndexOf("IN");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("IN", "NOT IN", index, 2);
                    }
                }

                //AND、OR
                if (subString.Contains("AND"))
                {
                    var index = sqlWrapper.LastIndexOf("AND");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("AND", "OR", index, 3);
                    }
                }
                if (subString.Contains("OR"))
                {
                    var index = sqlWrapper.LastIndexOf("OR");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("OR", "AND", index, 2);
                    }
                }

                //=、<>
                if (subString.Contains(" = "))
                {
                    var index = sqlWrapper.LastIndexOf(" = ");
                    if (index != -1)
                    {
                        sqlWrapper.Replace(" = ", " <> ", index, 3);
                    }
                }
                if (subString.Contains("<>"))
                {
                    var index = sqlWrapper.LastIndexOf("<>");
                    if (index != -1)
                    {
                        sqlWrapper.Replace("<>", "=", index, 2);
                    }
                }

                //>、<
                if (subString.Contains(" > "))
                {
                    var index = sqlWrapper.LastIndexOf(" > ");
                    if (index != -1)
                    {
                        sqlWrapper.Replace(" > ", " <= ", index, 3);
                    }
                }
                if (subString.Contains(" < "))
                {
                    var index = sqlWrapper.LastIndexOf(" < ");
                    if (index != -1)
                    {
                        sqlWrapper.Replace(" < ", " >= ", index, 3);
                    }
                }

                //>=、<=
                if (subString.Contains(" >= "))
                {
                    var index = sqlWrapper.LastIndexOf(" >= ");
                    if (index != -1)
                    {
                        sqlWrapper.Replace(" >= ", " < ", index, 4);
                    }
                }
                if (subString.Contains(" <= "))
                {
                    var index = sqlWrapper.LastIndexOf(" <= ");
                    if (index != -1)
                    {
                        sqlWrapper.Replace(" <= ", " > ", index, 4);
                    }
                }
            }

            return(sqlWrapper);
        }
예제 #8
0
        /// <summary>
        /// Update
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper Update(UnaryExpression expression, SqlWrapper sqlWrapper)
        {
            SqlExpressionProvider.Update(expression.Operand, sqlWrapper);

            return(sqlWrapper);
        }
예제 #9
0
        /// <summary>
        /// Insert
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper Insert(UnaryExpression expression, SqlWrapper sqlWrapper)
        {
            SqlExpressionProvider.Insert(expression.Operand, sqlWrapper);

            return(sqlWrapper);
        }
예제 #10
0
        /// <summary>
        /// OrderBy
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <param name="orders">排序方式</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper OrderBy(UnaryExpression expression, SqlWrapper sqlWrapper, params OrderType[] orders)
        {
            SqlExpressionProvider.OrderBy(expression.Operand, sqlWrapper, orders);

            return(sqlWrapper);
        }
예제 #11
0
        /// <summary>
        /// GroupBy
        /// </summary>
        /// <param name="expression">表达式树</param>
        /// <param name="sqlWrapper">sql打包对象</param>
        /// <returns>SqlWrapper</returns>
        public override SqlWrapper GroupBy(UnaryExpression expression, SqlWrapper sqlWrapper)
        {
            SqlExpressionProvider.GroupBy(expression.Operand, sqlWrapper);

            return(sqlWrapper);
        }