/// <summary>
/// Join
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Join(BinaryExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Join(expression.Left, sqlPack);
var operatorIndex = sqlPack.Sql.Length;
//嵌套条件
var flag = false;
if (expression.Right is BinaryExpression binaryExpression && (binaryExpression.Right as BinaryExpression) != null)
{
flag = true;
sqlPack += "(";
}
SqlBuilderProvider.Where(expression.Right, sqlPack);
if (flag)
{
sqlPack += ")";
}
var sqlLength = sqlPack.Sql.Length;
if (sqlLength - operatorIndex == 5 && sqlPack.ToString().ToUpper().EndsWith("NULL"))
{
OperatorParser(expression.NodeType, operatorIndex, sqlPack, true);
}
else
{
OperatorParser(expression.NodeType, operatorIndex, sqlPack);
}
return(sqlPack);
}
/// <summary>
/// ToLower
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void ToLower(MethodCallExpression expression, SqlPack sqlPack)
{
if (expression.Object != null)
{
sqlPack += "LOWER(";
SqlBuilderProvider.Where(expression.Object, sqlPack);
sqlPack += ")";
}
}
/// <summary>
/// TrimEnd
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void TrimEnd(MethodCallExpression expression, SqlPack sqlPack)
{
if (expression.Object != null)
{
sqlPack += "RTRIM(";
SqlBuilderProvider.Where(expression.Object, sqlPack);
sqlPack += ")";
}
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <returns>SqlBuilderCore</returns>
public SqlBuilderCore <T> Where(Expression <Func <T, bool> > expression)
{
if (!(expression.Body.NodeType == ExpressionType.Constant && expression.Body.ToObject() is bool b && b))
{
this._sqlPack += " WHERE ";
SqlBuilderProvider.Where(expression.Body, this._sqlPack);
}
return(this);
}
/// <summary>
/// IsNullOrEmpty
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void IsNullOrEmpty(MethodCallExpression expression, SqlPack sqlPack)
{
sqlPack += "(";
SqlBuilderProvider.Where(expression.Arguments[0], sqlPack);
sqlPack += " IS NULL OR ";
SqlBuilderProvider.Where(expression.Arguments[0], sqlPack);
sqlPack += " = ''";
sqlPack += ")";
}
/// <summary>
/// Join
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Join(BinaryExpression expression, SqlPack sqlPack)
{
//左侧嵌套
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.Join(expression.Left, sqlPack);
if (leftNested)
{
sqlPack += ")";
}
var operatorIndex = sqlPack.Sql.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 += ")";
}
var sqlLength = sqlPack.Sql.Length;
if (sqlLength - operatorIndex == 5 && sqlPack.ToString().ToUpper().EndsWith("NULL"))
{
OperatorParser(expression.NodeType, operatorIndex, sqlPack, true);
}
else
{
OperatorParser(expression.NodeType, operatorIndex, sqlPack);
}
return(sqlPack);
}
/// <summary>
/// Contains
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void Contains(MethodCallExpression expression, SqlPack sqlPack)
{
if (expression.Object != null)
{
SqlBuilderProvider.Where(expression.Object, sqlPack);
switch (sqlPack.DatabaseType)
{
case DatabaseType.SQLServer:
sqlPack += " LIKE '%' + ";
break;
case DatabaseType.MySQL:
case DatabaseType.PostgreSQL:
sqlPack += " LIKE CONCAT('%',";
break;
case DatabaseType.Oracle:
case DatabaseType.SQLite:
sqlPack += " LIKE '%' || ";
break;
default:
break;
}
SqlBuilderProvider.Where(expression.Arguments[0], sqlPack);
switch (sqlPack.DatabaseType)
{
case DatabaseType.SQLServer:
sqlPack += " + '%'";
break;
case DatabaseType.MySQL:
case DatabaseType.PostgreSQL:
sqlPack += ",'%')";
break;
case DatabaseType.Oracle:
case DatabaseType.SQLite:
sqlPack += " || '%'";
break;
default:
break;
}
}
else if (expression.Arguments.Count > 1 && expression.Arguments[1] is MemberExpression memberExpression)
{
SqlBuilderProvider.Where(memberExpression, sqlPack);
sqlPack += " IN ";
SqlBuilderProvider.In(expression.Arguments[0], sqlPack);
}
}
/// <summary>
/// OrWhere
/// </summary>
/// <param name="expression">表达式树</param>
/// <returns>SqlBuilderCore</returns>
public SqlBuilderCore <T> OrWhere(Expression <Func <T, bool> > expression)
{
var sql = this._sqlPack.ToString();
if (sql.Contains("WHERE") && !string.IsNullOrEmpty(sql.Substring("WHERE").Trim()))
{
this._sqlPack += " OR ";
}
else
{
this._sqlPack += " WHERE ";
}
SqlBuilderProvider.Where(expression.Body, this._sqlPack);
return(this);
}
/// <summary>
/// Like
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void Like(MethodCallExpression expression, SqlPack sqlPack)
{
if (expression.Object != null)
{
SqlBuilderProvider.Where(expression.Object, sqlPack);
}
SqlBuilderProvider.Where(expression.Arguments[0], sqlPack);
switch (sqlPack.DatabaseType)
{
case DatabaseType.SQLServer:
sqlPack += " LIKE '%' + ";
break;
case DatabaseType.MySQL:
case DatabaseType.PostgreSQL:
sqlPack += " LIKE CONCAT('%',";
break;
case DatabaseType.Oracle:
case DatabaseType.SQLite:
sqlPack += " LIKE '%' || ";
break;
default:
break;
}
SqlBuilderProvider.Where(expression.Arguments[1], sqlPack);
switch (sqlPack.DatabaseType)
{
case DatabaseType.SQLServer:
sqlPack += " + '%'";
break;
case DatabaseType.MySQL:
case DatabaseType.PostgreSQL:
sqlPack += ",'%')";
break;
case DatabaseType.Oracle:
case DatabaseType.SQLite:
sqlPack += " || '%'";
break;
default:
break;
}
}
/// <summary>
/// Equals
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void Equals(MethodCallExpression expression, SqlPack sqlPack)
{
if (expression.Object != null)
{
SqlBuilderProvider.Where(expression.Object, sqlPack);
}
var signIndex = sqlPack.Length;
SqlBuilderProvider.Where(expression.Arguments[0], sqlPack);
if (sqlPack.ToString().ToUpper().EndsWith("NULL"))
{
sqlPack.Sql.Insert(signIndex, " IS ");
}
else
{
sqlPack.Sql.Insert(signIndex, " = ");
}
}
/// <summary>
/// Trim
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void Trim(MethodCallExpression expression, SqlPack sqlPack)
{
if (expression.Object != null)
{
if (sqlPack.DatabaseType == DatabaseType.SQLServer)
{
sqlPack += "LTRIM(RTRIM(";
}
else
{
sqlPack += "TRIM(";
}
SqlBuilderProvider.Where(expression.Object, sqlPack);
if (sqlPack.DatabaseType == DatabaseType.SQLServer)
{
sqlPack += "))";
}
else
{
sqlPack += ")";
}
}
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(UnaryExpression expression, SqlPack sqlPack)
{
var startIndex = sqlPack.Length;
SqlBuilderProvider.Where(expression.Operand, sqlPack);
if (expression.NodeType == ExpressionType.Not)
{
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);
}
}
//=、<>
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);
}
}
//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);
}
}
}
return(sqlPack);
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(LambdaExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Where(expression.Body, sqlPack);
return(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>
/// 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;
SqlBuilderProvider.Where(expression.Left, sqlPack);
var signIndex = sqlPack.Length;
//嵌套条件
var flag = false;
if (expression.Right is BinaryExpression binaryExpression && (binaryExpression.Right as BinaryExpression) != null)
{
flag = true;
sqlPack += "(";
}
SqlBuilderProvider.Where(expression.Right, sqlPack);
if (flag)
{
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>
/// 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>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(InvocationExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Where(expression.Expression, sqlPack);
return(sqlPack);
}