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.

Previous
Offset and Limit (Pagination)

© 2024 dbExpression. All rights reserved.