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: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 6.5.4 | OS: | Windows |
| Assigned to: | Fernando Gonzalez.Sanchez | CPU Architecture: | Any |
[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.
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)