General Actions:
DevForce 2010 Resource Center » DevForce development » Query » Query using LINQ » Pass parameters to a query
Last modified on August 15, 2012 17:22
Contents
We often want to create a query where we can pass parameters to a query to control some aspect of the query. For database developers there is often a temptation to look for a query parameters collection on the query to accomplish this task. While such a collection does exist, it is ONLY intended for use with a very specific subset of POCO queries. In all other cases, including most POCO query use cases, the alternative provided below is both simpler and more easily understood.
Closures
Parameters can be easily simulated by using .NET closures. This is simply a complicated way of saying that we can just include a reference to a local variable in our query and the query will use the value of this variable when the query is executed. For example, in the query below, we create a single query object but change the parameter customerName between executions of this query.
| C# | String customerName = null; // The customerName variable is a 'parameter' var query = myEntityManager.Customers.Where(c => c.CompanyName.StartsWith(customerName)); customerName = "ABC"; var customersStartingWithABC = query.ToList(); customerName = "XYZ"; var customersStartingWithXYZ = query.ToList(); |
| VB | Dim customerName As String = Nothing ' The customerName variable is a 'parameter' Dim query = myEntityManager.Customers.Where(Function(c) c.CompanyName.StartsWith(customerName)) customerName = "ABC" Dim customersStartingWithABC = query.ToList() customerName = "XYZ" Dim customersStartingWithXYZ = query.ToList() |
Basically a variable can be used anywhere a constant value would normally be used in a query. The value of the variable will be evaluated when the query is executed, not when the query is composed. Another example is shown below:
| C# | float minDiscount = 0; var query2 = _em1.OrderSummaries.Where(o => o.OrderDetails.Any(od => od.Discount > minDiscount)); minDiscount = 0.0F; var ordersWithAnyDiscount = query2.ToList(); minDiscount = 0.1F; var ordersWithLargeDiscount = query2.ToList(); |
| VB | Dim minDiscount As Single = 0 Dim query2 = _em1.OrderSummaries.Where(Function(o) _ o.OrderDetails.Any(Function(od) od.Discount > minDiscount)) minDiscount = 0.0F Dim ordersWithAnyDiscount = query2.ToList() minDiscount = 0.1F Dim ordersWithLargeDiscount = query2.ToList() |
More complex queries that are composed completely dynamically are discussed in dynamic queries.
One interesting side note here is that the QueryCache, will contain the "resolved" version of the query. This means that if the same query is executed twice with two different values provided for any closure "parameters"; two entries will be made in the query cache. For example, the following two queries will be treated identically by the EntityManager.
| C# | customerName = "ABC"; var query1 = myEntityManager.Customers.Where(c => c.CompanyName.StartsWith(customerName)); var customersStartingWithABC = query1.ToList(); var query2 = myEntityManager.Customers.Where(c => c.CompanyName.StartsWith("ABC")); var sameCustomersFromSameQuery = query2.ToList(); |
| VB | customerName = "ABC" Dim query1 = myEntityManager.Customers.Where(Function(c) _ c.CompanyName.StartsWith(customerName)) Dim customersStartingWithABC = query1.ToList() Dim query2 = myEntityManager.Customers.Where(Function(c) _ c.CompanyName.StartsWith("ABC")) Dim sameCustomersFromSameQuery = query2.ToList() |