Core Concepts > Basic Queries
Union and Union All
Use the Union
and Union All
methods while composing a query to append multiple select statements and return a single rowset.
IEnumerable<dynamic> results = db.SelectMany(
dbo.Person.Id,
dbo.Person.FirstName,
dbo.Person.LastName
)
.From(dbo.Person)
.Union()
.SelectMany(
dbo.Address.Id,
dbo.Address.Line1,
dbo.Address.Line2
)
.From(dbo.Address)
.Execute();
IEnumerable<dynamic> results = db.SelectMany(
dbo.Person.Id,
dbo.Person.FirstName,
dbo.Person.LastName
)
.From(dbo.Person)
.UnionAll()
.SelectMany(
dbo.Address.Id,
dbo.Address.Line1,
dbo.Address.Line2
)
.From(dbo.Address)
.Execute();
Union and Union All can be used in conjunction with other database functions, grouping, ordering, etc. For example, the following pivots data using different database functions and aggregation, finally ordering the result set (purely for example, we know there's a better way):
IEnumerable<dynamic> results = db.SelectMany(
dbex.Alias<string>("Pivot", "State"),
db.fx.Sum(("Pivot", "ShippingCount")).As("Shipping"),
db.fx.Sum(("Pivot", "MailingCount")).As("Mailing"),
db.fx.Sum(("Pivot", "BillingCount")).As("Billing")
).From(
db.SelectMany(
dbo.Address.State,
db.fx.Count().As("ShippingCount"),
dbex.Null.As("MailingCount"),
dbex.Null.As("BillingCount")
).From(dbo.Address)
.Where(dbo.Address.AddressType == AddressType.Shipping)
.GroupBy(dbo.Address.State)
.UnionAll()
.SelectMany(
dbo.Address.State,
dbex.Null,
db.fx.Count(),
dbex.Null
).From(dbo.Address)
.Where(dbo.Address.AddressType == AddressType.Mailing)
.GroupBy(dbo.Address.State)
.UnionAll()
.SelectMany(
dbo.Address.State,
dbex.Null,
dbex.Null,
db.fx.Count()
).From(dbo.Address)
.Where(dbo.Address.AddressType == AddressType.Billing)
.GroupBy(dbo.Address.State)
).As("Pivot")
.GroupBy(("Pivot", "State"))
.OrderBy(("Pivot", "State"))
.Execute();
Note the use of tuples to define the alias in the outer select clause's Sum function. This requires an additional namespace in a using statement DbExpression.Sql.Builder.Alias
, see Aliasing for more details.