//p=>p //p=>p.t //p=>p.Id protected override Expression VisitLambda <T>(Expression <T> node) { LambdaExpression lambda = node as LambdaExpression; if (lambda.Body.NodeType == ExpressionType.Parameter) { // 例: a=> a Type type = lambda.Body.Type; string alias = _aliases.GetTableAlias(lambda); this.VisitAllMember(type, alias); return(node); } else if (lambda.Body.CanEvaluate()) { // 例:a=>1 base.Visit(lambda.Body.Evaluate()); // 选择字段 string newName = AddColumn(_columns, "__Constant__"); // 添加字段别名 _builder.AppendAs(newName); return(node); } else if (lambda.Body.NodeType == ExpressionType.MemberAccess) { // 例: t=> t.a // => SELECT a.ClientId Type type = lambda.Body.Type; return(TypeUtils.IsPrimitiveType(type) ? base.VisitLambda(node) : this.VisitAllMember(type, _aliases.GetTableAlias(lambda.Body), node)); } return(base.VisitLambda(node)); }
// p=>p p=>p.t p=>p.Id protected override Expression VisitLambda <T>(Expression <T> node) { LambdaExpression lambda = node as LambdaExpression; if (lambda.Body.NodeType == ExpressionType.Parameter) { // 例: a=> a Type type = lambda.Body.Type; string alias = _aliases.GetTableAlias(lambda); this.VisitAllMember(type, alias); return(node); } else if (lambda.Body.CanEvaluate()) { // 例:a=>1 base.Visit(lambda.Body.Evaluate()); // 选择字段 string newName = _pickColumns.Add(Constant.CONSTANTNAME); // 添加字段别名 _builder.AppendAs(newName); return(node); } else if (lambda.Body.NodeType == ExpressionType.MemberAccess) { // 例: t=> t.a // => SELECT a.ClientId Type type = lambda.Body.Type; if (!TypeUtils.IsPrimitiveType(type)) { return(this.VisitAllMember(type, _aliases.GetTableAlias(lambda.Body), node)); } else { var newNode = this.VisitWithoutRemark(x => base.VisitLambda(node)); string newName = _pickColumns.Add((lambda.Body as MemberExpression).Member.Name); return(newNode); } } else { var newNode = base.VisitLambda(node); if (_pickColumns.Count == 0) { // 选择字段 string newName = _pickColumns.Add(Constant.CONSTANTNAME); // 添加字段别名 _builder.AppendAs(newName); } return(newNode); } }
/// <summary> /// 追加列名 /// </summary> /// <param name="aliases">表别名</param> /// <param name="expression">列名表达式</param> /// <returns>返回解析到的表别名</returns> public string AppendMember(TableAliasCache aliases, Expression expression) { Expression exp = expression; LambdaExpression lambdaExpression = exp as LambdaExpression; if (lambdaExpression != null) { exp = lambdaExpression.Body; } if (expression.CanEvaluate()) { ConstantExpression c = expression.Evaluate(); string value = this.GetSqlValue(c.Value); _innerBuilder.Append(value); return(value); } else { MemberExpression m = expression as MemberExpression; string alias = aliases == null ? null : aliases.GetTableAlias(m); this.AppendMember(alias, m.Member.Name); return(alias); } }
// Cross Join private void AppendCrossJoin(ISqlBuilder jf, DbExpression dbExpression, TableAliasCache aliases) { if (!usedKeyword) { jf.AppendNewLine(); jf.Append(_keywordName); usedKeyword = true; } else { jf.Append(','); jf.AppendNewLine(); jf.Append(_pad); } LambdaExpression lambda = dbExpression.Expressions[1] as LambdaExpression; Type type = lambda.Parameters[1].Type; var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); string alias = aliases.GetTableAlias(lambda.Parameters[1]); jf.Append(' '); jf.Append(alias); }
// 获取 CROSS JOIN 子句关联表的的别名 private void PrepareCrossJoinAlias(DbExpression dbExpression, TableAliasCache aliases) { LambdaExpression lambdaExp = dbExpression.Expressions[1] as LambdaExpression; for (int index = 0; index < lambdaExp.Parameters.Count; ++index) { aliases.GetTableAlias(lambdaExp.Parameters[index]); } }
// Cross Join private void AppendCrossJoin(ISqlBuilder builder, DbExpression exp, TableAliasCache aliases) { LambdaExpression lambdaExp = exp.Expressions[1] as LambdaExpression; Type type = lambdaExp.Parameters[1].Type; var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type); builder.Append(' '); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); string alias = aliases.GetTableAlias(lambdaExp.Parameters[1]); builder.Append(' '); builder.Append(alias); builder.Append(' '); }
//p=>p //p=>p.t //p=>p.Id protected override Expression VisitLambda <T>(Expression <T> node) { LambdaExpression lambda = node as LambdaExpression; if (lambda.Body.NodeType == ExpressionType.Parameter) { // 例: a=> a Type type = lambda.Body.Type; string alias = _aliases.GetTableAlias(lambda); this.VisitAllMember(type, alias); return(node); } else if (lambda.Body.CanEvaluate()) { // 例:a=>1 base.Visit(lambda.Body.Evaluate()); // 选择字段 string newName = AddColumn(_columns, "__Constant__", null); // 添加字段别名 _builder.AppendAs(newName); return(node); } else if (lambda.Body.NodeType == ExpressionType.MemberAccess) { // 例: t=> t.a // => SELECT a.ClientId Type type = lambda.Body.Type; if (!TypeUtils.IsPrimitiveType(type)) { return(this.VisitAllMember(type, _aliases.GetTableAlias(lambda.Body), node)); } else { var newNode = base.VisitLambda(node); string alias = _visitedMark.Current != null?_aliases.GetTableAlias(_visitedMark.Current) : null; string newName = AddColumn(_columns, (lambda.Body as MemberExpression).Member.Name, alias); //_builder.AppendAs(newName); //_builder.Append(','); //_builder.AppendNewLine(); return(newNode); } } else { return(base.VisitLambda(node)); } }
// 获取 LEFT JOIN / INNER JOIN 子句关联表的的别名 private void PrepareLfInJoinAlias(DbExpression dbExpression, TableAliasCache aliases) { Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0]; string name = TypeRuntimeInfoCache.GetRuntimeInfo(type).TableName; // on a.Name equals b.Name 或 on new{ Name = a.Name,Id=a.Id } equals new { Name = b.Name,Id=b.Id } LambdaExpression left = dbExpression.Expressions[1] as LambdaExpression; LambdaExpression right = dbExpression.Expressions[2] as LambdaExpression; 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) { aliases.GetTableAlias(body1.Arguments[index]); } for (int index = 0; index < body2.Arguments.Count; ++index) { string alias = aliases.GetTableAlias(body2.Arguments[index]); // 记录显示指定的LEFT JOIN 表别名 aliases.AddOrUpdateJoinTableAlias(name, alias); } } 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) { aliases.GetTableAlias((body1.Bindings[index] as MemberAssignment).Expression); } for (int index = 0; index < body2.Bindings.Count; ++index) { string alias = aliases.GetTableAlias((body2.Bindings[index] as MemberAssignment).Expression); // 记录显示指定的LEFT JOIN 表别名 aliases.AddOrUpdateJoinTableAlias(name, alias); } } else { aliases.GetTableAlias(dbExpression.Expressions[1]); string alias = aliases.GetTableAlias(dbExpression.Expressions[2]); // 记录显示指定的LEFT JOIN 表别名 aliases.AddOrUpdateJoinTableAlias(name, alias); } }
// 添加导航属性关联 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 "); } } } }
// 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()); } }
// 添加导航属性关联 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()); } }
// 访问导航属性 protected virtual string VisitNavMember(Expression expression, string memberName = null) { // 表达式 => b.Client.Address.AddressName Expression node = expression; Stack <KeyValuePair <string, MemberExpression> > stack = null; string alias = string.Empty; while (node != null && node.Acceptable()) { if (node.NodeType != ExpressionType.MemberAccess) { break; } if (stack == null) { stack = new Stack <KeyValuePair <string, MemberExpression> >(); } MemberExpression memberExpression = node as MemberExpression; TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(memberExpression.Expression.Type); ForeignKeyAttribute attribute = typeRuntime.GetInvokerAttribute <ForeignKeyAttribute>(memberExpression.Member.Name); if (attribute == null) { break; } string key = memberExpression.GetKeyWidthoutAnonymous(); stack.Push(new KeyValuePair <string, MemberExpression>(key, memberExpression)); node = memberExpression.Expression; if (node.NodeType == ExpressionType.Call) { node = (node as MethodCallExpression).Object; } } if (stack != null && stack.Count > 0) { while (stack != null && stack.Count > 0) { KeyValuePair <string, MemberExpression> kvp = stack.Pop(); string key = kvp.Key; MemberExpression m = kvp.Value; Type type = m.Type; if (type.IsGenericType) { type = type.GetGenericArguments()[0]; } TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type); // 检查查询表达式是否显示指定该表关联 alias = _aliases.GetJoinTableAlias(typeRuntime.TableName); if (string.IsNullOrEmpty(alias)) { // 如果没有,则使用导航属性别名 alias = _aliases.GetNavigationTableAlias(key); if (!_navMembers.ContainsKey(kvp.Key)) { _navMembers.Add(kvp); } } // 例: a.Client.ClientId if (stack.Count == 0 && !string.IsNullOrEmpty(memberName)) { _builder.AppendMember(alias, memberName); } } } else { // => SelectMany 也会产生类似 'b.Client.Address.AddressName' 这样的表达式 alias = _aliases.GetTableAlias(expression); _builder.AppendMember(alias, memberName); } // fix issue# Join 表达式显式指定导航属性时时,alias 为空 return(alias); }
// LEFT OR INNER JOIN private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder wf, ISqlBuilder on, DbExpression exp, TableAliasCache aliases) { bool useExists = false; LambdaExpression left = exp.Expressions[1] as LambdaExpression; LambdaExpression right = exp.Expressions[2] as LambdaExpression; NewExpression body1 = left.Body as NewExpression; NewExpression body2 = right.Body as NewExpression; // t0(t1) string alias = body1 == null ? aliases.GetTableAlias(exp.Expressions[2]) : aliases.GetTableAlias(right.Parameters[0]);//(body2.Arguments[0]); IDbQueryable sQuery = (IDbQueryable)((exp.Expressions[0] as ConstantExpression).Value); if (sQuery.DbExpressions.Count == 1 && sQuery.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable) { if (!_appendedKeyword) { jf.AppendNewLine(); jf.Append(_keywordName); _appendedKeyword = true; } else { jf.Append(','); jf.AppendNewLine(); jf.Append(" "); } jf.Append(' '); Type type = exp.Expressions[0].Type.GetGenericArguments()[0]; var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias); if (on.Length > 0) { on.Append(" AND "); } } else { useExists = true; if ((wf != null && wf.Length > 0) || (on != null && on.Length > 0)) { if (wf != null && wf.Length > 0) { wf.Append("AND "); } wf.AppendNewLine(); } wf.Append("EXISTS("); wf.Indent += 1; wf.AppendNewLine(); wf.Append("SELECT 1 FROM("); var cmd = sQuery.Resolve(wf.Indent + 1, false, wf.Parameters); wf.Append(cmd.CommandText); wf.AppendNewLine(); wf.Append(')'); wf.Append(' '); wf.Append(alias); wf.Append(" WHERE "); } ISqlBuilder tbuilder = useExists ? wf : on; if (body1 == null) { tbuilder.AppendMember(aliases, left.Body.ReduceUnary()); tbuilder.Append(" = "); tbuilder.AppendMember(aliases, right.Body.ReduceUnary()); } else { for (int index = 0; index < body1.Arguments.Count; ++index) { tbuilder.AppendMember(aliases, body1.Arguments[index]); tbuilder.Append(" = "); tbuilder.AppendMember(aliases, body2.Arguments[index]); if (index < body1.Arguments.Count - 1) { tbuilder.Append(" AND "); } } } if (useExists) { wf.Indent -= 1; wf.AppendNewLine(); wf.Append(')'); } }