Bug #74943 6.9.5 broke Contains, StartWith in Linq with EF 6
Submitted: 20 Nov 2014 19:18 Modified: 9 Dec 2014 9:42
Reporter: Bart Calixto Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.5 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[20 Nov 2014 19:18] Bart Calixto
Description:
when doing :

.Where(c => c.display_name.Contains("someword")

query is executed without problem

if I do

string text = "someword";
.Where(c => c.display_name.Contains(text)

query return no result. Investigating the query it turns out that the generated SQL statement is :

LIKE '%someword%' vs LIKE '%p__linq__1%'

from my understanding, the 2nd like is missing the @ parameter, it must be :

LIKE '%@p__linq__1%'

all my contains in production enviroment are gone for good. Can I expect a patch release like right now ? I'm really tired of the incompetence of the MySql connector team.

no connector goes out without some stupid bugs.

How to repeat:
when doing :

.Where(c => c.display_name.Contains("someword")

query is executed without problem

if I do

string text = "someword";
.Where(c => c.display_name.Contains(text)

query return no result.
[21 Nov 2014 12:20] Van Fem
Affects StartsWith, Contains, EndsWith
Affects EF6, EF5
Affects Connector/NET 6.9.5, 6.8.4, 6.7.6: those with update note
"Generated code that matched against the start of a VARCHAR/CHAR column now correctly uses "LIKE" instead of location functions..."
[21 Nov 2014 12:30] Van Fem
This is caused by the parameter being enclosed in quotes like so:
'%p__linq__0%'

This causes MySQL to search for strings containing "p__linq__0", and will return them if you have any in database.
[21 Nov 2014 20:46] Bart Calixto
Workaround :

instead of doing .Where(x => x.name.Contains(text)

doing .Where(x => x.name.Contains(text.Trim()) // or ToLower()

does returns correct results.
[24 Nov 2014 3:33] Aaron Clauson
I can verify this issue as well.

Windows 7, MySql.Data & MySql.Data.Entity.EF6 6.9.5, MySQL DB 5.6.15.
[27 Nov 2014 10:37] Damien DALY
I have downloaded the source code and maybe I have found something :

- In the project "MySql.Data.Entity", the file Generators\SqlGenerator.cs
- In the function
    protected LikeFragment TryPromoteToLike(DbExpression left, DbExpression right, string op)

There are 3 lines for the 3 LIKE patterns, like this one :
                // Pattern LIKE "%..." in EF6
                like.Pattern = new LiteralFragment(string.Format("'%{0}'", par.ParameterName));

I think it may be replaced with something like this :
                like.Pattern = new LiteralFragment(string.Format("'%' + @{0}", par.ParameterName));

So the executed query would work as excepted.
[27 Nov 2014 13:14] Andrej Gerbec
Caught it on 6.9.5. I'm in tears.
[5 Dec 2014 8:41] Thomas L.
I can confirm this on 6.9.5 with EF6, code is like this:

foreach (var searchTerm in searchTermList)
{
	string term = searchTerm;

	articles = articles.Where(d => d.DescriptionLong.Contains(term) || d.Ident.Contains(term) || d.SchematicReference.Contains(term) || d.FootprintReference.Contains(term));
}

If the only searchTerm in searchTermList is "WI100", no elements will be found. If I replace "term" with the string "WI100" all 233 elements will be found correctly.

Really?!?!?
[5 Dec 2014 14:28] Bart Calixto
Thomas L. : Yes, worst part is silence from MySql team.

Get used to this or switch asap to other SQL. As I see this is getting only worse each time.
[9 Dec 2014 9:42] Chiranjeevi Battula
Hello Bart Calixto,

Thank you for the bug report.
This is duplicate of Bug #74918.

Thanks,
Chiranjeevi.