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");
}
