/// <summary>
/// 实例化 <see cref="SqlServerMapperCommand"/> 类的新实例
/// </summary>
/// <param name="context">数据查询提供者</param>
/// <param name="aliases">别名</param>
/// <param name="token">解析上下文参数</param>
public SqlServerMapperCommand(SqlServerDbContext context, TableAliasCache aliases, ResolveToken token)
: base(context.Provider, aliases, token)
{
_aliases = aliases;
_context = context;
_provider = context.Provider as SqlServerDbQueryProvider;
}
/// <summary>
/// 初始化 <see cref="SqlServerJoinExpressionVisitor"/> 类的新实例
/// </summary>
internal SqlServerJoinExpressionVisitor(SqlClient.SqlServerDbContext context, TableAlias aliases, List <DbExpression> joins)
: base(context.Provider, aliases, joins)
{
_joins = joins;
_aliases = aliases;
_context = context;
_provider = context.Provider as SqlClient.SqlServerDbQueryProvider;
}
// 创建 SELECT 命令
protected override Command ParseSelectCommand <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,
var subQueryInfo = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQueryInfo.HasMany && subQueryInfo != null && subQueryInfo.StatisExpression != null)
{
sQueryInfo = subQueryInfo;
}
bool useStatis = sQueryInfo.StatisExpression != null;
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
bool useSubQuery = sQueryInfo.HasDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0;
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 && useSubQuery)
{
// 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();
}
if (sQueryInfo.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 StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQueryInfo.HasDistinct)
{
jf.Append("DISTINCT ");
}
// TOP 子句
if (sQueryInfo.Take > 0 && sQueryInfo.Skip == 0)
{
jf.AppendFormat("TOP({0})", this.DbValue.GetSqlValue(sQueryInfo.Take, token));
}
// Any
if (sQueryInfo.HasAny)
{
jf.Append("TOP 1 1");
}
#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.ParseSelectCommand <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(' ');
SqlServerDbContext context = (SqlServerDbContext)dbQueryable.DbContext;
if (context.NoLock && !string.IsNullOrEmpty(this._widthNoLock))
{
jf.Append(this._widthNoLock);
}
}
// 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.Skip > 0)
{
if (sQueryInfo.OrderBys.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
wf.AppendNewLine();
wf.Append("OFFSET ");
wf.Append(this.DbValue.GetSqlValue(sQueryInfo.Skip, token));
wf.Append(" ROWS");
if (sQueryInfo.Take > 0)
{
wf.Append(" FETCH NEXT ");
wf.Append(this.DbValue.GetSqlValue(sQueryInfo.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 (sQueryInfo.HasMany && subQueryInfo.StatisExpression == null && subQueryInfo != null && subQueryInfo.OrderBys.Count > 0 && !(subQueryInfo.Skip > 0 || subQueryInfo.Take > 0))
{
// TODO Include 从表，没分页，OrderBy 报错
cmd.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQueryInfo.OrderBys);
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)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") SELECT 1 ELSE SELECT 0");
}
#endregion Any 子句
return(cmd);
}