Bug #65197 LINQ: Adding multiple .Where() to a query results in incorrect SQL WHERE clause
Submitted: 3 May 2012 21:35 Modified: 7 Aug 2012 16:57
Reporter: Matt Houser Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.5.4 OS:Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any

[3 May 2012 21:35] Matt Houser
Description:
Using LINQ to Entities (with MySQL Connector):

When I build a query by adding .Where clauses in a loop (seems to only happen when added in a loop), the SQL that's generated is not correct WRT the WHERE clause.

How to repeat:
Performing the following LINQ (in C#) query:

var db = new MyEntities();

var orders = (from o in db.Orders
  select o);

string sSearch = "AB CD";

var terms = sSearch.Split(new char[] { ' ' }, 
  StringSplitOptions.RemoveEmptyEntries);

foreach (string term in terms)
{
  orders = orders.Where(o => o.Job.Contains(term));
}
orders.ToArray();

The above code gives me the following SQL on the server:

SELECT
`Extent1`.`OrderId`, 
`Extent1`.`Job`, 
FROM `orders` AS `Extent1`
 WHERE ((LOCATE('CD', `Extent1`.`Job`)) > 0) AND ((LOCATE('CD', `Extent1`.`Job`)) > 0) 

Notice that "CD" is in the WHERE clause twice, and "AB" is absent.

Suggested fix:
There WHERE clause should read:

WHERE ((LOCATE('AB', `Extent1`.`Job`)) > 0) AND ((LOCATE('CD', `Extent1`.`Job`)) > 0)
[3 May 2012 21:51] Matt Houser
Updated submission to OS=Windows from OS=Any.
[5 Jul 2012 21:28] Fernando Gonzalez.Sanchez
Thanks for your report, this is actually a LINQ to objects issue, since the parameter values are already passed with the wrong values 
at method System.Data.Objects.ObjectQuery<T>.GetResults, (referenec to property  base.QueryState.Parameters).

The good news is that is very simple to workaround it, just add a temporary variable:

In your sample code
foreach (string term in terms)
{
  orders = orders.Where(o => o.Job.Contains(term));
}

Replace that code by
foreach (string term in terms)
{
  string tmp = term;
  orders = orders.Where(o => o.Job.Contains( tmp ));
}

('term' is the same for the whole cycle, while 'tmp' is recreated for each iteration, that hints the LINQ generator).
These is documented at http://stackoverflow.com/questions/1280871/multiple-defered-where-clause-expressions-in-li...
I have been able to reproduce the same issue with MSSQL 2008 provider, pure LINQ-to-objects & Connector/NET.

Thanks.
[6 Aug 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Aug 2012 2:05] Matt Houser
The provided workaround fixes my issue.