編譯後查詢（LINQ to Entities）
當您在 Entity Framework 中多次執行結構類似的查詢的應用程式時，您可以藉由編譯查詢一次並使用不同的參數執行數次，來經常提升效能。 例如，應用程式可能必須擷取特定城市中的所有客戶，使用者在執行時會在表單中指定城市。 LINQ to Entities 支持針對此目的使用已編譯的查詢。
從 .NET Framework 4.5 開始，會自動快取 LINQ 查詢。 不過，您仍然可以使用已編譯的 LINQ 查詢來降低後續執行和已編譯查詢的成本，比自動快取的 LINQ 查詢更有效率。 不會自動快取將 Enumerable.Contains 運算子套用至記憶體內部集合的 LINQ to Entities 查詢。 此外，不允許在編譯的 LINQ 查詢中參數化記憶體內部集合。
CompiledQuery 類別提供查詢的編譯和快取，以供重複使用。 這個類別在概念上包含了多個多載的 CompiledQueryCompile 方法。 呼叫 Compile 方法，以建立新的委派來表示已編譯的查詢。 提供 ObjectContext 和參數值的 Compile 方法會傳回一個會產生某些結果的委派（例如 IQueryable<T> 實例）。 查詢只會在第一次執行期間編譯一次。 編譯時為查詢設定的合併選項之後無法變更。 編譯查詢之後，您只能提供基本類型的參數，但無法取代變更所產生 SQL 的查詢部分。 如需詳細資訊，請參閱 EF Merge Options 和 Compiled Query。
CompiledQuery
Compile 方法編譯的 LINQ to Entities 查詢表示式是由其中一個泛型 Func 委派表示，例如 Func<T1,T2,T3,T4,TResult>。 查詢表達式最多可以封裝 ObjectContext 參數、傳回參數和 16 個查詢參數。 如果需要超過 16 個查詢參數，您可以建立結構，其屬性代表查詢參數。 接著，您可以在設定屬性之後，在查詢表達式中使用 結構上的屬性。
範例 1
下列範例會編譯然後叫用接受 Decimal 輸入參數的查詢，並傳回一連串訂單，其中總到期金額大於或等於 $200.00：
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: {order.SalesOrderID} Order date: {order.OrderDate} Total due: {order.TotalDue}");
}
}
}
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
範例 2
以下範例會先編譯，然後呼叫一個查詢以傳回 ObjectQuery<T> 的實體：
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);
}
}
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
範例 3
下列範例會編譯並執行一個查詢，該查詢會以 Decimal 型態值傳回產品清單價格的平均值：
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 $: {averageProductPrice}");
}
}
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
範例 4
下列範例會編譯後執行一個查詢，此查詢接受 String 作為輸入參數，然後傳回電子郵件地址以指定字串開頭的 Contact。
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': {foundContact.EmailAddress}");
}
}
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
範例 5
下列範例會編譯然後叫用接受 DateTime 和 Decimal 輸入參數的查詢，並傳回訂單日期晚於 2003 年 3 月 8 日，且到期總額小於 $300.00 的訂單序列：
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: {order.SalesOrderID} Order date: {order.OrderDate} Total due: {order.TotalDue}");
}
}
}
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
範例 6
下列範例會編譯然後叫用接受 DateTime 輸入參數的查詢，並傳回訂單日期晚於 2004 年 3 月 8 日的訂單序列。 此查詢會將順序信息當做匿名型別的序列傳回。 匿名型別是由編譯程式推斷，因此您無法在 CompiledQuery的 Compile 方法中指定型別參數，而且型別是在查詢本身中定義。
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: {order.SalesOrderID} Order date: {order.OrderDate} Total due: {order.TotalDue}");
}
}
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
範例 7
下列範例會編譯並執行一個查詢，該查詢接受使用者自定義的結構作為輸入參數，並傳回一系列的訂單。 結構會定義開始日期、結束日期和到期查詢參數總計，而查詢會傳回 2003 年 3 月 3 日到 3 月 8 日之間的訂單，總到期金額大於 $700.00。
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: {sale.SalesOrderID}");
Console.WriteLine($"Ship date: {sale.ShipDate}");
Console.WriteLine($"Total due: {sale.TotalDue}");
}
}
}
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
定義查詢參數的結構：
struct MyParams
{
public DateTime startDate;
public DateTime endDate;
public decimal totalDue;
}
Public Structure MyParams
Public startDate As DateTime
Public endDate As DateTime
Public totalDue As Decimal
End Structure