HOW TO:執行傳回基本型別的查詢 (Entity Framework)
本主題提供的範例將說明如何執行可傳回基本型別 (Primitive Type) 的查詢,也會顯示使用下列每一個 實體架構 查詢技術的相同範例:
LINQ to Entities
使用 ObjectQuery<T> 的 Entity SQL
ObjectQuery<T> 的查詢產生器方法
本主題的範例是根據 Adventure Works Sales Model。若要執行此範例中的程式碼,您必須已經將 AdventureWorks Sales Model 加入到專案中,並設定您的專案使用 Entity Framework。若要這樣做,請完成 HOW TO:手動設定 Entity Framework 專案和 HOW TO:以手動方式定義 Entity Data Model (Entity Framework) 中的程序。您也可以使用 [Entity Data Model 精靈] 定義 AdventureWorks Sales Model。如需詳細資訊,請參閱 HOW TO:使用 Entity Data Model 精靈 (Entity Framework)。
範例
以下是 LINQ 到實體 範例。
Dim contactId As Integer = 377
Using advWorksContext As New AdventureWorksEntities
Try
'Select a value.
Dim orders As ObjectQuery(Of SalesOrderHeader) _
= advWorksContext.SalesOrderHeader
Dim orderQuery = _
From order In orders _
Where order.Contact.ContactID = contactId _
Select order.PurchaseOrderNumber.Length
' Iterate through the collection of values.
For Each result In orderQuery
Console.WriteLine("{0}", result)
Next
' Use a nullable DateTime value because ShipDate can be null.
Dim shipDateQuery As IQueryable(Of Date?) = _
From order In orders _
Where order.Contact.ContactID = contactId _
Select order.ShipDate
' Iterate through the collection of values.
For Each shipDate In shipDateQuery
Dim shipDateMessage As String = "date not set"
If Not shipDate = Nothing Then
shipDateMessage = shipDate.ToString()
End If
Console.WriteLine("Ship Date: {0}.", shipDateMessage)
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
Catch ex As InvalidOperationException
Console.WriteLine(ex.ToString())
End Try
End Using
int contactId = 377;
using (AdventureWorksEntities advWorksContext
= new AdventureWorksEntities())
{
try
{
// Select a value.
ObjectQuery<SalesOrderHeader> orders
= advWorksContext.SalesOrderHeader;
IQueryable<Int32> orderQuery =
from order in orders
where order.Contact.ContactID == contactId
select order.PurchaseOrderNumber.Length;
// Iterate through the collection of values.
foreach (Int32 result in orderQuery)
{
Console.WriteLine("{0}", result);
}
// Use a nullable DateTime value because ShipDate can be null.
IQueryable<DateTime?> shipDateQuery =
from order in orders
where order.Contact.ContactID == contactId
select order.ShipDate;
// Iterate through the collection of values.
foreach (DateTime? shipDate in shipDateQuery)
{
string shipDateMessage = "date not set";
if (shipDate != null)
{
shipDateMessage = shipDate.ToString();
}
Console.WriteLine("Ship Date: {0}.", shipDateMessage);
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
以下是 實體 SQL 範例。
Dim contactId As Integer = 377
Using advWorksContext As New AdventureWorksEntities
Dim orderQueryString As String = "SELECT VALUE Length(order.PurchaseOrderNumber) " & _
"FROM AdventureWorksEntities.SalesOrderHeader AS order " & _
"WHERE order.CustomerID = @contactId"
Dim shipDateQueryString As String = "SELECT VALUE order.ShipDate " & _
"FROM AdventureWorksEntities.SalesOrderHeader AS order " & _
"WHERE order.CustomerID = @contactId"
Try
' Use the SelectValue method to select a value.
Dim orderQuery As ObjectQuery(Of Int32) = _
New ObjectQuery(Of Int32)(orderQueryString, _
advWorksContext, MergeOption.NoTracking)
orderQuery.Parameters.Add( _
New ObjectParameter("contactId", contactId))
' Iterate through the collection of values.
For Each result In orderQuery
Console.WriteLine("{0}", result)
Next
' Use a nullable DateTime value because ShipDate can be null.
Dim shipDateQuery As ObjectQuery(Of Nullable(Of Date)) = _
New ObjectQuery(Of Nullable(Of Date))(shipDateQueryString, _
advWorksContext, MergeOption.NoTracking)
shipDateQuery.Parameters.Add( _
New ObjectParameter("contactId", contactId))
' Iterate through the collection of values.
For Each shipDate In shipDateQuery
Dim shipDateMessage As String = "date not set"
If Not shipDate = Nothing Then
shipDateMessage = shipDate.ToString()
End If
Console.WriteLine("Ship Date: {0}.", shipDateMessage)
Next
Catch ex As EntityException
Console.WriteLine(ex.ToString())
Catch ex As InvalidOperationException
Console.WriteLine(ex.ToString())
End Try
End Using
int contactId = 377;
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
string orderQueryString = @"SELECT VALUE Length(order.PurchaseOrderNumber)
FROM AdventureWorksEntities.SalesOrderHeader AS order
WHERE order.CustomerID = @contactId";
string shipDateQueryString = @"SELECT VALUE order.ShipDate
FROM AdventureWorksEntities.SalesOrderHeader AS order
WHERE order.CustomerID = @contactId";
try
{
// Use the SelectValue method to select a value.
ObjectQuery<Int32> orderQuery =
new ObjectQuery<Int32>(orderQueryString,
advWorksContext, MergeOption.NoTracking);
orderQuery.Parameters.Add(
new ObjectParameter("contactId", contactId));
// Iterate through the collection of values.
foreach (Int32 result in orderQuery)
{
Console.WriteLine("{0}", result);
}
// Use a nullable DateTime value because ShipDate can be null.
ObjectQuery<Nullable<DateTime>> shipDateQuery =
new ObjectQuery<Nullable<DateTime>>(shipDateQueryString,
advWorksContext, MergeOption.NoTracking);
shipDateQuery.Parameters.Add(
new ObjectParameter("contactId", contactId));
// Iterate through the collection of values.
foreach (Nullable<DateTime> shipDate in shipDateQuery)
{
string shipDateMessage = "date not set";
if (shipDate != null)
{
shipDateMessage = shipDate.ToString();
}
Console.WriteLine("Ship Date: {0}.", shipDateMessage);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
以下是查詢產生器方法範例。
Dim contactId As Integer = 377
Using advWorksContext As New AdventureWorksEntities
Try
' Use the SelectValue method to select a value.
Dim orderQuery As ObjectQuery(Of Int32) = _
advWorksContext.SalesOrderHeader _
.Where("it.CustomerID = @contactId", _
New ObjectParameter("contactId", contactId)) _
.SelectValue(Of Int32)("Length(it.PurchaseOrderNumber)")
' Iterate through the collection of values.
For Each result In orderQuery
Console.WriteLine("{0}", result)
Next
' Use a nullable DateTime value because ShipDate can be null.
Dim shipDateQuery As ObjectQuery(Of Nullable(Of Date)) = _
advWorksContext.SalesOrderHeader _
.Where("it.CustomerID = @contactId", _
New ObjectParameter("contactId", contactId)) _
.SelectValue(Of Nullable(Of Date))("it.ShipDate")
' Iterate through the collection of values.
For Each shipDate In shipDateQuery
Dim shipDateMessage As String = "date not set"
If Not shipDate = Nothing Then
shipDateMessage = shipDate.ToString()
End If
Console.WriteLine("Ship Date: {0}.", shipDateMessage)
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
Catch ex As InvalidOperationException
Console.WriteLine(ex.ToString())
End Try
End Using
int contactId = 377;
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
try
{
// Use the SelectValue method to select a value.
ObjectQuery<Int32> orderQuery =
advWorksContext.SalesOrderHeader
.Where("it.CustomerID = @contactId",
new ObjectParameter("contactId", contactId))
.SelectValue<Int32>("Length(it.PurchaseOrderNumber)");
// Iterate through the collection of values.
foreach (Int32 result in orderQuery)
{
Console.WriteLine("{0}", result);
}
// Use a nullable DateTime value because ShipDate can be null.
ObjectQuery<Nullable<DateTime>> shipDateQuery =
advWorksContext.SalesOrderHeader
.Where("it.CustomerID = @contactId",
new ObjectParameter("contactId", contactId))
.SelectValue<Nullable<DateTime>>("it.ShipDate");
// Iterate through the collection of values.
foreach (Nullable<DateTime> shipDate in shipDateQuery)
{
string shipDateMessage = "date not set";
if (shipDate != null)
{
shipDateMessage = shipDate.ToString();
}
Console.WriteLine("Ship Date: {0}.", shipDateMessage);
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
另請參閱
工作
HOW TO:執行傳回實體類型的查詢 (Entity Framework)
HOW TO:執行傳回匿名型別的查詢 (Entity Framework)
HOW TO:執行參數化查詢 (Entity Framework)