MySQL Bugs: #74943: 6.9.5 broke Contains, StartWith in Linq with EF 6

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:	Windows
Assigned to:		CPU Architecture:	Any

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.

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

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.

Workaround :

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

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

does returns correct results.

I can verify this issue as well.

Windows 7, MySql.Data & MySql.Data.Entity.EF6 6.9.5, MySQL DB 5.6.15.

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.

Caught it on 6.9.5. I'm in tears.

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?!?!?

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.

Hello Bart Calixto,

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

Thanks,
Chiranjeevi.