// 创建 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 关系的，需要使用嵌套查询，否则分页查询会有问题
// 导航属性中有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;
// 分组分页
bool groupByPaging = sQuery.GroupBy != null && sQuery.Skip > 0;
// 没有统计函数或者使用 '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;
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();
}
if (sQuery.HaveAny)
{
jf.Append("IF EXISTS(");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
}
jf.Append("SELECT ");
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 ");
}
// TOP 子句
if (sQuery.Take > 0 && sQuery.Skip == 0)
{
jf.AppendFormat("TOP({0})", jf.GetSqlValue(sQuery.Take));
}
// Any
if (sQuery.HaveAny)
{
jf.Append("TOP 1 1");
}
#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('(');
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 ");
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, 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 分页查询
if (sQuery.Skip > 0)
{
if (sQuery.OrderBy.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
wf.AppendNewLine();
wf.Append("OFFSET ");
wf.Append(wf.GetSqlValue(sQuery.Skip));
wf.Append(" ROWS");
if (sQuery.Take > 0)
{
wf.Append(" FETCH NEXT ");
wf.Append(wf.GetSqlValue(sQuery.Take));
wf.Append(" ROWS ONLY ");
}
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.Convergence();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(" ) t0");
}
#endregion
#region 嵌套导航
if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0))
{
// TODO Include 从表，没分页，OrderBy 报错
// 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 Any 子句
// 'Any' 子句
if (sQuery.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);
}
// 创建 UPDATE 命令
protected override DbCommandDefinition ParseUpdateCommand <T>(DbQueryableInfo_Update <T> uQuery, List <IDbDataParameter> parameters = null)
{
ISqlBuilder builder = this.CreateSqlBuilder(parameters);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("UPDATE ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" t0 SET");
builder.AppendNewLine();
if (uQuery.Entity != null)
{
object entity = uQuery.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(builder.Parameters);
bool useKey = false;
int length = 0;
foreach (var kv in typeRuntime.Invokers)
{
MemberInvokerBase invoker = kv.Value;
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 = builder.GetSqlValueWidthDefault(value, 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 kv in typeRuntime.KeyInvokers)
{
MemberInvokerBase invoker = kv.Value;
var column = invoker.Column;
var value = invoker.Invoke(entity);
var seg = builder.GetSqlValueWidthDefault(value, 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 (uQuery.Expression != null)
{
TableAliasCache aliases = this.PrepareAlias <T>(uQuery.SelectInfo);
ExpressionVisitorBase visitor = null;
visitor = new OracleUpdateExpressionVisitor(this, aliases, uQuery.Expression);
visitor.Write(builder);
var cmd2 = new OracleDbCommandDefinition_Delete(this, aliases, builder.Parameters);
cmd2.HaveListNavigation = uQuery.SelectInfo.HaveListNavigation;
var visitor0 = new OracleJoinExpressionVisitor(this, aliases, uQuery.SelectInfo.Join, uQuery.SelectInfo.Where);
visitor0.Write(cmd2);
var visitor1 = new OracleWhereExpressionVisitor(this, aliases, uQuery.SelectInfo.Where);
visitor1.Write(cmd2.WhereFragment);
cmd2.AddNavMembers(visitor1.NavMembers);
builder.Append(cmd2.CommandText);
}
builder.Append(';');
return(new DbCommandDefinition(builder.ToString(), builder.Parameters, System.Data.CommandType.Text));
}
// 添加导航属性关联
protected virtual void AppendNavigation()
{
if (this._navMembers == null || this._navMembers.Count == 0)
{
return;
}
// 如果有一对多的导航属性，肯定会产生嵌套查询。那么内层查询别名肯定是t0，所以需要清掉
if (this.HasManyNavigation)
{
_aliases = new TableAliasCache(_aliases.Declared);
}
//开始产生LEFT JOIN 子句
ISqlBuilder builder = this.JoinFragment;
foreach (var kvp in _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 (_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);
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 ");
}
}
}
}
/// <summary>
/// 创建 INSRT 命令
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
TableAlias aliases = new TableAlias();
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
var caseSensitive = token != null && token.DbContext != null ? ((NpgDbContext)token.DbContext).CaseSensitive : false;
if (dbQuery.Entity != null)
{
object entity = dbQuery.Entity;
ISqlBuilder seg_Columns = this.CreateSqlBuilder(token);
ISqlBuilder seg_Values = this.CreateSqlBuilder(token);
// 指定插入列
MemberAccessorCollection memberAccessors = typeRuntime.Members;
if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
{
memberAccessors = new MemberAccessorCollection();
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]);
}
var member = curExpr as MemberExpression;
string name = member.Member.Name;
memberAccessors[name] = typeRuntime.Members[name];
}
}
foreach (var m in memberAccessors)
{
var column = m.Column;
if (column != null && column.NoMapped)
{
continue;
}
if (m.ForeignKey != null)
{
continue;
}
if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
if (m != typeRuntime.Identity)
{
seg_Columns.AppendMember(m.Member.Name);
seg_Columns.Append(',');
var value = m.Invoke(entity);
string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
seg_Values.Append(seg);
seg_Values.Append(',');
}
}
seg_Columns.Length -= 1;
seg_Values.Length -= 1;
if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(seg_Columns);
builder.Append(')');
builder.AppendNewLine();
builder.AppendTab();
builder.Append("VALUES");
}
builder.Append('(');
builder.Append(seg_Values);
builder.Append(')');
if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos)
{
builder.Append(",");
}
if (dbQuery.Bulk == null && typeRuntime.Identity != null)
{
builder.Append(';');
builder.AppendNewLine();
// sequence，命名原则是 tablename_columnname_seq.
builder.AppendFormat("SELECT CURRVAL('{2}{0}_{1}_seq{2}')", typeRuntime.TableName, typeRuntime.Identity.Member.Name, caseSensitive ? "\"" : string.Empty);
builder.Append(" AS ");
builder.Append(this.QuotePrefix);
builder.Append(Constant.AUTO_INCREMENT_NAME);
builder.Append(this.QuoteSuffix);
}
}
else if (dbQuery.Query != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
MappingCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Query, 0, false, token) as MappingCommand;
//for (int i = 0; i < seg.Columns.Count; i++)
foreach (var column in cmd.PickColumns)
{
builder.AppendMember(column.Name);
if (i < cmd.PickColumns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd.CommandText);
}
if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos)
{
builder.Append(';');
}
return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 DELETE 命令
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected override RawCommand ResolveDeleteCommand <T>(IDbQueryableInfo_Delete dbQuery, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("DELETE FROM ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" t0 ");
if (dbQuery.Entity != null)
{
if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
{
throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
}
object entity = dbQuery.Entity;
builder.AppendNewLine();
builder.Append("WHERE ");
foreach (var m in typeRuntime.KeyMembers)
{
var column = m.Column;
var value = m.Invoke(entity);
var seg = this.DbValue.GetSqlValue(value, token, column);
builder.AppendMember("t0", m.Member.Name);
builder.Append(" = ");
builder.Append(seg);
builder.Append(" AND ");
}
builder.Length -= 5;
}
else if (dbQuery.Query != null)
{
// 解析查询用来确定是否需要嵌套
var cmd = this.ResolveSelectCommand(dbQuery.Query, 1, false, null) as MappingCommand;
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0)
{
// 最外层仅选择 RowID 列
var outQuery = dbQuery.Query;
outQuery.Select = new DbExpression(DbExpressionType.Select, Expression.Constant("t0.RowId", typeof(string)));
var iterator = outQuery;
while (iterator.Subquery != null)
{
var subQuery = new OracleDbQueryableInfo_Select(iterator.Subquery);
iterator.Subquery = subQuery;
iterator = subQuery;
}
// 解析成 RowId IN 结构
cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, token);
builder.Append("WHERE t0.RowId IN(");
builder.AppendNewLine(cmd.CommandText);
builder.Append(')');
}
else
{
TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token);
var visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Query.Joins);
visitor.Write(builder);
var visitor_ = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where);
visitor_.Write(builder);
}
}
builder.Append(';');
return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
public override Expression Visit(Expression node)
{
if (node == null)
{
return(node);
}
var propertyExpression = (node as ConstantExpression)?.Value as PropertyExpression;
if (propertyExpression == null)
{
return(base.Visit(node));
}
// Group By 解析 ?? 未测试
if (_groupBy != null && node.IsGrouping())
{
string memberName = propertyExpression.PropertyName;
// CompanyName = g.Key.Name
LambdaExpression keySelector = _groupBy.Expressions[0] as LambdaExpression;
Expression exp = null;
Expression body = keySelector.Body;
if (body.NodeType == ExpressionType.MemberAccess)
{
// group xx by a.CompanyName
exp = body;
//
//
//
//
}
else if (body.NodeType == ExpressionType.New)
{
// group xx by new { Name = a.CompanyName }
NewExpression newExpression = body as NewExpression;
int index = newExpression.Members.IndexOf(x => x.Name == memberName);
exp = newExpression.Arguments[index];
}
else if (body.NodeType == ExpressionType.MemberInit)
{
// group xx by new App { Name = a.CompanyName }
MemberInitExpression initExpression = body as MemberInitExpression;
int index = initExpression.Bindings.IndexOf(x => x.Member.Name == memberName);
exp = ((MemberAssignment)initExpression.Bindings[index]).Expression;
}
return(this.Visit(exp));
}
string alias = _ag.GetTableAlias(propertyExpression.Parameter);
if (!string.IsNullOrEmpty(_alias))
{
alias = _alias;
}
_builder.AppendMember(alias, propertyExpression.PropertyName);
//// 嵌套
//if (!string.IsNullOrEmpty(_alias))
// _builder.AppendMember(_alias, node.Member, node.Expression.Type);
//else
// base.VisitMember(node);
return(node);
}
/// <summary>
/// 创建 DELETE 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateDeleteCommand <T>(DbQueryDeleteTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("DELETE t0 FROM ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append(" t0 ");
if (tree.Entity != null)
{
if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
{
throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
}
object entity = tree.Entity;
builder.AppendNewLine();
builder.Append("WHERE ");
foreach (FieldAccessorBase m in typeRuntime.KeyMembers)
{
var value = m.Invoke(entity);
var sqlExpression = this.Constor.GetSqlValue(value, context, m.Column);
builder.AppendMember("t0", m.Member, typeRuntime.Type);
builder.Append(" = ");
builder.Append(sqlExpression);
builder.Append(" AND ");
}
builder.Length -= 5;
}
else if (tree.Select != null)
{
AliasGenerator ag = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
var cmd = new DbSelectCommand(context, ag, tree.Select.SelectHasMany);
// 标记当前解析上下文是删除语句产生的
var isDelete = ((MySqlTranslateContext)context).IsDelete;
((MySqlTranslateContext)context).IsDelete = true;
if (tree.Select.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, cmd.JoinFragment);
visitor.Visit(tree.Select.Joins);
}
if (tree.Select.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, cmd.WhereFragment);
visitor.Visit(tree.Select.Wheres);
cmd.AddNavMembers(visitor.NavMembers);
}
builder.Append(cmd.CommandText);
// 恢复当前解析上下文的删除标记
((MySqlTranslateContext)context).IsDelete = isDelete;
}
builder.Append(';');
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 UPDATE 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateUpdateCommand <T>(DbQueryUpdateTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("UPDATE ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append(" t0");
if (tree.Entity != null)
{
object entity = tree.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(context);
bool useKey = false;
int length = 0;
builder.AppendNewLine(" SET");
foreach (var item in typeRuntime.Members)
{
var m = item as FieldAccessorBase;
if (m == null || !m.IsDbField)
{
continue;
}
if (m.Column != null && m.Column.IsIdentity)
{
goto LABEL; // Fix issue# 自增列同时又是主键
}
builder.AppendMember("t0", m.Member, typeRuntime.Type);
builder.Append(" = ");
LABEL:
var value = m.Invoke(entity);
var seg = this.Constor.GetSqlValueWidthDefault(value, context, m.Column);
if (m.Column == null || !m.Column.IsIdentity)
{
builder.Append(seg);
length = builder.Length;
builder.Append(',');
builder.AppendNewLine();
}
if (m.Column != null && m.Column.IsKey)
{
useKey = true;
whereBuilder.AppendMember("t0", m.Member, typeRuntime.Type);
whereBuilder.Append(" = ");
whereBuilder.Append(seg);
whereBuilder.Append(" AND ");
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require T must have key column.");
}
builder.Length = length;
whereBuilder.Length -= 5;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (tree.Expression != null)
{
AliasGenerator ag = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
DbExpressionVisitor visitor = null;
var cmd = new DbSelectCommand(context, ag, tree.Select.SelectHasMany);
if (tree.Select.Joins != null)
{
visitor = new JoinExpressionVisitor(ag, cmd.JoinFragment);
visitor.Visit(tree.Select.Joins);
}
cmd.WhereFragment.AppendNewLine();
cmd.WhereFragment.AppendNewLine("SET");
visitor = new UpdateExpressionVisitor(ag, cmd.WhereFragment);
visitor.Visit(tree.Expression);
if (tree.Select.Wheres != null)
{
visitor = new WhereExpressionVisitor(ag, cmd.WhereFragment);
visitor.Visit(tree.Select.Wheres);
cmd.AddNavMembers(visitor.NavMembers);
}
builder.Append(cmd.CommandText);
}
builder.Append(';');
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
// 解译 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);
}
/// <summary>
/// 创建 INSERT 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
if (tree.Entity != null)
{
object entity = tree.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context);
// 指定插入列
var members = typeRuntime.Members;
if (tree.EntityColumns != null && tree.EntityColumns.Count > 0)
{
members = new MemberAccessorCollection();
for (int i = 0; i < tree.EntityColumns.Count; i++)
{
Expression curExpr = tree.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}", tree.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
members[name] = typeRuntime.Members[name];
}
}
foreach (var item in members)
{
var m = item as FieldAccessorBase;
if (m == null || !m.IsDbField)
{
continue;
}
if (m != typeRuntime.Identity)
{
columnsBuilder.AppendMember(null, m.Member, typeRuntime.Type);
columnsBuilder.Append(',');
var value = m.Invoke(entity);
string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column);
valuesBuilder.Append(sqlExpression);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (tree.Bulk == null || !tree.Bulk.OnlyValue)
{
builder.Append("INSERT INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(columnsBuilder);
builder.Append(')');
builder.AppendNewLine();
builder.AppendTab();
builder.Append("VALUES");
}
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (tree.Bulk != null && !tree.Bulk.IsEndPos)
{
builder.Append(",");
}
if (tree.Bulk == null && typeRuntime.Identity != null)
{
builder.Append(';');
builder.AppendNewLine();
builder.Append("SELECT LAST_INSERT_ID()");
builder.AppendAs(AppConst.AUTO_INCREMENT_NAME);
}
}
else if (tree.Select != null)
{
builder.Append("INSERT INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append('(');
var srcExpressionType = context.CurrentExpressionType;
var srcIsOutermost = context.CurrentIsOutermost;
context.CurrentExpressionType = DbExpressionType.Insert;
context.CurrentIsOutermost = true;
var cmd = this.TranslateSelectCommandImpl(tree.Select, 0, true, context) as DbSelectCommand;
context.CurrentExpressionType = srcExpressionType;
context.CurrentIsOutermost = srcIsOutermost;
int index = 0;
foreach (var column in cmd.SelectedColumns)
{
builder.AppendMember(column.NewName);
if (index < cmd.SelectedColumns.Count - 1)
{
builder.Append(',');
}
index++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd.CommandText);
}
if (tree.Bulk == null || tree.Bulk.IsEndPos)
{
builder.Append(';');
}
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 添加导航属性关联
/// </summary>
protected override void TanslateNavMember()
{
if (base.NavMembers == null || base.NavMembers.Count == 0)
{
return;
}
// 如果有一对多的导航属性，肯定会产生嵌套查询。那么内层查询别名肯定是t0，所以需要清掉
if (this.HasMany)
{
_ag = new AliasGenerator(_ag.ReserveQty);
}
//开始产生LEFT JOIN 子句
ISqlBuilder builder = this.JoinFragment;
foreach (var nav in base.NavMembers)
{
string key = nav.Key;
MemberExpression m = nav.Expression;
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(m.Expression.Type);
ForeignKeyAttribute attribute = typeRuntime.GetMemberAttribute <ForeignKeyAttribute>(m.Member.Name);
string innerKey = string.Empty;
string outerKey = key;
string innerAlias = string.Empty;
if (!m.Expression.Visitable())
{
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).TableFullName;
innerAlias = _ag.GetJoinTableAlias(name);
if (string.IsNullOrEmpty(innerAlias))
{
string keyLeft = mLeft.GetKeyWidthoutAnonymous();
if (base.NavMembers.Contains(keyLeft))
{
innerKey = keyLeft;
}
innerAlias = _ag.GetNavTableAlias(innerKey);
}
}
string alias1 = !string.IsNullOrEmpty(innerAlias) ? innerAlias : _ag.GetTableAlias(innerKey);
string alias2 = _ag.GetNavTableAlias(outerKey);
builder.AppendNewLine();
builder.Append("LEFT JOIN ");
Type type = m.Type;
if (type.IsGenericType)
{
type = type.GetGenericArguments()[0];
}
var typeRuntime2 = TypeRuntimeInfoCache.GetRuntimeInfo(type);
builder.AppendTable(typeRuntime2.TableSchema, typeRuntime2.TableFullName, typeRuntime2.IsTemporary);
builder.Append(" ");
builder.Append(alias2);
bool withNoLock = !typeRuntime2.IsTemporary && _isNoLock && !string.IsNullOrEmpty(_withNoLock);
if (withNoLock)
{
builder.Append(' ');
builder.Append(_withNoLock);
}
builder.Append(" ON ");
for (int i = 0; i < attribute.InnerKeys.Length; i++)
{
if (attribute.InnerKeys[i].StartsWith(AppConst.CONSTANT_FOREIGNKEY, StringComparison.Ordinal))
{
builder.Append(attribute.InnerKeys[i].Substring(7));
}
else
{
builder.Append(alias1);
builder.Append('.');
builder.AppendMember(attribute.InnerKeys[i]);
}
builder.Append(" = ");
if (attribute.OuterKeys[i].StartsWith(AppConst.CONSTANT_FOREIGNKEY, StringComparison.Ordinal))
{
builder.Append(attribute.OuterKeys[i].Substring(7));
}
else
{
builder.Append(alias2);
builder.Append('.');
builder.AppendMember(attribute.OuterKeys[i]);
}
if (i < attribute.InnerKeys.Length - 1)
{
builder.Append(" AND ");
}
}
if (nav.Predicate != null)
{
string alias = _ag.GetNavTableAlias(nav.Key);
var visitor = new NavPredicateExpressionVisitor(_ag, builder, alias);
visitor.Visit(nav.Predicate);
}
}
}
// 添加导航属性关联
protected override void AppendNavigation()
{
if (this.NavMembers == null || this.NavMembers.Count == 0)
{
return;
}
// 如果有一对多的导航属性，肯定会产生嵌套查询。那么内层查询别名肯定是t0，所以需要清掉
if (this.HasMany)
{
_aliases = new TableAliasCache(_aliases.Declared);
}
//开始产生 USING 子句
ISqlBuilder jf = this.JoinFragment;
int index = -1;
// 未生成USING子句
if (_aliases.Declared <= 1)
{
jf.AppendNewLine();
jf.Append(_keywordName);
}
else
{
jf.Append(',');
jf.AppendNewLine();
}
foreach (var kvp in this.NavMembers)
{
index++;
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 (this.NavMembers.ContainsKey(keyLeft))
{
innerKey = keyLeft;
}
innerAlias = _aliases.GetNavigationTableAlias(innerKey);
}
}
string alias1 = !string.IsNullOrEmpty(innerAlias) ? innerAlias : _aliases.GetTableAlias(innerKey);
string alias2 = _aliases.GetNavigationTableAlias(outerKey);
// 补充与USING字符串同等间距的空白
if (_aliases.Declared > 1 || index > 0)
{
jf.Append(_pad);
}
Type type = m.Type;
var typeRumtime2 = TypeRuntimeInfoCache.GetRuntimeInfo(type);
if (type.IsGenericType)
{
type = type.GetGenericArguments()[0];
}
jf.AppendMember(typeRumtime2.TableName, typeRumtime2.IsTemporary);
jf.Append(' ');
jf.Append(alias2);
if (_onPhrase.Length > 0)
{
_onPhrase.Append(" AND ");
}
for (int i = 0; i < attribute.InnerKeys.Length; i++)
{
_onPhrase.Append(alias1);
_onPhrase.Append('.');
_onPhrase.AppendMember(attribute.InnerKeys[i]);
_onPhrase.Append(" = ");
_onPhrase.Append(alias2);
_onPhrase.Append('.');
_onPhrase.AppendMember(attribute.OuterKeys[i]);
}
if (index < this.NavMembers.Count - 1)
{
jf.Append(',');
jf.AppendNewLine();
}
}
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder on, DbExpression dbExpression, TableAliasCache aliases)
{
IDbQueryable sQuery = (IDbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
if (!usedKeyword)
{
jf.AppendNewLine();
jf.Append(_keywordName);
usedKeyword = true;
}
else
{
jf.Append(',');
jf.AppendNewLine();
jf.Append(_pad);
}
if (sQuery.DbExpressions.Count == 1 && sQuery.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
}
else
{
// 嵌套
var cmd = sQuery.Resolve(jf.Indent + _dbExpressionType == DbExpressionType.Delete ? 2 : 1, false, jf.Token);
jf.Append("( ");
jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText);
jf.Append(')');
}
LambdaExpression left = dbExpression.Expressions[1] as LambdaExpression;
LambdaExpression 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]);
jf.Append(' ');
jf.Append(alias);
if (on.Length > 0)
{
on.Append(" AND ");
}
if (left.Body.NodeType == ExpressionType.New)
{
NewExpression body1 = left.Body as NewExpression;
NewExpression body2 = right.Body as NewExpression;
for (int index = 0; index < body1.Arguments.Count; ++index)
{
on.AppendMember(aliases, body1.Arguments[index]);
on.Append(" = ");
on.AppendMember(aliases, body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
on.Append(" AND ");
}
}
}
else if (left.Body.NodeType == ExpressionType.MemberInit)
{
MemberInitExpression body1 = left.Body as MemberInitExpression;
MemberInitExpression body2 = right.Body as MemberInitExpression;
for (int index = 0; index < body1.Bindings.Count; ++index)
{
on.AppendMember(aliases, (body1.Bindings[index] as MemberAssignment).Expression);
on.Append(" = ");
on.AppendMember(aliases, (body2.Bindings[index] as MemberAssignment).Expression);
if (index < body1.Bindings.Count - 1)
{
on.Append(" AND ");
}
}
}
else
{
on.AppendMember(aliases, left.Body.ReduceUnary());
on.Append(" = ");
on.AppendMember(aliases, right.Body.ReduceUnary());
}
}
// 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)
{
useExists = true;
if ((wf != null && wf.Length > 0) || (on != null && on.Length > 0))
{
wf.AppendNewLine();
if (wf != null && wf.Length > 0)
{
wf.Append("AND ");
}
}
wf.Append("EXISTS(");
wf.Indent += 1;
wf.AppendNewLine();
wf.Append("SELECT 1 FROM ");
Type type = exp.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
wf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
wf.Append(' ');
wf.Append(alias);
wf.AppendNewLine();
wf.Append("WHERE ");
}
else
{
useExists = true;
if ((wf != null && wf.Length > 0) || (on != null && on.Length > 0))
{
wf.AppendNewLine();
if (wf != null && wf.Length > 0)
{
wf.Append("AND ");
}
}
wf.Append("EXISTS(");
wf.Indent += 1;
wf.AppendNewLine();
wf.Append("SELECT 1 FROM(");
var cmd2 = sQuery.Resolve(wf.Indent + 1, false, wf.Parameters);
wf.Append(cmd2.CommandText);
wf.AppendNewLine();
wf.Append(')');
wf.Append(' ');
wf.Append(alias);
wf.Append(" WHERE ");
}
ISqlBuilder builder = useExists ? wf : on;
if (body1 == null)
{
builder.AppendMember(aliases, left.Body.ReduceUnary());
builder.Append(" = ");
builder.AppendMember(aliases, right.Body.ReduceUnary());
}
else
{
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 ");
}
}
}
//if (_where != null && _where.Expressions != null) this.AppendWhere(builder, right);
if (useExists)
{
wf.Indent -= 1;
wf.AppendNewLine();
wf.Append(')');
}
}
// 创建 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);
}
/// <summary>
/// 创建 INSRT 命令
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token)
{
ISqlBuilder builder = this.CreateSqlBuilder(token);
TableAlias aliases = new TableAlias();
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
if (dbQuery.Entity != null)
{
// 如果没有Sequence列，使用 INSERT ALL INTO 语法，否则就一条一条逐行写入~~
// 批量 INSERT，自增列不会自动赋值
object entity = dbQuery.Entity;
ISqlBuilder seg_Columns = this.CreateSqlBuilder(token);
ISqlBuilder seg_Values = this.CreateSqlBuilder(token);
// 指定插入列
MemberAccessorCollection memberAccessors = typeRuntime.Members;
if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
{
memberAccessors = new MemberAccessorCollection();
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;
memberAccessors[name] = typeRuntime.Members[name];
}
}
// 自增列参数
IDbDataParameter seqParameter = null;
// 自增列标记
ColumnAttribute seqColumn = null;
foreach (var m in memberAccessors)
{
var column = m.Column;
if (column != null && column.NoMapped)
{
continue;
}
if (m.ForeignKey != null)
{
continue;
}
if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
seg_Columns.AppendMember(m.Member.Name);
seg_Columns.Append(',');
if (m == typeRuntime.Identity)
{
seqColumn = column;
if (dbQuery.Bulk == null)
{
// 非批量INSERT，产生一个 OUTPUT 类型的参数
string pName = string.Format("{0}p{1}", this.ParameterPrefix, token.Parameters.Count);
var database = token.DbContext.Database;
seqParameter = database.CreateParameter(pName, -1, direction: ParameterDirection.Output);
token.Parameters.Add(seqParameter);
seg_Values.Append(seqParameter.ParameterName);
seg_Values.Append(',');
}
else
{
seg_Values.Append(((OracleColumnAttribute)column).SEQName);
seg_Values.Append(".NEXTVAL");
seg_Values.Append(',');
}
}
else
{
var value = m.Invoke(entity);
string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
seg_Values.Append(seg);
seg_Values.Append(',');
}
}
seg_Columns.Length -= 1;
seg_Values.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(seg_Columns);
builder.Append(')');
builder.AppendNewLine();
builder.AppendTab();
builder.Append("VALUES");
builder.Append('(');
builder.Append(seg_Values);
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.Query != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
MappingCommand cmd = this.ResolveSelectCommand(dbQuery.Query, 0, false, token) as MappingCommand;
foreach (var column in cmd.PickColumns)
{
builder.AppendMember(column.Name);
if (i < cmd.PickColumns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd.CommandText);
builder.Append(';');
}
var result = new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text);
return(result);
}
// 创建 INSRT 命令
protected override DbCommandDefinition ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQuery, List <IDbDataParameter> parameters = null)
{
ISqlBuilder builder = this.CreateSqlBuilder(parameters);
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
TableAliasCache aliases = new TableAliasCache();
if (nQuery.Entity != null)
{
object entity = nQuery.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(builder.Parameters);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(builder.Parameters);
// 指定插入列
Dictionary <string, MemberInvokerBase> invokers = typeRuntime.Invokers;
if (nQuery.EntityColumns != null && nQuery.EntityColumns.Count > 0)
{
invokers = new Dictionary <string, MemberInvokerBase>();
for (int i = 0; i < nQuery.EntityColumns.Count; i++)
{
Expression curExpr = nQuery.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}", nQuery.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
invokers[name] = typeRuntime.Invokers[name];
}
}
foreach (var kv in invokers)
{
MemberInvokerBase invoker = kv.Value;
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 != nQuery.AutoIncrement)
{
columnsBuilder.AppendMember(invoker.Member.Name);
columnsBuilder.Append(',');
var value = invoker.Invoke(entity);
string seg = builder.GetSqlValueWidthDefault(value, column);
valuesBuilder.Append(seg);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (nQuery.Bulk == null || !nQuery.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");
}
if (nQuery.Bulk != null && nQuery.Bulk.OnlyValue)
{
builder.AppendNewTab();
}
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (nQuery.Bulk != null && !nQuery.Bulk.IsEndPos)
{
builder.Append(",");
builder.AppendNewLine();
}
if (nQuery.Bulk == null && nQuery.AutoIncrement != null)
{
builder.Append(';');
builder.AppendNewLine();
builder.Append("SELECT LAST_INSERT_ID()");
builder.AppendAs(Constant.AUTOINCREMENTNAME);
}
}
else if (nQuery.SelectInfo != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
SelectDbCommandDefinition cmd2 = this.ParseSelectCommandImpl(nQuery.SelectInfo, 0, true, builder.Parameters) as SelectDbCommandDefinition;
foreach (var kvp in cmd2.Columns)
{
builder.AppendMember(kvp.Key);
if (i < cmd2.Columns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd2.CommandText);
}
if (nQuery.Bulk == null || nQuery.Bulk.IsEndPos)
{
builder.Append(';');
}
return(new DbCommandDefinition(builder.ToString(), builder.Parameters, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 UPDATE 命令
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update 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 seg_Where = this.CreateSqlBuilder(token);
bool useKey = false;
int length = 0;
foreach (var m in typeRuntime.Members)
{
var column = m.Column;
if (column != null && column.IsIdentity)
{
goto gotoLabel; // Fix issue# 自增列同时又是主键
}
if (column != null && column.NoMapped)
{
continue;
}
if (m.ForeignKey != null)
{
continue;
}
if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
builder.AppendMember(m.Member.Name);
builder.Append(" = ");
gotoLabel:
if (column == null || !column.IsIdentity)
{
var value = m.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 m in typeRuntime.KeyMembers)
{
var column = m.Column;
var value = m.Invoke(entity);
var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);
index += 1;
seg_Where.AppendMember(m.Member.Name);
seg_Where.Append(" = ");
seg_Where.Append(seg);
if (index < typeRuntime.KeyMembers.Count - 1)
{
seg_Where.Append(" AND ");
}
}
builder.Length = length;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(seg_Where);
}
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 @init = body as MemberInitExpression;
var bindings = new List <MemberBinding>(@init.Bindings);
foreach (var m in typeRuntime.KeyMembers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
var binding = Expression.Bind(m.Member, member);
if (!bindings.Any(x => x.Member == m.Member))
{
bindings.Add(binding);
}
}
expression = Expression.MemberInit(@init.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 m = typeRuntime.GetMember(newExpression.Members[i].Name);
var binding = Expression.Bind(m.Member, newExpression.Arguments[i].Type != m.DataType
? Expression.Convert(newExpression.Arguments[i], m.DataType)
: newExpression.Arguments[i]);
bindings.Add(binding);
}
foreach (var m in typeRuntime.KeyMembers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
var binding = Expression.Bind(m.Member, member);
if (!bindings.Any(x => x.Member == m.Member))
{
bindings.Add(binding);
}
}
var newExpression2 = Expression.New(typeRuntime.Constructor.Constructor);
expression = Expression.MemberInit(newExpression2, bindings);
}
// 解析查询以确定是否需要嵌套
dbQuery.Query.Select = new DbExpression(DbExpressionType.Select, expression);
var cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, null);//, token);
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0)
{
// 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。UPDATE 不能用rowid
// 有导航属性或者关联查询，使用 MERGE INTO 语法。要求必须有主键
if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.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 = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, token);
builder.AppendNewLine(cmd.CommandText);
builder.Append(") t1 ON (");
foreach (var m in typeRuntime.KeyMembers)
{
builder.AppendMember("t0", m.Name);
builder.Append(" = ");
builder.AppendMember("t1", m.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 语法
TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token);
var visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression);
visitor.Write(builder);
var visitor_ = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where);
visitor_.Write(builder);
}
}
builder.Append(';');
return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
// 创建 DELETE 命令
protected override DbCommandDefinition ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQuery, List <IDbDataParameter> parameters = null)
{
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
ISqlBuilder builder = this.CreateSqlBuilder(parameters);
bool useKey = false;
builder.Append("DELETE t0 FROM ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append(" t0 ");
if (dQuery.Entity != null)
{
object entity = dQuery.Entity;
builder.AppendNewLine();
builder.Append("WHERE ");
foreach (var kv in typeRuntime.Invokers)
{
MemberInvokerBase invoker = kv.Value;
var column = invoker.Column;
if (column != null && column.IsKey)
{
useKey = true;
var value = invoker.Invoke(entity);
var seg = builder.GetSqlValue(value, column);
builder.AppendMember("t0", invoker.Member.Name);
builder.Append(" = ");
builder.Append(seg);
builder.Append(" AND ");
}
;
}
builder.Length -= 5;
if (!useKey)
{
throw new XFrameworkException("Delete<T>(T value) require T must have key column.");
}
}
else if (dQuery.SelectInfo != null)
{
TableAliasCache aliases = this.PrepareAlias <T>(dQuery.SelectInfo);
var cmd2 = new SelectDbCommandDefinition(this, aliases, builder.Parameters)
{
HaveListNavigation = dQuery.SelectInfo.HaveListNavigation
};
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dQuery.SelectInfo.Join);
visitor.Write(cmd2.JoinFragment);
visitor = new WhereExpressionVisitor(this, aliases, dQuery.SelectInfo.Where);
visitor.Write(cmd2.WhereFragment);
cmd2.AddNavMembers(visitor.NavMembers);
builder.Append(cmd2.CommandText);
}
builder.Append(';');
return(new DbCommandDefinition(builder.ToString(), builder.Parameters, System.Data.CommandType.Text));
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder builder, DbExpression dbExpression, TableAliasCache aliases)
{
builder.Append(' ');
IDbQueryable sQuery = (IDbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
if (sQuery.DbExpressions.Count == 1 && sQuery.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
}
else
{
// 嵌套
var cmd = sQuery.Resolve(builder.Indent + 1, false, builder.Token);
builder.Append("(");
builder.Append(cmd.CommandText);
builder.AppendNewLine();
builder.Append(')');
}
LambdaExpression left = dbExpression.Expressions[1] as LambdaExpression;
LambdaExpression 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(' ');
// ON a.Name = b.Name AND a.Id = b.Id
builder.Append("ON ");
if (left.Body.NodeType == ExpressionType.New)
{
NewExpression body1 = left.Body as NewExpression;
NewExpression 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)
{
MemberInitExpression body1 = left.Body as MemberInitExpression;
MemberInitExpression 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());
}
}
// 创建 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);
}
// 创建 UPDATE 命令
protected override Command ParseUpdateCommand <T>(DbQueryableInfo_Update <T> uQuery, ParserToken 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 (uQuery.Entity != null)
{
object entity = uQuery.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(token);
bool useKey = false;
int length = 0;
foreach (var kv in typeRuntime.Invokers)
{
MemberInvokerBase invoker = kv.Value;
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:
var value = invoker.Invoke(entity);
var seg = builder.GetSqlValueWidthDefault(value, 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 T must have key column.");
}
builder.Length = length;
whereBuilder.Length -= 5;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (uQuery.Expression != null)
{
TableAliasCache aliases = this.PrepareAlias <T>(uQuery.SelectInfo, token);
ExpressionVisitorBase visitor = null;
visitor = new NpgUpdateExpressionVisitor(this, aliases, uQuery.Expression);
visitor.Write(builder);
var cmd2 = new NpgSelectInfoCommand(this, aliases, NpgCommandType.UPDATE, token);
cmd2.HasManyNavigation = uQuery.SelectInfo.HasManyNavigation;
var visitor0 = new NpgExistsExpressionVisitor(this, aliases, uQuery.SelectInfo.Joins, NpgCommandType.UPDATE);
visitor0.Write(cmd2);
var visitor1 = new NpgWhereExpressionVisitor(this, aliases, uQuery.SelectInfo.WhereExpression);
visitor1.Write(cmd2.WhereFragment);
cmd2.AddNavMembers(visitor1.NavMembers);
builder.Append(cmd2.CommandText);
}
builder.Append(';');
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 UPDATE 命令
/// </summary>
/// <param name="dbQuery">查询语义</param>
/// <param name="token">解析上下文</param>
/// <returns></returns>
protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update 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 seg_Where = this.CreateSqlBuilder(token);
bool useKey = false;
int length = 0;
foreach (var m in typeRuntime.Members)
{
var column = m.Column;
if (column != null && column.IsIdentity)
{
goto gotoLabel; // Fix issue# 自增列同时又是主键
}
if (column != null && column.NoMapped)
{
continue;
}
if (m.ForeignKey != null)
{
continue;
}
if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
{
continue;
}
builder.AppendMember(m.Member.Name);
builder.Append(" = ");
gotoLabel:
var value = m.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;
seg_Where.AppendMember(m.Member.Name);
seg_Where.Append(" = ");
seg_Where.Append(seg);
seg_Where.Append(" AND ");
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require T must have key column.");
}
builder.Length = length;
seg_Where.Length -= 5;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(seg_Where);
}
else if (dbQuery.Expression != null)
{
TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token);
var visitor = new NpgUpdateExpressionVisitor(this, aliases, dbQuery.Expression);
visitor.Write(builder);
var cmd = new NpgMappingDbCommand(this, aliases, DbExpressionType.Update, token)
{
HasMany = dbQuery.Query.HasMany
};
var visitor_ = new NpgJoinExpressionVisitor(this, aliases, dbQuery.Query.Joins, DbExpressionType.Update);
visitor_.Write(cmd);
var visitor__ = new NpgWhereExpressionVisitor(this, aliases, dbQuery.Query.Where);
visitor__.Write(cmd.WhereFragment);
cmd.AddNavMembers(visitor__.NavMembers);
builder.Append(cmd.CommandText);
}
builder.Append(';');
return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 添加导航属性关联
/// </summary>
protected override void TanslateNavMember()
{
if (this.NavMembers == null || this.NavMembers.Count == 0)
{
return;
}
// 如果有一对多的导航属性，肯定会产生嵌套查询。那么内层查询别名肯定是t0，所以需要清掉
if (this.HasMany)
{
_aliasGenerator = new AliasGenerator(_aliasGenerator.ReserveQty);
}
//开始产生 USING 子句
ISqlBuilder jf = this.JoinFragment;
int index = -1;
// 未生成USING子句
if (_aliasGenerator.ReserveQty <= 1)
{
jf.AppendNewLine();
jf.Append(_keywordName);
}
else
{
jf.Append(',');
jf.AppendNewLine();
}
foreach (var nav in this.NavMembers)
{
index++;
string key = nav.Key;
MemberExpression m = nav.Expression;
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(m.Expression.Type);
ForeignKeyAttribute attribute = typeRuntime.GetMemberAttribute <ForeignKeyAttribute>(m.Member.Name);
string innerKey = string.Empty;
string outerKey = key;
string innerAlias = string.Empty;
if (!m.Expression.Visitable())
{
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).TableFullName;
innerAlias = _aliasGenerator.GetJoinTableAlias(name);
if (string.IsNullOrEmpty(innerAlias))
{
string keyLeft = mLeft.GetKeyWidthoutAnonymous();
if (this.NavMembers.Contains(keyLeft))
{
innerKey = keyLeft;
}
innerAlias = _aliasGenerator.GetNavTableAlias(innerKey);
}
}
string alias1 = !string.IsNullOrEmpty(innerAlias) ? innerAlias : _aliasGenerator.GetTableAlias(innerKey);
string alias2 = _aliasGenerator.GetNavTableAlias(outerKey);
// 补充与USING字符串同等间距的空白
if (_aliasGenerator.ReserveQty > 1 || index > 0)
{
jf.Append(_pad);
}
Type type = m.Type;
var typeRumtime2 = TypeRuntimeInfoCache.GetRuntimeInfo(type);
if (type.IsGenericType)
{
type = type.GetGenericArguments()[0];
}
jf.AppendTable(typeRumtime2.TableSchema, typeRumtime2.TableName, typeRumtime2.IsTemporary);
jf.Append(' ');
jf.Append(alias2);
if (_onPhrase.Length > 0)
{
_onPhrase.Append(" AND ");
}
for (int i = 0; i < attribute.InnerKeys.Length; i++)
{
if (attribute.InnerKeys[i].StartsWith(AppConst.CONSTANT_FOREIGNKEY, StringComparison.Ordinal))
{
_onPhrase.Append(attribute.InnerKeys[i].Substring(7));
}
else
{
_onPhrase.Append(alias1);
_onPhrase.Append('.');
_onPhrase.AppendMember(attribute.InnerKeys[i]);
}
_onPhrase.Append(" = ");
if (attribute.OuterKeys[i].StartsWith(AppConst.CONSTANT_FOREIGNKEY, StringComparison.Ordinal))
{
_onPhrase.Append(attribute.OuterKeys[i].Substring(7));
}
else
{
_onPhrase.Append(alias2);
_onPhrase.Append('.');
_onPhrase.AppendMember(attribute.OuterKeys[i]);
}
}
if (nav.Predicate != null)
{
string alias = _aliasGenerator.GetNavTableAlias(nav.Key);
var visitor = new NavPredicateExpressionVisitor(_aliasGenerator, _onPhrase, alias);
visitor.Visit(nav.Predicate);
}
if (index < this.NavMembers.Count - 1)
{
jf.Append(',');
jf.AppendNewLine();
}
}
}
// 创建 INSRT 命令
protected override DbCommandDefinition ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQuery, List <IDbDataParameter> parameters = null)
{
ISqlBuilder builder = this.CreateSqlBuilder(parameters);
TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
TableAliasCache aliases = new TableAliasCache();
bool useSEQ = false;
if (nQuery.Entity != null)
{
// 如果没有Sequence列，使用 INSERT ALL INTO 语法，否则就一条一条逐行写入~~
// 批量 INSERT，自增列不会自动赋值
object entity = nQuery.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(builder.Parameters);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(builder.Parameters);
// 指定插入列
Dictionary <string, MemberInvokerBase> invokers = typeRuntime.Invokers;
if (nQuery.EntityColumns != null && nQuery.EntityColumns.Count > 0)
{
invokers = new Dictionary <string, MemberInvokerBase>();
for (int i = 0; i < nQuery.EntityColumns.Count; i++)
{
Expression curExpr = nQuery.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}", nQuery.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 kv in invokers)
{
MemberInvokerBase invoker = kv.Value;
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 == nQuery.AutoIncrement)
{
seqColumn = column;
if (nQuery.Bulk == null)
{
// 非批量INSERT，产生一个 OUTPUT 类型的参数
string pName = string.Format("{0}p{1}", this.ParameterPrefix, builder.Parameters.Count);
var database = nQuery.SourceQuery.DbContext.Database;
seqParameter = database.CreateParameter(pName, -1, direction: ParameterDirection.Output);
builder.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 = builder.GetSqlValueWidthDefault(value, column);
valuesBuilder.Append(seg);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (nQuery.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 (!nQuery.Bulk.OnlyValue || seqColumn != null)
{
builder.Append("INSERT ");
}
// 如果有自增列则不使用 INSERT ALL INTO 语法
if (!nQuery.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 (nQuery.Bulk == null)
{
builder.Append(';');
}
else
{
if (seqColumn != null)
{
if (nQuery.Bulk.IsEndPos)
{
builder.Append(";");
}
else
{
builder.AppendNewLine(";");
}
}
else
{
builder.AppendNewLine();
if (nQuery.Bulk.IsEndPos)
{
builder.Append("SELECT 1 FROM DUAL;");
}
}
}
}
else if (nQuery.SelectInfo != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
DbCommandDefinition_Select cmd2 = this.ParseSelectCommand(nQuery.SelectInfo, 0, false, builder.Parameters) as DbCommandDefinition_Select;
foreach (var kvp in cmd2.Columns)
{
builder.AppendMember(kvp.Key);
if (i < cmd2.Columns.Count - 1)
{
builder.Append(',');
}
i++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd2.CommandText);
builder.Append(';');
}
var cmd = new OracleDbCommandDefinition_Insert(builder.ToString(), builder.Parameters, System.Data.CommandType.Text);
cmd.HaveSEQ = useSEQ;
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)
{
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];
}
}
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 != 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.Append(';');
builder.AppendNewLine();
// sequence，命名原则是 tablename_columnname_seq.
builder.AppendFormat("SELECT CURRVAL('{0}_{1}_seq')", typeRuntime.TableName, dbQuery.AutoIncrement.Member.Name);
builder.Append(" AS ");
builder.Append(this.QuotePrefix);
builder.Append(Constant.AUTOINCREMENTNAME);
builder.Append(this.QuoteSuffix);
}
}
else if (dbQuery.SelectInfo != null)
{
builder.Append("INSERT INTO ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.Append('(');
int i = 0;
MapperCommand cmd2 = this.ResolveSelectCommandImpl(dbQuery.SelectInfo, 0, false, token) as MapperCommand;
//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 (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos)
{
builder.Append(';');
}
return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
}
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);
}
// 创建 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 t0 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("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 (uQueryInfo.Expression != null)
{
TableAliasCache aliases = this.PrepareAlias <T>(uQueryInfo.SelectInfo, token);
ExpressionVisitorBase visitor = null;
visitor = new UpdateExpressionVisitor(this, aliases, uQueryInfo.Expression);
visitor.Write(builder);
builder.AppendNewLine();
builder.Append("FROM ");
builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
builder.AppendAs("t0");
var cmd2 = new MappingCommand(this, aliases, token)
{
HasMany = uQueryInfo.SelectInfo.HasMany
};
visitor = new JoinExpressionVisitor(this, aliases, uQueryInfo.SelectInfo.Joins);
visitor.Write(cmd2.JoinFragment);
visitor = new WhereExpressionVisitor(this, aliases, uQueryInfo.SelectInfo.WhereExpression);
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));
}