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