LINQ query where dynamic values does not reach correctly to the generated TSQL
Recently we came across an interesting LINQ issue where we are trying to find out number of customers who have purchased specific products and we were getting incorrect data
Here is the structure of the sample tables which I worked with
We were trying to get the count of the customer using the following LINQ code
DataClasses2DataContext blog = new DataClasses2DataContext();
var cust = from cst inblog.Customers
wherecst.Orders.Where(or => or.ProductID.Equals(2) ).Count() > 0
&& cst.Orders.Where(or => or.ProductID.Equals(1)).Count() > 0
selectcst;
txtCount.Text = cust.Count().ToString();
Here is the TSQL reaching to the SQL Server
exec sp_executesql SELECT COUNT(*) AS[value]
FROM [dbo].[Customers] AS[t0]
WHERE ((( SELECT COUNT(*)
FROM [dbo].[Orders] AS[t1]
WHERE ([t1].[ProductID] = @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
)) > @p1) AND (((
SELECT COUNT(*)
FROM [dbo].[Orders] AS[t2]
WHERE ([t2].[ProductID] = @p2) AND ([t2].[CustomerID] = [t0].[CustomerID])
)) > @p3)',N'@p0 int,@p1 int,@p2 int,@p3 int’,@p0=2,@p1=0,@p2=1,@p3=0
So we were trying to get the count of customers who have ordered productID 1 and 2 both. and this worked fine.
We got 11 records (the data has been taken from the northwind table)
But in the real world these values would be dynamic where we will get those values in some list and then we will form a query like this
DataClasses2DataContext blog = new DataClasses2DataContext();
DataClasses2DataContext blog = new DataClasses2DataContext();
List<int> prodIds = new List<int>();
prodIds.Add(1);
prodIds.Add(2);
var cust = from cst inblog.Customers
selectcst;
foreach (int i inprodIds)
{
cust = cust.Where(cst => cst.Orders.Where(ord => ord.ProductID.Equals(i)).Count() > 0);
}
txtCount.Text = cust.Count().ToString();
As in the above code we are actually passing the same value but not hardcoded it should go from the LIST.
Let see the TSQL reaching to the SQL Server
exec sp_executesql N' SELECT COUNT(*) AS[value]
FROM [dbo].[Customers] AS[t0]
WHERE (((
SELECT COUNT(*)
FROM [dbo].[Orders] AS[t1]
WHERE ([t1].[ProductID] = @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
)) > @p1) AND (((
SELECT COUNT(*)
FROM [dbo].[Orders] AS[t2]
WHERE ([t2].[ProductID] = @p2) AND ([t2].[CustomerID] = [t0].[CustomerID])
)) > @p3)',N'@p0 int,@p1 int,@p2 int,@p3 int’,@p0=2,@p1=0,@p2=2,@p3=0
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
foreach (int i inprodIds)
{
intx = i;
cust = cust.Where(cst => cst.Orders.Where(ord => ord.ProductID.Equals(x)).Count() > 0);
}
txtCount.Text = cust.Count().ToString();
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.
Consideration
============
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