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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.