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.

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.

public static Func<Northwnd, string, IQueryable<Customer>>
    CustomersByCity =
        CompiledQuery.Compile((Northwnd db, string city) =>
            from c in db.Customers where c.City == city select c);

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


// The following example invokes such a compiled query in the main
// program.

public IEnumerable<Customer> GetCustomersByCity(string city)
{
    var myDb = GetNorthwind();
    return Queries.CustomersByCity(myDb, city);
}

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
{
    public string ContactName { get; set; }
}

class Queries2
{
    public static Func<Northwnd, string, IEnumerable<SimpleCustomer>> CustomersByCity =
        CompiledQuery.Compile<Northwnd, string, IEnumerable<SimpleCustomer>>(
        (Northwnd db, string city) =>
        from c in db.Customers
        where c.City == city
        select new SimpleCustomer { ContactName = c.ContactName });
}


From http://msdn.microsoft.com/en-us/library/bb399335.aspx