クエリ パスを使用して結果を構築する方法 (Entity Framework)

このトピックでは、クエリ パスを指定する方法について説明します。クエリ パスは、Entity Data Model (EDM) で特定のオブジェクトを照会したときに、関連するオブジェクトとして何が返されるかを定義するものです。この例のクエリからは、単一の Contact オブジェクトの他に、関連するすべての SalesOrderHeader オブジェクトおよび SalesOrderDetail オブジェクトが返されます。次の各 エンティティ フレームワーク クエリ テクノロジを使用して同じことを行う例が紹介されています。

• LINQ to Entities

• Entity SQL と ObjectQuery<T>

• ObjectQuery<T> のクエリ ビルダ メソッド

このトピックの例には、Adventure Works Sales Model が使用されています。この例のコードを実行するには、あらかじめプロジェクトに AdventureWorks Sales Model を追加し、Entity Framework が使用されるようにプロジェクトを構成しておく必要があります。具体的な方法については、「Entity Framework プロジェクトを手動で構成する方法」および「Entity Data Model を手動で定義する方法 (Entity Framework)」の手順を参照してください。Entity Data Model ウィザードを使用して、AdventureWorks Sales Model を定義することもできます。詳細については、「Entity Data Model ウィザードを使用する方法 (Entity Framework)」を参照してください。

例

これは、LINQ to Entities の例です。

Using context As New AdventureWorksEntities
    ' Define a LINQ query with a path that returns 
    ' orders and items for a contact.
    Dim contacts = (From contact In context.Contact _
        .Include("SalesOrderHeader.SalesOrderDetail") _
        Select contact).FirstOrDefault()

    Try
        ' Execute the query and display information for each item 
        ' in the orders that belong to the contact.
        For Each order As SalesOrderHeader In contacts.SalesOrderHeader
            Console.WriteLine(String.Format("PO Number: {0}", _
                order.PurchaseOrderNumber))
            Console.WriteLine(String.Format("Order Date: {0}", _
                order.OrderDate.ToString()))
            Console.WriteLine("Order items:")
            For Each item As SalesOrderDetail In order.SalesOrderDetail
                Console.WriteLine(String.Format("Product:{0}" _
                    + "Quantity: {1}", item.ProductID.ToString(), _
                    item.OrderQty.ToString()))
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As EntityCommandExecutionException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define a LINQ query with a path that returns 
    // orders and items for a contact.
    var contacts = (from contact in context.Contact
                  .Include("SalesOrderHeader.SalesOrderDetail")
                  select contact).FirstOrDefault();

    try
    {
        // Execute the query and display information for each item 
        // in the orders that belong to the contact.
        foreach (SalesOrderHeader order in contacts
            .SalesOrderHeader)
        {
            Console.WriteLine(String.Format("PO Number: {0}",
                order.PurchaseOrderNumber));
            Console.WriteLine(String.Format("Order Date: {0}",
                order.OrderDate.ToString()));
            Console.WriteLine("Order items:");
            foreach (SalesOrderDetail item in order.SalesOrderDetail)
            {
                Console.WriteLine(String.Format("Product: {0} "
                    + "Quantity: {1}", item.ProductID.ToString(),
                    item.OrderQty.ToString()));
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (EntityCommandExecutionException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

これは、Entity SQL の例です。

Using context As New AdventureWorksEntities
    ' Define an object query with a path that returns 
    ' orders and items for a specific contact.              
    Dim queryString As String = _
        "SELECT VALUE TOP(1) Contact FROM " + _
        "AdventureWorksEntities.Contact AS Contact"

    ' Define the object query with the query string.
    Dim contactQuery As New ObjectQuery(Of Contact)(queryString, _
        context, MergeOption.NoTracking)

    Dim contact As Contact = _
    contactQuery.Include("SalesOrderHeader.SalesOrderDetail") _
        .FirstOrDefault()

    Try
        ' Execute the query and display information for each item 
        ' in the orders that belong to the first contact.
        For Each order As SalesOrderHeader In contact.SalesOrderHeader
            Console.WriteLine(String.Format("PO Number: {0}", _
                order.PurchaseOrderNumber))
            Console.WriteLine(String.Format("Order Date: {0}", _
                order.OrderDate.ToString()))
            Console.WriteLine("Order items:")
            For Each item As SalesOrderDetail In order.SalesOrderDetail
                Console.WriteLine(String.Format("Product:{0}" _
                    + "Quantity: {1}", item.ProductID.ToString(), _
                    item.OrderQty.ToString()))
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As EntityCommandExecutionException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define an object query with a path that returns 
    // orders and items for a specific contact.              
    string queryString =
        @"SELECT VALUE TOP(1) Contact FROM " + 
        "AdventureWorksEntities.Contact AS Contact";

    // Define the object query with the query string.
    ObjectQuery<Contact> contactQuery = new ObjectQuery<Contact>(queryString, 
        context, MergeOption.NoTracking);
    
    Contact contact =
        contactQuery.Include("SalesOrderHeader.SalesOrderDetail")
        .FirstOrDefault();

    try
    {
        // Execute the query and display information for each item 
        // in the orders that belong to the first contact.
        foreach (SalesOrderHeader order in contact
            .SalesOrderHeader)
        {
            Console.WriteLine(String.Format("PO Number: {0}",
                order.PurchaseOrderNumber));
            Console.WriteLine(String.Format("Order Date: {0}",
                order.OrderDate.ToString()));
            Console.WriteLine("Order items:");
            foreach (SalesOrderDetail item in order.SalesOrderDetail)
            {
                Console.WriteLine(String.Format("Product: {0} "
                    + "Quantity: {1}", item.ProductID.ToString(),
                    item.OrderQty.ToString()));
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (EntityCommandExecutionException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

これは、クエリ ビルダ メソッドの例です。

Using context As New AdventureWorksEntities
    ' Create an object query with a path that returns orders and items for a contact.
    Dim contact As Contact = _
        context.Contact.Include("SalesOrderHeader.SalesOrderDetail") _
        .FirstOrDefault()
    Try
        ' Execute the query and display information for each item 
        ' in the orders that belong to the returned contact.
        Dim order As SalesOrderHeader
        For Each order In contact.SalesOrderHeader
            Console.WriteLine(String.Format("PO Number: {0}", _
                    order.PurchaseOrderNumber))
            Console.WriteLine(String.Format("Order Date: {0}", _
                    order.OrderDate.ToString()))
            Console.WriteLine("Order items:")
            Dim item As SalesOrderDetail
            For Each item In order.SalesOrderDetail
                Console.WriteLine(String.Format("Product:{0}" _
                    + "Quantity: {1}", item.ProductID.ToString(), _
                    item.OrderQty.ToString()))
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As EntityCommandExecutionException
        Console.WriteLine(ex.ToString())
    End Try
End Using

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define an object query with a path that returns 
    // orders and items for a specific contact.
    Contact contact =
        context.Contact.Include("SalesOrderHeader.SalesOrderDetail")
        .FirstOrDefault();
    
    try
    {
        // Execute the query and display information for each item 
        // in the orders that belong to the first contact.
        foreach (SalesOrderHeader order in contact
            .SalesOrderHeader)
        {
            Console.WriteLine(String.Format("PO Number: {0}",
                order.PurchaseOrderNumber));
            Console.WriteLine(String.Format("Order Date: {0}",
                order.OrderDate.ToString()));
            Console.WriteLine("Order items:");
            foreach (SalesOrderDetail item in order.SalesOrderDetail)
            {
                Console.WriteLine(String.Format("Product: {0} "
                    + "Quantity: {1}", item.ProductID.ToString(),
                    item.OrderQty.ToString()));
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (EntityCommandExecutionException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

参照

処理手順

エンティティ型を返すクエリの実行方法 (Entity Framework)
ナビゲーション プロパティを使用してリレーションシップをナビゲートする方法 (Entity Framework)

概念

クエリ結果の構造化 (Entity Framework)
クエリ ビルダ メソッド (Entity Framework)
Entity SQL の概要

その他のリソース

LINQ to Entities