作法:執行可傳回複雜類型的查詢
本主題說明如何執行 Entity SQL 查詢,以傳回包含複雜類型屬性的實體類型物件。
執行此範例中的程式碼
將 AdventureWorks Sales Model 加入至專案中,並將專案設定成使用 Entity Framework。 如需詳細資訊,請參閱如何:使用實體資料模型精靈。
在應用程式的代碼頁中,新增下列
using
指示詞 (Imports
在 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;
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
在實體設計工具的 [模型瀏覽器] 視窗中,按兩下 .edmx 檔案以顯示模型。 在實體設計工具介面上,選取
Contact
實體類型的Email
和Phone
屬性,然後按一下滑鼠右鍵並選取 [重構至新的複雜類型]。含有所選取
Email
和Phone
屬性的新複雜類型隨即會新增至 [模型瀏覽器]。 複雜類型已有預設名稱:請在 [屬性] 視窗中將類型重新命名為EmailPhone
。 也會將新的ComplexProperty
屬性加入至Contact
實體類型。 將屬性重新命名為EmailPhoneComplexType.
如需使用 [實體資料模型精靈] 建立和修改複雜類型的資訊,請參閱操作說明:將現有屬性重構到複雜類型屬性和操作說明:建立和修改複雜類型。
範例
下列範例所執行的查詢,會傳回 Contact
物件的集合並顯示 Contact
物件的兩個屬性:ContactID
和 EmailPhoneComplexType
複雜類型的值。
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();
}
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