已編譯的查詢 (LINQ to Entities)
當您的應用程式執行了 Entity Framework 中結構類似的查詢多次時，您可經常增加效能，其方式是編譯查詢一次，然後使用不同的參數執行查詢多次。 例如，應用程式可能必須擷取特定城市中的所有客戶；此城市是使用者在執行階段於表單中所指定。 LINQ to Entities 支援針對這個用途所編譯的查詢。
CompiledQuery 類別提供查詢的編譯和快取以供重複使用。 就概念而言，這個類別包含數個多載之 CompiledQuery 的 Compile 方法。 呼叫 Compile 方法可建立新委派來代表已編譯的查詢。 ObjectContext 所提供的 Compile 方法和參數值會傳回一個產生某個結果 (例如 IQueryable 執行個體) 的委派。 此查詢只會在第一次執行期間編譯一次。 不過，您之後無法變更在編譯階段中，針對查詢所設定的合併選項。 一旦查詢已編譯之後，您就只能提供基本型別的參數，但是無法取代會變更產生之 SQL 的查詢部分。 如需詳細資訊，請參閱 Entity Framework 合併選項和編譯的查詢 (英文)。
CompiledQuery 之 Compile 方法所編譯的 LINQ to Entities 查詢運算式是由其中一個泛型 Func 委派代表，例如 Func。 查詢運算式最多只能封裝一個 ObjectContext 參數、一個傳回參數和十六個查詢參數。 如果需要使用十六個以上的查詢參數，您可以建立其屬性代表查詢參數的結構。 然後，當您設定這些參數之後，就可以將此結構的屬性用於查詢運算式中。
範例
下列範例會先編譯然後再叫用接受 Decimal 輸入參數的查詢，並且傳回訂單序列，其中的總到期金額大於或等於 $200.00：
ReadOnly s_compQuery2 As Func(Of AdventureWorksEntities, Decimal, IQueryable(Of SalesOrderHeader)) = _
CompiledQuery.Compile(Of AdventureWorksEntities, Decimal, IQueryable(Of SalesOrderHeader))( _
Function(ctx As AdventureWorksEntities, total As Decimal) _
From order In ctx.SalesOrderHeaders _
Where (order.TotalDue >= total) _
Select order)
Sub CompiledQuery2()
Using context As New AdventureWorksEntities()
Dim totalDue As Decimal = 200.0
Dim orders As IQueryable(Of SalesOrderHeader) = s_compQuery2.Invoke(context, totalDue)
For Each order In orders
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", _
order.SalesOrderID, _
order.OrderDate, _
order.TotalDue)
Next
End Using
End Sub
static readonly Func<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery2 =
CompiledQuery.Compile<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>>(
(ctx, total) => from order in ctx.SalesOrderHeaders
where order.TotalDue >= total
select order);
static void CompiledQuery2()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
Decimal totalDue = 200.00M;
IQueryable<SalesOrderHeader> orders = s_compiledQuery2.Invoke(context, totalDue);
foreach (SalesOrderHeader order in orders)
{
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}",
order.SalesOrderID,
order.OrderDate,
order.TotalDue);
}
}
}
範例
下列範例會先編譯再叫用傳回 ObjectQuery 執行個體的查詢：
ReadOnly s_compQuery1 As Func(Of AdventureWorksEntities, ObjectQuery(Of SalesOrderHeader)) = _
CompiledQuery.Compile(Of AdventureWorksEntities, ObjectQuery(Of SalesOrderHeader))( _
Function(ctx) ctx.SalesOrderHeaders)
Sub CompiledQuery1_MQ()
Using context As New AdventureWorksEntities()
Dim orders As ObjectQuery(Of SalesOrderHeader) = s_compQuery1.Invoke(context)
For Each order In orders
Console.WriteLine(order.SalesOrderID)
Next
End Using
End Sub
static readonly Func<AdventureWorksEntities, ObjectQuery<SalesOrderHeader>> s_compiledQuery1 =
CompiledQuery.Compile<AdventureWorksEntities, ObjectQuery<SalesOrderHeader>>(
ctx => ctx.SalesOrderHeaders);
static void CompiledQuery1_MQ()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> orders = s_compiledQuery1.Invoke(context);
foreach (SalesOrderHeader order in orders)
Console.WriteLine(order.SalesOrderID);
}
}
範例
下列範例會先編譯再叫用一個查詢，此查詢會以 Decimal 值形式傳回產品標價的平均值。
Using context As New AdventureWorksEntities()
Dim compQuery = CompiledQuery.Compile(Of AdventureWorksEntities, Decimal)( _
Function(ctx) ctx.Products.Average(Function(Product) Product.ListPrice))
Dim averageProductPrice As Decimal = compQuery.Invoke(context)
Console.WriteLine("The average of the product list prices is $: {0}", averageProductPrice)
End Using
static readonly Func<AdventureWorksEntities, Decimal> s_compiledQuery3MQ = CompiledQuery.Compile<AdventureWorksEntities, Decimal>(
ctx => ctx.Products.Average(product => product.ListPrice));
static void CompiledQuery3_MQ()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
Decimal averageProductPrice = s_compiledQuery3MQ.Invoke(context);
Console.WriteLine("The average of the product list prices is $: {0}", averageProductPrice);
}
}
範例
下列範例會先編譯然後再叫用接受 String 輸入參數的查詢，然後傳回電子郵件地址以指定字串開始的 Contact：
Using context As New AdventureWorksEntities()
Dim compQuery = CompiledQuery.Compile(Of AdventureWorksEntities, String, Contact)( _
Function(ctx, name) ctx.Contacts.First(Function(contact) contact.EmailAddress.StartsWith(name)))
Dim contactName As String = "caroline"
Dim foundContact As Contact = compQuery.Invoke(context, contactName)
Console.WriteLine("An email address starting with 'caroline': {0}", _
foundContact.EmailAddress)
End Using
static readonly Func<AdventureWorksEntities, string, Contact> s_compiledQuery4MQ =
CompiledQuery.Compile<AdventureWorksEntities, string, Contact>(
(ctx, name) => ctx.Contacts.First(contact => contact.EmailAddress.StartsWith(name)));
static void CompiledQuery4_MQ()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
string contactName = "caroline";
Contact foundContact = s_compiledQuery4MQ.Invoke(context, contactName);
Console.WriteLine("An email address starting with 'caroline': {0}",
foundContact.EmailAddress);
}
}
範例
下列範例會先編譯然後再叫用接受 DateTime 和 Decimal 輸入參數的查詢，並且傳回訂單序列，其中的訂單日期晚於 2003 年 3 月 8 日，總應付金額則少於 $300.00：
ReadOnly s_compQuery5 = _
CompiledQuery.Compile(Of AdventureWorksEntities, DateTime, Decimal, IQueryable(Of SalesOrderHeader))( _
Function(ctx, orderDate, totalDue) From product In ctx.SalesOrderHeaders _
Where product.OrderDate > orderDate _
And product.TotalDue < totalDue _
Order By product.OrderDate _
Select product)
Sub CompiledQuery5()
Using context As New AdventureWorksEntities()
Dim orderedAfterDate As DateTime = New DateTime(2003, 3, 8)
Dim amountDue As Decimal = 300.0
Dim orders As IQueryable(Of SalesOrderHeader) = _
s_compQuery5.Invoke(context, orderedAfterDate, amountDue)
For Each order In orders
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", _
order.SalesOrderID, order.OrderDate, order.TotalDue)
Next
End Using
End Sub
static readonly Func<AdventureWorksEntities, DateTime, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery5 =
CompiledQuery.Compile<AdventureWorksEntities, DateTime, Decimal, IQueryable<SalesOrderHeader>>(
(ctx, orderDate, totalDue) => from product in ctx.SalesOrderHeaders
where product.OrderDate > orderDate
&& product.TotalDue < totalDue
orderby product.OrderDate
select product);
static void CompiledQuery5()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
DateTime date = new DateTime(2003, 3, 8);
Decimal amountDue = 300.00M;
IQueryable<SalesOrderHeader> orders = s_compiledQuery5.Invoke(context, date, amountDue);
foreach (SalesOrderHeader order in orders)
{
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", order.SalesOrderID, order.OrderDate, order.TotalDue);
}
}
}
範例
下列範例會先編譯然後再叫用接受 DateTime 輸入參數的查詢，並且傳回訂單序列，其中的訂單日期晚於 2004 年 3 月 8 日。 此查詢會傳回匿名型別序列形式的訂單資訊。 匿名型別是由編譯器所推斷，所以您無法在 CompiledQuery 的 Compile 方法中指定型別參數，而且此型別會在查詢本身定義。
Using context As New AdventureWorksEntities()
Dim compQuery = CompiledQuery.Compile( _
Function(ctx As AdventureWorksEntities, orderDate As DateTime) _
From order In ctx.SalesOrderHeaders _
Where order.OrderDate > orderDate _
Select New With {order.OrderDate, order.SalesOrderID, order.TotalDue})
Dim orderedAfterDate As DateTime = New DateTime(2004, 3, 8)
Dim orders = compQuery.Invoke(context, orderedAfterDate)
For Each order In orders
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", _
order.SalesOrderID, order.OrderDate, order.TotalDue)
Next
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var compiledQuery = CompiledQuery.Compile((AdventureWorksEntities ctx, DateTime orderDate) =>
from order in ctx.SalesOrderHeaders
where order.OrderDate > orderDate
select new {order.OrderDate, order.SalesOrderID, order.TotalDue});
DateTime date = new DateTime(2004, 3, 8);
var results = compiledQuery.Invoke(context, date);
foreach (var order in results)
{
Console.WriteLine("ID: {0} Order date: {1} Total due: {2}", order.SalesOrderID, order.OrderDate, order.TotalDue);
}
}
範例
下列範例會先編譯然後再叫用接受使用者定義結構輸入參數的查詢，並傳回訂單序列。 此結構會定義開始日期、結束日期和應付總額查詢參數，而且此查詢會傳回在 2003 年 3 月 3 日與 3 月 8 日之間出貨而且應付總額超過 $700.00 的訂單。
ReadOnly s_compQuery = CompiledQuery.Compile(Of AdventureWorksEntities, MyParams, IQueryable(Of SalesOrderHeader))( _
Function(ctx, mySearchParams) _
From sale In ctx.SalesOrderHeaders _
Where sale.ShipDate > mySearchParams.startDate _
And sale.ShipDate < mySearchParams.endDate _
And sale.TotalDue > mySearchParams.totalDue _
Select sale)
Sub CompiledQuery7()
Using context As New AdventureWorksEntities()
Dim myParams As MyParams = New MyParams()
myParams.startDate = New DateTime(2003, 3, 3)
myParams.endDate = New DateTime(2003, 3, 8)
myParams.totalDue = 700.0
Dim sales = s_compQuery.Invoke(context, myParams)
For Each sale In sales
Console.WriteLine("ID: {0}", sale.SalesOrderID)
Console.WriteLine("Ship date: {0}", sale.ShipDate)
Console.WriteLine("Total due: {0}", sale.TotalDue)
Next
End Using
End Sub
static Func<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>> s_compiledQuery =
CompiledQuery.Compile<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>>(
(ctx, myparams) => from sale in ctx.SalesOrderHeaders
where sale.ShipDate > myparams.startDate && sale.ShipDate < myparams.endDate
&& sale.TotalDue > myparams.totalDue
select sale);
static void CompiledQuery7()
{
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
MyParams myParams = new MyParams();
myParams.startDate = new DateTime(2003, 3, 3);
myParams.endDate = new DateTime(2003, 3, 8);
myParams.totalDue = 700.00M;
IQueryable<SalesOrderHeader> sales = s_compiledQuery.Invoke(context, myParams);
foreach (SalesOrderHeader sale in sales)
{
Console.WriteLine("ID: {0}", sale.SalesOrderID);
Console.WriteLine("Ship date: {0}", sale.ShipDate);
Console.WriteLine("Total due: {0}", sale.TotalDue);
}
}
}
定義查詢參數的結構：
Public Structure MyParams
Public startDate As DateTime
Public endDate As DateTime
Public totalDue As Decimal
End Structure
struct MyParams
{
public DateTime startDate;
public DateTime endDate;
public decimal totalDue;
}
另請參閱
概念
ADO.NET Entity Framework
LINQ to Entities