/// <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);
}