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:

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.