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 .