在 Entity Framework 中多次执行结构相似的查询的应用程序时，可以通过编译查询一次并使用不同参数多次执行查询来频繁提高性能。 例如，应用程序可能必须检索特定城市中的所有客户;用户以窗体在运行时指定城市。 LINQ to Entities 支持出于此目的使用已编译的查询。
从 .NET Framework 4.5 开始，LINQ 查询会自动缓存。 但是，你仍然可以使用已编译的 LINQ 查询来降低后续执行中的此成本，并且编译的查询比自动缓存的 LINQ 查询更高效。 将 Enumerable.Contains 运算符应用于内存集合的 LINQ to Entities 查询不会自动缓存。 此外，不允许在编译的 LINQ 查询中参数化内存中集合。
该 CompiledQuery 类提供用于重用的查询的编译和缓存。 从概念上讲，此类包含 CompiledQuery 的 Compile 方法以及若干重载。
Compile调用该方法以创建新的委托来表示已编译的查询。 给定 Compile 及其参数值，ObjectContext 方法将返回生成某个结果的委托（例如 IQueryable<T> 实例）。 查询仅在第一次执行期间编译一次。 编译时为查询设置的合并选项以后无法更改。 编译查询后，只能提供基元类型的参数，但不能替换将更改生成的 SQL 的查询的各个部分。 有关详细信息，请参阅 EF 合并选项和已编译的查询。
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