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 (2)

  1. Couple of blog-posts that I came across; Behaviour of variables in LINQ to Entities and, unrelated,…

  2. Last Friday I blogged about making local variables behave consistently in LINQ to Entities queries .