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