/// <summary>
/// 新增
/// </summary>
/// <param name="returnLastIdentity"></param>
/// <returns></returns>
public SqlExpressionCore <T> Insert(bool returnLastIdentity)
{
var addFields = new List <string>();
var atFields = new List <string>();
var identityPi = typeof(T).GetIdentityField();
var pis = typeof(T).GetProperties();
foreach (var pi in pis)
{
if (identityPi?.Name == pi.Name)
{
continue;
}
addFields.Add($"{pi.Name.ParamSql(sqlGenerate.DatabaseType)}");
atFields.Add($"@{pi.Name}");
}
sqlGenerate.Clear();
sqlGenerate += $"insert into {sqlGenerate.TableName}({string.Join(", ", addFields)}) values({string.Join(", ", atFields)});";
if (identityPi != null && returnLastIdentity)
{
sqlGenerate += sqlGenerate.DatabaseType.SelectLastIdentity();
}
return(this);
}
protected override SqlGenerate Where(LambdaExpression expression, SqlGenerate sqlGenerate)
{
if (expression.Body.NodeType == ExpressionType.MemberAccess)
{
var memberExpression = expression.Body as MemberExpression;
if (memberExpression.Expression == null)
{
return(sqlGenerate);
}
//添加属性
SqlExpressionProvider.Where(memberExpression, sqlGenerate);
if (memberExpression.Expression.Type.IsNullableType())
{
return(sqlGenerate);
}
sqlGenerate += " = 1";
return(sqlGenerate);
}
SqlExpressionProvider.Where(expression.Body, sqlGenerate);
return(sqlGenerate);
}
protected override SqlGenerate Where(MemberExpression expression, SqlGenerate sqlGenerate)
{
if (expression.Expression != null)
{
if (expression.Member.DeclaringType.IsNullableType())
{
if (expression.Member.Name == "Value") //Can't use C# 6 yet: nameof(Nullable<bool>.Value)
{
SqlExpressionProvider.Where(expression.Expression, sqlGenerate);
return(sqlGenerate);
}
if (expression.Member.Name == "HasValue")
{
var doesNotEqualNull = Expression.MakeBinary(ExpressionType.NotEqual, expression.Expression, Expression.Constant(null));
SqlExpressionProvider.Where(doesNotEqualNull, sqlGenerate);
return(sqlGenerate);
}
throw new ArgumentException($"Expression '{expression}' accesses unsupported property '{expression.Member}' of Nullable<T>");
}
if (expression.IsParameterOrConvertAccess())
{
sqlGenerate += $" {expression.Member.Name.ParamSql(sqlGenerate)}";
return(sqlGenerate);
}
}
var val = SqlExpressionCompiler.Evaluate(expression);
sqlGenerate.AddDbParameter(val);
return(sqlGenerate);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="fields"></param>
/// <returns></returns>
public SqlExpressionCore <T> Update(IEnumerable <string> fields = null)
{
var setFields = new List <string>();
var whereFields = new List <string>();
var pis = typeof(T).GetProperties();
foreach (var pi in pis)
{
var obs = pi.GetCustomAttributes(typeof(KeyAttribute), false);
if (obs?.Count() > 0)
{
whereFields.Add($"{pi.Name.ParamSql(sqlGenerate.DatabaseType)} = @{pi.Name}");
}
else
{
if ((fields?.Count() ?? 0) <= 0 || fields.Contains(pi.Name))
{
setFields.Add($"{pi.Name.ParamSql(sqlGenerate.DatabaseType)} = @{pi.Name}");
}
}
}
if (whereFields.Count <= 0)
{
throw new Exception($"实体未设置主键Key属性");
}
if (setFields.Count <= 0)
{
throw new Exception($"实体未标记任何更新字段");
}
sqlGenerate.Clear();
sqlGenerate += $"update {sqlGenerate.TableName} set {string.Join(", ", setFields)} where {string.Join(", ", whereFields)}";
return(this);
}
/// <summary>
/// Limit
/// </summary>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <returns></returns>
public SqlExpressionCore <T> Limit(int page, int rows)
{
var skip = (page - 1) * rows;
switch (sqlGenerate.DatabaseType)
{
case DatabaseType.SqlServer:
sqlGenerate.Sql = new StringBuilder($"SELECT it.* FROM ({sqlGenerate.Sql}) it where it.RowNumber > {skip} AND it.RowNumber <= {page * rows}");
break;
case DatabaseType.GteSqlServer2012:
sqlGenerate += $" OFFSET {skip} ROW FETCH NEXT {rows} rows only";
break;
case DatabaseType.MySql:
sqlGenerate += $" limit {skip}, {rows}";
break;
case DatabaseType.SQLite:
sqlGenerate += $" limit {rows} offset {skip}";
break;
default:
break;
}
return(this);
}
/// <summary>
/// Offset
/// </summary>
/// <param name="offset"></param>
/// <param name="size"></param>
/// <returns></returns>
public SqlExpressionCore <T> Offset(int offset, int size)
{
switch (sqlGenerate.DatabaseType)
{
case DatabaseType.SqlServer:
sqlGenerate.Sql = new StringBuilder($"SELECT it.* FROM ({sqlGenerate.Sql}) it where it.RowNumber > {offset} AND it.RowNumber <= {offset + size}");
break;
case DatabaseType.GteSqlServer2012:
sqlGenerate += $" OFFSET {offset} ROW FETCH NEXT {size} rows only";
break;
case DatabaseType.MySql:
sqlGenerate += $" limit {offset}, {size}";
break;
case DatabaseType.SQLite:
sqlGenerate += $" limit {size} offset {offset}";
break;
default:
break;
}
return(this);
}
protected override SqlGenerate Where(BinaryExpression expression, SqlGenerate sqlGenerate)
{
int leftBracketIndex = -1, rightBracketIndex = -1, signIndex = -1, sqlLength = -1;
leftBracketIndex = sqlGenerate.Length;
#region 内部内容
if (expression.NodeType == ExpressionType.AndAlso || expression.NodeType == ExpressionType.OrElse)
{
if (expression.Left.IsBooleanComparison())
{
SqlExpressionProvider.Where(expression.Left, sqlGenerate);
sqlGenerate += $" = 1";
}
else
{
SqlExpressionProvider.Where(expression.Left, sqlGenerate);
}
signIndex = sqlGenerate.Length;
if (expression.Right.IsBooleanComparison())
{
SqlExpressionProvider.Where(expression.Right, sqlGenerate);
sqlGenerate += $" = 1";
}
else
{
SqlExpressionProvider.Where(expression.Right, sqlGenerate);
}
sqlLength = sqlGenerate.Length;
}
else
{
SqlExpressionProvider.Where(expression.Left, sqlGenerate);
signIndex = sqlGenerate.Length;
SqlExpressionProvider.Where(expression.Right, sqlGenerate);
sqlLength = sqlGenerate.Length;
}
if (sqlLength - signIndex == 5 && sqlGenerate.ToString().EndsWith("null"))
{
OperatorParser(expression.NodeType, signIndex, sqlGenerate, true);
}
else
{
OperatorParser(expression.NodeType, signIndex, sqlGenerate);
}
#endregion
if (expression.NodeType == ExpressionType.OrElse || expression.NodeType == ExpressionType.AndAlso)
{
sqlGenerate.Sql.Insert(leftBracketIndex, " ( ");
rightBracketIndex = sqlGenerate.Length;
sqlGenerate.Sql.Insert(rightBracketIndex, " ) ");
}
return(sqlGenerate);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public SqlExpressionCore <T> Update(Expression <Func <object> > expression = null)
{
sqlGenerate.Clear();
sqlGenerate += $"update {sqlGenerate.TableName} set ";
SqlExpressionProvider.Update(expression, sqlGenerate);
return(this);
}
protected override SqlGenerate Select(NewExpression expression, SqlGenerate sqlGenerate)
{
foreach (Expression item in expression.Arguments)
{
SqlExpressionProvider.Select(item, sqlGenerate);
}
return(sqlGenerate);
}
protected override SqlGenerate In(MemberExpression expression, SqlGenerate sqlGenerate)
{
var result = SqlExpressionCompiler.Evaluate(expression);
var inArgs = (result as IEnumerable).Flatten();
sqlGenerate.AddDbParameter(inArgs);
return(sqlGenerate);
}
private static void StartsWith(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Where(expression.Object, sqlGenerate);
SqlExpressionProvider.Where(expression.Arguments[0], sqlGenerate);
sqlGenerate += " like ";
var val = SqlExpressionCompiler.Evaluate(expression.Arguments[0]);
sqlGenerate.AddDbParameter($"{val}%");
}
/// <summary>
/// Where条件
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public SqlExpressionCore <T> Where(Expression <Func <T, bool> > expression)
{
if (expression == null)
{
return(this);
}
sqlGenerate += $"{Environment.NewLine}where";
SqlExpressionProvider.Where(expression, sqlGenerate);
return(this);
}
protected override SqlGenerate Where(UnaryExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Where(expression.Operand, sqlGenerate);
switch (expression.NodeType)
{
case ExpressionType.Not:
sqlGenerate += " = 0";
break;
}
return(sqlGenerate);
}
protected override SqlGenerate In(NewArrayExpression expression, SqlGenerate sqlGenerate)
{
var list = new List <object>();
foreach (var expressionItem in expression.Expressions)
{
var obj = SqlExpressionCompiler.Evaluate(expressionItem);
list.Add(obj);
}
sqlGenerate.AddDbParameter(list);
return(sqlGenerate);
}
protected override SqlGenerate OrderBy(NewExpression expression, SqlGenerate sqlGenerate)
{
foreach (Expression item in expression.Arguments)
{
SqlExpressionProvider.OrderBy(item, sqlGenerate);
sqlGenerate += ",";
}
if (sqlGenerate[sqlGenerate.Length - 1] == ',')
{
sqlGenerate.Sql.Remove(sqlGenerate.Length - 1, 1);
}
return(sqlGenerate);
}
protected override SqlGenerate In(ListInitExpression expression, SqlGenerate sqlGenerate)
{
var list = new List <object>();
foreach (var elementInit in expression.Initializers)
{
foreach (var expre in elementInit.Arguments)
{
var obj = SqlExpressionCompiler.Evaluate(expre);
list.Add(obj);
}
}
sqlGenerate.AddDbParameter(list);
return(sqlGenerate);
}
protected override SqlGenerate Select(MemberExpression expression, SqlGenerate sqlGenerate)
{
if (IsEnumerable(expression))
{
var result = SqlExpressionCompiler.Evaluate(expression);
var fields = (result as IEnumerable).Flatten();
if (fields?.Count > 0)
{
sqlGenerate.SelectFields.AddRange(fields.Select(field => field.ToString().ParamSql(sqlGenerate)));
}
return(sqlGenerate);
}
sqlGenerate.SelectFields.Add(expression.Member.Name.ParamSql(sqlGenerate));
return(sqlGenerate);
}
private static void Format(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
var formatString = SqlExpressionCompiler.Evaluate(expression.Arguments[0]);
var formatArgs = new List <string>();
var args = expression.Arguments;
if (args.Count > 1)
{
for (int i = 1; i < args.Count; i++)
{
var val = SqlExpressionCompiler.Evaluate(expression.Arguments[i]);
formatArgs.Add(val?.ToString());
}
}
sqlGenerate += string.Format(formatString.ToString(), formatArgs.ToArray());
}
protected override SqlGenerate Update(MemberExpression expression, SqlGenerate sqlGenerate)
{
var obj = SqlExpressionCompiler.Evaluate(expression);
if (obj == null)
{
throw new ArgumentException($"Expression '{expression}' accesses unsupported property '{expression.Member}' of Nullable<T>");
}
if (obj.GetType().IsValueType)
{
throw new ArgumentException($"Expression '{expression}' accesses unsupported valuetype");
}
if (obj.GetType() == typeof(string))
{
sqlGenerate += obj.ToString();
}
else if (obj is IDictionary dictionary)
{
foreach (string key in dictionary.Keys)
{
sqlGenerate += $"{key.ParamSql(sqlGenerate)} = ";
sqlGenerate.AddDbParameter(dictionary[key]);
sqlGenerate += ",";
}
}
else
{
var pis = obj.GetType().GetProperties();
foreach (var p in pis)
{
sqlGenerate += $"{p.Name.ParamSql(sqlGenerate)} = ";
sqlGenerate.AddDbParameter(p.GetValue(obj));
sqlGenerate += ",";
}
}
if (sqlGenerate[sqlGenerate.Length - 1] == ',')
{
sqlGenerate.Sql.Remove(sqlGenerate.Length - 1, 1);
}
return(sqlGenerate);
}
protected override SqlGenerate Update(NewExpression expression, SqlGenerate sqlGenerate)
{
for (int i = 0; i < expression.Members.Count; i++)
{
var m = expression.Members[i];
sqlGenerate += $"{m.Name.ParamSql(sqlGenerate)} = ";
var val = SqlExpressionCompiler.Evaluate(expression.Arguments[i]);
sqlGenerate.AddDbParameter(val);
sqlGenerate += ",";
}
if (sqlGenerate[sqlGenerate.Length - 1] == ',')
{
sqlGenerate.Sql.Remove(sqlGenerate.Length - 1, 1);
}
return(sqlGenerate);
}
protected override SqlGenerate Where(UnaryExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Where(expression.Operand, sqlGenerate);
switch (expression.NodeType)
{
case ExpressionType.Not:
if (expression.Operand is MethodCallExpression)
{
sqlGenerate.RelaceLast("in", "not in");
}
else
{
sqlGenerate += " = 0";
}
break;
}
return(sqlGenerate);
}
protected override SqlGenerate Where(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
var key = expression.Method;
if (key.IsGenericMethod)
{
key = key.GetGenericMethodDefinition();
}
Action <MethodCallExpression, SqlGenerate> action;
if (_Methods.TryGetValue(key.Name, out action))
{
action(expression, sqlGenerate);
return(sqlGenerate);
}
throw new NotImplementedException("无法解析方法" + expression.Method);
}
private static void Contains(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
if (IsStaticArrayMethod(expression))
{
DoStaticArrayMethodCall(expression, sqlGenerate);
return;
}
if (IsEnumerableMethod(expression))
{
DoEnumerableMethodCall(expression, sqlGenerate);
return;
}
SqlExpressionProvider.Where(expression.Object, sqlGenerate);
sqlGenerate += " like ";
var val = SqlExpressionCompiler.Evaluate(expression.Arguments[0]);
sqlGenerate.AddDbParameter($"%{val}%");
}
protected override SqlGenerate Select(ConstantExpression expression, SqlGenerate sqlGenerate)
{
if (expression.Value == null)
{
sqlGenerate.SelectFields = new List <string>()
{
"*"
}
}
;
else
{
sqlGenerate.SelectFields = new List <string>()
{
expression.Value.ToString()
}
};
return(sqlGenerate);
}
}
protected override SqlGenerate Select(ListInitExpression expression, SqlGenerate sqlGenerate)
{
foreach (var elementInit in expression.Initializers)
{
foreach (var expre in elementInit.Arguments)
{
var obj = SqlExpressionCompiler.Evaluate(expre);
if (obj == null)
{
continue;
}
var fieldName = obj.ToString();
if (string.IsNullOrEmpty(fieldName))
{
continue;
}
sqlGenerate.SelectFields.Add(fieldName.ParamSql(sqlGenerate));
}
}
return(sqlGenerate);
}
/// <summary>
/// OrderBy
/// </summary>
/// <param name="orderBy"></param>
/// <returns></returns>
public SqlExpressionCore <T> OrderBy(string orderBy)
{
if (string.IsNullOrEmpty(orderBy))
{
var property = typeof(T).GetProperty <KeyAttribute>();
if (property == null)
{
property = typeof(T).GetProperties()[0];
}
orderBy = $"order by {property.Name} desc";
}
if (!orderBy.StartsWith("order by"))
{
orderBy = $"order by {orderBy}";
}
switch (sqlGenerate.DatabaseType)
{
case DatabaseType.SqlServer: // 2012版本支持 内部数据库版本706 【select DATABASEPROPERTYEX('master','version')】
sqlGenerate.Sql.Replace("select", $"select row_number() over({orderBy}) as RowNumber,");
break;
case DatabaseType.GteSqlServer2012:
sqlGenerate += $"{Environment.NewLine}{orderBy}";
break;
case DatabaseType.MySql:
sqlGenerate += $"{Environment.NewLine}{orderBy}";
break;
case DatabaseType.SQLite:
sqlGenerate += $"{Environment.NewLine}{orderBy}";
break;
}
return(this);
}
protected override SqlGenerate Select(UnaryExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Select(expression.Operand, sqlGenerate);
return(sqlGenerate);
}
private static void Equals(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Where(expression.Object, sqlGenerate);
sqlGenerate += " = ";
SqlExpressionProvider.Where(expression.Arguments[0], sqlGenerate);
}
private static new void In(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Where(expression.Arguments[0], sqlGenerate);
sqlGenerate += " in ";
SqlExpressionProvider.In(expression.Arguments[1], sqlGenerate);
}
internal static void DoStaticArrayMethodCall(MethodCallExpression expression, SqlGenerate sqlGenerate)
{
SqlExpressionProvider.Where(expression.Arguments[expression.Arguments.Count - 1], sqlGenerate);
sqlGenerate += " in ";
var memberExpr = expression.Arguments[0];
if (memberExpr.NodeType == ExpressionType.MemberAccess)
{
memberExpr = expression.Arguments[0] as MemberExpression;
}
SqlExpressionProvider.In(memberExpr, sqlGenerate);
}