MySQL Bugs: #65197: LINQ: Adding multiple .Where() to a query results in incorrect SQL WHERE clause

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

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)

Updated submission to OS=Windows from OS=Any.

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.

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".

The provided workaround fixes my issue.