What you see there the value of the parameter is same in p0 and p2 against the value in the TSQL generated by the hardcoded code.
What is happening here “if you see in the for each loop only one I variable is created and it is being referred by both of the variables p0 and p2 when we come out of the loop and finally generate the TSQL we are referring to the same memory location and hence have the last value in the list .
To resolve this we have to take a variable declared inside the foreach loop which will temporarily hold the value of LIST and then we can use it in the LINQ
So now each value is temporarily being kept in the variable int x and then parameter is pointing to that variable which will be different memory location considering the variable is being created each time loop through.
We will see the same issue if we are using the complex data types like class. to resolve that we either we can take different variables to handle various data member of the class or we can copy the object if the class has implemented cloning.
Author : Lalitesh(MSFT) SQL Developer Engineer, Microsoft and Naresh(MSFT), SQL Developer Technical Lead , Microsoft
Reviewed by : Ambuj(MSFT), SQL Developer Technical Lead , Microsoft