Store and Reuse Queries

ALinq Document > Programming Guide > Querying the Database

Store and Reuse Queries

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. ALinq supports the use of compiled queries for this purpose.

Example

public static Func<AccessNorthwind, string, IQueryable<Customer>>
            CustomersByCity = CompiledQuery.Compile((AccessNorthwind db, string city) =>
                                            db.Customers.Where(c => c.City == city));  
                                            
public static Func<AccessNorthwind, string, IQueryable<Customer>>
            CustomersById = CompiledQuery.Compile((AccessNorthwind db, string id) => 
                                          db.Customers.Where(c => c.CustomerID == id));

    Class Queries

        Public Shared CustomersByCity As  _
            Func(Of AccessNorthwind, String, IQueryable(Of Customer)) = _
                CompiledQuery.Compile(Function(db As AccessNorthwind, _
        city As String) _
            From c In db.Customers Where c.City = city Select c)

        Public Shared CustomersById As  _
            Func(Of AccessNorthwind, String, IQueryable(Of Customer)) = _
                CompiledQuery.Compile(Function(db As AccessNorthwind, _
        id As String) _
            db.Customers.Where(Function(c) c.CustomerID = id))

    End Class

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.

var db = new AccessNorthwind("C:/Northwind.mdb");
var customers = CustomersByCity(db, "London").ToList();

foreach (var cust in customers)
    Console.WriteLine(cust.ContactName);

Dim db = New AccessNorthwind("C:/Northwind.mdb")
Dim customers = Queries.CustomersByCity(db, "London").ToList()

For Each cust In customers
    Console.WriteLine(cust.ContactName)
Next