/// <summary>
/// 创建 SQL 命令
/// </summary>
/// <param name="dbQuery">查询 语句</param>
/// <param name="indent">缩进</param>
/// <param name="isOuter">是否最外层，内层查询不需要结束符(;)</param>
/// <param name="token">解析上下文参数</param>
/// <returns></returns>
public RawCommand Resolve <T>(IDbQueryable <T> dbQuery, int indent, bool isOuter, ResolveToken token)
{
// 参数化设置
if (token == null)
{
token = new ResolveToken();
}
if (!dbQuery.HasSetParameterized)
{
dbQuery.Parameterized = true;
}
if (!token.HasSetParameterized)
{
token.Parameterized = dbQuery.Parameterized;
}
if (token.Parameterized && token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
if (token.DbContext == null)
{
token.DbContext = dbQuery.DbContext;
}
// 解析查询语义
IDbQueryableInfo result = DbQueryParser.Parse <T>(dbQuery);
// 查询
var result_Query = result as IDbQueryableInfo_Select;
if (result_Query != null)
{
return(this.ResolveSelectCommand(result_Query, indent, isOuter, token));
}
// 新增
var result_Insert = result as IDbQueryableInfo_Insert;
if (result_Insert != null)
{
return(this.ResolveInsertCommand <T>(result_Insert, token));
}
// 更新
var result_Update = result as IDbQueryableInfo_Update;
if (result_Update != null)
{
return(this.ResolveUpdateCommand <T>(result_Update, token));
}
// 删除
var result_Delete = result as IDbQueryableInfo_Delete;
if (result_Delete != null)
{
return(this.ResolveDeleteCommand <T>(result_Delete, token));
}
throw new NotImplementedException();
}
/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected virtual IDbDataParameter AddParameter(object value, ResolveToken token,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
string name = string.Format("{0}p{1}", _provider.ParameterPrefix, token.Parameters.Count);
var parameter = _provider.DbProviderFactory.CreateParameter(name, value, null, size, precision, scale, direction);
token.Parameters.Add(parameter);
return(parameter);
}
/// <summary>
/// 实例化 <see cref="SqlBuilder"/> 类的新实例
/// </summary>
/// <param name="provider">查询语义提供者</param>
/// <param name="token">解析上下文参数</param>
public SqlBuilder(IDbQueryProvider provider, ResolveToken token)
{
_provider = provider;
_token = token;
_innerBuilder = new StringBuilder(128);
_escCharLeft = _provider.QuotePrefix;
_escCharRight = _provider.QuoteSuffix;
_escCharQuote = _provider.SingleQuoteChar;
}
/// <summary>
/// 实例化 <see cref="MappingCommand"/> 类的新实例
/// </summary>
/// <param name="provider">数据查询提供者</param>
/// <param name="aliases">别名</param>
/// <param name="token">解析上下文参数</param>
public MappingCommand(IDbQueryProvider provider, TableAliasCache aliases, ResolveToken token)
: base(string.Empty, token != null ? token.Parameters : null, System.Data.CommandType.Text)
{
_provider = provider;
_aliases = aliases;
_navMembers = new Dictionary <string, MemberExpression>();
_joinFragment = provider.CreateSqlBuilder(token);
_whereFragment = provider.CreateSqlBuilder(token);
}
/// <summary>
/// 创建 SQL 命令
/// </summary>
/// <param name="dbQuery">查询 语句</param>
/// <param name="indent">缩进</param>
/// <param name="isOuter">是否最外层，内层查询不需要结束符(;)</param>
/// <param name="token">解析上下文参数</param>
/// <returns></returns>
public Command Resolve <T>(IDbQueryable <T> dbQuery, int indent, bool isOuter, ResolveToken token)
{
// 设置该查询是否需要参数化
if (token == null)
{
token = new ResolveToken();
}
if (!((DbQueryable)dbQuery).HasSetParameterized)
{
dbQuery.Parameterized = true;
}
if (dbQuery.Parameterized && token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
// 调试模式
if (token != null && !token.HasSetIsDebug)
{
token.IsDebug = dbQuery.DbContext.IsDebug;
}
// 解析查询语义
IDbQueryableInfo <T> result = DbQueryParser.Parse(dbQuery);
// 查询
DbQueryableInfo_Select <T> result_Query = result as DbQueryableInfo_Select <T>;
if (result_Query != null)
{
return(this.ResolveSelectCommand <T>(result_Query, indent, isOuter, token));
}
// 新增
DbQueryableInfo_Insert <T> result_Insert = result as DbQueryableInfo_Insert <T>;
if (result_Insert != null)
{
return(this.ResolveInsertCommand <T>(result_Insert, token));
}
// 更新
DbQueryableInfo_Update <T> result_Update = result as DbQueryableInfo_Update <T>;
if (result_Update != null)
{
return(this.ResolveUpdateCommand <T>(result_Update, token));
}
// 删除
DbQueryableInfo_Delete <T> result_Delete = result as DbQueryableInfo_Delete <T>;
if (result_Delete != null)
{
return(this.ResolveDeleteCommand <T>(result_Delete, token));
}
throw new NotImplementedException();
}
/// <summary>
/// 创建 SQL 命令
/// </summary>
/// <param name="dbQueryable">查询 语句</param>
/// <param name="indent">缩进</param>
/// <param name="isOuter">是否最外层，内层查询不需要结束符(;)</param>
/// <param name="token">解析上下文参数</param>
/// <returns></returns>
public Command Resolve <T>(IDbQueryable <T> dbQueryable, int indent, bool isOuter, ResolveToken token)
{
// 设置该查询是否需要参数化
if (token == null)
{
token = new ResolveToken();
}
if (!((DbQueryable)dbQueryable).HasSetParameterized)
{
dbQueryable.Parameterized = true;
}
if (dbQueryable.Parameterized && token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
// 调试模式
if (token != null && !token.HasSetIsDebug)
{
token.IsDebug = dbQueryable.DbContext.IsDebug;
}
// 解析查询语义
IDbQueryableInfo <T> dbQueryInfo = DbQueryParser.Parse(dbQueryable);
DbQueryableInfo_Select <T> sQueryInfo = dbQueryInfo as DbQueryableInfo_Select <T>;
if (sQueryInfo != null)
{
return(this.ParseSelectCommand <T>(sQueryInfo, indent, isOuter, token));
}
DbQueryableInfo_Insert <T> nQueryInfo = dbQueryInfo as DbQueryableInfo_Insert <T>;
if (nQueryInfo != null)
{
return(this.ParseInsertCommand <T>(nQueryInfo, token));
}
DbQueryableInfo_Update <T> uQueryInfo = dbQueryInfo as DbQueryableInfo_Update <T>;
if (uQueryInfo != null)
{
return(this.ParseUpdateCommand <T>(uQueryInfo, token));
}
DbQueryableInfo_Delete <T> dQueryInfo = dbQueryInfo as DbQueryableInfo_Delete <T>;
if (dQueryInfo != null)
{
return(this.ParseDeleteCommand <T>(dQueryInfo, token));
}
throw new NotImplementedException();
}
// 获取 JOIN 子句关联表的的别名
protected TableAliasCache PrepareAlias <T>(DbQueryableInfo_Select <T> query, ResolveToken token)
{
TableAliasCache aliases = new TableAliasCache((query.Joins != null ? query.Joins.Count : 0) + 1, token != null ? token.TableAliasName : null);
foreach (DbExpression exp in query.Joins)
{
// [INNER/LEFT JOIN]
if (exp.DbExpressionType == DbExpressionType.GroupJoin || exp.DbExpressionType == DbExpressionType.Join || exp.DbExpressionType == DbExpressionType.GroupRightJoin)
{
this.PrepareLfInJoinAlias(exp, aliases);
}
else if (exp.DbExpressionType == DbExpressionType.SelectMany)
{
this.PrepareCrossJoinAlias(exp, aliases);
}
}
return(aliases);
}
/// <summary>
/// 生成关联子句所表示的别名列表
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected TableAlias PrepareTableAlias(IDbQueryableInfo_Select dbQuery, ResolveToken token)
{
var aliases = new TableAlias((dbQuery.Joins != null ? dbQuery.Joins.Count : 0) + 1, token != null ? token.AliasPrefix : null);
foreach (DbExpression exp in dbQuery.Joins)
{
// [INNER/LEFT JOIN]
if (exp.DbExpressionType == DbExpressionType.GroupJoin || exp.DbExpressionType == DbExpressionType.Join || exp.DbExpressionType == DbExpressionType.GroupRightJoin)
{
this.PrepareJoinAlias(exp, aliases);
}
else if (exp.DbExpressionType == DbExpressionType.SelectMany)
{
this.PrepareCrossAlias(exp, aliases);
}
}
return(aliases);
}
// 生成集合对应的 SQL 片断
private string GetSqlValue(IEnumerable value, ResolveToken token, object dbType, int?size, int?precision, int?scale, ParameterDirection?direction = null)
{
if (value == null)
{
return("NULL");
}
var iterator = value.GetEnumerator();
List <string> sqlValues = new List <string>();
while (iterator.MoveNext())
{
string text = this.GetSqlValue(iterator.Current, token, dbType, size, precision, scale, direction);
sqlValues.Add(text);
}
// =>a,b,c
string sql = string.Join(",", sqlValues);
return(sql);
}
/// <summary>
/// 访问 IDbQueryable.Contains 方法
/// </summary>
protected virtual Expression VisitQueryableContains(MethodCallExpression m)
{
ResolveToken token = _builder.Token;
IDbQueryable subQuery = m.Arguments[0].Evaluate().Value as IDbQueryable;
// 设置子查询的参数化
subQuery.Parameterized = _builder.Parameterized;
var cmd = subQuery.Resolve(_builder.Indent + 1, false, token != null ? new ResolveToken
{
Parameters = token.Parameters,
TableAliasName = "s",
IsDebug = token.IsDebug
} : null) as MappingCommand;
if (this.NotMethods.Contains(m))
{
_builder.Append("NOT ");
}
_builder.Append("EXISTS(");
_builder.Append(cmd.CommandText);
if (cmd.WhereFragment.Length > 0)
{
_builder.Append(" AND ");
}
else
{
_builder.Append("WHERE ");
}
_builder.Append(cmd.PickColumnText);
_builder.Append(" = ");
_visitor.Visit(m.Arguments[1]);
_builder.Append(")");
return(m);
}
// 创建 UPDATE 命令 protected abstract Command ParseUpdateCommand <T>(DbQueryableInfo_Update <T> uQuery, ResolveToken token);
// 创建 DELETE 命令 protected abstract Command ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQuery, ResolveToken token);
// 创建 INSRT 命令 protected abstract Command ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQuery, ResolveToken token);
// 创建 SELECT 命令 protected abstract Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, ResolveToken token);
/// <summary> /// 解析 SELECT 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="indent">缩进</param> /// <param name="isOuter">指示是最外层查询</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected abstract RawCommand ResolveSelectCommand(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token);
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
public string GetSqlValue(object value, ResolveToken token,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
// https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017
// 1.Date 3个字节,DateTime 8个字节 DateTime2 <=4 6个字节 其它8个字节，外加1个字节存储精度
// 2.如果转换时没有指定数据类型的长度，则SQServer自动提供长度为30
// 3.隐式转换优先级 https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017
// 4.参数化查询只需要重写 CreateParameter
if (value == null)
{
return("NULL");
}
Type type = value.GetType();
if (token != null && token.Parameters != null)
{
// 参数化 ##########
if (!(value is string) && !(value is byte[]) && (value is IEnumerable))
{
return(this.GetSqlValue(value as IEnumerable, token, dbType, size, precision, scale));
}
else
{
return(this.AddParameter(value, token, dbType, size, precision, scale, direction).ParameterName);
}
}
else
{
// 非参数化 ##########
// 枚举类型
if (type.IsEnum)
{
return(this.GetSqlValueByEnum(value));
}
// Guid 类型
else if (value is Guid)
{
return(this.GetSqlValueByGuid(value));
}
// 数据类型
else if (TypeUtils.IsNumberType(type))
{
return(this.GetSqlValueByNumber(value));
}
// byte[] 类型
else if (value is byte[])
{
return(this.GetSqlValueByBytes(value));
}
// 布尔类型
else if (value is bool)
{
return(this.GetSqlValueByBoolean(value, dbType));
}
// 字符类型
else if (value is char || value is string)
{
return(this.GetSqlValueByString(value, dbType, size));
}
// 时间类型
else if (value is TimeSpan)
{
return(this.GetSqlValueByTime(value, dbType, scale));
}
// 日期类型
else if (value is DateTime)
{
return(this.GetSqlValueByDateTime(value, dbType, scale));
}
// 日期类型（带时区）
else if (value is DateTimeOffset)
{
return(this.GetSqlValueByDateTimeOffset(value, dbType, scale));
}
// 集合类型
else if (value is IEnumerable)
{
return(this.GetSqlValue(value as IEnumerable, token, dbType, size, precision, scale));
}
else
{
throw new NotSupportedException(string.Format("type {0} not supported serialize to string", type.FullName));
}
}
}
/// <summary> /// 解析成 SQL 命令 /// </summary> /// <param name="indent">缩进</param> /// <param name="isOuter">是否最外层，内层查询不需要结束符(;)</param> /// <param name="token">解析上下文参数</param> /// <returns></returns> public abstract RawCommand Resolve(int indent, bool isOuter, ResolveToken token);
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="token">解析上下文</param>
/// <param name="m">value 对应的成员</param>
/// <returns></returns>
public string GetSqlValue(object value, ResolveToken token, MemberVisitedStack.VisitedMember m)
{
ColumnAttribute column = m != null?TypeUtils.GetColumnAttribute(m.Member, m.ReflectedType) : null;
return(this.GetSqlValue(value, token, column));
}
/// <summary> /// 创建 UPDATE 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected abstract RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token);
/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected abstract RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token);
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <returns></returns>
public string GetSqlValue(object value, ResolveToken token)
{
return(this.GetSqlValue(value, token, (ColumnAttribute)null));
}
/// <summary>
/// 解析成 SQL 命令
/// </summary>
/// <param name="indent">缩进</param>
/// <param name="isOuter">是否最外层，内层查询不需要结束符(;)</param>
/// <param name="token">解析上下文参数</param>
/// <returns></returns>
public override Command Resolve(int indent, bool isOuter, ResolveToken token)
{
var cmd = this.Provider.Resolve(this, indent, isOuter, token);
return(cmd);
}
/// <summary>
/// 实例化 <see cref="NpgMappingDbCommand" /> 的新实例
/// </summary>
/// <param name="provider">数据查询提供者</param>
/// <param name="aliases">别名</param>
/// <param name="dbExpressionType">表达式类型</param>
/// <param name="token">解析上下文参数</param>
public NpgMappingDbCommand(IDbQueryProvider provider, TableAlias aliases, DbExpressionType dbExpressionType, ResolveToken token)
: base(provider, aliases, token)
{
_provider = provider;
_aliases = aliases;
_onPhrase = _provider.CreateSqlBuilder(token);
_dbExpressionType = dbExpressionType;
if (_dbExpressionType == DbExpressionType.Delete)
{
_keywordName = "USING ";
}
else if (_dbExpressionType == DbExpressionType.Update)
{
_keywordName = "FROM ";
}
_pad = "".PadLeft(_keywordName.Length, ' ');
}
/// <summary>
/// 实例化 <see cref="SqlServerMappingCommand"/> 类的新实例
/// </summary>
/// <param name="context">数据查询提供者</param>
/// <param name="aliases">别名</param>
/// <param name="token">解析上下文参数</param>
public SqlServerMappingCommand(SqlClient.SqlServerDbContext context, TableAlias aliases, ResolveToken token)
: base(context.Provider, aliases, token)
{
_aliases = aliases;
_context = context;
_provider = context.Provider as SqlClient.SqlServerDbQueryProvider;
}
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <param name="node">成员访问表达式</param>
/// <returns></returns>
public string GetSqlValue(object value, ResolveToken token, MemberExpression node = null)
{
ColumnAttribute column = this.GetColumnAttribute(node);
return(this.GetSqlValue(value, token, column));
}
/// <summary>
/// 解析 SQL 命令
/// <para>
/// 返回的已经解析语义中执行批次用 null 分开
/// </para>
/// </summary>
/// <param name="dbQueryables">查询语句</param>
/// <returns></returns>
public virtual List <Command> Resolve(List <object> dbQueryables)
{
List <Command> sqlList = new List <Command>();
ResolveToken token = null;
foreach (var obj in dbQueryables)
{
if (obj == null)
{
continue;
}
if (obj is IDbQueryable)
{
IDbQueryable dbQueryable = (IDbQueryable)obj;
dbQueryable.Parameterized = true;
if (token == null)
{
token = new ResolveToken();
}
if (token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
var cmd2 = dbQueryable.Resolve(0, true, token);
sqlList.Add(cmd2);
if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
token = new ResolveToken();
token.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is RawSql)
{
RawSql rawSql = (RawSql)obj;
if (token == null)
{
token = new ResolveToken();
}
if (token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.DbValue.GetSqlValue(x, token)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd2 = new Command(sql, token.Parameters, CommandType.Text);
sqlList.Add(cmd2);
if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
token = new ResolveToken();
token.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is string)
{
string sql = obj.ToString();
sqlList.Add(new Command(sql));
}
else
{
// 解析批量插入操作
List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
if (bulkList != null && bulkList.Count > 0)
{
this.ResolveBulk(sqlList, bulkList);
}
}
}
return(sqlList);
}
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <param name="member">成员</param>
/// <param name="objType">成员所在类型</param>
/// <returns></returns>
public string GetSqlValue(object value, ResolveToken token, MemberInfo member, Type objType)
{
ColumnAttribute column = this.GetColumnAttribute(member, objType);
return(this.GetSqlValue(value, token, column));
}
/// <summary> /// 创建 SQL 构造器 /// </summary> /// <param name="parameter">参数列表，NULL 或者 Parameters=NULL 时表示不使用参数化</param> /// <returns></returns> public abstract ISqlBuilder CreateSqlBuilder(ResolveToken parameter);