Behavior of Variables in LINQ to Entities Queries - Zlatko Michailov on Entity SQL - Site Home

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.