/// <summary> /// Count /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Count(Expression expression, SqlPack sqlPack) => Count((T)expression, sqlPack);
/// <summary> /// Sum /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Sum(Expression expression, SqlPack sqlPack) => Sum((T)expression, sqlPack);
/// <summary> /// Min /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Min(Expression expression, SqlPack sqlPack) => Min((T)expression, sqlPack);
/// <summary> /// Avg /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Avg(Expression expression, SqlPack sqlPack) => Avg((T)expression, sqlPack);
/// <summary> /// OrderBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <param name="orders">排序方式</param> /// <returns>SqlPack</returns> public override SqlPack OrderBy(MemberExpression expression, SqlPack sqlPack, params OrderType[] orders) { string tableName = string.Empty; if (expression.Expression.NodeType == ExpressionType.Parameter) { var type = expression.Expression.Type != expression.Member.DeclaringType ? expression.Expression.Type : expression.Member.DeclaringType; tableName = sqlPack.GetTableName(type); } if (expression.Expression.NodeType == ExpressionType.Constant) { tableName = sqlPack.GetTableName(sqlPack.DefaultType); } sqlPack.SetTableAlias(tableName); var tableAlias = sqlPack.GetTableAlias(tableName); if (!tableAlias.IsNullOrEmpty()) { tableAlias += "."; } if (expression.Expression.NodeType == ExpressionType.Parameter) { sqlPack += tableAlias + sqlPack.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName; if (orders?.Length > 0) { sqlPack += $" { (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++) { SqlBuilderProvider.OrderBy(Expression.Constant(array[i], array[i].GetType()), sqlPack); if (i <= orders.Length - 1) { sqlPack += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},"; } else if (!array[i].ToUpper().Contains("ASC") && !array[i].ToUpper().Contains("DESC")) { sqlPack += " ASC,"; } else { sqlPack += ","; } } sqlPack.Sql.Remove(sqlPack.Length - 1, 1); } if (type == "List`1" && obj is List <string> list) { for (var i = 0; i < list.Count; i++) { SqlBuilderProvider.OrderBy(Expression.Constant(list[i], list[i].GetType()), sqlPack); if (i <= orders.Length - 1) { sqlPack += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},"; } else if (!list[i].ToUpper().Contains("ASC") && !list[i].ToUpper().Contains("DESC")) { sqlPack += " ASC,"; } else { sqlPack += ","; } } sqlPack.Sql.Remove(sqlPack.Length - 1, 1); } if (type == "String" && obj is string str) { SqlBuilderProvider.OrderBy(Expression.Constant(str, str.GetType()), sqlPack); str = str.ToUpper(); if (!str.Contains("ASC") && !str.Contains("DESC")) { if (orders.Length >= 1) { sqlPack += $" { (orders[0] == OrderType.Descending ? "DESC" : "ASC")},"; } else { sqlPack += " ASC,"; } sqlPack.Sql.Remove(sqlPack.Length - 1, 1); } } } } return(sqlPack); }
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public override SqlPack Where(BinaryExpression expression, SqlPack sqlPack) { var startIndex = sqlPack.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) { sqlPack += "("; } SqlBuilderProvider.Where(expression.Left, sqlPack); if (leftNested) { sqlPack += ")"; } var signIndex = sqlPack.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) { sqlPack += "("; } SqlBuilderProvider.Where(expression.Right, sqlPack); if (rightNested) { sqlPack += ")"; } //表达式左侧为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 = sqlPack.ToString().Substring(startIndex, sqlPack.ToString().Length - startIndex).ToUpper(); //IS NOT、IS if (subString.Contains("IS NOT")) { var index = sqlPack.ToString().LastIndexOf("IS NOT"); if (index != -1) { sqlPack.Sql.Replace("IS NOT", "IS", index, 6); } } if (subString.Contains("IS") && subString.LastIndexOf("IS") != subString.LastIndexOf("IS NOT")) { var index = sqlPack.ToString().LastIndexOf("IS"); if (index != -1) { sqlPack.Sql.Replace("IS", "IS NOT", index, 2); } } //NOT LIKE、LIKE if (subString.Contains("NOT LIKE")) { var index = sqlPack.ToString().LastIndexOf("NOT LIKE"); if (index != -1) { sqlPack.Sql.Replace("NOT LIKE", "LIKE", index, 8); } } if (subString.Contains("LIKE") && subString.LastIndexOf("LIKE") != (subString.LastIndexOf("NOT LIKE") + 4)) { var index = sqlPack.ToString().LastIndexOf("LIKE"); if (index != -1) { sqlPack.Sql.Replace("LIKE", "NOT LIKE", index, 4); } } //NOT IN、IN if (subString.Contains("NOT IN")) { var index = sqlPack.ToString().LastIndexOf("NOT IN"); if (index != -1) { sqlPack.Sql.Replace("NOT IN", "IN", index, 6); } } if (subString.Contains("IN") && subString.LastIndexOf("IN") != (subString.LastIndexOf("NOT IN") + 4)) { var index = sqlPack.ToString().LastIndexOf("IN"); if (index != -1) { sqlPack.Sql.Replace("IN", "NOT IN", index, 2); } } //AND、OR if (subString.Contains("AND")) { var index = sqlPack.ToString().LastIndexOf("AND"); if (index != -1) { sqlPack.Sql.Replace("AND", "OR", index, 3); } } if (subString.Contains("OR")) { var index = sqlPack.ToString().LastIndexOf("OR"); if (index != -1) { sqlPack.Sql.Replace("OR", "AND", index, 2); } } //=、<> if (subString.Contains(" = ")) { var index = sqlPack.ToString().LastIndexOf(" = "); if (index != -1) { sqlPack.Sql.Replace(" = ", " <> ", index, 3); } } if (subString.Contains("<>")) { var index = sqlPack.ToString().LastIndexOf("<>"); if (index != -1) { sqlPack.Sql.Replace("<>", "=", index, 2); } } //>、< if (subString.Contains(" > ")) { var index = sqlPack.ToString().LastIndexOf(" > "); if (index != -1) { sqlPack.Sql.Replace(" > ", " <= ", index, 3); } } if (subString.Contains(" < ")) { var index = sqlPack.ToString().LastIndexOf(" < "); if (index != -1) { sqlPack.Sql.Replace(" < ", " >= ", index, 3); } } //>=、<= if (subString.Contains(" >= ")) { var index = sqlPack.ToString().LastIndexOf(" >= "); if (index != -1) { sqlPack.Sql.Replace(" >= ", " < ", index, 4); } } if (subString.Contains(" <= ")) { var index = sqlPack.ToString().LastIndexOf(" <= "); if (index != -1) { sqlPack.Sql.Replace(" <= ", " > ", index, 4); } } } }
/// <summary> /// In /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack In(Expression expression, SqlPack sqlPack) => In((T)expression, sqlPack);
/// <summary> /// Not In /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> private static void NotIn(MethodCallExpression expression, SqlPack sqlPack) { SqlBuilderProvider.Where(expression.Arguments[0], sqlPack); sqlPack += " NOT IN "; SqlBuilderProvider.In(expression.Arguments[1], sqlPack); }
/// <summary> /// Join /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Join(Expression expression, SqlPack sqlPack) => Join((T)expression, sqlPack);
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Where(Expression expression, SqlPack sqlPack) => Where((T)expression, sqlPack);
/// <summary> /// Select /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Select(Expression expression, SqlPack sqlPack) => Select((T)expression, sqlPack);
/// <summary> /// Update /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Update(Expression expression, SqlPack sqlPack) => Update((T)expression, sqlPack);
/// <summary> /// OperatorParser /// </summary> /// <param name="expressionNodeType">表达式树节点类型</param> /// <param name="operatorIndex">操作符索引</param> /// <param name="sqlPack">sql打包对象</param> /// <param name="useIs">是否使用is</param> private void OperatorParser(ExpressionType expressionNodeType, int operatorIndex, SqlPack sqlPack, bool useIs = false) { switch (expressionNodeType) { case ExpressionType.And: case ExpressionType.AndAlso: sqlPack.Sql.Insert(operatorIndex, " AND "); break; case ExpressionType.Equal: if (useIs) { sqlPack.Sql.Insert(operatorIndex, " IS "); } else { sqlPack.Sql.Insert(operatorIndex, " = "); } break; case ExpressionType.GreaterThan: sqlPack.Sql.Insert(operatorIndex, " > "); break; case ExpressionType.GreaterThanOrEqual: sqlPack.Sql.Insert(operatorIndex, " >= "); break; case ExpressionType.NotEqual: if (useIs) { sqlPack.Sql.Insert(operatorIndex, " IS NOT "); } else { sqlPack.Sql.Insert(operatorIndex, " <> "); } break; case ExpressionType.Or: case ExpressionType.OrElse: sqlPack.Sql.Insert(operatorIndex, " OR "); break; case ExpressionType.LessThan: sqlPack.Sql.Insert(operatorIndex, " < "); break; case ExpressionType.LessThanOrEqual: sqlPack.Sql.Insert(operatorIndex, " <= "); break; case ExpressionType.Add: sqlPack.Sql.Insert(operatorIndex, " + "); break; case ExpressionType.Subtract: sqlPack.Sql.Insert(operatorIndex, " - "); break; case ExpressionType.Multiply: sqlPack.Sql.Insert(operatorIndex, " * "); break; case ExpressionType.Divide: sqlPack.Sql.Insert(operatorIndex, " / "); break; case ExpressionType.Modulo: sqlPack.Sql.Insert(operatorIndex, " % "); break; default: throw new NotImplementedException("未实现的节点类型" + expressionNodeType); } }
/// <summary> /// GroupBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public virtual SqlPack GroupBy(T expression, SqlPack sqlPack) => throw new NotImplementedException("未实现" + typeof(T).Name + "ISqlBuilder.GroupBy方法");
/// <summary> /// GroupBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack GroupBy(Expression expression, SqlPack sqlPack) => GroupBy((T)expression, sqlPack);
/// <summary> /// OrderBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <param name="orders">排序方式</param> /// <returns>SqlPack</returns> public virtual SqlPack OrderBy(T expression, SqlPack sqlPack, params OrderType[] orders) => throw new NotImplementedException("未实现" + typeof(T).Name + "ISqlBuilder.OrderBy方法");
/// <summary> /// OrderBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <param name="orders">排序方式</param> /// <returns>SqlPack</returns> public SqlPack OrderBy(Expression expression, SqlPack sqlPack, params OrderType[] orders) => OrderBy((T)expression, sqlPack, orders);
/// <summary> /// In /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public override SqlPack In(ConstantExpression expression, SqlPack sqlPack) { sqlPack.AddDbParameter(expression.Value); return(sqlPack); }
/// <summary> /// Max /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Max(Expression expression, SqlPack sqlPack) => Max((T)expression, sqlPack);
/// <summary> /// Insert /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public override SqlPack Insert(MemberExpression expression, SqlPack sqlPack) { var objectArray = new List <object>(); var fields = new List <string>(); var obj = expression.ToObject(); if (obj.GetType().IsArray) { objectArray.AddRange(obj as object[]); } else if (obj.GetType().Name == "List`1") { objectArray.AddRange(obj as IEnumerable <object>); } else { objectArray.Add(obj); } for (var i = 0; i < objectArray.Count; i++) { if (sqlPack.DatabaseType != DatabaseType.Oracle) { sqlPack.Sql.Append("("); } if (i > 0 && sqlPack.DatabaseType == DatabaseType.Oracle) { sqlPack.Sql.Append(" UNION ALL SELECT "); } var properties = objectArray[i]?.GetType().GetProperties(); foreach (var p in properties) { var type = p.DeclaringType.ToString().Contains("AnonymousType") ? sqlPack.DefaultType : p.DeclaringType; (string columnName, bool isInsert, bool isUpdate) = sqlPack.GetColumnInfo(type, p); if (isInsert) { var value = p.GetValue(objectArray[i], null); if (value != null || (sqlPack.IsEnableNullValue && value == null)) { sqlPack.AddDbParameter(value); if (!fields.Contains(columnName)) { fields.Add(columnName); } sqlPack += ","; } } } if (sqlPack[sqlPack.Length - 1] == ',') { sqlPack.Sql.Remove(sqlPack.Length - 1, 1); if (sqlPack.DatabaseType != DatabaseType.Oracle) { sqlPack.Sql.Append("),"); } else { sqlPack.Sql.Append(" FROM DUAL"); } } } if (sqlPack[sqlPack.Length - 1] == ',') { sqlPack.Sql.Remove(sqlPack.Length - 1, 1); } sqlPack.Sql = new StringBuilder(string.Format(sqlPack.ToString(), string.Join(",", fields).TrimEnd(','))); return(sqlPack); }
/// <summary> /// In /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public virtual SqlPack In(T expression, SqlPack sqlPack) => throw new NotImplementedException("未实现" + typeof(T).Name + "ToSql.In方法");