How to: Order the Union of Two Queries
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>
Query builder methods of ObjectQuery<T>
The example in this topic is based on the Adventure Works Sales Model. To run the code in this topic, you must have already added the Adventure Works Sales Model to your project and configured your project to use the Entity Framework. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework) or How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (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 context As New AdventureWorksEntities()
Dim esqlQuery As String = "SELECT P2.Name, P2.ListPrice FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice " & _
" FROM AdventureWorksEntities.Products as P1 where P1.Name like 'A%') union all" & _
" (SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice FROM AdventureWorksEntities.Products as P1" & _
" WHERE P1.Name like 'B%')) as P2 ORDER BY P2.Name"
For Each rec As DbDataRecord In New ObjectQuery(Of DbDataRecord)(esqlQuery, context)
Console.WriteLine("Name: {0}; ListPrice: {1}", rec(0), rec(1))
Next
End Using
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
String esqlQuery = @"SELECT P2.Name, P2.ListPrice
FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Products as P1
where P1.Name like 'A%')
union all
(SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Products as P1
WHERE P1.Name like 'B%')
) as P2
ORDER BY P2.Name";
foreach (DbDataRecord rec in
new ObjectQuery<DbDataRecord>(esqlQuery, context))
{
Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
}
}
This is the query builder method example.
Using context As New AdventureWorksEntities()
Dim query As ObjectQuery(Of DbDataRecord) = _
context.Products.Select("it.Name, it.ProductID As Pid, it.ListPrice") _
.Where("it.Name LIKE 'A%'").Union(context.Products.Select("it.Name, it.ProductID As Pid, it.ListPrice") _
.Where("it.Name LIKE 'B%'")).Select("it.Name, it.ListPrice").OrderBy("it.Name")
For Each rec As DbDataRecord In query
Console.WriteLine("Name: {0}; ListPrice: {1}", rec(0), rec(1))
Next
End Using
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
ObjectQuery<DbDataRecord> query =
context.Products.Select("it.Name, it.ProductID As Pid, it.ListPrice")
.Where("it.Name LIKE 'A%'").Union(context.Products
.Select("it.Name, it.ProductID As Pid, it.ListPrice")
.Where("it.Name LIKE 'B%'")).Select("it.Name, it.ListPrice").OrderBy("it.Name");
foreach (DbDataRecord rec in query)
{
Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
}
}
The following example shows how to order the union of two queries by using LINQ to Entities.
Using context As New AdventureWorksEntities()
Dim query = (From a In context.Products Where a.Name.StartsWith("A") _
Select (New With {a.Name, .pid = a.ProductID, a.ListPrice})). _
Union( _
From b In context.Products Where b.Name.StartsWith("B") _
Select (New With {b.Name, .pid = b.ProductID, b.ListPrice})). _
Select(Function(c) New With {c.Name, c.ListPrice}).OrderBy(Function(d) d.Name)
For Each result In query
Console.WriteLine(result.Name)
Next
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = (from a in context.Products
where a.Name.StartsWith("A")
select new { a.Name, pid = a.ProductID, a.ListPrice })
.Union
(from b in context.Products
where b.Name.StartsWith("B")
select new { b.Name, pid = b.ProductID, b.ListPrice })
.Select(c => new { c.Name, c.ListPrice }).OrderBy(d => d.Name);
foreach (var result in query)
{
Console.WriteLine(result.Name);
}
}