如何:执行返回基元类型的查询(实体框架)
本主题提供有关如何执行返回基元类型的查询的示例。将使用以下每种 实体框架 查询技术演示同一示例:
LINQ to Entities
Entity SQL with ObjectQuery<T>
ObjectQuery <T> 的查询生成器方法
本主题中的示例基于 Adventure Works 销售模型。若要运行本示例中的代码,必须已将 AdventureWorks 销售模型添加到您的项目中,并将项目配置为使用实体框架。为此,请完成如何:手动配置实体框架项目和如何:手动定义实体数据模型(实体框架) 中的过程。也可以使用实体数据模型向导定义 AdventureWorks 销售模型。有关更多信息,请参见如何:使用实体数据模型向导(实体框架)。
示例
以下是 LINQ to Entities 示例。
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());
}
}
以下是 Entity 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());
}
}
另请参见
任务
如何:执行返回实体类型的查询(实体框架)
如何:执行返回匿名类型的查询(实体框架)
如何:执行参数化查询(实体框架)