Avoid impurities in Linq To SQL
This problem actually troubled me for hours until I accidentally figured out how to get around it. What I learned is try to avoid impurities in Linq statements. For instance, if you lambda expression's filter is applied by indexing an array value, do not access the array directly inside the lambda value. Instead get the value from the array assign it to a variable and use the variable in the Linq to SQL filter statements. I am not sure why I have to declare a variable to avoid an impurity. But if you do not follow this technique, more than likely you would end up with runtime error in your Linq statement. Even if you get pass the runtime error, you would end up with a wrong filter value being applied on your lambda expressions. Let's take a look at an example to understand the problem I am trying to describe.
In the above example I grab the top 2 order for every city in my array list which happens to be London and New York. In order to accomplish that I am looping through my array of cities and for each city, I am creating a lambda expression filtered by city. On the next iteration of the loop I am going to the else statement and combining my existing Iqueryable of orders with the old one. Basically in terms of SQL, I am simply applying Union operator to union Order records for both cities. However if the query were to execute the sql generated has city filter that set to New York as shown in the diagram. We would have expected that first part of the query would have the filter for the city of London and next part of the query would have a filter of New York. However in this case both portions of the query had a filter of New York. This is the side effect of accessing cities array directly inside of a lambda expression. Since we accessed cities array directly, the last value the array had on the last iteration of the loop, is the value that Linq to sql uses for all the filters in the query.
In order to fix the issue simply grab the value from the array and store it in a variable and use that variable inside of the lambda expression. Here is the correct version of the query that applies the correct filter.
