How to: Order Two Unionized Queries (Entity Framework)

This topic shows how to combine the results of two queries into a single result set, and then order the result set. The same example is shown using each of the following Entity Framework query technologies:

• Entity SQL with ObjectQuery<T>

• Query builder methods of ObjectQuery<T>

The examples in this topic are based on the AdventureWorks Sales Model (EDM). To run the code in these examples, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework). You can also use the Entity Data Model Wizard to define the AdventureWorks Sales Model. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).

Example

The following is the Entity SQL example. To unionize and order Entity SQL queries, you must use nesting. In Entity SQL, nested queries must be enclosed in parentheses.

Using advWorksContext As AdventureWorksEntities = New AdventureWorksEntities
    Dim esqlQuery As String = "SELECT P2.Name, P2.ListPrice " & _
        " FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice " & _
        " FROM AdventureWorksEntities.Product as P1 " & _
        " where P1.Name like 'A%') " & _
        " union all " & _
        " (SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice  " & _
        " FROM AdventureWorksEntities.Product as P1 " & _
        " WHERE P1.Name like 'B%') " & _
        " ) as P2 " & _
        " ORDER BY P2.Name"
    Try
        Dim objQuery As New ObjectQuery(Of DbDataRecord)(esqlQuery, advWorksContext)
        For Each rec As DbDataRecord In objQuery
            Console.WriteLine("Name: {0} ListPrice: {1}", rec.Item(0), rec.Item(1))
        Next
    Catch ex As EntityException
        Console.WriteLine(ex.ToString())
    Catch ex As InvalidOperationException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    String esqlQuery = @"SELECT P2.Name, P2.ListPrice
        FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice 
            FROM AdventureWorksEntities.Product as P1
            where P1.Name like 'A%')
        union all
            (SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice 
            FROM AdventureWorksEntities.Product as P1
            WHERE P1.Name like 'B%')
        ) as P2
        ORDER BY P2.Name";
    try
    {
        foreach (DbDataRecord rec in
            new ObjectQuery<DbDataRecord>(esqlQuery, advWorksContext))
        {
            Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

This is the query builder method example.

Using advWorksContext As New AdventureWorksEntities()
    Dim query As ObjectQuery(Of DbDataRecord) = _
        advWorksContext.Product.Select("it.Name, it.ProductID As Pid, it.ListPrice") _
        .Where("it.Name LIKE 'A%'").Union(advWorksContext.Product _
        .Select("it.Name, it.ProductID As Pid, it.ListPrice") _
        .Where("it.Name LIKE 'B%'")).Select("it.Name, it.ListPrice").OrderBy("it.Name")

    Try
        For Each rec As DbDataRecord In query
            Console.WriteLine("Name:{0}ListPrice: {1}", rec(0), rec(1))
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    ObjectQuery<DbDataRecord> query = 
        advWorksContext.Product.Select("it.Name, it.ProductID As Pid, it.ListPrice")
        .Where("it.Name LIKE 'A%'").Union(advWorksContext.Product
        .Select("it.Name, it.ProductID As Pid, it.ListPrice")
        .Where("it.Name LIKE 'B%'")).Select("it.Name, it.ListPrice").OrderBy("it.Name");

        try
        {
            foreach (DbDataRecord rec in query)
            {
                Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
            }
        }
        catch (EntitySqlException ex)
        {
            Console.WriteLine(ex.ToString());
        }
}

See Also

Other Resources

Querying an Entity Data Model (Entity Framework Tasks)