Export (0) Print
Expand All

How to: Execute a Query that Returns Nested Collections

This shows how to execute a command against a conceptual model by using an EntityCommand object, and how to retrieve the nested collection results by using an 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. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).

  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 System.Data.Metadata.Edm;
        
    

Example

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


using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
// Create a nested query.
string esqlQuery =
@"Select c.ContactID, c.SalesOrderHeaders
From AdventureWorksEntities.Contacts 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"]);
}
}
}
}
conn.Close();
}
    

See Also




Build Date:

2012-08-02
Show:
© 2014 Microsoft