How to: Store and Reuse Queries (LINQ to SQL)
When you have an application that executes structurally similar queries many times, you can often 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 who are in a particular city, where the city is specified at runtime by the user in a form. LINQ to SQL supports the use of compiled queries for this purpose.
Note: |
|---|
This pattern of usage represents the most common use for compiled queries. Other approaches are possible. For example, compiled queries can be stored as static members on a partial class that extends the code generated by the designer. |
In many scenarios you might want to reuse the queries across thread boundaries. In such cases, storing the compiled queries in static variables is especially effective. The following code example assumes a Queries class designed to store compiled queries, and assumes a Northwind class that represents a strongly typed DataContext.
Class Queries Public Shared CustomersByCity As _ Func(Of Northwnd, String, IQueryable(Of Customer)) = _ CompiledQuery.Compile(Function(db As Northwnd, _ city As String) _ From c In db.Customers Where c.City = city Select c) Public Shared CustomersById As _ Func(Of Northwnd, String, IQueryable(Of Customer)) = _ CompiledQuery.Compile(Function(db As Northwnd, _ id As String) _ db.Customers.Where(Function(c) c.CustomerID = id)) End Class
You cannot currently store (in static variables) queries that return an anonymous type, because type has no name to provide as a generic argument. The following example shows how you can work around the issue by creating a type that can represent the result, and then use it as a generic argument.
Class SimpleCustomer Private _ContactName As String Public Property ContactName() As String Get Return _ContactName End Get Set(ByVal value As String) _ContactName = value End Set End Property End Class Class Queries2 Public Shared CustomersByCity As Func(Of Northwnd, String, IEnumerable(Of SimpleCustomer)) = _ CompiledQuery.Compile(Of Northwnd, String, IEnumerable(Of SimpleCustomer))( _ Function(db As Northwnd, city As String) _ From c In db.Customers _ Where c.City = city _ Select New SimpleCustomer With {.ContactName = c.ContactName}) End Class
Note: