How to: Execute a Query that Returns Nested Collections (EntityClient)

This topic provides an example of how to execute a command against an Entity Data Model by using EntityCommand, and retrieve the nested collection results by using EntityDataReader.

To run the code in this example

  1. Add the AdventureWorks Sales Model to your project and configure your project to use the Entity Framework. To do this, do one of the following:

  2. In the code page for your application, add the following using statements (Imports in Visual Basic):.

    using System;
    using System.Collections.Generic;
    using System.Collections;
    using System.Data.Common;
    using System.Data;
    using System.IO;
    using System.Data.SqlClient;
    using System.Data.EntityClient;
    using AdventureWorksModel;
    using System.Data.Metadata.Edm;
    
    

Example

A nested collection is a collection inside another collection. The following code retrieves a collection of Contacts and nested collections of SalesOrderHeaders associated with each Contact.

using (EntityConnection conn =
    new EntityConnection("name=AdventureWorksEntities"))
{
    conn.Open();
    try
    {
        // Create an EntityCommand.
        using (EntityCommand cmd = conn.CreateCommand())
        {
            // Create a nested query.
            string esqlQuery =
                @"Select c.ContactID, c.SalesOrderHeader
            From AdventureWorksEntities.Contact as c";

            cmd.CommandText = esqlQuery;
            // Execute the command.
            using (EntityDataReader rdr =
                cmd.ExecuteReader(CommandBehavior.SequentialAccess))
            {
                // The result returned by this query contains 
                // ContactID and a nested collection of SalesOrderHeader items.
                // associated with this Contact.
                while (rdr.Read())
                {
                    // the first column contains Contact ID.
                    Console.WriteLine("Contact ID: {0}", rdr["ContactID"]);

                    // The second column contains a collection of SalesOrderHeader 
                    // items associated with the Contact.
                    DbDataReader nestedReader = rdr.GetDataReader(1);
                    while (nestedReader.Read())
                    {
                        Console.WriteLine("   SalesOrderID: {0} ", nestedReader["SalesOrderID"]);
                        Console.WriteLine("   OrderDate: {0} ", nestedReader["OrderDate"]);
                    }
                }
            }
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    conn.Close();
}

See Also

Community Additions

Show: