/// <summary> /// TrimEnd /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void TrimEnd(MethodCallExpression expression, SqlWrapper sqlWrapper) { if (expression.Object != null) { if (expression.Arguments?.Count > 0) { var trimString = expression.Object.ToObject()?.ToString(); if (!trimString.IsNullOrEmpty()) { string constant; var argument = expression.Arguments[0].ToObject(); if (argument is char @char) { constant = trimString.TrimEnd(@char); } else { constant = trimString.TrimEnd((char[])argument); } SqlExpressionProvider.Where(Expression.Constant(constant), sqlWrapper); } } else { sqlWrapper += "RTRIM("; SqlExpressionProvider.Where(expression.Object, sqlWrapper); sqlWrapper += ")"; } } }
/// <summary> /// Trim /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void Trim(MethodCallExpression expression, SqlWrapper sqlWrapper) { if (expression.Object != null) { if (sqlWrapper.DatabaseType == DatabaseType.SqlServer) { sqlWrapper += "LTRIM(RTRIM("; } else { sqlWrapper += "TRIM("; } SqlExpressionProvider.Where(expression.Object, sqlWrapper); if (sqlWrapper.DatabaseType == DatabaseType.SqlServer) { sqlWrapper += "))"; } else { sqlWrapper += ")"; } } }
/// <summary> /// Join /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Join(BinaryExpression expression, SqlWrapper sqlWrapper) { //左侧嵌套 var leftBinary = expression.Left as BinaryExpression; var isBinaryLeft = leftBinary?.Left is BinaryExpression; var isBoolMethodCallLeft = (leftBinary?.Left as MethodCallExpression)?.Method.ReturnType == typeof(bool); var isBinaryRight = leftBinary?.Right is BinaryExpression; var isBoolMethodCallRight = (leftBinary?.Right as MethodCallExpression)?.Method.ReturnType == typeof(bool); var leftNested = (isBinaryLeft || isBoolMethodCallLeft) && (isBinaryRight || isBoolMethodCallRight); if (leftNested) { sqlWrapper += "("; } SqlExpressionProvider.Join(expression.Left, sqlWrapper); if (leftNested) { sqlWrapper += ")"; } var operatorIndex = sqlWrapper.Length; //右侧嵌套 var rightBinary = expression.Right as BinaryExpression; isBinaryLeft = rightBinary?.Left is BinaryExpression; isBoolMethodCallLeft = (rightBinary?.Left as MethodCallExpression)?.Method.ReturnType == typeof(bool); isBinaryRight = rightBinary?.Right is BinaryExpression; isBoolMethodCallRight = (rightBinary?.Right as MethodCallExpression)?.Method.ReturnType == typeof(bool); var rightNested = (isBinaryLeft || isBoolMethodCallLeft) && (isBinaryRight || isBoolMethodCallRight); if (rightNested) { sqlWrapper += "("; } SqlExpressionProvider.Where(expression.Right, sqlWrapper); if (rightNested) { sqlWrapper += ")"; } //表达式左侧为bool类型常量且为true时,不进行sql拼接 if (!(expression.Left.NodeType == ExpressionType.Constant && expression.Left.ToObject() is bool b && b)) { var sqlLength = sqlWrapper.Length; OperatorResolver( expression.NodeType, operatorIndex, sqlWrapper, sqlLength - operatorIndex == 5 && sqlWrapper.EndsWith("NULL")); } return(sqlWrapper); }
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql包装器</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Where(NewArrayExpression expression, SqlWrapper sqlWrapper) { foreach (var item in expression.Expressions) { SqlExpressionProvider.Where(item, sqlWrapper); } return(sqlWrapper); }
/// <summary> /// TrimEnd /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void TrimEnd(MethodCallExpression expression, SqlWrapper sqlWrapper) { if (expression.Object != null) { sqlWrapper += "RTRIM("; SqlExpressionProvider.Where(expression.Object, sqlWrapper); sqlWrapper += ")"; } }
/// <summary> /// IsNullOrEmpty /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void IsNullOrEmpty(MethodCallExpression expression, SqlWrapper sqlWrapper) { sqlWrapper += "("; SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper); sqlWrapper += " IS NULL OR "; SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper); sqlWrapper += " = ''"; sqlWrapper += ")"; }
/// <summary> /// ToLower /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void ToLower(MethodCallExpression expression, SqlWrapper sqlWrapper) { if (expression.Object != null) { sqlWrapper += "LOWER("; SqlExpressionProvider.Where(expression.Object, sqlWrapper); sqlWrapper += ")"; } }
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Where(ConditionalExpression expression, SqlWrapper sqlWrapper) { var res = (bool)expression.Test.ToObject(); if (res) { SqlExpressionProvider.Where(expression.IfTrue, sqlWrapper); } else { SqlExpressionProvider.Where(expression.IfFalse, sqlWrapper); } return(sqlWrapper); }
/// <summary> /// NotLike /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void NotLike(MethodCallExpression expression, SqlWrapper sqlWrapper) { if (expression.Object != null) { SqlExpressionProvider.Where(expression.Object, sqlWrapper); } SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper); switch (sqlWrapper.DatabaseType) { case DatabaseType.SqlServer: sqlWrapper += " NOT LIKE '%' + "; break; case DatabaseType.MySql: case DatabaseType.PostgreSql: sqlWrapper += " NOT LIKE CONCAT('%',"; break; case DatabaseType.Oracle: case DatabaseType.Sqlite: sqlWrapper += " NOT LIKE '%' || "; break; default: break; } SqlExpressionProvider.Where(expression.Arguments[1], 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; } }
/// <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; if (new[] { ExpressionType.ArrayLength, ExpressionType.ArrayIndex }.Contains(expression.NodeType)) { sqlWrapper.AddDbParameter(expression.ToObject()); } else { SqlExpressionProvider.Where(expression.Operand, sqlWrapper); //取非解析 ExpressionNotResolver(expression, sqlWrapper, startIndex); } return(sqlWrapper); }
/// <summary> /// Equals /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> private static void Equals(MethodCallExpression expression, SqlWrapper sqlWrapper) { if (expression.Object != null) { SqlExpressionProvider.Where(expression.Object, sqlWrapper); } var signIndex = sqlWrapper.Length; SqlExpressionProvider.Where(expression.Arguments[0], sqlWrapper); if (sqlWrapper.EndsWith("NULL")) { sqlWrapper.Insert(signIndex, " IS "); } else { sqlWrapper.Insert(signIndex, " = "); } }
/// <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> /// 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> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Where(LambdaExpression expression, SqlWrapper sqlWrapper) { SqlExpressionProvider.Where(expression.Body, sqlWrapper); return(sqlWrapper); }
/// <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> /// 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); }
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql包装器</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Where(InvocationExpression expression, SqlWrapper sqlWrapper) { SqlExpressionProvider.Where(expression.Expression, sqlWrapper); return(sqlWrapper); }
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql打包对象</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Where(BinaryExpression expression, SqlWrapper sqlWrapper) { var startIndex = sqlWrapper.Length; //左侧嵌套 var leftBinary = expression.Left as BinaryExpression; var isBinaryLeft = leftBinary?.Left is BinaryExpression; var isBoolMethodCallLeft = (leftBinary?.Left as MethodCallExpression)?.Method.ReturnType == typeof(bool); var isBinaryRight = leftBinary?.Right is BinaryExpression; var isBoolMethodCallRight = (leftBinary?.Right as MethodCallExpression)?.Method.ReturnType == typeof(bool); var leftNested = (isBinaryLeft || isBoolMethodCallLeft) && (isBinaryRight || isBoolMethodCallRight); if (leftNested) { sqlWrapper += "("; } SqlExpressionProvider.Where(expression.Left, sqlWrapper); if (leftNested) { sqlWrapper += ")"; } var signIndex = sqlWrapper.Length; //右侧嵌套 var rightBinary = expression.Right as BinaryExpression; isBinaryLeft = rightBinary?.Left is BinaryExpression; isBoolMethodCallLeft = (rightBinary?.Left as MethodCallExpression)?.Method.ReturnType == typeof(bool); isBinaryRight = rightBinary?.Right is BinaryExpression; isBoolMethodCallRight = (rightBinary?.Right as MethodCallExpression)?.Method.ReturnType == typeof(bool); var rightNested = (isBinaryLeft || isBoolMethodCallLeft) && (isBinaryRight || isBoolMethodCallRight); if (rightNested) { sqlWrapper += "("; } SqlExpressionProvider.Where(expression.Right, sqlWrapper); if (rightNested) { sqlWrapper += ")"; } //表达式左侧为bool类型常量且为true时,不进行sql拼接 if (!(expression.Left.NodeType == ExpressionType.Constant && expression.Left.ToObject() is bool b && b)) { //若表达式右侧为bool类型,且为false时,条件取非 if ((expression.Right.NodeType == ExpressionType.Constant || (expression.Right.NodeType == ExpressionType.Convert && expression.Right is UnaryExpression unary && unary.Operand.NodeType == ExpressionType.Constant)) && expression.Right.ToObject() is bool r) { if (!r) { 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); } } } }
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql包装器</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Where(ConditionalExpression expression, SqlWrapper sqlWrapper) { var isNot = false; var testExpression = expression.Test; //UnaryExpression if (testExpression is UnaryExpression unaryExpression) { isNot = unaryExpression.NodeType == ExpressionType.Not; testExpression = unaryExpression.Operand; } //MethodCallExpression if (testExpression is MethodCallExpression methodCallExpression) { var test = methodCallExpression.ToObject <bool>(out var res); if (res) { if (isNot ? !test : test) { SqlExpressionProvider.Where(expression.IfTrue, sqlWrapper); } else { SqlExpressionProvider.Where(expression.IfFalse, sqlWrapper); } } } //BinaryExpression if (testExpression is BinaryExpression binaryExpression) { var test = binaryExpression.ToObject <bool>(out var res); if (!res) { test = binaryExpression.Left.ToObject <bool>(out var left); if (!left) { SqlExpressionProvider.Where(binaryExpression.Left, sqlWrapper); BinaryExpressionResolver.OperatorResolver( binaryExpression.NodeType, sqlWrapper.Length, sqlWrapper, sqlWrapper.EndsWith("NULL")); } var right = false; if (!left) { test = binaryExpression.Right.ToObject <bool>(out right); } if (left || !right) { SqlExpressionProvider.Where(binaryExpression.Right, sqlWrapper); BinaryExpressionResolver.OperatorResolver( binaryExpression.NodeType, sqlWrapper.Length, sqlWrapper, sqlWrapper.EndsWith("NULL")); } } if (isNot ? !test : test) { SqlExpressionProvider.Where(expression.IfTrue, sqlWrapper); } else { SqlExpressionProvider.Where(expression.IfFalse, sqlWrapper); } } return(sqlWrapper); }