public string ToSelectSql(Expression exp)
{
if (exp == null)
{
return(string.Empty);
}
var sqlBuilder = new StringBuilder();
if (exp is LambdaExpression lambdaExpression && lambdaExpression.Body is NewExpression newExpression && newExpression.Members.Any())
{
for (var i = 0; i < newExpression.Members.Count; i++)
{
var col = GetColumn(newExpression.Members[i]);
if (col != null)
{
if (col.Name.Equals(col.PropertyInfo.Name))
{
sqlBuilder.Append(_sqlAdapter.AppendQuote(col.Name));
}
else
{
sqlBuilder.AppendFormat("{0} AS '{1}'", _sqlAdapter.AppendQuote(col.Name), col.PropertyInfo.Name);
}
if (i != newExpression.Members.Count - 1)
{
sqlBuilder.Append(",");
}
}
}
}
return(sqlBuilder.ToString());
}
public string GetColumnName(MemberExpression exp)
{
var descriptor = JoinDescriptors.First(m => m.EntityDescriptor.EntityType == exp.Expression.Type);
var col = descriptor.EntityDescriptor.Columns.FirstOrDefault(m => m.PropertyInfo.Name.Equals(exp.Member.Name));
Check.NotNull(col, nameof(col), $"({exp.Member.Name})列不存在");
return($"{_sqlAdapter.AppendQuote(descriptor.Alias)}.{_sqlAdapter.AppendQuote(col.Name)}");
}
/// <summary>
/// 获取列名
/// </summary>
/// <param name="name"></param>
/// <param name="descriptor"></param>
/// <returns></returns>
public string GetColumnName(string name, QueryJoinDescriptor descriptor)
{
var col = GetColumnDescriptor(name, descriptor);
//只有一个实体的时候，不需要别名
if (JoinDescriptors.Count == 1)
{
// ReSharper disable once PossibleNullReferenceException
return(_sqlAdapter.AppendQuote(col.Name));
}
// ReSharper disable once PossibleNullReferenceException
return($"{_sqlAdapter.AppendQuote(descriptor.Alias)}.{_sqlAdapter.AppendQuote(col.Name)}");
}
/// <summary>
/// 获取列名
/// </summary>
/// <param name="name"></param>
/// <param name="descriptor"></param>
/// <returns></returns>
public string GetColumnName(string name, QueryJoinDescriptor descriptor)
{
var col = descriptor.EntityDescriptor.Columns.FirstOrDefault(m => m.PropertyInfo.Name.Equals(name));
Check.NotNull(col, nameof(col), $"({name})列不存在");
//只有一个实体的时候，不需要别名
if (JoinDescriptors.Count == 1)
{
// ReSharper disable once PossibleNullReferenceException
return(_sqlAdapter.AppendQuote(col.Name));
}
// ReSharper disable once PossibleNullReferenceException
return($"{_sqlAdapter.AppendQuote(descriptor.Alias)}.{_sqlAdapter.AppendQuote(col.Name)}");
}
private void MemberInitResolve(Expression exp)
{
if (exp == null || !(exp is MemberInitExpression initExp) || !initExp.Bindings.Any())
{
return;
}
for (var i = 0; i < initExp.Bindings.Count; i++)
{
if (initExp.Bindings[i] is MemberAssignment assignment)
{
var descriptor = _queryBody.JoinDescriptors.First(m => m.EntityDescriptor.EntityType == initExp.Type);
var col = descriptor.EntityDescriptor.Columns.FirstOrDefault(m => m.PropertyInfo.Name.Equals(assignment.Member.Name));
if (col != null)
{
if (_queryBody.JoinDescriptors.Count > 1)
{
_sqlBuilder.Append($"{_sqlAdapter.AppendQuote(descriptor.Alias)}.{_sqlAdapter.AppendQuote(col.Name)}");
}
else
{
_sqlBuilder.Append(_sqlAdapter.AppendQuote(col.Name));
}
_sqlBuilder.Append("=");
Resolve(assignment.Expression);
if (i < initExp.Bindings.Count - 1)
{
_sqlBuilder.Append(",");
}
}
}
}
}
public Task <TResult> Max <TResult>(Expression <Func <TEntity, TResult> > expression)
{
Check.NotNull(expression, nameof(expression), "未指定求最大值的列");
var memberExpression = expression.Body as MemberExpression;
if (memberExpression == null)
{
throw new ArgumentException("无法解析表达式", nameof(expression));
}
var col = _descriptor.Columns.FirstOrDefault(c => c.PropertyInfo.Name.Equals(memberExpression.Member.Name));
if (col == null)
{
throw new ArgumentException("指定的求最大值的列不存在", nameof(TResult));
}
var sql = $"SELECT MAX({_sqlAdapter.AppendQuote(col.Name)}) FROM {_sqlAdapter.AppendQuote(_descriptor.TableName)} {WhereSql};";
return(_dbSet.ExecuteScalarAsync <TResult>(sql, null, _transaction));
}
public string UpdateSqlBuild(out IQueryParameters parameters)
{
var tableName = _queryBody.JoinDescriptors.First().TableName;
Check.NotNull(tableName, nameof(tableName), "未指定更新表");
var sqlBuilder = new StringBuilder();
parameters = new QueryParameters();
var updateSql = ResolveUpdate(parameters);
Check.NotNull(updateSql, nameof(updateSql), "生成更新sql异常");
sqlBuilder.AppendFormat("UPDATE {0} SET ", _sqlAdapter.AppendQuote(tableName));
sqlBuilder.Append(updateSql);
SetModifiedBy(sqlBuilder, parameters);
var whereSql = ResolveWhere(parameters);
Check.NotNull(whereSql, nameof(whereSql), "生成条件sql异常");
sqlBuilder.AppendFormat(" WHERE {0}", whereSql);
var sql = sqlBuilder.ToString();
_logger?.LogDebug("Update:" + sql);
return(sql);
}
public string SoftDeleteSqlBuild(out IQueryParameters parameters)
{
var entityDescriptor = _queryBody.JoinDescriptors.First().EntityDescriptor;
if (!entityDescriptor.SoftDelete)
{
throw new Exception("非软删除实体无法调用该方法");
}
var tableName = _queryBody.JoinDescriptors.First().TableName;
Check.NotNull(tableName, nameof(tableName), "未指定更新表");
var deletedColumnName = entityDescriptor.Columns.First(m => m.PropertyInfo.Name.Equals("Deleted")).Name;
var deletedTimeColumnName = entityDescriptor.Columns.First(m => m.PropertyInfo.Name.Equals("DeletedTime")).Name;
var deletedByTimeColumnName = entityDescriptor.Columns.First(m => m.PropertyInfo.Name.Equals("DeletedBy")).Name;
parameters = new QueryParameters();
var sqlBuilder = new StringBuilder($"UPDATE {GetTableName(tableName)} SET ");
sqlBuilder.AppendFormat("{0}={1},", _sqlAdapter.AppendQuote(deletedColumnName), _sqlAdapter.SqlDialect == SqlDialect.PostgreSQL ? "TRUE" : "1");
sqlBuilder.AppendFormat("{0}={1},", _sqlAdapter.AppendQuote(deletedTimeColumnName), _sqlAdapter.AppendParameter("P1"));
parameters.Add(DateTime.Now);
sqlBuilder.AppendFormat("{0}={1} ", _sqlAdapter.AppendQuote(deletedByTimeColumnName), _sqlAdapter.AppendParameter("P2"));
var deleteBy = Guid.Empty;
if (_dbContext.LoginInfo != null)
{
deleteBy = _dbContext.LoginInfo.AccountId;
}
parameters.Add(deleteBy);
var whereSql = ResolveWhere(parameters);
Check.NotNull(whereSql, nameof(whereSql), "生成条件sql异常");
sqlBuilder.AppendFormat(" WHERE {0}", whereSql);
var sql = sqlBuilder.ToString();
_logger?.LogDebug("SoftDelete:" + sql);
return(sql);
}
private string GetTableName(string tableName)
{
//PostgreSQL数据库需要带上模式(schema)
return(_adapter.SqlDialect == SqlDialect.PostgreSQL ? $"{_adapter.Database}{_adapter.AppendQuote(tableName)}" : _adapter.AppendQuote(tableName));
}
/// <summary>
/// 设置插入语句
/// </summary>
private void SetInsertSql()
{
var sb = new StringBuilder();
sb.Append("INSERT INTO ");
_sqlAdapter.AppendQuote(sb, _descriptor.TableName);
sb.Append("(");
var valuesSql = new StringBuilder(") VALUES(");
foreach (var col in _descriptor.Columns)
{
//排除自增主键
if (col.IsPrimaryKey && (_descriptor.PrimaryKeyType == PrimaryKeyType.Int || _descriptor.PrimaryKeyType == PrimaryKeyType.Long))
{
continue;
}
_sqlAdapter.AppendQuote(sb, col.Name);
sb.Append(",");
_sqlAdapter.AppendParameter(valuesSql, col.PropertyInfo.Name);
valuesSql.Append(",");
}
//删除最后一个","
sb.Remove(sb.Length - 1, 1);
//删除最后一个","
valuesSql.Remove(valuesSql.Length - 1, 1);
valuesSql.Append(");");
sb.Append(valuesSql);
Insert = sb.ToString();
}
public string QuerySqlBuild(out QueryParameters parameters)
{
string sql;
parameters = new QueryParameters();
//分页查询
if (_queryBody.Take > 0)
{
var select = ResolveSelect();
var from = ResolveFrom(parameters);
var where = ResolveWhere(parameters);
var sort = ResolveOrder();
#region ==SqlServer分页需要指定排序==
//SqlServer分页需要指定排序，此处判断是否有主键，有主键默认按照主键排序
if (_sqlAdapter.SqlDialect == SqlDialect.SqlServer && sort.IsNull())
{
var first = _queryBody.JoinDescriptors.First();
if (first.EntityDescriptor.PrimaryKey.IsNo())
{
throw new ArgumentNullException("OrderBy", "SqlServer数据库没有主键的表需要指定排序字段才可以分页查询");
}
if (_queryBody.JoinDescriptors.Count > 1)
{
sort = $"{_sqlAdapter.AppendQuote(first.Alias)}.{_sqlAdapter.AppendQuote(first.EntityDescriptor.PrimaryKey.Name)}";
}
else
{
sort = first.EntityDescriptor.PrimaryKey.Name;
}
}
#endregion
sql = _sqlAdapter.GeneratePagingSql(select, from, where, sort, _queryBody.Skip, _queryBody.Take);
}
else
{
var sqlBuilder = new StringBuilder("SELECT ");
ResolveSelect(sqlBuilder);
sqlBuilder.Append(" FROM ");
ResolveFrom(sqlBuilder, parameters);
ResolveWhere(sqlBuilder, parameters);
ResolveOrder(sqlBuilder);
sql = sqlBuilder.ToString();
}
_logger?.LogDebug("Query:{0}", sql);
return(sql);
}
/// <summary>
/// 获取插入语句
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public string Insert(string tableName)
{
if (tableName.NotNull())
{
return(string.Format(_insert, _adapter.AppendQuote(tableName)));
}
if (_defaultInsert.IsNull())
{
_defaultInsert = string.Format(_insert, _adapter.AppendQuote(_descriptor.TableName));
}
return(_defaultInsert);
}