HOW TO：排序兩個查詢的聯集 (Entity Framework)

本主題說明如何將兩個查詢的結果結合成單一結果集，然後排序此結果集。 相同範例會使用下列每個 Entity Framework 查詢技術顯示：

• Entity SQL with ObjectQuery<T>

• Query builder methods of ObjectQuery<T>

• Query builder methods of ObjectQuery<T>

本主題的範例根據 Adventure Works Sales Model。若要執行此主題中的程式碼，您必須已經將 Adventure Works Sales Model 加入到專案中，並設定您的專案使用 Entity Framework。如需詳細資訊，請參閱 HOW TO：使用實體資料模型精靈 (Entity Framework) 或 HOW TO：手動設定 Entity Framework 專案及 HOW TO：手動設定 Entity Framework 專案。

範例

以下是 Entity SQL 範例。 若要聯集及排序 Entity SQL 查詢，您必須使用巢狀化。 在 Entity SQL 中，巢狀查詢必須括在括號中。

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

這是查詢產生器方法範例。

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

下列範例會示範如何使用 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);
    }
}

另請參閱

概念

查詢概念模型 (Entity Framework)