/// <summary>
/// 解析 SELECT 命令
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="indent">缩进</param>
/// <param name="isOuter">指示是最外层查询</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected override RawCommand ResolveSelectCommand(IDbQueryableInfo_Select 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.Subquery as IDbQueryableInfo_Select;
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.AliasPrefix) ? (token.AliasPrefix + "0") : "t0";
bool useSubQuery = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0;
bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));
var context = (SqlServerDbContext)token.DbContext;
TableAlias aliases = this.PrepareTableAlias(dbQuery, token);
var result = new SqlServerMappingCommand(context, aliases, token)
{
HasMany = dbQuery.HasMany
};
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useSubQuery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
visitor_.Write(jf);
result.AddNavMembers(visitor_.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 visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
visitor_.Write(jf);
result.AddNavMembers(visitor_.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 visitor_ = new ColumnExpressionVisitor(this, aliases, dbQuery);
visitor_.Write(jf);
result.PickColumns = visitor_.PickColumns;
result.PickColumnText = visitor_.PickColumnText;
result.PickNavDescriptors = visitor_.PickNavDescriptors;
result.AddNavMembers(visitor_.NavMembers);
}
#endregion 字段
}
#endregion 择子句
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (dbQuery.Subquery != null)
{
// 子查询
jf.Append('(');
RawCommand cmd = this.ResolveSelectCommand(dbQuery.Subquery, indent + 1, false, token);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType);
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.Where);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (dbQuery.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
visitor.Write(wf);
result.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)
{
result.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 报错
result.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);
visitor.Write(jf);
}
#endregion 嵌套导航
#region 并集查询
// UNION 子句
if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < dbQuery.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
RawCommand cmd = this.ResolveSelectCommand(dbQuery.Unions[index], indent, isOuter, token);
jf.Append(cmd.CommandText);
}
}
#endregion 并集查询
#region Any 子句
// 'Any' 子句
if (dbQuery.HasAny)
{
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") SELECT 1 ELSE SELECT 0");
}
#endregion Any 子句
return(result);
}
// 创建 SELECT 命令
protected override Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ParserToken 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.HasManyNavigation && 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.HaveDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0;
bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HaveAny && (!sQueryInfo.ResultByManyNavigation || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0));
IDbQueryable dbQueryable = sQueryInfo.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token);
SelectCommand cmd = new SelectCommand(this, aliases, token)
{
HasManyNavigation = sQueryInfo.HasManyNavigation
};
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.HaveAny)
{
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.HaveDistinct)
{
jf.Append("DISTINCT ");
}
// TOP 子句
if (sQueryInfo.Take > 0 && sQueryInfo.Skip == 0)
{
jf.AppendFormat("TOP({0})", jf.GetSqlValue(sQueryInfo.Take));
}
// Any
if (sQueryInfo.HaveAny)
{
jf.Append("TOP 1 1");
}
#region 字段
if (!sQueryInfo.HaveAny)
{
// SELECT 范围
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo);
visitor2.Write(jf);
cmd.Columns = visitor2.Columns;
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(' ');
SqlDbContext context = (SqlDbContext)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(wf.GetSqlValue(sQueryInfo.Skip));
wf.Append(" ROWS");
if (sQueryInfo.Take > 0)
{
wf.Append(" FETCH NEXT ");
wf.Append(wf.GetSqlValue(sQueryInfo.Take));
wf.Append(" ROWS ONLY ");
}
}
#endregion
#region 嵌套查询
if (useStatis && useSubQuery)
{
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
#endregion
#region 嵌套导航
if (sQueryInfo.HasManyNavigation && subQueryInfo.StatisExpression == null && subQueryInfo != null && subQueryInfo.OrderBys.Count > 0 && !(subQueryInfo.Skip > 0 || subQueryInfo.Take > 0))
{
// TODO Include 从表，没分页，OrderBy 报错
cmd.Convergence();
visitor = new OrderByExpressionVisitor(this, aliases, subQueryInfo.OrderBys);
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0)
{
cmd.Convergence();
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.HaveAny)
{
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") SELECT 1 ELSE SELECT 0");
}
#endregion
return(cmd);
}
// 创建 SELECT 命令
protected override DbCommandDefinition ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQuery, int indent = 0, bool isOuter = true, List <IDbDataParameter> parameters = null)
{
// 说明：
// 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,
DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null)
{
sQuery = innerQuery;
}
bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0;
bool useStatis = sQuery.Statis != null;
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0));
IDbQueryable dbQueryable = sQuery.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQuery);
DbCommandDefinition_Select cmd = new DbCommandDefinition_Select(this, aliases, parameters)
{
HaveListNavigation = sQuery.HaveListNavigation
};
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 StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0");
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 (sQuery.HaveAny)
{
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, sQuery.Statis, sQuery.GroupBy);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQuery.HaveDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!sQuery.HaveAny)
{
// SELECT 范围
ISqlBuilder sf = this.CreateSqlBuilder(jf.Parameters);
sf.Indent = jf.Indent + ((sQuery.Skip > 0 || sQuery.Take > 0) ? 2 : 0);
(sf as OracleSqlBuilder).IsOuter = (sQuery.Skip > 0 || sQuery.Take > 0) ? false : (jf as OracleSqlBuilder).IsOuter;
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery);
visitor2.Write(sf);
cmd.Columns = visitor2.Columns;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
// 分页，产生两层嵌套
if (sQuery.Skip > 0 || sQuery.Take > 0)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var entry in cmd.Columns)
{
jf.AppendMember("t0", entry.Key);
jf.AppendAs(entry.Key);
index += 1;
if (index < cmd.Columns.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 entry in cmd.Columns)
{
jf.AppendMember("t0", entry.Key);
jf.AppendAs(entry.Key);
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 (sQuery.SubQueryInfo != null)
{
// 子查询
jf.Append('(');
DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") t0 ");
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(" t0 ");
//if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock);
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (sQuery.OrderBy.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(" ) t0");
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0))
{
// OrderBy("a.CloudServer.CloudServerName");
cmd.Convergence();
visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQuery.Union != null && sQuery.Union.Count > 0)
{
cmd.Convergence();
for (int index = 0; index < sQuery.Union.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region 分页查询
if (sQuery.Take > 0 || sQuery.Skip > 0)
{
// 合并 WHERE
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") t0");
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") t0");
jf.AppendNewLine();
jf.Append("WHERE ");
if (sQuery.Skip > 0)
{
jf.Append("t0.Row_Number0 > ");
jf.Append(jf.GetSqlValue(sQuery.Skip));
}
if (sQuery.Take > 0)
{
if (sQuery.Skip > 0)
{
jf.Append(" AND ");
}
jf.Append("t0.Row_Number0 <= ");
jf.Append(jf.GetSqlValue((sQuery.Skip + sQuery.Take)));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (sQuery.HaveAny)
{
// 产生 WHERE 子句
cmd.Convergence();
// 如果没有分页，则显式指定只查一笔记录
if (sQuery.Take == 0 && sQuery.Skip == 0)
{
if (sQuery.Where != null && sQuery.Where.Expressions != null)
{
jf.Append(" AND ROWNUM <= 1");
}
else
{
jf.AppendNewLine();
jf.Append("WHERE ROWNUM <= 1");
}
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") t0");
}
#endregion
return(cmd);
}
RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select 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.如果只有 Skip 没有 Take，则使用 Row_Number() Over()分页语法，其它使用 LIMIT ## OFFSET 语法
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
var subQuery = dbQuery.Subquery as IDbQueryableInfo_Select;
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.AliasPrefix) ? (token.AliasPrefix + "0") : "t0";
bool useSubquery = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0;
bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));
TableAlias aliases = this.PrepareTableAlias(dbQuery, token);
var result = new MappingCommand(this, aliases, token)
{
HasMany = dbQuery.HasMany
};
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
ISqlBuilder sf = null;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useSubquery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
visitor_.Write(jf);
result.AddNavMembers(visitor_.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 (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 && !useSubquery)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
visitor_.Write(jf);
result.AddNavMembers(visitor_.NavMembers);
}
else
{
// DISTINCT 子句
if (dbQuery.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!dbQuery.HasAny)
{
// SELECT 范围
var visitor_ = new ColumnExpressionVisitor(this, aliases, dbQuery);
if (dbQuery.Skip > 0 && dbQuery.Take == 0)
{
sf = this.CreateSqlBuilder(token);
sf.Indent = jf.Indent + 1;
visitor_.Write(sf);
}
else
{
visitor_.Write(jf);
}
result.PickColumns = visitor_.PickColumns;
result.PickColumnText = visitor_.PickColumnText;
result.PickNavDescriptors = visitor_.PickNavDescriptors;
result.AddNavMembers(visitor_.NavMembers);
if (sf != null)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in result.PickColumns)
{
jf.AppendMember(alias0, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < result.PickColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
jf.Append(sf);
jf.Append(',');
jf.AppendNewLine();
if (dbQuery.OrderBys.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
jf.Append("ROW_NUMBER() OVER(");
var visitor3 = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
visitor3.Write(jf, false);
result.AddNavMembers(visitor3.NavMembers);
jf.Append(") Row_Number0");
}
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (dbQuery.Subquery != null)
{
// 子查询
jf.Append("(");
var cmd = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType);
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.Where);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (dbQuery.OrderBys.Count > 0 && useOrderBy)// && !groupByPaging)
{
visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
// LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
// LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
// 初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
// Limit n,-1 语法不支持，使用ROW_Number()语法代替
if (dbQuery.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token));
wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token));
}
#endregion
#region 嵌套查询
if (useStatis && useSubquery)
{
result.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))
{
result.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < dbQuery.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
RawCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Unions[index], indent, isOuter, token);
jf.Append(cmd.CommandText);
}
}
#endregion
#region 分页查询
if (sf != null)
{
// 合并 WHERE
result.CombineFragments();
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));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (dbQuery.HasAny)
{
// 产生 WHERE 子句
result.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (dbQuery.Take == 0 && dbQuery.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 0,1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(result);
}
// 创建 SELECT 命令
Command ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, ParserToken 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 = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQuery.HasManyNavigation && subQuery != null && subQuery.StatisExpression != null)
{
sQuery = subQuery;
}
bool useStatis = sQuery.StatisExpression != null;
bool useNesting = sQuery.HaveDistinct || sQuery.GroupByExpression != null || sQuery.Skip > 0 || sQuery.Take > 0;
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.ResultByManyNavigation || (sQuery.Skip > 0 || sQuery.Take > 0));
IDbQueryable dbQueryable = sQuery.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQuery, token);
SelectCommand cmd = new SelectCommand(this, aliases, token)
{
HasManyNavigation = sQuery.HasManyNavigation
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
(jf as NpgSqlBuilder).IsOuter = isOuter;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useNesting)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.StatisExpression, sQuery.GroupByExpression, alias0);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
(jf as NpgSqlBuilder).IsOuter = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (sQuery.HaveAny)
{
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, sQuery.StatisExpression, sQuery.GroupByExpression);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQuery.HaveDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!sQuery.HaveAny)
{
// SELECT 范围
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery);
visitor2.Write(jf);
cmd.Columns = visitor2.Columns;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (sQuery.SubQueryInfo != null)
{
// 子查询
jf.Append('(');
Command cmd2 = this.ParseSelectCommandImpl <T>(sQuery.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(sQuery.FromType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias0);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Joins);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, sQuery.WhereExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, sQuery.HavingExpression, sQuery.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (sQuery.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBys, sQuery.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
if (sQuery.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take));
}
if (sQuery.Skip > 0)
{
wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip));
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (sQuery.HasManyNavigation && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
{
cmd.Convergence();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQuery.Unions != null && sQuery.Unions.Count > 0)
{
cmd.Convergence();
for (int index = 0; index < sQuery.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
Command cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (sQuery.HaveAny)
{
// 产生 WHERE 子句
cmd.Convergence();
// 如果没有分页，则显式指定只查一笔记录
if (sQuery.Take == 0 && sQuery.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(cmd);
}
// 创建 SELECT 命令
RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select 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.Subquery as IDbQueryableInfo_Select;
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.AliasPrefix) ? (token.AliasPrefix + "0") : "t0";
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));
TableAlias aliases = this.PrepareTableAlias(dbQuery, token);
var result = new MappingCommand(this, aliases, token)
{
HasMany = dbQuery.HasMany
};
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
(jf as NpgSqlBuilder).UseQuote = isOuter;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useNesting)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
visitor_.Write(jf);
result.AddNavMembers(visitor_.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
(jf as NpgSqlBuilder).UseQuote = 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 visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
visitor_.Write(jf);
result.AddNavMembers(visitor_.NavMembers);
}
else
{
// DISTINCT 子句
if (dbQuery.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!dbQuery.HasAny)
{
// SELECT 范围
var visitor_ = new ColumnExpressionVisitor(this, aliases, dbQuery);
visitor_.Write(jf);
result.PickColumns = visitor_.PickColumns;
result.PickColumnText = visitor_.PickColumnText;
result.PickNavDescriptors = visitor_.PickNavDescriptors;
result.AddNavMembers(visitor_.NavMembers);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (dbQuery.Subquery != null)
{
// 子查询
jf.Append('(');
RawCommand cmd2 = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType);
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.Where);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (dbQuery.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
visitor.Write(wf);
result.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
if (dbQuery.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token));
}
if (dbQuery.Skip > 0)
{
wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token));
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
result.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))
{
result.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < dbQuery.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
RawCommand cmd2 = this.ResolveSelectCommandImpl(dbQuery.Unions[index], indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (dbQuery.HasAny)
{
// 产生 WHERE 子句
result.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (dbQuery.Take == 0 && dbQuery.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(result);
}
// 创建 SELECT 命令
DbCommandDefinition ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, List <IDbDataParameter> parameters = null)
{
// 说明：
// 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.如果只有 Skip 没有 Take，则使用 Row_Number() Over()分页语法，其它使用 LIMIT ## OFFSET 语法
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null)
{
sQuery = innerQuery;
}
bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0;
bool useStatis = sQuery.Statis != null;
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0));
IDbQueryable dbQueryable = sQuery.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQuery);
SelectDbCommandDefinition cmd = new SelectDbCommandDefinition(this, aliases, parameters)
{
HaveListNavigation = sQuery.HaveListNavigation
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
ISqlBuilder sf = null;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useNesting)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0");
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 (sQuery.HaveAny)
{
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, sQuery.Statis, sQuery.GroupBy);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQuery.HaveDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!sQuery.HaveAny)
{
// SELECT 范围
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery);
if (sQuery.Skip > 0 && sQuery.Take == 0)
{
sf = this.CreateSqlBuilder(parameters);
sf.Indent = jf.Indent + 1;
visitor2.Write(sf);
}
else
{
visitor2.Write(jf);
}
cmd.Columns = visitor2.Columns;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
if (sf != null)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var entry in cmd.Columns)
{
jf.AppendMember("t0", entry.Key);
jf.AppendAs(entry.Key);
index += 1;
if (index < cmd.Columns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
jf.Append(sf);
jf.Append(',');
jf.AppendNewLine();
if (sQuery.OrderBy.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
jf.Append("ROW_NUMBER() OVER(");
var visitor3 = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy);
visitor3.Write(jf, false);
cmd.AddNavMembers(visitor3.NavMembers);
jf.Append(") Row_Number0");
}
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (sQuery.SubQueryInfo != null)
{
// 子查询
jf.Append("(");
var cmd2 = this.ParseSelectCommandImpl <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") t0 ");
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(" t0 ");
//if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock);
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (sQuery.OrderBy.Count > 0 && useOrderBy)// && !groupByPaging)
{
visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
// LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
// LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
// 初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
// Limit n,-1 语法不支持，使用ROW_Number()语法代替
if (sQuery.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take));
wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip));
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(" ) t0");
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0))
{
cmd.Convergence();
visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQuery.Union != null && sQuery.Union.Count > 0)
{
cmd.Convergence();
for (int index = 0; index < sQuery.Union.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
DbCommandDefinition cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region 分页查询
if (sf != null)
{
// 合并 WHERE
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") t0");
jf.AppendNewLine();
jf.Append("WHERE ");
if (sQuery.Skip > 0)
{
jf.Append("t0.Row_Number0 > ");
jf.Append(jf.GetSqlValue(sQuery.Skip));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (sQuery.HaveAny)
{
// 产生 WHERE 子句
cmd.Convergence();
// 如果没有分页，则显式指定只查一笔记录
if (sQuery.Take == 0 && sQuery.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 0,1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") t0");
}
#endregion
return(cmd);
}
// 解译 SELECT 语句
private DbRawCommand TranslateSelectCommandImpl(DbQuerySelectTree tree, int indent, bool isOutermost, ITranslateContext context)
{
// 说明：
// 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.如果只有 Skip 没有 Take，则使用 Row_Number() Over()分页语法，其它使用 LIMIT ## OFFSET 语法
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
var subquery = tree.Subquery as DbQuerySelectTree;
if (tree.SelectHasMany && subquery != null && subquery.Aggregate != null)
{
tree = subquery;
}
var srcDbExpressionType = context.CurrentExpressionType;
var srcIsOutmost = context.CurrentIsOutermost;
if (srcDbExpressionType == null)
{
context.CurrentExpressionType = DbExpressionType.Select;
}
if (srcIsOutmost == null || !isOutermost)
{
context.CurrentIsOutermost = isOutermost;
}
bool useAggregate = tree.Aggregate != null;
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
// 第一层的表别名
string alias = context != null && !string.IsNullOrEmpty(context.AliasPrefix) ? (context.AliasPrefix + "0") : "t0";
bool useSubquery = tree.HasDistinct || tree.GroupBy != null || tree.Skip > 0 || tree.Take > 0;
bool useOrderBy = (!useAggregate || tree.Skip > 0) && !tree.HasAny && (!tree.SelectHasMany || (tree.Skip > 0 || tree.Take > 0));
AliasGenerator ag = this.PrepareTableAlias(tree, context != null ? context.AliasPrefix : null);
var result = new DbSelectCommand(context, ag, tree.SelectHasMany);
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
ISqlBuilder sf = null;
jf.Indent = indent;
#region 嵌套查询
if (useAggregate && useSubquery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, alias);
visitor.Visit(tree.Aggregate);
result.AddNavMembers(visitor.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
context.CurrentIsOutermost = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (tree.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 (useAggregate && !useSubquery)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor_ = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, null);
visitor_.Visit(tree.Aggregate);
result.AddNavMembers(visitor_.NavMembers);
}
else
{
// DISTINCT 子句
if (tree.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!tree.HasAny)
{
// SELECT 范围
ColumnExpressionVisitor visitor = null;
if (tree.Skip > 0 && tree.Take == 0)
{
sf = this.CreateSqlBuilder(context);
sf.Indent = jf.Indent + 1;
visitor = new ColumnExpressionVisitor(ag, sf, tree);
visitor.Visit(tree.Select);
}
else
{
visitor = new ColumnExpressionVisitor(ag, jf, tree);
visitor.Visit(tree.Select);
}
result.SelectedColumns = visitor.SelectedColumns;
result.SelectedColumnText = visitor.SelectedColumnText;
result.SelectedNavs = visitor.SelectedNavDescriptors;
result.AddNavMembers(visitor.NavMembers);
if (sf != null)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in result.SelectedColumns)
{
jf.AppendMember(alias, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < result.SelectedColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
jf.Append(sf);
jf.Append(',');
jf.AppendNewLine();
if (tree.OrderBys == null || tree.OrderBys.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
jf.Append("ROW_NUMBER() OVER(");
var visitor3 = new OrderByExpressionVisitor(ag, jf, tree.GroupBy, null);
visitor3.Visit(tree.OrderBys, false);
result.AddNavMembers(visitor3.NavMembers);
jf.Append(") Row_Number0");
}
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (tree.Subquery != null)
{
// 子查询
jf.Append("(");
var cmd = this.TranslateSelectCommandImpl(tree.Subquery, indent + 1, false, context);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else if (tree.FromSql != null)
{
if (tree.FromSql.DbContext == null)
{
tree.FromSql.DbContext = context.DbContext;
}
DbRawSql rawSql = tree.FromSql;
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
// 子查询
jf.Append('(');
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(tree.From);
jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
if (tree.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, jf);
visitor.Visit(tree.Joins);
}
wf.Indent = jf.Indent;
// WHERE 子句
if (tree.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, wf);
visitor.Visit(tree.Wheres);
result.AddNavMembers(visitor.NavMembers);
}
// GROUP BY 子句
if (tree.GroupBy != null)
{
var visitor = new GroupByExpressionVisitor(ag, wf);
visitor.Visit(tree.GroupBy);
result.AddNavMembers(visitor.NavMembers);
}
// HAVING 子句
if (tree.Havings != null)
{
var visitor = new HavingExpressionVisitor(ag, wf, tree.GroupBy);
visitor.Visit(tree.Havings);
result.AddNavMembers(visitor.NavMembers);
}
// ORDER 子句
if (tree.OrderBys != null && tree.OrderBys.Count > 0 && useOrderBy)// && !groupByPaging)
{
var visitor = new OrderByExpressionVisitor(ag, wf, tree.GroupBy, null);
visitor.Visit(tree.OrderBys);
result.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
// LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
// LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
// 初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
// Limit n,-1 语法不支持，使用ROW_Number()语法代替
if (tree.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.Constor.GetSqlValue(tree.Take, context));
wf.AppendFormat(" OFFSET {0}", this.Constor.GetSqlValue(tree.Skip, context));
}
#endregion
#region 嵌套查询
if (useAggregate && useSubquery)
{
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
//if (tree.HasMany && subquery != null && subquery.OrderBys.Count > 0 && subquery.Aggregate == null && !(subquery.Skip > 0 || subquery.Take > 0))
if (tree.SelectHasMany && subquery.Aggregate == null &&
subquery != null && subquery.OrderBys != null && subquery.OrderBys.Count > 0 && !(subquery.Skip > 0 || subquery.Take > 0))
{
result.CombineFragments();
var visitor = new OrderByExpressionVisitor(ag, jf, null, null);
visitor.Visit(subquery.OrderBys);
}
#endregion
#region 并集查询
// UNION 子句
if (tree.Unions != null && tree.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < tree.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
DbRawCommand cmd = this.TranslateSelectCommandImpl(tree.Unions[index], indent, isOutermost, context);
jf.Append(cmd.CommandText);
}
}
#endregion
#region 分页查询
if (sf != null)
{
// 合并 WHERE
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.AppendNewLine();
jf.Append("WHERE ");
if (tree.Skip > 0)
{
jf.AppendMember(alias, "Row_Number0");
jf.Append(" > ");
jf.Append(this.Constor.GetSqlValue(tree.Skip, context));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (tree.HasAny)
{
// 产生 WHERE 子句
result.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (tree.Take == 0 && tree.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 0,1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 还原状态
context.CurrentExpressionType = srcDbExpressionType;
context.CurrentIsOutermost = srcIsOutmost;
#endregion
return(result);
}
// 创建 SELECT 命令
protected override CommandDefine ParseSelectCommand <T>(DbQueryableInfo_Select <T> qQuery, int indent = 0)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有统计函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
bool willNest = qQuery.HaveDistinct || qQuery.GroupBy != null || qQuery.Skip > 0 || qQuery.Take > 0;
bool useStatis = qQuery.Statis != null;
bool groupByPaging = qQuery.GroupBy != null && qQuery.Skip > 0; // 分组分页
bool useOrderBy = (!useStatis || qQuery.Skip > 0) && !qQuery.HaveAny; // 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
ExpressionVisitorBase visitor = null;
TableAliasCache aliases = this.PrepareAlias <T>(qQuery);
string statName = string.Empty;
CommandDefine_Select sc = new CommandDefine_Select(this.EscCharLeft, this.EscCharRight, aliases);
SqlBuilder jf = sc.JoinFragment;
SqlBuilder wf = sc.WhereFragment;
if (groupByPaging)
{
indent = indent + 1;
}
jf.Indent = indent;
#region 嵌套查询
if (useStatis && willNest)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
visitor = new StatisExpressionVisitor(this, aliases, qQuery.Statis, qQuery.GroupBy, "t0");
visitor.Write(jf);
statName = (visitor as StatisExpressionVisitor).ColumnName;
sc.AddNavigation(visitor.Navigations);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
}
#endregion
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
if (qQuery.HaveAny)
{
jf.Append("IF EXISTS(");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (useStatis && !willNest)
{
// 如果有统计函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
visitor = new StatisExpressionVisitor(this, aliases, qQuery.Statis, qQuery.GroupBy);
visitor.Write(jf);
sc.AddNavigation(visitor.Navigations);
}
else
{
// DISTINCT 子句
if (qQuery.HaveDistinct)
{
jf.Append("DISTINCT ");
}
// TOP 子句
if (qQuery.Take > 0 && qQuery.Skip == 0)
{
jf.AppendFormat("TOP({0})", qQuery.Take);
}
// Any
if (qQuery.HaveAny)
{
jf.Append("TOP 1 1");
}
#region 择字段
if (!qQuery.HaveAny)
{
// SELECT 范围
visitor = new ColumnExpressionVisitor(this, aliases, qQuery.Expression, qQuery.GroupBy);
visitor.Write(jf);
sc.Columns = (visitor as ColumnExpressionVisitor).Columns;
sc.NavDescriptors = (visitor as ColumnExpressionVisitor).NavDescriptors;
sc.AddNavigation(visitor.Navigations);
// 如果有统计，选择列中还要追加统计的列
if (useStatis && willNest)
{
string columnName = statName;
if (!string.IsNullOrEmpty(columnName) && !sc.Columns.ContainsKey(columnName))
{
if (sc.Columns.Count > 0)
{
jf.Append(",");
}
visitor = new ColumnExpressionVisitor(this, aliases, qQuery.Statis, qQuery.GroupBy, true);
visitor.Write(jf);
sc.Columns.Add(columnName, new Column {
Name = columnName, Duplicate = 1
});
sc.AddNavigation(visitor.Navigations);
}
}
// 如果分组后再分页，此时需要在原先的选择字段上再加上 'OrderBy' 子句指定的字段，外层的分页时需要用到这些排序字段
if (qQuery.OrderBy.Count > 0 && useOrderBy && groupByPaging)
{
if (sc.Columns.Count > 0)
{
jf.Append(",");
}
for (int i = 0; i < qQuery.OrderBy.Count; i++)
{
visitor = new ColumnExpressionVisitor(this, aliases, qQuery.OrderBy[i], qQuery.GroupBy, true);
visitor.Write(jf);
sc.AddNavigation(visitor.Navigations);
if (i < qQuery.OrderBy.Count - 1)
{
jf.AppendNewLine(",");
}
}
}
}
#endregion
}
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (qQuery.Subquery != null)
{
// 子查询
jf.Append("(");
CommandDefine define = this.ParseSelectCommand <T>(qQuery.Subquery as DbQueryableInfo_Select <T>, indent + 1);
jf.Append(define.CommandText);
jf.AppendNewLine();
jf.Append(")");
}
else
{
jf.AppendMember(TypeRuntimeInfoCache.GetRuntimeInfo(qQuery.FromType).TableName);
}
jf.Append(" t0 ");
if (!string.IsNullOrEmpty(DbQueryProvider.NOLOCK))
{
jf.Append(DbQueryProvider.NOLOCK);
}
// LEFT<INNER> JOIN 子句
visitor = new JoinExpressionVisitor(this, aliases, qQuery.Join);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, qQuery.Where);
visitor.Write(wf);
sc.AddNavigation(visitor.Navigations);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, qQuery.GroupBy);
visitor.Write(wf);
sc.AddNavigation(visitor.Navigations);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, qQuery.Having, qQuery.GroupBy);
visitor.Write(wf);
sc.AddNavigation(visitor.Navigations);
// ORDER 子句
if (qQuery.OrderBy.Count > 0 && useOrderBy && !groupByPaging)
{
visitor = new OrderByExpressionVisitor(this, aliases, qQuery.OrderBy, qQuery.GroupBy);
visitor.Write(wf);
sc.AddNavigation(visitor.Navigations);
}
#region 分页查询
if (qQuery.Skip > 0 && !groupByPaging)
{
if (qQuery.OrderBy.Count == 0)
{
throw new XfwException("The method 'OrderBy' must be called before the method 'Skip'.");
}
wf.AppendNewLine();
wf.Append("OFFSET ");
wf.Append(qQuery.Skip);
wf.Append(" ROWS");
if (qQuery.Take > 0)
{
wf.Append(" FETCH NEXT ");
wf.Append(qQuery.Take);
wf.Append(" ROWS ONLY ");
}
}
#endregion
#region 嵌套查询
if (useStatis && willNest)
{
string inner = sc.CommandText;
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(" ) t0");
}
#endregion
#region 分组分页
if (groupByPaging)
{
SqlBuilder builder = new SqlBuilder(this.EscCharLeft, this.EscCharRight);
// SELECT
int index = -1;
builder.Append("SELECT ");
foreach (var kvp in sc.Columns)
{
index += 1;
builder.AppendNewLine();
builder.AppendMember("t0", kvp.Key);
if (index < sc.Columns.Count - 1)
{
builder.Append(",");
}
}
builder.AppendNewLine();
builder.Append("FROM ( ");
string inner = sc.CommandText;
//jf.Replace(Environment.NewLine, Environment.NewLine + SqlBuilder.TAB);
jf.Insert(0, builder);
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(" ) t0");
// 排序
if (qQuery.OrderBy.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, qQuery.OrderBy, null, "t0");
visitor.Write(jf);
}
// 分页
if (qQuery.Skip > 0)
{
jf.AppendNewLine();
jf.Append("OFFSET ");
jf.Append(qQuery.Skip);
jf.Append(" ROWS");
if (qQuery.Take > 0)
{
jf.Append(" FETCH NEXT ");
jf.Append(qQuery.Take);
jf.Append(" ROWS ONLY ");
}
}
}
#endregion
// 'Any' 子句
if (qQuery.HaveAny)
{
string inner = sc.CommandText;
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") SELECT 1 ELSE SELECT 0");
}
// UNION 子句
if (qQuery.Union != null && qQuery.Union.Count > 0)
{
string inner = sc.CommandText;
for (int index = 0; index < qQuery.Union.Count; index++)
{
jf.AppendNewLine();
jf.AppendNewLine("UNION ALL");
CommandDefine define = this.ParseSelectCommand <T>(qQuery.Union[index] as DbQueryableInfo_Select <T>);
jf.Append(define.CommandText);
}
}
return(sc);
}
// 创建 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);
}
// 创建 SELECT 命令
DbRawCommand ResolveSelectCommandImpl(DbQuerySelectTree tree, int indent, bool isOutermost, ITranslateContext context)
{
// 说明：
// 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 = tree.Subquery as DbQuerySelectTree;
if (tree.SelectHasMany && subquery != null && subquery.Aggregate != null)
{
tree = subquery;
}
var srcDbExpressionType = context.CurrentExpressionType;
var srcIsOutermost = context.CurrentIsOutermost;
if (srcDbExpressionType == null)
{
context.CurrentExpressionType = DbExpressionType.Select;
}
if (srcIsOutermost == null || !isOutermost)
{
context.CurrentIsOutermost = isOutermost;
}
bool useAggregate = tree.Aggregate != null;
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
// 第一层的表别名
string alias = context != null && !string.IsNullOrEmpty(context.AliasPrefix) ? (context.AliasPrefix + "0") : "t0";
bool useSubquery = tree.HasDistinct || tree.GroupBy != null || tree.Skip > 0 || tree.Take > 0;
bool useOrderBy = (!useAggregate || tree.Skip > 0) && !tree.HasAny && (!tree.SelectHasMany || (tree.Skip > 0 || tree.Take > 0));
AliasGenerator ag = this.PrepareTableAlias(tree, context != null ? context.AliasPrefix : null);
var result = new DbSelectCommand(context, ag, tree.SelectHasMany);
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
(jf as NpgSqlBuilder).UseQuote = isOutermost;
jf.Indent = indent;
#region 嵌套查询
if (useAggregate && useSubquery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, alias);
visitor.Visit(tree.Aggregate);
result.AddNavMembers(visitor.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
(jf as NpgSqlBuilder).UseQuote = false;
context.CurrentIsOutermost = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (tree.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 (useAggregate && !useSubquery)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, null);
visitor.Visit(tree.Aggregate);
result.AddNavMembers(visitor.NavMembers);
}
else
{
// DISTINCT 子句
if (tree.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!tree.HasAny)
{
// SELECT 范围
var visitor = new ColumnExpressionVisitor(ag, jf, tree);
visitor.Visit(tree.Select);
result.SelectedColumns = visitor.SelectedColumns;
result.SelectedColumnText = visitor.SelectedColumnText;
result.SelectedNavs = visitor.SelectedNavDescriptors;
result.AddNavMembers(visitor.NavMembers);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (tree.Subquery != null)
{
// 子查询
jf.Append('(');
DbRawCommand cmd2 = this.ResolveSelectCommandImpl(tree.Subquery, indent + 1, false, context);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else if (tree.FromSql != null)
{
if (tree.FromSql.DbContext == null)
{
tree.FromSql.DbContext = context.DbContext;
}
DbRawSql rawSql = tree.FromSql;
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
// 子查询
jf.Append('(');
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(tree.From);
jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
if (tree.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, jf);
visitor.Visit(tree.Joins);
}
wf.Indent = jf.Indent;
// WHERE 子句
if (tree.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, wf);
visitor.Visit(tree.Wheres);
result.AddNavMembers(visitor.NavMembers);
}
// GROUP BY 子句
if (tree.GroupBy != null)
{
var visitor = new GroupByExpressionVisitor(ag, wf);
visitor.Visit(tree.GroupBy);
result.AddNavMembers(visitor.NavMembers);
}
// HAVING 子句
if (tree.Havings != null)
{
var visitor = new HavingExpressionVisitor(ag, wf, tree.GroupBy);
visitor.Visit(tree.Havings);
result.AddNavMembers(visitor.NavMembers);
}
// ORDER 子句
if (tree.OrderBys != null && tree.OrderBys.Count > 0 && useOrderBy)
{
var visitor = new OrderByExpressionVisitor(ag, wf, tree.GroupBy, null);
visitor.Visit(tree.OrderBys);
result.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
if (tree.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.Constor.GetSqlValue(tree.Take, context));
}
if (tree.Skip > 0)
{
wf.AppendFormat(" OFFSET {0}", this.Constor.GetSqlValue(tree.Skip, context));
}
#endregion
#region 嵌套查询
if (useAggregate && useSubquery)
{
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (tree.SelectHasMany && subquery.Aggregate == null &&
subquery != null && subquery.OrderBys != null && subquery.OrderBys.Count > 0 && !(subquery.Skip > 0 || subquery.Take > 0))
{
result.CombineFragments();
var visitor = new OrderByExpressionVisitor(ag, jf, null, null);
visitor.Visit(subquery.OrderBys);
}
#endregion
#region 并集查询
// UNION 子句
if (tree.Unions != null && tree.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < tree.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
DbRawCommand cmd2 = this.ResolveSelectCommandImpl(tree.Unions[index], indent, isOutermost, context);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (tree.HasAny)
{
// 产生 WHERE 子句
result.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (tree.Take == 0 && tree.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 还原状态
context.CurrentExpressionType = srcDbExpressionType;
context.CurrentIsOutermost = srcIsOutermost;
#endregion
return(result);
}
