編譯的查詢 (LINQ to Entities) Compiled queries (LINQ to Entities)

當您的應用程式執行了 Entity Framework 中結構類似的查詢多次時,您可經常增加效能,其方式是編譯查詢一次,然後使用不同的參數執行查詢多次。When you have an application that executes structurally similar queries many times in the Entity Framework, you can frequently increase performance by compiling the query one time and executing it several times with different parameters. 例如,應用程式可能必須擷取特定城市中的所有客戶;此城市是使用者在執行階段於表單中所指定。For example, an application might have to retrieve all the customers in a particular city; the city is specified at runtime by the user in a form. LINQ to Entities 支援針對這個用途所編譯的查詢。LINQ to Entities supports using compiled queries for this purpose.

從 .NET Framework 4.5 開始,會自動快取 LINQ 查詢。Starting with .NET Framework 4.5, LINQ queries are cached automatically. 不過,之後執行時您仍可以使用已編譯的 LINQ 查詢減少這種成本,且已編譯查詢可能比自動快取的 LINQ 查詢更有效率。However, you can still use compiled LINQ queries to reduce this cost in later executions and compiled queries can be more efficient than LINQ queries that are automatically cached. Enumerable.Contains不會自動快取將運算子套用至記憶體中集合的 LINQ to Entities 查詢。LINQ to Entities queries that apply the Enumerable.Contains operator to in-memory collections are not automatically cached. 此外,也不允許在編譯的 LINQ 查詢中,將記憶體中的集合參數化。Also, parameterizing in-memory collections in compiled LINQ queries is not allowed.

CompiledQuery 類別提供查詢的編譯和快取以供重複使用。The CompiledQuery class provides compilation and caching of queries for reuse. 就概念而言,這個類別包含數個多載之 CompiledQueryCompile 方法。Conceptually, this class contains a CompiledQuery's Compile method with several overloads. 呼叫 Compile 方法可建立新委派來代表已編譯的查詢。Call the Compile method to create a new delegate to represent the compiled query. Compile 所提供的 ObjectContext 方法和參數值會傳回一個產生某個結果 (例如 IQueryable<T> 執行個體) 的委派。The Compile methods, provided with a ObjectContext and parameter values, return a delegate that produces some result (such as an IQueryable<T> instance). 此查詢只會在第一次執行期間編譯一次。The query compiles once during only the first execution. 不過,您之後無法變更在編譯階段中,針對查詢所設定的合併選項。The merge options set for the query at the time of the compilation cannot be changed later. 一旦編譯查詢之後,您就只能提供基本類型的參數,但您無法取代會變更產生之 SQL 的查詢部分。Once the query is compiled, you can only supply parameters of primitive type but you cannot replace parts of the query that would change the generated SQL. 如需詳細資訊,請參閱 EF Merge 選項和編譯的查詢For more information, see EF Merge Options and Compiled Queries.

的方法所編譯的 LINQ to Entities 查詢運算式 CompiledQuery Compile 是由其中一個泛型 Func 委派(例如)表示 Func<T1,T2,T3,T4,TResult>The LINQ to Entities query expression that the CompiledQuery's Compile method compiles is represented by one of the generic Func delegates, such as Func<T1,T2,T3,T4,TResult>. 查詢運算式最多只能封裝一個 ObjectContext 參數、一個傳回參數和十六個查詢參數。At most, the query expression can encapsulate an ObjectContext parameter, a return parameter, and 16 query parameters. 如果需要使用十六個以上的查詢參數,您可以建立其屬性代表查詢參數的結構。If more than 16 query parameters are required, you can create a structure whose properties represent query parameters. 然後,當您設定這些參數之後,就可以將此結構的屬性用於查詢運算式中。You can then use the properties on the structure in the query expression after you set the properties.

範例 1Example 1

下列範例會先編譯然後再叫用接受 Decimal 輸入參數的查詢,並且傳回訂單序列,其中的總到期金額大於或等於 $200.00:The following example compiles and then invokes a query that accepts a Decimal input parameter and returns a sequence of orders where the total due is greater than or equal to $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: {0}  Order date: {1} Total due: {2}",
                order.SalesOrderID,
                order.OrderDate,
                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

範例 2Example 2

下列範例會先編譯再叫用傳回 ObjectQuery<T> 執行個體的查詢:The following example compiles and then invokes a query that returns an ObjectQuery<T> instance:

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

範例 3Example 3

下列範例會先編譯再叫用一個查詢,此查詢會以 Decimal 值形式傳回產品標價的平均值。The following example compiles and then invokes a query that returns the average of the product list prices as a Decimal value:

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);
    }
}
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

範例 4Example 4

下列範例會先編譯然後再叫用接受輸入參數的查詢,然後傳回 String Contact 其電子郵件地址開頭為指定字串的:The following example compiles and then invokes a query that accepts a String input parameter and then returns a Contact whose email address starts with the specified string:

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);
    }
}
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

範例 5Example 5

下列範例會先編譯然後再叫用接受 DateTimeDecimal 輸入參數的查詢,並且傳回訂單序列,其中的訂單日期晚於 2003 年 3 月 8 日,總應付金額則少於 $300.00:The following example compiles and then invokes a query that accepts DateTime and Decimal input parameters and returns a sequence of orders where the order date is later than March 8, 2003, and the total due is less than $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: {0} Order date: {1} Total due: {2}", order.SalesOrderID, order.OrderDate, 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

範例 6Example 6

下列範例會先編譯然後再叫用接受 DateTime 輸入參數的查詢,並且傳回訂單序列,其中的訂單日期晚於 2004 年 3 月 8 日。The following example compiles and then invokes a query that accepts a DateTime input parameter and returns a sequence of orders where the order date is later than March 8, 2004. 此查詢會傳回匿名型別序列形式的訂單資訊。This query returns the order information as a sequence of anonymous types. 匿名型別是由編譯器所推斷,所以您無法在 CompiledQueryCompile 方法中指定型別參數,而且此型別會在查詢本身定義。Anonymous types are inferred by the compiler, so you cannot specify type parameters in the CompiledQuery's Compile method and the type is defined in the query itself.

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);
    }
}
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

範例 7Example 7

下列範例會先編譯然後再叫用接受使用者定義結構輸入參數的查詢,並且傳回訂單序列。The following example compiles and then invokes a query that accepts a user-defined structure input parameter and returns a sequence of orders. 此結構會定義開始日期、結束日期和應付總額查詢參數,而且此查詢會傳回在 2003 年 3 月 3 日與 3 月 8 日之間出貨而且應付總額超過 $700.00 的訂單。The structure defines start date, end date, and total due query parameters, and the query returns orders shipped between March 3 and March 8, 2003 with a total due greater than $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: {0}", sale.SalesOrderID);
            Console.WriteLine("Ship date: {0}", sale.ShipDate);
            Console.WriteLine("Total due: {0}", 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

定義查詢參數的結構:The structure that defines the query parameters:

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

另請參閱See also