Behavior of Variables in LINQ to Entities Queries
LINQ is very nice about referencing local variables without any additional declaration. When I ran my first such LINQ to Entities query, I was ecstatic. The next moment I was puzzled – are the values of those variables picked at compile-time and later used as constants, or are they picked at each execution, so they are real parameters?
I did some investigation, and unfortunately there is no single answer. First of all, it’s implementation-dependent. That means the behavior of variables in LINQ to Entities may be different from the behavior of variables in LINQ to SQL. This post is all about LINQ to Entities.
I’ll be showing some examples over the Products entity set of the Northwind model. The set contains 77 items with consecutive ID’s from 1 through 77:
ProductID |
ProductName |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
UnitsOnOrder |
ReorderLevel |
Discontinued |
1 |
Chai |
10 boxes x 20 bags |
18.0000 |
39 |
0 |
10 |
False |
2 |
Chang |
24 - 12 oz bottles |
19.0000 |
17 |
40 |
25 |
False |
3 |
Aniseed Syrup |
12 - 550 ml bottles |
10.0000 |
13 |
70 |
25 |
False |
4 |
Chef Anton's Cajun Seasoning |
48 - 6 oz jars |
22.0000 |
53 |
0 |
0 |
False |
5 |
Chef Anton's Gumbo Mix |
36 boxes |
21.3500 |
0 |
0 |
0 |
True |
6 |
Grandma's Boysenberry Spread |
12 - 8 oz jars |
25.0000 |
120 |
0 |
25 |
False |
7 |
Uncle Bob's Organic Dried Pears |
12 - 1 lb pkgs. |
30.0000 |
15 |
0 |
10 |
False |
8 |
Northwoods Cranberry Sauce |
12 - 12 oz jars |
40.0000 |
6 |
0 |
0 |
False |
9 |
Mishi Kobe Niku |
18 - 500 g pkgs. |
97.0000 |
29 |
0 |
0 |
True |
10 |
Ikura |
12 - 200 ml jars |
31.0000 |
31 |
0 |
0 |
False |
11 |
Queso Cabrales |
1 kg pkg. |
21.0000 |
22 |
30 |
30 |
False |
12 |
Queso Manchego La Pastora |
10 - 500 g pkgs. |
38.0000 |
86 |
0 |
0 |
False |
13 |
Konbu |
2 kg box |
6.0000 |
24 |
0 |
5 |
False |
14 |
Tofu |
40 - 100 g pkgs. |
23.2500 |
35 |
0 |
0 |
False |
15 |
Genen Shouyu |
24 - 250 ml bottles |
15.5000 |
39 |
0 |
5 |
False |
16 |
Pavlova |
32 - 500 g boxes |
17.4500 |
29 |
0 |
10 |
False |
17 |
Alice Mutton |
20 - 1 kg tins |
39.0000 |
0 |
0 |
0 |
True |
18 |
Carnarvon Tigers |
16 kg pkg. |
62.5000 |
42 |
0 |
0 |
False |
19 |
Teatime Chocolate Biscuits |
10 boxes x 12 pieces |
9.2000 |
25 |
0 |
5 |
False |
20 |
Sir Rodney's Marmalade |
30 gift boxes |
81.0000 |
40 |
0 |
0 |
False |
21 |
Sir Rodney's Scones |
24 pkgs. x 4 pieces |
10.0000 |
3 |
40 |
5 |
False |
22 |
Gustaf's Knäckebröd |
24 - 500 g pkgs. |
21.0000 |
104 |
0 |
25 |
False |
23 |
Tunnbröd |
12 - 250 g pkgs. |
9.0000 |
61 |
0 |
25 |
False |
24 |
Guaraná Fantástica |
12 - 355 ml cans |
4.5000 |
20 |
0 |
0 |
True |
25 |
NuNuCa Nuß-Nougat-Creme |
20 - 450 g glasses |
14.0000 |
76 |
0 |
30 |
False |
… |
… |
... |
... |
... |
... |
... |
... |
I expected the following example to retrieve either item 11 twice, or item 11 and item 25. However it returned neither – it returned item 11 and item 21.
using (Northwind northwind = new Northwind(Program.NorthwindConnectionString))
{
int bottomProductID = 10;
int skipProducts = 1;
var productByVars = (from product in northwind.Products
where product.ProductID >= bottomProductID // Lambda expression
orderby product.ProductID
select product)
.Skip(skipProducts) // Static expression
.Take(1);
// Execute and render results (1)
Console.WriteLine("bottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);
Product theProduct = productByVars.First();
Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);
// Modify the parameter variables
bottomProductID = 20;
skipProducts = 5;
// Execute and render results (2)
Console.WriteLine("\n\nbottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);
theProduct = productByVars.First();
Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);
}
The explanation for that behavior is this: when a local variable is used in a lambda expression it behaves as a parameter, i.e. it’s value is re-evaluated each time the query is executed; when a variable is used in a static expression, it’s value is picked at compile-time, and then it’s used as a constant. Even if you know that rule (and now that you do), unless you use the method-based style of writing LINQ to Entities queries, it’s not very clear what expression is static and what is lambda.
So is there a way to guarantee a consistent behavior of local variables? We’ve gotten to the “good news” part of the post – yes, there is. In Beta 3 we are introducing the concept of “compiled LINQ to Entities queries”. Compiled queries are explicit about what their parameters are. If local variables are still referenced, they are used as constants. The following example retrieves items 11 and 25 as it should be expected:
int bottomProductID = 10;
int skipProducts = 1;
var productByVars = CompiledQuery.Compile(
(Northwind northwind, int bottomProductIDArg, int skipProductsArg) =>
(from product in northwind.Products
where product.ProductID >= bottomProductIDArg
orderby product.ProductID
select product)
.Skip(skipProductsArg)
.Take(1));
using (Northwind northwind = new Northwind(Program.NorthwindConnectionString))
{
// Execute and render results (1)
Console.WriteLine("bottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);
Product theProduct = productByVars(northwind, bottomProductID, skipProducts).First();
Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);
// Modify the parameter variables
bottomProductID = 20;
skipProducts = 5;
// Execute and render results (2)
Console.WriteLine("\n\nbottomProductID={0}, skipProducts={1}", bottomProductID, skipProducts);
theProduct = productByVars(northwind, bottomProductID, skipProducts).First();
Console.WriteLine("{0}: {1}", theProduct.ProductID, theProduct.ProductName);
}
If you replace the usage of the parameters bottomProductIDArg and skipProductsArg inside the query with the local variables bottomProductID and skipProducts respectively, the query will return item 11 twice.
Comments
Anonymous
November 16, 2007
Couple of blog-posts that I came across; Behaviour of variables in LINQ to Entities and, unrelated,...Anonymous
November 19, 2007
Last Friday I blogged about making local variables behave consistently in LINQ to Entities queries .