How to: Execute a Query that Returns Complex Types (EntityClient)
This topic shows how to execute an Entity SQL query that returns compex types using EntityCommand. This example uses the schemas defined in How to: Define a Model with Complex Type (Entity Framework). For information about configuring your project, and an example of how to execute a query that returns complex types using Object Services, see How to: Create and Execute Object Queries with Complex Types (Entity Framework).
Example
The following example shows how to create and execute a query with a complex type. The complex type represents a type that includes a set of properties (like an entity type) but does not include a key property. The Address property of the CCustomer entity is implemented as complex type. The following example outputs two properties of CCustomer type: CustomerId and Address. Because Address is a complex type, the code outputs values of Address's properites.
Using conn As EntityConnection = New EntityConnection("name=CustomerComplexAddrContext")
conn.Open()
' Create an EntityCommand.
Using cmd As EntityCommand = conn.CreateCommand()
' Create a query that returns Address complex type.
Dim esqlQuery As String = "SELECT VALUE customers FROM " & _
"CustomerComplexAddrContext.CCustomers " & _
"AS customers WHERE customers.CustomerId < 3"
cmd.CommandText = esqlQuery
' Execute the command.
Using rdr As EntityDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
' The result returned by this query contains
' Address complex Types.
Do While rdr.Read
' Display CustomerID
Console.WriteLine("Customer ID: {0}", _
rdr.Item("CustomerId"))
' Display Address information.
Dim nestedRecord As DbDataRecord = DirectCast(rdr.Item("Address"), DbDataRecord)
Console.WriteLine("Address:")
For i = 0 To nestedRecord.FieldCount - 1
Console.WriteLine(" " + nestedRecord.GetName(i) & _
": " + nestedRecord.GetValue(i))
Next i
Loop
End Using
End Using
conn.Close()
End Using
using (EntityConnection conn =
new EntityConnection("name=CustomerComplexAddrContext"))
{
conn.Open();
// Create a query that returns Address complex type.
string esqlQuery =
@"SELECT VALUE customers FROM
CustomerComplexAddrContext.CCustomers
AS customers WHERE customers.CustomerId < 3";
try
{
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// The result returned by this query contains
// Address complex Types.
while (rdr.Read())
{
// Display CustomerID
Console.WriteLine("Customer ID: {0}",
rdr["CustomerId"]);
// Display Address information.
DbDataRecord nestedRecord =
rdr["Address"] as DbDataRecord;
Console.WriteLine("Address:");
for (int i = 0; i < nestedRecord.FieldCount; i++)
{
Console.WriteLine(" " + nestedRecord.GetName(i) +
": " + nestedRecord.GetValue(i));
}
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}