/// <summary> /// Join /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlWrapper">sql包装器</param> /// <returns>SqlWrapper</returns> public override SqlWrapper Join(LambdaExpression expression, SqlWrapper sqlWrapper) { SqlExpressionProvider.Join(expression.Body, sqlWrapper); 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); }
/// <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> /// 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); }