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

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

• 使用 ObjectQuery<T> 的 Entity SQL

• ObjectQuery<T> 的查詢產生器方法

本主題的範例是根據 AdventureWorks Sales Model (EDM)。若要執行這些範例中的程式碼，您必須已經將 AdventureWorks Sales Model 加入到專案中，並設定您的專案使用 實體架構。若要這樣做，請完成 HOW TO：手動設定 Entity Framework 專案和 HOW TO：以手動方式定義 Entity Data Model (Entity Framework) 中的程序。您也可以使用 [Entity Data Model 精靈] 來定義 AdventureWorks Sales Model。如需詳細資訊，請參閱 HOW TO：使用 Entity Data Model 精靈 (Entity Framework)。

範例

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

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

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

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

另請參閱

其他資源

查詢 Entity Data Model (Entity Framework 工作)