public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args.Length != 2)
{
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "Right()", "", " 2 "));
}
//Right(t0.descript,case when CHARINDEX('c',t0.descript) = 0 then 0 else
//(LEN(t0.descript)- (CHARINDEX('c',t0.descript)+LEN('c'))+1) end)
builder.Append("Right(");
builder.Visit(args[0]);
builder.Append(",IIF(InStr(");
builder.Visit(args[0]);
builder.Append(",");
builder.Visit(args[1]);
builder.Append(") = 0,0,(LEN(");
builder.Visit(args[0]);
builder.Append(") - (InStr(");
builder.Visit(args[0]);
builder.Append(",");
builder.Visit(args[1]);
builder.Append(")+LEN(");
builder.Visit(args[1]);
builder.Append("))+1)))");
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
//SUBSTRING(userName,1,2)+'ddd'+SUBSTRING(userName,3,LEN(userName))
if (args.Length != 3)
{
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "Insert", "", "3"));
}
//var secondStartIndex = Expression.Constant((int)(args[1] as ConstantExpression).Value + 1);
builder.Append("(CASE WHEN ");
builder.Visit(args[1]);
builder.Append(" > LEN(");
builder.Visit(args[0]);
builder.Append(") THEN null ELSE (SUBSTRING(");
builder.Visit(args[0]);
builder.Append(",1,");
builder.Visit(Expression.Subtract(Expression.Property(args[1], "Value"), Expression.Constant(1, Types.Int32)));
builder.Append(") + ");
builder.Visit(args[2]);
builder.Append(" + SUBSTRING(");
builder.Visit(args[0]);
builder.Append(",");
builder.Visit(args[1]);
builder.Append(",LEN(");
builder.Visit(args[0]);
builder.Append("))) END)");
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args.Length != 2 && args.Length != 3)
{
throw new ArgumentException(string.Format(Res.ArgumentCountError, "CASE", "", " 2 or 3 "));
}
var whens = (args[0] as ICollection<Expression>).ToArray();
var thens = (args[1] as ICollection<Expression>).ToArray();
var @else = args[2] as Expression;
builder.Append("CASE");
var lenght = whens.Length;
for (int i = 0; i < lenght; i++)
{
builder.Append(" WHEN ");
builder.Visit(whens[i]);
builder.Append(" THEN ");
builder.Visit(thens[i]);
}
if (@else != null)
{
builder.Append(" ELSE ");
builder.Visit(@else);
}
builder.Append(" END");
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
var value = args[1];
var constExp = args[2] as ConstantExpression;
if (constExp != null)
{
value = FormatValue(value,
(bool)constExp.Value
, (bool)(args[3] as ConstantExpression).Value
, (bool)(args[4] as ConstantExpression).Value);
}
else
{
var nv = args[2] as NamedValueExpression;
if (nv != null)
{
constExp = nv.Value as ConstantExpression;
value = FormatValue(value,
(bool)constExp.Value
, (bool)((args[3] as NamedValueExpression).Value as ConstantExpression).Value
, (bool)((args[4] as NamedValueExpression).Value as ConstantExpression).Value);
}
}
builder.Append("(");
builder.Visit(args[0]);
builder.Append(" LIKE ");
builder.Visit(value);
builder.Append(")");
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args == null)
throw new NotSupportedException("args");
//if (args.Length != 1)
// throw new NotSupportedException("'IsNullOrWhiteSpace' function takes 1 arguments");
var isNot = args.Length == 2;
if (isNot)
{
builder.Append("(");
builder.Visit(args[0]);
builder.Append(" IS NOT NULL OR ");
builder.Visit(args[0]);
builder.Append(" <> '' OR LTRIM(");
builder.Visit(args[0]);
builder.Append(") = '')");
}
else
{
builder.Append("(");
builder.Visit(args[0]);
builder.Append(" IS NULL OR ");
builder.Visit(args[0]);
builder.Append(" = '' OR LTRIM(");
builder.Visit(args[0]);
builder.Append(") = '')");
}
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args == null || (args.Length != 2 && args.Length != 3))
{
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "substring", "", "2 or 3"));
}
builder.Append("CASE WHEN LENGTH(");
builder.Visit(args[1]);
builder.Append(") > LENGTH(");
builder.Visit(args[0]);
builder.Append(") THEN NULL ELSE SUBSTR(");
builder.Visit(args[0]);
builder.Append(",").Visit(args[1]);
if (args.Length == 2)
{
builder.Append(",LENGTH(").Visit(args[0]);
builder.Append(")");
}
else
{
builder.Append(",").Visit(args[2]);
}
builder.Append(") END");
}
public virtual void Render(ISqlBuilder builder, params Expression[] args)
{
builder.Append(name);
builder.Append("(");
builder.VisitEnumerable(args);
builder.Append(")");
}
public void Render(ISqlBuilder ctx, params Expression[] args)
{
if (args.Length != 3)
{
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "Insert", "", "3"));
}
var secondStartIndex = args[1];// Expression.Constant((int)(args[1] as ConstantExpression).Value + 1);
ctx.Append("(CASE WHEN ");
ctx.Visit(secondStartIndex);
ctx.Append(" > LENGTH(");
ctx.Visit(args[0]);
ctx.Append(") THEN null ELSE (SUBSTR(");
ctx.Visit(args[0]);
ctx.Append(",1,");
ctx.Visit(Expression.Subtract(Expression.Property(args[1], "Value"), Expression.Constant(1, Types.Int32)));
ctx.Append(") || ");
ctx.Visit(args[2]);
ctx.Append(" || SUBSTR(");
ctx.Visit(args[0]);
ctx.AppendFormat(",");
ctx.Visit(secondStartIndex);
ctx.Append(",LENGTH(");
ctx.Visit(args[0]);
ctx.Append(")))END)");
}
public void Render(ISqlBuilder visitor, params Expression[] args)
{
var whens = (args[0] as ICollection<Expression>).ToArray();
var thens = (args[1] as ICollection<Expression>).ToArray();
var @else = args[2] as Expression;
var lenght = whens.Length;
for (int i = 0; i < lenght; i++)
{
if (i != 0)
visitor.Append(",");
visitor.Append("IIF(");
visitor.Visit(whens[i]);
visitor.Append(",");
visitor.Visit(thens[i]);
if (i == lenght - 1)
{
if (@else != null)
{
visitor.Append(",");
visitor.Visit(@else);
}
for (var j = i; j >= 0; j--)
visitor.Append(")");
}
}
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args.Length != 2 && args.Length != 3)
{
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "PadRight", "", "2 or 3"));
}
builder.Append("CASE WHEN LEN(");
builder.Visit(args[0]);
builder.Append(") >= ");
builder.Visit(args[1]);
builder.Append(" THEN ");
builder.Visit(args[0]);
builder.Append(" ELSE (");
builder.Visit(args[0]);
builder.Append("+");
if (args.Length == 2)
{
builder.Append("SPACE(");
builder.Visit(args[1]);
builder.Append(" - LEN(");
builder.Visit(args[0]);
builder.Append(")))");
}
else
{
builder.Append("REPLICATE(");
builder.Visit(args[2]);
builder.Append(",");
builder.Visit(args[1]);
builder.Append(" - LEN(");
builder.Visit(args[0]);
builder.Append(")))");
}
builder.Append(" END");
}
public virtual void Render(ISqlBuilder visitor, params Expression[] args)
{
var targetArg = args[0];
visitor.Append("RTRIM(LTRIM(");
visitor.Visit(targetArg);
visitor.Append("))");
}
public void Render(ISqlBuilder visitor, params Expression[] args)
{
if (args == null || (args.Length != 2 && args.Length != 3))
{
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "remove()", "", " 2 or 3 "));
}
visitor.Append("IIF((LEN(");
visitor.Visit(args[0]);
visitor.Append(") < ");
visitor.Visit(args[1]);
visitor.Append("),NULL,REPLACE(");
visitor.Visit(args[0]);
visitor.Append(",MID(");
visitor.Visit(args[0]);
visitor.Append(",");
visitor.Visit(args[1]);
if (args.Length == 2)
{
visitor.Append(",8000)");
}
else
{
visitor.Append(",");
visitor.Visit(args[2]);
visitor.Append(")");
}
visitor.Append(",''))");
}
public void SetupTest()
{
_sqlBuilder = MockRepository.GenerateMock<ISqlBuilder>();
_paramMgr = MockRepository.GenerateMock<IParameterManager>();
_queryBuilder = new QueryBuilder( _sqlBuilder, _paramMgr );
_result = _queryBuilder.Add( _inputSql );
}
internal SqlStatementFormatter(EntityDescriptor mainEntityDescriptor, EntityMapping mainEntityMapping, ISqlBuilder mainEntitySqlBuilder)
{
this.MainEntityType = mainEntityDescriptor.EntityType;
this.MainEntityDescriptor = mainEntityDescriptor;
this.MainEntitySqlBuilder = mainEntitySqlBuilder;
this.MainEntityMapping = mainEntityMapping;
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="mainEntityDescriptor">Main entity descriptor</param>
/// <param name="mainEntityMapping">Main entity mappings</param>
/// <param name="mainEntitySqlBuilder">SQL mapper for the main entity</param>
/// <param name="forceColumnAsTableColumnResolution">If true, the format identifier 'C' will be treated as 'TC' </param>
internal SqlStatementFormatter(EntityDescriptor mainEntityDescriptor, EntityMapping mainEntityMapping, ISqlBuilder mainEntitySqlBuilder, bool forceColumnAsTableColumnResolution)
{
_forceColumnAsTableColumnResolution = forceColumnAsTableColumnResolution;
this.MainEntityType = mainEntityDescriptor.EntityType;
this.MainEntityDescriptor = mainEntityDescriptor;
this.MainEntitySqlBuilder = mainEntitySqlBuilder;
this.MainEntityMapping = mainEntityMapping;
}
public void SetupTest()
{
_sqlBuilder = MockRepository.GenerateMock<ISqlBuilder>();
_paramMgr = MockRepository.GenerateMock<IParameterManager>();
_paramMgr.Stub( m => m.AddParameters( _parameterValue ) ).Return( _paramNames );
_queryBuilder = new QueryBuilder( _sqlBuilder, _paramMgr );
_result = _queryBuilder.Add( _inputSql, _parameterValue );
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args != null && args.Length > 0)
throw new ArgumentException(string.Format(Res.ArgumentCountError, Name, "no", ""));
builder.Append(Name);
builder.Append("()");
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args == null)
throw new NotSupportedException("args");
if (args.Length != 2 && args.Length != 3)
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "LastIndexOf", "", "2 or 3"));
builder.Append("CASE WHEN INSTR(");
builder.Visit(args[0]);
builder.Append(",");
builder.Visit(args[1]);
builder.Append(") = 0 then -1 else");
if (args.Length == 3)
{
builder.Append("(CASE WHEN (INSTR(");
builder.Append("SUBSTRING(");
builder.Visit(args[0]);
builder.Append(",1,");
//args[2] = Expression.Subtract(Expression.Property(args[2], "Value"), Expression.Constant(1, Types.Int32));
builder.Visit(args[2]);
builder.Append("),");
builder.Visit(args[1]);
builder.Append(")) = 0 or (");
builder.Visit(args[2]);
builder.Append(" > LENGTH(");
builder.Visit(args[0]);
builder.Append(")) THEN -1 ELSE LENGTH(SUBSTRING(");
builder.Visit(args[0]);
builder.Append(",1,");
builder.Visit(args[2]);
builder.Append(")) - (INSTR(");
builder.Append("REVERSE(SUBSTRING(");
builder.Visit(args[0]);
builder.Append(",1,");
builder.Visit(args[2]);
builder.Append(")),");
builder.Append("REVERSE(");
builder.Visit(args[1]);
builder.Append(")) + LENGTH(");
builder.Visit(args[1]);
builder.Append(") -1) END ) END");
}
else
{
builder.Append(" (LENGTH(");
builder.Visit(args[0]);
builder.Append(")- (INSTR(");
builder.Append("REVERSE(");
builder.Visit(args[0]);
builder.Append("),REVERSE(");
builder.Visit(args[1]);
builder.Append(")) + LENGTH(");
builder.Visit(args[1]);
builder.Append(") - 1)) END");
}
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args == null)
throw new NotSupportedException("args");
if (args.Length != 2 && args.Length != 3)
throw new NotSupportedException(string.Format(Res.ArgumentCountError, "LastIndexOf()", "", " 2 or 3 "));
//var isChar = args[1].Type == Types.Char;
builder.Append("IIF(INSTR(");
builder.Visit(args[0]);
builder.Append(",");
builder.Visit(args[1]);
builder.Append(") = 0,-1,");
if (args.Length == 3)
{
//args[2] = Expression.Constant((int)(args[2] as ConstantExpression).Value + 1);
builder.Append("(IIf((INSTR(");
builder.Append("MID(");
builder.Visit(args[0]);
builder.Append(",1,");
builder.Visit(args[2]);
builder.Append("),");
builder.Visit(args[1]);
builder.Append(") = 0) OR (");
builder.Visit(args[2]);
builder.Append(" > LEN(");
builder.Visit(args[0]);
builder.Append(")),-1 ,(LEN(MID(");
builder.Visit(args[0]);
builder.Append(",1,");
builder.Visit(args[2]);
builder.Append(")) - (INSTR(");
builder.Append("STRREVERSE(MID(");
builder.Visit(args[0]);
builder.Append(",1,");
builder.Visit(args[2]);
builder.Append(")),");
builder.Append("STRREVERSE(");
builder.Visit(args[1]);
builder.Append(")) + LEN(");
builder.Visit(args[1]);
builder.Append(")-1)))))");
}
else
{
builder.Append("(LEN(");
builder.Visit(args[0]);
builder.Append(")- (INSTR(STRREVERSE(");
builder.Visit(args[0]);
builder.Append("),STRREVERSE(");
builder.Visit(args[1]);
builder.Append(")) + LEN(");
builder.Visit(args[1]);
builder.Append(")-1)))");
}
}
public static IDataDriver Get(SqlConfig config, ISqlBuilder sqlBuilder)
{
switch (config.DataDriverType)
{
case DataDriverType.Sqlite:
return new SqliteDriver(config, sqlBuilder);
}
return null;
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
var datePart = (DateParts)(args[0] as ConstantExpression).Value;
IFunctionView f;
if (functions.TryGetValue(datePart, out f))
f.Render(builder, args[1], args[2]);
else
throw new NotSupportedException(string.Format(Res.NotSupported, "The 'DatePart." + datePart, ""));
}
public QueryBuilder()
{
ExpressionLanguageParser = ObjectFactory.Get<IExpressionLanguageParser>();
ExpressionDispatcher = ObjectFactory.Get<IExpressionDispatcher>();
PrequelAnalyzer = ObjectFactory.Get<IPrequelAnalyzer>();
ExpressionOptimizer = ObjectFactory.Get<IExpressionOptimizer>();
SpecialExpressionTranslator = ObjectFactory.Get<ISpecialExpressionTranslator>();
SqlBuilder = ObjectFactory.Get<ISqlBuilder>();
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args.Length == 1)
{
builder.Append("DATE_FORMAT(");
builder.Visit(args[0]);
builder.Append("，'%H:%i:%s')");
}
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
builder.Append("CDate(");
builder.Visit(args[0]);
builder.Append(" & '/' & ");
builder.Visit(args[1]);
builder.Append(" & '/' & ");
builder.Visit(args[2]);
builder.Append(")");
}
protected override string GetDescription(SqlCaluseBuilderItemWithData builderItem, ISqlBuilder builder)
{
string result = string.Empty;
if ((Guid)builderItem.Data == Guid.Empty)
result = "NULL";
else
result = builder.CheckUnicodeQuotationMark(builderItem.Data.ToString());
return result;
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
var year = args[0];
var month = args[1];
builder.Append("(CASE");
builder.Append(" WHEN ");
builder.Visit(year);
builder.Append("% 4 = 0 AND(");
builder.Visit(year);
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
builder.Append("TRUNC(");
builder.Append(Name);
builder.Append("(");
builder.Visit(args[0]);
builder.Append(")");
builder.Append(",");
builder.Append(MaxDecimalDigits);
builder.Append(")");
}
public void Render(ISqlBuilder visitor, params Expression[] args)
{
if (args == null)
throw new ArgumentNullException("args");
if (args.Length != 2 && args.Length != 3)
throw new ArgumentException(string.Format(Res.ArgumentCountError, "remove", "", "2 or 3"));
if (args.Length == 2)
LocateWith2Params.Render(visitor, args);
else
LocateWith3Params.Render(visitor, args);
}
public void Render(ISqlBuilder visitor, params Expression[] args)
{
if (args.Length != 2)
throw new ArgumentException(string.Format(Res.ArgumentCountError, "dateadd", "", " 2 "));
var addPart = (args[1] as ConstantExpression).Value;
visitor.Append("(");
visitor.Visit(args[0]);
visitor.Append(" + interval '" + addPart.ToString() + "' " + Type.ToString() + ")");
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
builder.Append("IIF(");
builder.Visit(args[0]);
builder.Append(" = ");
builder.Visit(args[1]);
builder.Append(", 0, IIF(");
builder.Visit(args[0]);
builder.Append(" < ");
builder.Visit(args[1]);
builder.Append(", -1, 1))");
}
/// <summary> /// Adds a custom implementation of <see cref="ISqlBuilder"/> /// for the specified ADO.NET connection type. /// </summary> /// <param name="connectionType"> /// The ADO.NET conncetion type to use the <paramref name="builder"/> with. /// Example: <c>typeof(SqlConnection)</c>. /// </param> /// <param name="builder">An implementation of the <see cref="ISqlBuilder"/> interface.</param> public static void AddSqlBuilder(Type connectionType, ISqlBuilder builder) => _sqlBuilders[connectionType.Name.ToLower()] = builder;
/// <summary>
/// 设置子查询条件
/// </summary>
/// <param name="builder">Sql生成器</param>
/// <param name="expression">列名表达式,范例：t => t.Name</param>
/// <param name="action">子查询操作</param>
/// <param name="condition">该值为true时添加查询条件，否则忽略</param>
/// <param name="operator">运算符</param>
public static ISqlBuilder WhereIf <TEntity>(this ISqlBuilder builder, Expression <Func <TEntity, object> > expression, Action <ISqlBuilder> action,
bool condition, Operator @operator = Operator.Equal) where TEntity : class
{
return(condition ? builder.Where(expression, action, @operator) : builder);
}
protected Dictionary <string, string> GetEasyJoinInfo(Expression joinExpression, ref string shortName, ISqlBuilder builder, params Type[] entityTypeArray)
{
Dictionary <string, string> result = new Dictionary <string, string>();
var lambdaParameters = ((LambdaExpression)joinExpression).Parameters.ToList();
shortName = lambdaParameters.First().Name;
var index = 1;
foreach (var item in entityTypeArray)
{
result.Add(UtilConstants.Space + lambdaParameters[index].Name, item.Name);
++index;
}
return(result);
}
/// <summary>
/// 初始化Oracle 表连接子句
/// </summary>
/// <param name="sqlBuilder">Sql生成器</param>
/// <param name="dialect">方言</param>
/// <param name="resolver">实体解析器</param>
/// <param name="register">实体注册器</param>
/// <param name="parameterManager">参数管理器</param>
/// <param name="tableDatabase">表数据库</param>
public OracleJoinClause(ISqlBuilder sqlBuilder, IDialect dialect, IEntityResolver resolver, IEntityAliasRegister register,
IParameterManager parameterManager, ITableDatabase tableDatabase)
: base(sqlBuilder, dialect, resolver, register, parameterManager, tableDatabase)
{
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder wf, ISqlBuilder on, DbExpression exp, TableAliasCache aliases)
{
bool useExists = false;
LambdaExpression left = exp.Expressions[1] as LambdaExpression;
LambdaExpression right = exp.Expressions[2] as LambdaExpression;
NewExpression body1 = left.Body as NewExpression;
NewExpression body2 = right.Body as NewExpression;
// t0(t1)
string alias = body1 == null
? aliases.GetTableAlias(exp.Expressions[2])
: aliases.GetTableAlias(right.Parameters[0]);//(body2.Arguments[0]);
IDbQueryable sQuery = (IDbQueryable)((exp.Expressions[0] as ConstantExpression).Value);
if (sQuery.DbExpressions.Count == 1 && sQuery.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
if (!_appendedKeyword)
{
jf.AppendNewLine();
jf.Append(_keywordName);
_appendedKeyword = true;
}
else
{
jf.Append(',');
jf.AppendNewLine();
jf.Append(" ");
}
jf.Append(' ');
Type type = exp.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias);
if (on.Length > 0)
{
on.Append(" AND ");
}
}
else
{
useExists = true;
if ((wf != null && wf.Length > 0) || (on != null && on.Length > 0))
{
if (wf != null && wf.Length > 0)
{
wf.Append("AND ");
}
wf.AppendNewLine();
}
wf.Append("EXISTS(");
wf.Indent += 1;
wf.AppendNewLine();
wf.Append("SELECT 1 FROM(");
var cmd = sQuery.Resolve(wf.Indent + 1, false, wf.Parameters);
wf.Append(cmd.CommandText);
wf.AppendNewLine();
wf.Append(')');
wf.Append(' ');
wf.Append(alias);
wf.Append(" WHERE ");
}
ISqlBuilder tbuilder = useExists ? wf : on;
if (body1 == null)
{
tbuilder.AppendMember(aliases, left.Body.ReduceUnary());
tbuilder.Append(" = ");
tbuilder.AppendMember(aliases, right.Body.ReduceUnary());
}
else
{
for (int index = 0; index < body1.Arguments.Count; ++index)
{
tbuilder.AppendMember(aliases, body1.Arguments[index]);
tbuilder.Append(" = ");
tbuilder.AppendMember(aliases, body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
tbuilder.Append(" AND ");
}
}
}
if (useExists)
{
wf.Indent -= 1;
wf.AppendNewLine();
wf.Append(')');
}
}
// 创建 SELECT 命令
protected override Command ResolveSelectCommand <T>(DbQueryableInfo_Select <T> dbQuery, int indent, bool isOuter, ResolveToken token)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有聚合函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
var subQuery = dbQuery.SubQueryInfo as DbQueryableInfo_Select <T>;
if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null)
{
dbQuery = subQuery;
}
bool useStatis = dbQuery.Aggregate != null;
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
bool useSubQuery = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0;
bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.SubQueryOfMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));
IDbQueryable sourceQuery = dbQuery.SourceQuery;
var context = (SqlServerDbContext)sourceQuery.DbContext;
TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery, token);
MapperCommand cmd = new SqlServerMapperCommand(context, aliases, token)
{
HasMany = dbQuery.HasMany
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useSubQuery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
}
#endregion 嵌套查询
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
if (dbQuery.HasAny)
{
jf.Append("IF EXISTS(");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (useStatis && !useSubQuery)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor2 = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (dbQuery.HasDistinct)
{
jf.Append("DISTINCT ");
}
// TOP 子句
if (dbQuery.Take > 0 && dbQuery.Skip == 0)
{
jf.AppendFormat("TOP({0})", this.DbValue.GetSqlValue(dbQuery.Take, token));
}
// Any
if (dbQuery.HasAny)
{
jf.Append("TOP 1 1");
}
#region 字段
if (!dbQuery.HasAny)
{
// SELECT 范围
var visitor2 = new ColumnExpressionVisitor(this, aliases, dbQuery);
visitor2.Write(jf);
cmd.PickColumns = visitor2.PickColumns;
cmd.PickColumnText = visitor2.PickColumnText;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
}
#endregion 字段
}
#endregion 择子句
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (dbQuery.SubQueryInfo != null)
{
// 子查询
jf.Append('(');
Command cmd2 = this.ResolveSelectCommand <T>(dbQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromEntityType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias0);
jf.Append(' ');
if (context.NoLock && !string.IsNullOrEmpty(this._widthNoLock))
{
jf.Append(this._widthNoLock);
}
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new SqlServerJoinExpressionVisitor(context, aliases, dbQuery.Joins);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Condtion);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (dbQuery.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion 顺序解析
#region 分页查询
if (dbQuery.Skip > 0)
{
if (dbQuery.OrderBys.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
wf.AppendNewLine();
wf.Append("OFFSET ");
wf.Append(this.DbValue.GetSqlValue(dbQuery.Skip, token));
wf.Append(" ROWS");
if (dbQuery.Take > 0)
{
wf.Append(" FETCH NEXT ");
wf.Append(this.DbValue.GetSqlValue(dbQuery.Take, token));
wf.Append(" ROWS ONLY ");
}
}
#endregion 分页查询
#region 嵌套查询
if (useStatis && useSubQuery)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
#endregion 嵌套查询
#region 嵌套导航
if (dbQuery.HasMany && subQuery.Aggregate == null && subQuery != null && subQuery.OrderBys.Count > 0 && !(subQuery.Skip > 0 || subQuery.Take > 0))
{
// TODO Include 从表，没分页，OrderBy 报错
cmd.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);
visitor.Write(jf);
}
#endregion 嵌套导航
#region 并集查询
// UNION 子句
if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
{
cmd.CombineFragments();
for (int index = 0; index < dbQuery.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
Command cmd2 = this.ResolveSelectCommand <T>(dbQuery.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion 并集查询
#region Any 子句
// 'Any' 子句
if (dbQuery.HasAny)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") SELECT 1 ELSE SELECT 0");
}
#endregion Any 子句
return(cmd);
}
// 创建 UPDATE 命令
protected override Command ResolveUpdateCommand <T>(DbQueryableInfo_Update <T> dbQuery, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
IDbQueryable sourceQuery = dbQuery.SourceQuery;
var context = (SqlServerDbContext)sourceQuery.DbContext;
builder.Append("UPDATE t0 SET");
builder.AppendNewLine();
if (dbQuery.Entity != null)
{
object entity = dbQuery.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(token);
bool useKey = false;
int length = 0;
foreach (var invoker in typeRuntime.Invokers)
{
var column = invoker.Column;
if (column != null && column.IsIdentity)
{
goto gotoLabel; // fix issue# 自增列同时又是主键
}
if (column != null && column.NoMapped)
{
continue;
}
if (invoker.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
{
continue; // 行版本号
}
if (invoker.ForeignKey != null)
{
continue;
}
if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
builder.AppendMember("t0", invoker.Member.Name);
builder.Append(" = ");
gotoLabel:
var value = invoker.Invoke(entity);
var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
if (column == null || !column.IsIdentity)
{
builder.Append(seg);
length = builder.Length;
builder.Append(',');
builder.AppendNewLine();
}
if (column != null && column.IsKey)
{
useKey = true;
whereBuilder.AppendMember("t0", invoker.Member.Name);
whereBuilder.Append(" = ");
whereBuilder.Append(seg);
whereBuilder.Append(" AND ");
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require entity must have key column.");
}
builder.Length = length;
whereBuilder.Length -= 5;
builder.AppendNewLine();
builder.Append("FROM ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" t0");
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (dbQuery.Expression != null)
{
TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery.SelectInfo, token);
ExpressionVisitorBase visitor = null;
visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression);
visitor.Write(builder);
builder.AppendNewLine();
builder.Append("FROM ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.AppendAs("t0");
var cmd2 = new SqlServerMapperCommand(context, aliases, token)
{
HasMany = dbQuery.SelectInfo.HasMany
};
visitor = new SqlServerJoinExpressionVisitor(context, aliases, dbQuery.SelectInfo.Joins);
visitor.Write(cmd2.JoinFragment);
visitor = new WhereExpressionVisitor(this, aliases, dbQuery.SelectInfo.Condtion);
visitor.Write(cmd2.WhereFragment);
cmd2.AddNavMembers(visitor.NavMembers);
builder.Append(cmd2.CommandText);
}
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 克隆
/// </summary>
/// <param name="builder">Sql生成器</param>
/// <param name="register">实体别名注册器</param>
/// <param name="parameterManager">参数管理器</param>
/// <returns></returns>
public virtual IWhereClause Clone(ISqlBuilder builder, IEntityAliasRegister register, IParameterManager parameterManager)
{
return(new WhereClause(builder, _dialect, _resolver, register, parameterManager,
new SqlCondition(_condition?.GetCondition())));
}
/// <summary>
/// 设置子查询条件
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <param name="expression">列名表达式</param>
/// <param name="builder">子查询Sql生成器</param>
/// <param name="operator">运算符</param>
public void Where <TEntity>(Expression <Func <TEntity, object> > expression, ISqlBuilder builder, Operator @operator = Operator.Equal) where TEntity : class
{
Where(_helper.GetColumn(expression), builder, @operator);
}
// 创建 UPDATE 命令
protected override Command ParseUpdateCommand <T>(DbQueryableInfo_Update <T> uQueryInfo, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("UPDATE ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" SET");
builder.AppendNewLine();
if (uQueryInfo.Entity != null)
{
object entity = uQueryInfo.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(token);
bool useKey = false;
int length = 0;
foreach (var invoker in typeRuntime.Invokers)
{
var column = invoker.Column;
if (column != null && column.IsIdentity)
{
goto gotoLabel; // fix issue# 自增列同时又是主键
}
if (column != null && column.NoMapped)
{
continue;
}
if (invoker.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
{
continue; // 行版本号
}
if (invoker.ForeignKey != null)
{
continue;
}
if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
builder.AppendMember(invoker.Member.Name);
builder.Append(" = ");
gotoLabel:
var value = invoker.Invoke(entity);
var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
if (column == null || !column.IsIdentity)
{
builder.Append(seg);
length = builder.Length;
builder.Append(',');
builder.AppendNewLine();
}
if (column != null && column.IsKey)
{
useKey = true;
whereBuilder.AppendMember(invoker.Member.Name);
whereBuilder.Append(" = ");
whereBuilder.Append(seg);
whereBuilder.Append(" AND ");
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require entity must have key column.");
}
builder.Length = length;
whereBuilder.Length -= 5;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (uQueryInfo.Expression != null)
{
// SELECT 表达式
LambdaExpression lambda = uQueryInfo.Expression as LambdaExpression;
var body = lambda.Body;
Expression expression = null;
if (body.NodeType == ExpressionType.MemberInit)
{
var memberInit = body as MemberInitExpression;
var bindings = new List <MemberBinding>(memberInit.Bindings);
foreach (var invoker in typeRuntime.KeyInvokers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], invoker.Member);
var binding = Expression.Bind(invoker.Member, member);
if (!bindings.Any(x => x.Member == invoker.Member))
{
bindings.Add(binding);
}
}
expression = Expression.MemberInit(memberInit.NewExpression, bindings);
}
else if (body.NodeType == ExpressionType.New)
{
var newExpression = body as NewExpression;
var bindings = new List <MemberBinding>();
for (int i = 0; i < newExpression.Members.Count; i++)
{
var invoker = typeRuntime.GetInvoker(newExpression.Members[i].Name);
var binding = Expression.Bind(invoker.Member, newExpression.Arguments[i].Type != invoker.DataType
? Expression.Convert(newExpression.Arguments[i], invoker.DataType)
: newExpression.Arguments[i]);
bindings.Add(binding);
}
foreach (var invoker in typeRuntime.KeyInvokers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], invoker.Member);
var binding = Expression.Bind(invoker.Member, member);
if (!bindings.Any(x => x.Member == invoker.Member))
{
bindings.Add(binding);
}
}
var newExpression2 = Expression.New(typeRuntime.ConstructInvoker.Constructor);
expression = Expression.MemberInit(newExpression2, bindings);
}
// 解析查询以确定是否需要嵌套
uQueryInfo.SelectInfo.SelectExpression = new DbExpression(DbExpressionType.Select, expression);
var cmd = (MappingCommand)this.ParseSelectCommand <T>(uQueryInfo.SelectInfo, 1, false, null);
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || uQueryInfo.SelectInfo.Joins.Count > 0)
{
if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0)
{
throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column.");
}
// SET 字段
var visitor = new SQLiteUpdateExpressionVisitor <T>(this, null, uQueryInfo, null);
visitor.ParseCommand = this.ParseSelectCommand;
visitor.Write(builder);
// WHERE部分
builder.AppendNewLine();
builder.Append("WHERE EXISTS");
visitor.VisitArgument(uQueryInfo.SelectInfo.SelectExpression.Expressions[0], true);
}
else
{
// 直接 SQL 的 UPDATE 语法
TableAliasCache aliases = this.PrepareAlias <T>(uQueryInfo.SelectInfo, token);
var visitor = new SQLiteUpdateExpressionVisitor <T>(this, aliases, uQueryInfo, null);
visitor.ParseCommand = this.ParseSelectCommand;
visitor.Write(builder);
var visitor2 = new WhereExpressionVisitor(this, null, uQueryInfo.SelectInfo.WhereExpression);
visitor2.Write(builder);
}
}
builder.Append(';');
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
// 创建 DELETE 命令
protected override Command ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQueryInfo, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("DELETE FROM ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" ");
if (dQueryInfo.Entity != null)
{
if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0)
{
throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
}
object entity = dQueryInfo.Entity;
builder.AppendNewLine();
builder.Append("WHERE ");
foreach (var invoker in typeRuntime.KeyInvokers)
{
var column = invoker.Column;
var value = invoker.Invoke(entity);
var seg = this.DbValue.GetSqlValue(value, token, column);
builder.AppendMember(invoker.Member.Name);
builder.Append(" = ");
builder.Append(seg);
builder.Append(" AND ");
}
builder.Length -= 5;
}
else if (dQueryInfo.SelectInfo != null)
{
LambdaExpression lambda = null;
var dbQuery = dQueryInfo.SourceQuery;
if (dbQuery.DbExpressions != null && dbQuery.DbExpressions.Count > 1)
{
switch (dbQuery.DbExpressions[1].DbExpressionType)
{
case DbExpressionType.Join:
case DbExpressionType.GroupJoin:
case DbExpressionType.GroupRightJoin:
lambda = (LambdaExpression)dbQuery.DbExpressions[1].Expressions[1];
break;
case DbExpressionType.Select:
case DbExpressionType.SelectMany:
lambda = (LambdaExpression)dbQuery.DbExpressions[1].Expressions[0];
break;
}
}
if (lambda == null)
{
DbExpression dbExpression = dQueryInfo.SelectInfo.SelectExpression;
dbExpression = dQueryInfo.SelectInfo.WhereExpression;
if (dbExpression != null && dbExpression.Expressions != null)
{
lambda = (LambdaExpression)dbExpression.Expressions[0];
}
}
// 解析查询以确定是否需要嵌套
var parameter = Expression.Parameter(typeof(SQLiteRowId), lambda != null ? lambda.Parameters[0].Name : "x");
var expression = Expression.MakeMemberAccess(parameter, (_rowIdExpression.Body as MemberExpression).Member);
dQueryInfo.SelectInfo.SelectExpression = new DbExpression(DbExpressionType.Select, expression);
var cmd = (MappingCommand)this.ParseSelectCommand <T>(dQueryInfo.SelectInfo, 1, false, null);
if (token != null && token.Extendsions == null)
{
token.Extendsions = new Dictionary <string, object>();
if (!token.Extendsions.ContainsKey("SQLiteDelete"))
{
token.Extendsions.Add("SQLiteDelete", null);
}
}
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dQueryInfo.SelectInfo.Joins.Count > 0)
{
cmd = (MappingCommand)this.ParseSelectCommand <T>(dQueryInfo.SelectInfo, 1, false, token);
builder.Append("WHERE ");
builder.AppendMember("RowID");
builder.Append(" IN(");
builder.AppendNewLine(cmd.CommandText);
builder.Append(')');
}
else
{
TableAliasCache aliases = this.PrepareAlias <T>(dQueryInfo.SelectInfo, token);
ExpressionVisitorBase visitor = null;
visitor = new JoinExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.Joins);
visitor.Write(builder);
visitor = new WhereExpressionVisitor(this, null, dQueryInfo.SelectInfo.WhereExpression);
visitor.Write(builder);
}
}
builder.Append(';');
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
// 创建 INSRT 命令
protected override Command ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQueryInfo, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
TableAliasCache aliases = new TableAliasCache();
if (nQueryInfo.Entity != null)
{
object entity = nQueryInfo.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(token);
// 指定插入列
MemberInvokerCollection invokers = typeRuntime.Invokers;
if (nQueryInfo.EntityColumns != null && nQueryInfo.EntityColumns.Count > 0)
{
invokers = new MemberInvokerCollection();
for (int i = 0; i < nQueryInfo.EntityColumns.Count; i++)
{
Expression curExpr = nQueryInfo.EntityColumns[i];
if (curExpr.NodeType == ExpressionType.Lambda)
{
curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
}
if (curExpr.NodeType != ExpressionType.MemberAccess)
{
throw new XFrameworkException("Can't read field name from expression {0}", nQueryInfo.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
invokers[name] = typeRuntime.Invokers[name];
}
}
foreach (var invoker in invokers)
{
var column = invoker.Column;
if (column != null && column.NoMapped)
{
continue;
}
if (invoker.ForeignKey != null)
{
continue;
}
if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
if (invoker != nQueryInfo.AutoIncrement)
{
columnsBuilder.AppendMember(invoker.Member.Name);
columnsBuilder.Append(',');
var value = invoker.Invoke(entity);
string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
valuesBuilder.Append(seg);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (nQueryInfo.Bulk == null || !nQueryInfo.Bulk.OnlyValue)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(columnsBuilder);
builder.Append(')');
builder.AppendNewLine();
builder.AppendNewTab();
builder.Append("VALUES");
}
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (nQueryInfo.Bulk != null && !nQueryInfo.Bulk.IsEndPos)
{
builder.Append(",");
}
if (nQueryInfo.Bulk == null && nQueryInfo.AutoIncrement != null)
{
builder.Append(';');
builder.AppendNewLine();
builder.AppendFormat("SELECT LAST_INSERT_ROWID()", typeRuntime.TableName, nQueryInfo.AutoIncrement.Member.Name);
builder.Append(" AS ");
builder.Append(this.QuotePrefix);
builder.Append(Constant.AUTOINCREMENTNAME);
builder.Append(this.QuoteSuffix);
}
}
else if (nQueryInfo.SelectInfo != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
MappingCommand cmd2 = this.ParseSelectCommandImpl(nQueryInfo.SelectInfo, 0, false, token) as MappingCommand;
//for (int i = 0; i < seg.Columns.Count; i++)
foreach (var column in cmd2.PickColumns)
{
builder.AppendMember(column.Name);
if (i < cmd2.PickColumns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd2.CommandText);
}
if (nQueryInfo.Bulk == null || nQueryInfo.Bulk.IsEndPos)
{
builder.Append(';');
}
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
// 创建 SELECT 命令
protected override Command ResolveSelectCommand <T>(DbQueryableInfo_Select <T> dbQuery, int indent, bool isOuter, ResolveToken token)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有聚合函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 8.如果有分页，则使用嵌套
// 9.如果有分页还有OrderBy，则使用嵌套的嵌套
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
var subQuery = dbQuery.SubQueryInfo as DbQueryableInfo_Select <T>;
if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null)
{
dbQuery = subQuery;
}
bool useStatis = dbQuery.Aggregate != null;
bool useNesting = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0;
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.SubQueryOfMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));
TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery, token);
MapperCommand cmd = new MapperCommand(this, aliases, token)
{
HasMany = dbQuery.HasMany
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
(jf as OracleSqlBuilder).IsOuter = isOuter;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useNesting)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
(jf as OracleSqlBuilder).IsOuter = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (dbQuery.HasAny)
{
jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT 1 ");
}
if (useStatis && !useNesting)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor2 = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (dbQuery.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!dbQuery.HasAny)
{
// SELECT 范围
ISqlBuilder sf = this.CreateSqlBuilder(token);
sf.Indent = jf.Indent + ((dbQuery.Skip > 0 || dbQuery.Take > 0) ? 2 : 0);
(sf as OracleSqlBuilder).IsOuter = (dbQuery.Skip > 0 || dbQuery.Take > 0) ? false : (jf as OracleSqlBuilder).IsOuter;
var visitor2 = new ColumnExpressionVisitor(this, aliases, dbQuery);
visitor2.Write(sf);
cmd.PickColumns = visitor2.PickColumns;
cmd.PickColumnText = visitor2.PickColumnText;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
// 分页，产生两层嵌套
if (dbQuery.Skip > 0 || dbQuery.Take > 0)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in cmd.PickColumns)
{
jf.AppendMember(alias0, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < cmd.PickColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
isOuter = false;
jf.Indent = indent;
(jf as OracleSqlBuilder).IsOuter = isOuter;
jf.AppendNewLine();
jf.Append("SELECT");
jf.AppendNewLine();
foreach (var column in cmd.PickColumns)
{
jf.AppendMember(alias0, column.NewName);
jf.AppendAs(column.NewName);
jf.Append(',');
jf.AppendNewLine();
}
jf.Append("ROWNUM AS Row_Number0");
jf.AppendNewLine();
jf.Append("FROM(");
// 第三层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
}
jf.Append(sf);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (dbQuery.SubQueryInfo != null)
{
// 子查询
jf.Append('(');
Command cmd2 = this.ResolveSelectCommand <T>(dbQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromEntityType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias0);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Condtion);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (dbQuery.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
{
// OrderBy("a.CloudServer.CloudServerName");
cmd.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
{
cmd.CombineFragments();
for (int index = 0; index < dbQuery.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
Command cmd2 = this.ResolveSelectCommand <T>(dbQuery.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region 分页查询
if (dbQuery.Take > 0 || dbQuery.Skip > 0)
{
// 合并 WHERE
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.AppendNewLine();
jf.Append("WHERE ");
if (dbQuery.Skip > 0)
{
jf.AppendMember(alias0, "Row_Number0");
jf.Append(" > ");
jf.Append(this.DbValue.GetSqlValue(dbQuery.Skip, token));
}
if (dbQuery.Take > 0)
{
if (dbQuery.Skip > 0)
{
jf.Append(" AND ");
}
jf.AppendMember(alias0, "Row_Number0");
jf.Append(" <= ");
jf.Append(this.DbValue.GetSqlValue((dbQuery.Skip + dbQuery.Take), token));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (dbQuery.HasAny)
{
// 产生 WHERE 子句
cmd.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (dbQuery.Take == 0 && dbQuery.Skip == 0)
{
if (dbQuery.Condtion != null && dbQuery.Condtion.Expressions != null)
{
jf.Append(" AND ROWNUM <= 1");
}
else
{
jf.AppendNewLine();
jf.Append("WHERE ROWNUM <= 1");
}
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(cmd);
}
// 添加导航属性关联
protected override void AppendNavigation()
{
if (base.NavMembers == null || base.NavMembers.Count == 0)
{
return;
}
// 如果有一对多的导航属性，肯定会产生嵌套查询。那么内层查询别名肯定是t0，所以需要清掉
if (this.HasMany)
{
_aliases = new TableAliasCache(_aliases.Declared);
}
//开始产生LEFT JOIN 子句
ISqlBuilder builder = this.JoinFragment;
foreach (var kvp in base.NavMembers)
{
string key = kvp.Key;
MemberExpression m = kvp.Value;
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(m.Expression.Type);
ForeignKeyAttribute attribute = typeRuntime.GetInvokerAttribute <ForeignKeyAttribute>(m.Member.Name);
string innerKey = string.Empty;
string outerKey = key;
string innerAlias = string.Empty;
if (!m.Expression.Acceptable())
{
innerKey = m.Expression.NodeType == ExpressionType.Parameter
? (m.Expression as ParameterExpression).Name
: (m.Expression as MemberExpression).Member.Name;
}
else
{
MemberExpression mLeft = null;
if (m.Expression.NodeType == ExpressionType.MemberAccess)
{
mLeft = m.Expression as MemberExpression;
}
else if (m.Expression.NodeType == ExpressionType.Call)
{
mLeft = (m.Expression as MethodCallExpression).Object as MemberExpression;
}
string name = TypeRuntimeInfoCache.GetRuntimeInfo(mLeft.Type).TableName;
innerAlias = _aliases.GetJoinTableAlias(name);
if (string.IsNullOrEmpty(innerAlias))
{
string keyLeft = mLeft.GetKeyWidthoutAnonymous();
if (base.NavMembers.ContainsKey(keyLeft))
{
innerKey = keyLeft;
}
innerAlias = _aliases.GetNavigationTableAlias(innerKey);
}
}
string alias1 = !string.IsNullOrEmpty(innerAlias) ? innerAlias : _aliases.GetTableAlias(innerKey);
string alias2 = _aliases.GetNavigationTableAlias(outerKey);
builder.AppendNewLine();
builder.Append("LEFT JOIN ");
Type type = m.Type;
if (type.IsGenericType)
{
type = type.GetGenericArguments()[0];
}
var typeRuntime2 = TypeRuntimeInfoCache.GetRuntimeInfo(type);
builder.AppendMember(typeRuntime2.TableName, !typeRuntime2.IsTemporary);
builder.Append(" ");
builder.Append(alias2);
bool withNoLock = !typeRuntime2.IsTemporary && _context.NoLock && !string.IsNullOrEmpty(_provider.WidthNoLock);
if (withNoLock)
{
builder.Append(' ');
builder.Append(_provider.WidthNoLock);
}
builder.Append(" ON ");
for (int i = 0; i < attribute.InnerKeys.Length; i++)
{
builder.Append(alias1);
builder.Append('.');
builder.AppendMember(attribute.InnerKeys[i]);
builder.Append(" = ");
builder.Append(alias2);
builder.Append('.');
builder.AppendMember(attribute.OuterKeys[i]);
if (i < attribute.InnerKeys.Length - 1)
{
builder.Append(" AND ");
}
}
}
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder builder, DbExpression dbExpression, TableAliasCache aliases)
{
bool withNoLock = false;
builder.Append(' ');
IDbQueryable dbQuery = (IDbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
if (dbQuery.DbExpressions.Count == 1 && dbQuery.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
withNoLock = !typeRuntime.IsTemporary && _context.NoLock && !string.IsNullOrEmpty(_provider.WidthNoLock);
}
else
{
// 嵌套
var cmd = dbQuery.Resolve(builder.Indent + 1, false, builder.Token);
builder.Append("(");
builder.Append(cmd.CommandText);
builder.AppendNewLine();
builder.Append(')');
}
var left = dbExpression.Expressions[1] as LambdaExpression;
var right = dbExpression.Expressions[2] as LambdaExpression;
// t0(t1)
string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit)
? aliases.GetTableAlias(dbExpression.Expressions[2])
: aliases.GetTableAlias(right.Parameters[0]);
builder.Append(' ');
builder.Append(alias);
builder.Append(' ');
if (withNoLock)
{
builder.Append(_provider.WidthNoLock);
builder.Append(' ');
}
// ON a.Name = b.Name AND a.Id = b.Id
builder.Append("ON ");
if (left.Body.NodeType == ExpressionType.New)
{
var body1 = left.Body as NewExpression;
var body2 = right.Body as NewExpression;
for (int index = 0; index < body1.Arguments.Count; ++index)
{
builder.AppendMember(aliases, body1.Arguments[index]);
builder.Append(" = ");
builder.AppendMember(aliases, body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
builder.Append(" AND ");
}
}
}
else if (left.Body.NodeType == ExpressionType.MemberInit)
{
var body1 = left.Body as MemberInitExpression;
var body2 = right.Body as MemberInitExpression;
for (int index = 0; index < body1.Bindings.Count; ++index)
{
builder.AppendMember(aliases, (body1.Bindings[index] as MemberAssignment).Expression);
builder.Append(" = ");
builder.AppendMember(aliases, (body2.Bindings[index] as MemberAssignment).Expression);
if (index < body1.Bindings.Count - 1)
{
builder.Append(" AND ");
}
}
}
else
{
builder.AppendMember(aliases, left.Body.ReduceUnary());
builder.Append(" = ");
builder.AppendMember(aliases, right.Body.ReduceUnary());
}
}
protected override string GetDescription(SqlCaluseBuilderItemWithData builderItem, ISqlBuilder builder)
{
return(builderItem.Data.ToString());
}
// 创建 INSRT 命令
protected override Command ResolveInsertCommand <T>(DbQueryableInfo_Insert <T> dbQuery, ResolveToken token)
{
TableAliasCache aliases = new TableAliasCache();
ISqlBuilder builder = this.CreateSqlBuilder(token);
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
if (dbQuery.Entity != null)
{
object entity = dbQuery.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(token);
// 指定插入列
MemberInvokerCollection invokers = typeRuntime.Invokers;
if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
{
invokers = new MemberInvokerCollection();
for (int i = 0; i < dbQuery.EntityColumns.Count; i++)
{
Expression curExpr = dbQuery.EntityColumns[i];
if (curExpr.NodeType == ExpressionType.Lambda)
{
curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
}
if (curExpr.NodeType != ExpressionType.MemberAccess)
{
throw new XFrameworkException("ERR {0}: Only support MemberAccess expression.", dbQuery.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
invokers[name] = typeRuntime.Invokers[name];
}
}
foreach (var invoker in invokers)
{
var column = invoker.Column;
if (column != null && column.NoMapped)
{
continue;
}
if (invoker.ForeignKey != null)
{
continue;
}
if (invoker.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
{
continue; // 行版本号
}
if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
if (invoker != dbQuery.AutoIncrement)
{
columnsBuilder.AppendMember(invoker.Member.Name);
columnsBuilder.Append(',');
var value = invoker.Invoke(entity);
string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
valuesBuilder.Append(seg);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(columnsBuilder);
builder.Append(')');
builder.AppendNewLine();
builder.AppendNewTab();
builder.Append("VALUES");
}
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos)
{
builder.Append(",");
}
if (dbQuery.Bulk == null && dbQuery.AutoIncrement != null)
{
builder.AppendNewLine();
builder.Append("SELECT SCOPE_IDENTITY()");
builder.AppendAs(Constant.AUTOINCREMENTNAME);
}
}
else if (dbQuery.SelectInfo != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
MapperCommand cmd2 = this.ResolveSelectCommand(dbQuery.SelectInfo, 0, true, token) as MapperCommand;
foreach (Column column in cmd2.PickColumns)
{
builder.AppendMember(column.NewName);
if (i < cmd2.PickColumns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd2.CommandText);
}
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
public BaseRepository()
{
_sqlBuilder = new SqlBuilderFactory <T>(this).CreateBuilder();
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlExpression{TEntity}"/>
/// class using the specified <see cref="ISqlBuilder"/>.
/// </summary>
/// <param name="sqlBuilder">The <see cref="ISqlBuilder"/> instance.</param>
public SqlExpression(ISqlBuilder sqlBuilder)
{
SqlBuilder = sqlBuilder;
}
public void Render(ISqlBuilder builder, params Expression[] args)
{
if (args.Length != 2)
{
throw new ArgumentException(string.Format(Res.ArgumentCountError, "datediff", "", "2"));
}
switch (Type)
{
case DateParts.Year:
builder.Append(" CAST(STRFTIME('%Y',");
builder.Visit(args[1]);
builder.Append(") AS INT) - ");
builder.Append(" CAST(STRFTIME('%Y',");
builder.Visit(args[0]);
builder.Append(") AS INT)");
break;
case DateParts.Month:
builder.Append(" (CAST(STRFTIME('%Y',");
builder.Visit(args[1]);
builder.Append(") AS INT) - ");
builder.Append(" CAST(STRFTIME('%Y',");
builder.Visit(args[0]);
builder.Append(") AS INT))*12");
builder.Append(" + ");
builder.Append(" CAST(STRFTIME('%m',");
builder.Visit(args[1]);
builder.Append(") AS INT) - ");
builder.Append(" CAST(STRFTIME('%m',");
builder.Visit(args[0]);
builder.Append(") AS INT)");
break;
case DateParts.Day:
builder.Append(" FLOOR((");
builder.Append(" JULIANDAY(");
builder.Visit(args[1]);
builder.Append(") - ");
builder.Append(" JULIANDAY(");
builder.Visit(args[0]);
builder.Append(")))");
break;
case DateParts.Hour:
builder.Append(" FLOOR((");
builder.Append(" JULIANDAY(");
builder.Visit(args[1]);
builder.Append(") - ");
builder.Append(" JULIANDAY(");
builder.Visit(args[0]);
builder.Append("))*24)");
break;
case DateParts.Minute:
builder.Append(" FLOOR((");
builder.Append(" JULIANDAY(");
builder.Visit(args[1]);
builder.Append(") - ");
builder.Append(" JULIANDAY(");
builder.Visit(args[0]);
builder.Append("))*24*60)");
break;
case DateParts.Second:
builder.Append(" FLOOR((");
builder.Append(" JULIANDAY(");
builder.Visit(args[1]);
builder.Append(") - ");
builder.Append(" JULIANDAY(");
builder.Visit(args[0]);
builder.Append("))*24*60*60)");
break;
case DateParts.Millisecond:
builder.Append(" FLOOR((");
builder.Append(" JULIANDAY(");
builder.Visit(args[1]);
builder.Append(") - ");
builder.Append(" JULIANDAY(");
builder.Visit(args[0]);
builder.Append("))*24*60*60*1000)");
break;
case DateParts.Week:
builder.Append(" FLOOR(ROUND((");
builder.Append(" JULIANDAY(");
builder.Visit(args[1]);
builder.Append(") - ");
builder.Append(" JULIANDAY(");
builder.Visit(args[0]);
builder.Append(")) , 0)/7)");
break;
}
}
/// <summary>
/// 设置子查询条件
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <param name="sqlQuery">Sql查询对象</param>
/// <param name="expression">列名表达式,范例：t => t.Name</param>
/// <param name="builder">子查询Sql生成器</param>
/// <param name="condition">该值为true时添加查询条件，否则忽略</param>
/// <param name="operator">运算符</param>
public static ISqlQuery WhereIf <TEntity>(this ISqlQuery sqlQuery, Expression <Func <TEntity, object> > expression, ISqlBuilder builder,
bool condition, Operator @operator = Operator.Equal) where TEntity : class
{
var sqlBuilder = sqlQuery.GetBuilder();
sqlBuilder.WhereIf(expression, builder, condition, @operator);
return(sqlQuery);
}
/// <summary>
/// 初始化Sql查询对象
/// </summary>
/// <param name="sqlBuilder">Sql生成器</param>
/// <param name="database">数据库</param>
protected SqlQueryBase(ISqlBuilder sqlBuilder, IDatabase database = null)
{
Builder = sqlBuilder ?? throw new ArgumentNullException(nameof(sqlBuilder));
_database = database;
}
protected override string GetDescription(SqlCaluseBuilderItemWithData builderItem, ISqlBuilder builder)
{
return("NULL");
}
/// <summary>
/// 设置查询条件
/// </summary>
/// <param name="builder">Sql生成器</param>
/// <param name="expression">查询条件表达式,范例：t => t.Name.Contains("a") && ( t.Code == "b" || t.Age > 1 )</param>
/// <param name="condition">该值为true时添加查询条件，否则忽略</param>
public static ISqlBuilder WhereIf <TEntity>(this ISqlBuilder builder, Expression <Func <TEntity, bool> > expression, bool condition) where TEntity : class
{
return(condition ? builder.Where(expression) : builder);
}
/// <summary>
/// 设置Not In条件
/// </summary>
/// <param name="source">源</param>
/// <param name="expression">列名表达式</param>
/// <param name="builder">Sql生成器</param>
public static ISqlBuilder NotIn <TEntity>(this ISqlBuilder source, Expression <Func <TEntity, object> > expression, ISqlBuilder builder) where TEntity : class
{
if (source == null)
{
throw new ArgumentNullException(nameof(source));
}
if (source is IClauseAccessor accessor)
{
accessor.WhereClause.NotIn(expression, builder);
}
return(source);
}
// 创建 UPDATE 命令
protected override Command ResolveUpdateCommand <T>(DbQueryableInfo_Update <T> dbQuery, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("UPDATE ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" t0 SET");
builder.AppendNewLine();
if (dbQuery.Entity != null)
{
object entity = dbQuery.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(token);
bool useKey = false;
int length = 0;
foreach (var invoker in typeRuntime.Invokers)
{
var column = invoker.Column;
if (column != null && column.IsIdentity)
{
goto gotoLabel; // fix issue# 自增列同时又是主键
}
if (column != null && column.NoMapped)
{
continue;
}
if (invoker.ForeignKey != null)
{
continue;
}
if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
builder.AppendMember(invoker.Member.Name);
builder.Append(" = ");
gotoLabel:
if (column == null || !column.IsIdentity)
{
var value = invoker.Invoke(entity);
var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
builder.Append(seg);
length = builder.Length;
builder.Append(',');
builder.AppendNewLine();
}
if (column != null && column.IsKey)
{
useKey = true;
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require T must have key column.");
}
// ORACLE 需要注意参数顺序问题
int index = -1;
foreach (var invoker in typeRuntime.KeyInvokers)
{
var column = invoker.Column;
var value = invoker.Invoke(entity);
var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
index += 1;
whereBuilder.AppendMember(invoker.Member.Name);
whereBuilder.Append(" = ");
whereBuilder.Append(seg);
if (index < typeRuntime.KeyInvokers.Count - 1)
{
whereBuilder.Append(" AND ");
}
}
builder.Length = length;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (dbQuery.Expression != null)
{
// SELECT 表达式
LambdaExpression lambda = dbQuery.Expression as LambdaExpression;
var body = lambda.Body;
Expression expression = null;
if (body.NodeType == ExpressionType.MemberInit)
{
var memberInit = body as MemberInitExpression;
var bindings = new List <MemberBinding>(memberInit.Bindings);
foreach (var invoker in typeRuntime.KeyInvokers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], invoker.Member);
var binding = Expression.Bind(invoker.Member, member);
if (!bindings.Any(x => x.Member == invoker.Member))
{
bindings.Add(binding);
}
}
expression = Expression.MemberInit(memberInit.NewExpression, bindings);
}
else if (body.NodeType == ExpressionType.New)
{
var newExpression = body as NewExpression;
var bindings = new List <MemberBinding>();
for (int i = 0; i < newExpression.Members.Count; i++)
{
var invoker = typeRuntime.GetInvoker(newExpression.Members[i].Name);
var binding = Expression.Bind(invoker.Member, newExpression.Arguments[i].Type != invoker.DataType
? Expression.Convert(newExpression.Arguments[i], invoker.DataType)
: newExpression.Arguments[i]);
bindings.Add(binding);
}
foreach (var invoker in typeRuntime.KeyInvokers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], invoker.Member);
var binding = Expression.Bind(invoker.Member, member);
if (!bindings.Any(x => x.Member == invoker.Member))
{
bindings.Add(binding);
}
}
var newExpression2 = Expression.New(typeRuntime.ConstructInvoker.Constructor);
expression = Expression.MemberInit(newExpression2, bindings);
}
// 解析查询以确定是否需要嵌套
dbQuery.SelectInfo.Select = new DbExpression(DbExpressionType.Select, expression);
var cmd = (MapperCommand)this.ResolveSelectCommand <T>(dbQuery.SelectInfo, 1, false, null);//, token);
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.SelectInfo.Joins.Count > 0)
{
// 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。UPDATE 不能用rowid
// 有导航属性或者关联查询，使用 MERGE INTO 语法。要求必须有主键
if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0)
{
throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column.");
}
builder.Length = 0;
builder.Append("MERGE INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.AppendNewLine(" t0");
builder.Append("USING (");
cmd = (MapperCommand)this.ResolveSelectCommand <T>(dbQuery.SelectInfo, 1, false, token);
builder.AppendNewLine(cmd.CommandText);
builder.Append(") t1 ON (");
foreach (var invoker in typeRuntime.KeyInvokers)
{
builder.AppendMember("t0", invoker.Name);
builder.Append(" = ");
builder.AppendMember("t1", invoker.Name);
builder.Append(" AND ");
}
builder.Length -= 5;
builder.Append(')');
// UPDATE
builder.AppendNewLine();
builder.AppendNewLine("WHEN MATCHED THEN UPDATE SET ");
// SET 字段
var visitor = new OracleUpdateExpressionVisitor(this, null, dbQuery.Expression);
visitor.Write(builder);
}
else
{
// 直接 SQL 的 UPDATE 语法
TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery.SelectInfo, token);
ExpressionVisitorBase visitor = null;
visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression);
visitor.Write(builder);
visitor = new WhereExpressionVisitor(this, aliases, dbQuery.SelectInfo.Condtion);
visitor.Write(builder);
}
}
builder.Append(';');
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 初始化From子句
/// </summary>
/// <param name="builder">Sql生成器</param>
/// <param name="dialect">方言</param>
/// <param name="resolver">实体解析器</param>
/// <param name="register">实体别名注册器</param>
/// <param name="tableDatabase">表数据库</param>
/// <param name="table">表</param>
public OracleFromClause(ISqlBuilder builder, IDialect dialect, IEntityResolver resolver, IEntityAliasRegister register, ITableDatabase tableDatabase, SqlItem table = null)
: base(builder, dialect, resolver, register, tableDatabase, table)
{
}
/// <summary>
/// Register a builder, you can only have one builder per application.
/// </summary>
/// <param name="sqlBuilder">The builder.</param>
/// <returns>The builder.</returns>
public static ISqlBuilder Register(ISqlBuilder sqlBuilder)
{
return(Register(sqlBuilder, false));
}
// 创建 SELECT 命令
Command ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ResolveToken token)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有统计函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
DbQueryableInfo_Select <T> subQuery = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQueryInfo.HasMany && subQuery != null && subQuery.StatisExpression != null)
{
sQueryInfo = subQuery;
}
bool useStatis = sQueryInfo.StatisExpression != null;
bool useNesting = sQueryInfo.HasDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0;
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HasAny && (!sQueryInfo.SubQueryByMany || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0));
IDbQueryable dbQueryable = sQueryInfo.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token);
MappingCommand cmd = new MappingCommand(this, aliases, token)
{
HasMany = sQueryInfo.HasMany
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useNesting)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (sQueryInfo.HasAny)
{
jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT 1 ");
}
if (useStatis && !useNesting)
{
// 如果有统计函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQueryInfo.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!sQueryInfo.HasAny)
{
// SELECT 范围
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo);
visitor2.Write(jf);
cmd.PickColumns = visitor2.PickColumns;
cmd.PickColumnText = visitor2.PickColumnText;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (sQueryInfo.SubQueryInfo != null)
{
// 子查询
jf.Append('(');
Command cmd2 = this.ParseSelectCommandImpl <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias0);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (sQueryInfo.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
if (sQueryInfo.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(sQueryInfo.Take, token));
}
else if (sQueryInfo.Take == 0 && sQueryInfo.Skip > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(-1, token));
}
if (sQueryInfo.Skip > 0)
{
wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(sQueryInfo.Skip, token));
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (sQueryInfo.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
{
cmd.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0)
{
cmd.CombineFragments();
for (int index = 0; index < sQueryInfo.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (sQueryInfo.HasAny)
{
// 产生 WHERE 子句
cmd.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (sQueryInfo.Take == 0 && sQueryInfo.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(cmd);
}
// 创建 INSRT 命令
protected override Command ResolveInsertCommand <T>(DbQueryableInfo_Insert <T> dbQuery, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
TableAliasCache aliases = new TableAliasCache();
if (dbQuery.Entity != null)
{
// 如果没有Sequence列，使用 INSERT ALL INTO 语法，否则就一条一条逐行写入~~
// 批量 INSERT，自增列不会自动赋值
object entity = dbQuery.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(token);
// 指定插入列
MemberInvokerCollection invokers = typeRuntime.Invokers;
if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
{
invokers = new MemberInvokerCollection();
for (int i = 0; i < dbQuery.EntityColumns.Count; i++)
{
Expression curExpr = dbQuery.EntityColumns[i];
if (curExpr.NodeType == ExpressionType.Lambda)
{
curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
}
if (curExpr.NodeType != ExpressionType.MemberAccess)
{
throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
invokers[name] = typeRuntime.Invokers[name];
}
}
// 自增列参数
IDbDataParameter seqParameter = null;
// 自增列标记
ColumnAttribute seqColumn = null;
foreach (var invoker in invokers)
{
var column = invoker.Column;
if (column != null && column.NoMapped)
{
continue;
}
if (invoker.ForeignKey != null)
{
continue;
}
if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
columnsBuilder.AppendMember(invoker.Member.Name);
columnsBuilder.Append(',');
if (invoker == dbQuery.AutoIncrement)
{
seqColumn = column;
if (dbQuery.Bulk == null)
{
// 非批量INSERT，产生一个 OUTPUT 类型的参数
string pName = string.Format("{0}p{1}", this.ParameterPrefix, token.Parameters.Count);
var database = dbQuery.SourceQuery.DbContext.Database;
seqParameter = database.CreateParameter(pName, -1, direction: ParameterDirection.Output);
token.Parameters.Add(seqParameter);
valuesBuilder.Append(seqParameter.ParameterName);
valuesBuilder.Append(',');
}
else
{
valuesBuilder.Append(((OracleColumnAttribute)column).SEQName);
valuesBuilder.Append(".NEXTVAL");
valuesBuilder.Append(',');
}
}
else
{
var value = invoker.Invoke(entity);
string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
valuesBuilder.Append(seg);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (dbQuery.Bulk == null)
{
// 非批量INSERT，产生一个 OUTPUT 类型的参数
if (seqParameter != null)
{
seqParameter.Direction = ParameterDirection.Output;
seqParameter.DbType = DbType.Int64;
builder.Append("SELECT ");
builder.Append(((OracleColumnAttribute)seqColumn).SEQName);
builder.Append(".NEXTVAL INTO ");
builder.Append(seqParameter.ParameterName);
builder.Append(" FROM DUAL;");
builder.AppendNewLine();
//useSEQ = true;
}
builder.Append("INSERT ");
}
else
{
// 批量 INSERT
if (!dbQuery.Bulk.OnlyValue || seqColumn != null)
{
builder.Append("INSERT ");
}
// 如果有自增列则不使用 INSERT ALL INTO 语法
if (!dbQuery.Bulk.OnlyValue && seqColumn == null)
{
builder.Append("ALL ");
}
}
builder.Append("INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(columnsBuilder);
builder.Append(')');
builder.AppendNewLine();
builder.AppendNewTab();
builder.Append("VALUES");
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (dbQuery.Bulk == null)
{
builder.Append(';');
}
else
{
if (seqColumn != null)
{
if (dbQuery.Bulk.IsEndPos)
{
builder.Append(";");
}
else
{
builder.AppendNewLine(";");
}
}
else
{
builder.AppendNewLine();
if (dbQuery.Bulk.IsEndPos)
{
builder.Append("SELECT 1 FROM DUAL;");
}
}
}
}
else if (dbQuery.SelectInfo != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
MapperCommand cmd2 = this.ResolveSelectCommand(dbQuery.SelectInfo, 0, false, token) as MapperCommand;
foreach (var column in cmd2.PickColumns)
{
builder.AppendMember(column.Name);
if (i < cmd2.PickColumns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd2.CommandText);
builder.Append(';');
}
var cmd = new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text);
return(cmd);
}