public virtual void Union() { SqlUnion union = new SqlUnion(); SelectQuery query = new SelectQuery(); query.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10")); query.FromClause.BaseTable = FromTerm.Table("products"); union.Add(query); query = new SelectQuery(); query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10")); query.FromClause.BaseTable = FromTerm.Table("products"); union.Add(query, DistinctModifier.All); query = new SelectQuery(); query.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10")); query.FromClause.BaseTable = FromTerm.Table("products"); union.Add(query, DistinctModifier.Distinct); string sql = Renderer.RenderUnion(union); Console.WriteLine(sql); RunSql(sql); }
public SqlTable FindSqlTable(Entity entity, bool searchSubqueries) { if (!searchSubqueries) { return(FindSqlTable(entity)); } EntityTables result; if (_tablesByEntity.TryGetValue(entity, out result)) { return(result.EntityTable); } foreach (var pair in _tablesByEntity) { SqlUnion subQueryUnion = pair.Value.EntityTable.SubQuery; if (subQueryUnion == null) { continue; } foreach (SqlQuery query in subQueryUnion.Queries) { SqlTable res = query.References.FindSqlTable(entity, true); if (res != null) { return(res); } } } return(null); }
internal override SqlExpression VisitAliasRef(SqlAliasRef aref) { SqlNode node = aref.Alias.Node; if (node is SqlTable || node is SqlTableValuedFunctionCall) { return(aref); } SqlUnion union = node as SqlUnion; if (union != null) { return(this.ExpandUnion(union)); } SqlSelect ss = node as SqlSelect; if (ss != null) { return(this.VisitExpression(ss.Selection)); } SqlExpression exp = node as SqlExpression; if (exp != null) { return(this.VisitExpression(exp)); } throw Error.CouldNotHandleAliasRef(node.NodeType); }
internal override SqlNode VisitUnion(SqlUnion su) { Scope save = this.current; this.current = null; SqlNode result = base.VisitUnion(su); this.current = save; return result; }
private SqlExpression ExpandUnion(SqlUnion union) { List<SqlExpression> exprs = new List<SqlExpression>(2); this.GatherUnionExpressions(union, exprs); this.sourceExpression = union.SourceExpression; SqlExpression result = this.ExpandTogether(exprs); return result; }
internal override SqlNode VisitUnion(SqlUnion su) { bool changedLeft = false; bool containsLongExpressionsLeft = false; SqlSelect left = su.Left as SqlSelect; if (left != null) { ConvertColumnsToMax(left, out changedLeft, out containsLongExpressionsLeft); } bool changedRight = false; bool containsLongExpressionsRight = false; SqlSelect right = su.Right as SqlSelect; if (right != null) { ConvertColumnsToMax(right, out changedRight, out containsLongExpressionsRight); } if (!su.All && (containsLongExpressionsLeft || containsLongExpressionsRight)) { // unless the UNION is 'ALL', the server will perform a DISTINCT operation, // which isn't valid for large types (text, ntext, image) this.annotations.Add(su, new CompatibilityAnnotation( Strings.TextNTextAndImageCannotOccurInUnion(su.SourceExpression), SqlServerProviderMode.Sql2000, SqlServerProviderMode.SqlCE)); } return(base.VisitUnion(su)); }
private void GatherUnionExpressions(SqlNode node, List <SqlExpression> exprs) { SqlUnion union = node as SqlUnion; if (union != null) { this.GatherUnionExpressions(union.Left, exprs); this.GatherUnionExpressions(union.Right, exprs); } else { SqlSelect sel = node as SqlSelect; if (sel != null) { SqlAliasRef aref = sel.Selection as SqlAliasRef; if (aref != null) { this.GatherUnionExpressions(aref.Alias.Node, exprs); } else { exprs.Add(sel.Selection); } } } }
public void TestUnionCondition1() { SqlPackage <Student> package = new SqlPackage <Student>(); SqlCondition <Student> condition = new SqlCondition <Student>(); var result = SqlUnion <Student> .Union(Sql <Student> .SelectAllByCondition + (condition > "Sid").Full, "table1", "table2", "table3"); Assert.Equal("SELECT * FROM `table1` WHERE Sid > @Sid UNION SELECT * FROM `table2` WHERE Sid > @Sid UNION SELECT * FROM `table3` WHERE Sid > @Sid", result); }
public void TestUnionCondition2() { SqlPackage <Student> package = new SqlPackage <Student>(); SqlCondition <Student> condition = new SqlCondition <Student>(); var result = SqlUnion <Student> .Union(Sql <Student> .SelectAllByCondition + (condition > "Sid").Full); Assert.Equal("SELECT * FROM `1` WHERE Sid > @Sid", result); }
internal override SqlNode VisitUnion(SqlUnion su) { // ordering does not carry through a union this.orders = null; su.Left = this.Visit(su.Left); this.orders = null; su.Right = this.Visit(su.Right); this.orders = null; return su; }
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) { var sequence1 = new SubQueryContext(builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0]))); var sequence2 = new SubQueryContext(builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[1], new SelectQuery()))); var union = new SqlUnion(sequence2.SelectQuery, methodCall.Method.Name == "Concat"); sequence1.SelectQuery.Unions.Add(union); return(new UnionContext(sequence1, sequence2, methodCall)); }
private SqlExpression ExpandUnion(SqlUnion union) { List <SqlExpression> exprs = new List <SqlExpression>(2); this.GatherUnionExpressions(union, exprs); this.sourceExpression = union.SourceExpression; SqlExpression result = this.ExpandTogether(exprs); return(result); }
internal override SqlNode VisitUnion(SqlUnion su) { Scope save = this.current; this.current = null; SqlNode result = base.VisitUnion(su); this.current = save; return(result); }
internal override SqlNode VisitUnion(SqlUnion su) { bool saveForceReferenceAll = _forceReferenceAll; _forceReferenceAll = true; su.Left = this.Visit(su.Left); su.Right = this.Visit(su.Right); _forceReferenceAll = saveForceReferenceAll; return(su); }
internal override SqlNode VisitUnion(SqlUnion su) { // ordering does not carry through a union this.orders = null; su.Left = this.Visit(su.Left); this.orders = null; su.Right = this.Visit(su.Right); this.orders = null; return(su); }
public void Ctor_WithStatementArray_SetsStatementsProperty() { var select1 = Sql.Select("Id").From("User").Go(); var select2 = Sql.Select("Id").From("Post").Go(); var select3 = Sql.Select("Id").From("Comment").Go(); var union = new SqlUnion(select1, select2, select3); Assert.NotNull(union); Assert.Equal(3, union.Statements.Count()); Assert.Same(select1, union.Statements.First()); Assert.Same(select2, union.Statements.Skip(1).First()); Assert.Same(select3, union.Statements.Last()); }
public void Ctor_WithStatementArray_SetsStatementsProperty() { var select1 = Sql.Select("Id").From("User").Go(); var select2 = Sql.Select("Id").From("Post").Go(); var select3 = Sql.Select("Id").From("Comment").Go(); var union = new SqlUnion(select1, select2, select3); Assert.NotNull(union); Assert.Equal(3, union.Statements.Count()); Assert.Same(select1, union.Statements.First()); Assert.Same(select2, union.Statements.Skip(1).First()); Assert.Same(select3, union.Statements.Last()); }
private SqlUnion GetUnion(SqlSource source) { SqlAlias alias = source as SqlAlias; if (alias != null) { SqlUnion union = alias.Node as SqlUnion; if (union != null) { return(union); } } return(null); }
/// <summary> /// Renders a UNION clause /// </summary> /// <param name="union">Union definition</param> /// <returns>Generated SQL statement</returns> public virtual string RenderUnion(SqlUnion union) { StringBuilder builder = new StringBuilder(); foreach (SqlUnionItem item in union.Items) { if (item != union.Items[0]) { builder.AppendFormat(" union {0} ", (item.RepeatingAction == DistinctModifier.All) ? "all" : ""); } builder.Append(RenderSelect(item.Query)); } return(builder.ToString()); }
internal override SqlSelect VisitSelect(SqlSelect select) { base.VisitSelect(@select); // enforce exact ordering of columns in union selects SqlUnion union = this.GetUnion(@select.From); if (union != null) { SqlSelect sleft = union.Left as SqlSelect; SqlSelect sright = union.Right as SqlSelect; if (sleft != null & sright != null) { // preset ordinals to high values (so any unreachable column definition is ordered last) for (int i = 0, n = sleft.Row.Columns.Count; i < n; i++) { sleft.Row.Columns[i].Ordinal = @select.Row.Columns.Count + i; } for (int i = 0, n = sright.Row.Columns.Count; i < n; i++) { sright.Row.Columns[i].Ordinal = @select.Row.Columns.Count + i; } // next assign ordinals to all direct columns in subselects for (int i = 0, n = @select.Row.Columns.Count; i < n; i++) { SqlExprSet es = @select.Row.Columns[i].Expression as SqlExprSet; if (es != null) { for (int e = 0, en = es.Expressions.Count; e < en; e++) { SqlColumnRef cr = es.Expressions[e] as SqlColumnRef; if (cr != null && e >= @select.Row.Columns.Count) { cr.Column.Ordinal = i; } } } } // next sort columns in left & right subselects Comparison <SqlColumn> comp = (x, y) => x.Ordinal - y.Ordinal; sleft.Row.Columns.Sort(comp); sright.Row.Columns.Sort(comp); } } return(@select); }
internal override SqlNode VisitUnion(SqlUnion su) { // we don't want to descend inward // just check that it's not a UNION ALL if (su.All) { _isValid = false; } // UNIONs are distinct _isDistinct = true; // get all members from selection this.AddIdentityMembers(su.GetClrType().GetProperties()); return(su); }
internal override SqlNode VisitUnion(SqlUnion su) { bool changedLeft = false; bool containsLongExpressionsLeft = false; SqlSelect left = su.Left as SqlSelect; if (left != null) { ConvertColumnsToMax(left, out changedLeft, out containsLongExpressionsLeft); } bool changedRight = false; bool containsLongExpressionsRight = false; SqlSelect right = su.Right as SqlSelect; if (right != null) { ConvertColumnsToMax(right, out changedRight, out containsLongExpressionsRight); } if (!su.All && (containsLongExpressionsLeft || containsLongExpressionsRight)) { // unless the UNION is 'ALL', the server will perform a DISTINCT operation, // which isn't valid for large types (text, ntext, image) this.annotations.Add(su, new SqlServerCompatibilityAnnotation( Strings.TextNTextAndImageCannotOccurInUnion(su.SourceExpression), SqlProvider.ProviderMode.Sql2000, SqlProvider.ProviderMode.SqlCE)); } return base.VisitUnion(su); }
protected override SqlExpression VisitUnion(SqlUnion expr) { _builder.Append("("); _depth++; NewLine(); Visit(expr.Left); NewLine(); _builder.Append("UNION"); if (expr.All) { _builder.Append(" ALL"); } NewLine(); Visit(expr.Right); NewLine(); _builder.Append(")"); _depth--; return(expr); }
internal override SqlNode VisitUnion(SqlUnion su) { this.canJoin = false; return(base.VisitUnion(su)); }
internal override SqlNode VisitUnion(SqlUnion su) { return su; }
internal override SqlNode VisitUnion(SqlUnion su) { return new SqlUnion(this.Visit(su.Left), this.Visit(su.Right), su.All); }
internal override SqlNode VisitUnion(SqlUnion su) { // we don't want to descend inward // just check that it's not a UNION ALL if (su.All) { this.IsValid = false; } // UNIONs are distinct this.IsDistinct = true; // get all members from selection this.AddIdentityMembers(su.GetClrType().GetProperties()); return su; }
private SqlNode VisitUnion(Expression source1, Expression source2) { SqlSelect left = this.VisitSequence(source1); SqlSelect right = this.VisitSequence(source2); SqlUnion union = new SqlUnion(left, right, false); SqlAlias alias = new SqlAlias(union); SqlAliasRef aref = new SqlAliasRef(alias); SqlSelect result = new SqlSelect(aref, alias, this.dominatingExpression); result.OrderingType = SqlOrderingType.Blocked; return result; }
protected override SqlExpression VisitUnion(SqlUnion expr) { _builder.Append("("); _depth++; NewLine(); Visit(expr.Left); NewLine(); _builder.Append("UNION"); if (expr.All) _builder.Append(" ALL"); NewLine(); Visit(expr.Right); NewLine(); _builder.Append(")"); _depth--; return expr; }
protected virtual SqlExpression VisitUnion(SqlUnion expr) { return(expr); }
internal override SqlNode VisitUnion(SqlUnion su) { bool saveForceReferenceAll = this.forceReferenceAll; this.forceReferenceAll = true; su.Left = this.Visit(su.Left); su.Right = this.Visit(su.Right); this.forceReferenceAll = saveForceReferenceAll; return su; }
/// <summary> /// Visits the specified <see cref="SqlUnion"/>. /// </summary> /// <param name="expression"> /// The expression to visit. /// </param> public virtual void Visit(SqlUnion expression) { }
internal override SqlNode VisitUnion(SqlUnion su) { this.canJoin = false; return base.VisitUnion(su); }
internal override SqlNode VisitUnion(SqlUnion su) { return(su); }
protected virtual SqlExpression VisitUnion(SqlUnion expr) { return expr; }
internal override SqlNode VisitUnion(SqlUnion su) { return(new SqlUnion(this.Visit(su.Left), this.Visit(su.Right), su.All)); }
/// <summary> /// Visits the specified <see cref="SqlUnion"/>. /// </summary> /// <param name="expression"> /// The expression to visit. /// </param> public virtual void Visit(SqlUnion expression) { }
internal virtual SqlNode VisitUnion(SqlUnion su) { su.Left = this.Visit(su.Left); su.Right = this.Visit(su.Right); return su; }
internal virtual SqlNode VisitUnion(SqlUnion su) { su.Left = this.Visit(su.Left); su.Right = this.Visit(su.Right); return su; }
/// <summary> /// Given a ClientCase and a list of sequence (one for each case), construct a structure /// that is equivalent to a CASE of SELECTs. To accomplish this we use UNION ALL and attach /// a WHERE clause which will pick the SELECT that matches the discriminator in the Client Case. /// </summary> private SqlSelect SimulateCaseOfSequences(SqlClientCase clientCase, List<SqlNode> sequences) { /* * There are two situations we may be in: * (1) There is exactly one case alternative. * Here, no where clause is needed. * (2) There is more than case alternative. * Here, each WHERE clause needs to be ANDed with [Disc]=D where D * is the literal discriminanator value. */ if (sequences.Count == 1) { return (SqlSelect)sequences[0]; } else { SqlNode union = null; SqlSelect sel = null; int elseIndex = clientCase.Whens.Count - 1; int elseCount = clientCase.Whens[elseIndex].Match == null ? 1 : 0; SqlExpression elseFilter = null; for (int i = 0; i < sequences.Count - elseCount; ++i) { sel = (SqlSelect)sequences[i]; SqlExpression discriminatorPredicate = sql.Binary(SqlNodeType.EQ, clientCase.Expression, clientCase.Whens[i].Match); sel.Where = sql.AndAccumulate(sel.Where, discriminatorPredicate); elseFilter = sql.AndAccumulate(elseFilter, sql.Binary(SqlNodeType.NE, clientCase.Expression, clientCase.Whens[i].Match)); if (union == null) { union = sel; } else { union = new SqlUnion(sel, union, true /* Union All */); } } // Handle 'else' if present. if (elseCount == 1) { sel = (SqlSelect)sequences[elseIndex]; sel.Where = sql.AndAccumulate(sel.Where, elseFilter); if (union == null) { union = sel; } else { union = new SqlUnion(sel, union, true /* Union All */); } } SqlAlias alias = new SqlAlias(union); SqlAliasRef aref = new SqlAliasRef(alias); return new SqlSelect(aref, alias, union.SourceExpression); } }
internal override SqlNode VisitUnion(SqlUnion su) { sb.Append("("); int saveDepth = this.depth; this.depth++; this.NewLine(); this.Visit(su.Left); this.NewLine(); sb.Append("UNION"); if (su.All) { sb.Append(" ALL"); } this.NewLine(); this.Visit(su.Right); this.NewLine(); sb.Append(")"); this.depth = saveDepth; return su; }
public void TestUnion() { var result = SqlUnion <One> .Union(Sql <One> .SelectAllByCondition + (condition > "oid").Full, "table1", "table2", "table3"); }