MySQL Bugs: #83715: MySQL Connector/NET uses LOCATE instead of LIKE for Contains, StartsWith queries

Bug #83715	MySQL Connector/NET uses LOCATE instead of LIKE for Contains, StartsWith queries
Submitted:	7 Nov 2016 8:49	Modified:	8 Nov 2016 5:50
Reporter:	Sparsh Khandelwal	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.8, 6.9.9	OS:	Windows (Windows 8.1)
Assigned to:		CPU Architecture:	Any
Tags:	connector / net, linq

Description:
I'm using EntityFramework and LINQ to get data from databases. Below is the query I'm using to get applicant's data from db - 

_DbContext.Set<Applicant>().Where(x => x.FirstName.Contains(searchTerm));

Ideally, this should generate query like - 
Select * from Applicant where FirstName like '%searchTerm%';

But, it is generating using LOCATE instead of LIKE - 
Select * from Applicant as Extent1 WHERE (LOCATE('searchTerm', Extent1.FirstName)) > 0;

Because of LOCATE, it isn't making use of any index placed on FirstName and thus performance is severely affected.

Similar bug has been logged earlier for MySql Connector 6.8.3 version here - http://bugs.mysql.com/bug.php?id=72058

How to repeat:
Create a new MySQL table, e.g.

CREATE TABLE transit_ukrail_rti.stoppoints (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(250) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX IDX_stoppoints_derivedName (derivedName),
)

Create a new .NET Project and then install/reference these libraries:

Entity Framework 6.1
MySQL Connector/Net 6.9.8, 6.9.9
MySQL for Visual Studio 1.2.6 (optional)

Write some LINQ code to retrieve search results from the DB and log the generated SQL, e.g:

context.Database.Log = s => Debug.WriteLine(s);
var searchTerm = "Test";
var matchingStops = context.stoppoints.Where(sp => sp.name.StartsWith(searchTerm));  // Ensure that searchTerm is a variable, and not directly passed as value here

Inspect the contents of the Debug window to view the generated SQL:

SELECT
`Extent1`.`id`, 
`Extent1`.`name`, 
FROM `stoppoints` AS `Extent1`
 WHERE (LOCATE(@p__linq__0, `Extent1`.`name`)) = 1

Verify that the LOCATE() function is being used rather than the 'SELECT...  LIKE...' syntax.

Suggested fix:
Ensure that generated SQL from LINQ queries use LIKE instead of LOCATE, when using Contains(), StartsWith() or EndsWith().

Hello  Sparsh Khandelwal,

Thank you for the bug report.
I could not repeat the issue at our end using with Visual Studio 2013, Connector/NET 6.9.9 version.
Could you please provide repeatable test case (exact steps/sample project, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Debug Trace:
Opened connection at 11/7/2016 04:16:22 +05:30

SELECT
`Extent1`.`id`, 
`Extent1`.`name`
FROM `stoppoints` AS `Extent1`
 WHERE `Extent1`.`name` LIKE @p__linq__0

Ok. I found the issue. I had an extra check in the query that I removed here for the sake of simplification. This is the linq query that I'm using - 

_DBContext.Set<Applicant>().Where(x => searchTerm != null ? x.FirstName.Contains(searchTerm.Trim()) : 1 == 1);       // Generates LOCATE

This query generates LINQ. But, if I remove the additional checks and just write the query like this - 
_DBContext.Set<Applicant>().Where(x => x.FirstName.Contains(searchTerm.Trim()));

Then, it generates LIKE, and not LOCATE.

Hello  Sparsh Khandelwal,

Thank you for the bug feedback.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.

output : 
Debug Trace:
Opened connection at 11/8/2016 11:14:54 +05:30

SELECT
`Extent1`.`id`, 
`Extent1`.`name`
FROM `stoppoints` AS `Extent1`
 WHERE (LOCATE(TRIM(@p__linq__0), `Extent1`.`name`)) = 1