如何:执行返回复杂类型的查询 (EntityClient)
本主题演示如何执行返回包含复杂类型属性的实体类型对象的 Entity SQL 查询。
运行本示例中的代码
将 AdventureWorks 销售模型添加到您的项目并将项目配置为使用实体框架 。 有关更多信息,请参见如何:使用实体数据模型向导(实体框架)。
在应用程序的代码页中,添加以下 using 语句(在 Visual Basic 中为 Imports):
Imports System Imports System.Collections.Generic Imports System.Collections Imports System.Data.Common Imports System.Data Imports System.IO Imports System.Data.SqlClient Imports System.Data.EntityClient Imports System.Data.Metadata.Edm
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;
双击 .edmx 文件可在实体设计器的模型浏览器窗口中显示模型。 在实体设计器图面上,选择 Contact 实体类型的 Email 和 Phone 属性,然后右击并选择**“重构为新的复杂类型”**。
具有选定 Email 和 Phone 属性的新复杂类型将添加到**“模型浏览器”。 该复杂类型具有一个默认名称:在“属性”**窗口中将该类型重命名为 EmailPhone。 此外,新的 ComplexProperty 属性将添加到 Contact 实体类型。 将该属性重命名为 EmailPhoneComplexType。
有关使用“实体数据模型向导”创建和修改复杂类型的信息,请参见How to: Refactor Existing Properties into a Complex Type Property (Entity Data Model Tools)和How to: Create and Modify Complex Types。
示例
下面的示例执行一个查询,该查询返回 Contact 对象的集合并显示 Contact 对象的两个属性:ContactID 和 EmailPhoneComplexType 复杂类型的值。
Using conn As New EntityConnection("name=AdventureWorksEntities")
conn.Open()
Dim esqlQuery As String = "SELECT VALUE contacts FROM" & _
" AdventureWorksEntities.Contacts AS contacts WHERE contacts.ContactID == @id"
' Create an EntityCommand.
Using cmd As EntityCommand = conn.CreateCommand()
cmd.CommandText = esqlQuery
Dim param As New EntityParameter()
param.ParameterName = "id"
param.Value = 3
cmd.Parameters.Add(param)
' Execute the command.
Using rdr As EntityDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
' The result returned by this query contains
' Address complex Types.
While rdr.Read()
' Display CustomerID
Console.WriteLine("Contact ID: {0}", rdr("ContactID"))
' Display Address information.
Dim nestedRecord As DbDataRecord = TryCast(rdr("EmailPhoneComplexProperty"), DbDataRecord)
Console.WriteLine("Email and Phone Info:")
For i As Integer = 0 To nestedRecord.FieldCount - 1
Console.WriteLine((" " & nestedRecord.GetName(i) & ": ") + nestedRecord.GetValue(i))
Next
End While
End Using
End Using
conn.Close()
End Using
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
string esqlQuery = @"SELECT VALUE contacts FROM
AdventureWorksEntities.Contacts AS contacts
WHERE contacts.ContactID == @id";
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
EntityParameter param = new EntityParameter();
param.ParameterName = "id";
param.Value = 3;
cmd.Parameters.Add(param);
// 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("Contact ID: {0}",
rdr["ContactID"]);
// Display Address information.
DbDataRecord nestedRecord =
rdr["EmailPhoneComplexProperty"] as DbDataRecord;
Console.WriteLine("Email and Phone Info:");
for (int i = 0; i < nestedRecord.FieldCount; i++)
{
Console.WriteLine(" " + nestedRecord.GetName(i) +
": " + nestedRecord.GetValue(i));
}
}
}
}
conn.Close();
}