MySQL Bugs: #72058: MySQL Connector/NET generates incorrect SQL for LINQ 'StartsWith' queries

Bug #72058	MySQL Connector/NET generates incorrect SQL for LINQ 'StartsWith' queries
Submitted:	17 Mar 2014 11:03	Modified:	19 Oct 2014 23:23
Reporter:	Carl Partridge	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.8.3	OS:	Any
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any
Tags:	connector, linq, NET

Description:
A common piece of .NET code involves searching a database and matching against the start of a VARCHAR column, e.g. for an 'autocomplete' function.

When using Entity Framework, this is often achieved by a LINQ statement such as the following:

var matchingStops = context.stoppoints.Where(sp => sp.name.StartsWith(stopName));

For the most efficient database lookup, this should produce SQL similar to the following:

SELECT * FROM stoppoints AS `Extent1`
WHERE `Extent1`.name LIKE 'searchterm%';

However, inspection of the generated SQL reveals that the 'LOCATE' SQL function is being used instead, i.e.:

FROM `stoppoints` AS `Extent1`
 WHERE (LOCATE('searchterm', `Extent1`.`derivedName`)) = 1

This latter syntax does not make use of any index placed upon the CHAR or VARCHAR column and, consequently, performance is significantly degraded.

This may be a regression of bug #64935

http://bugs.mysql.com/bug.php?id=64935

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.0.2
MySQL Connector/Net 6.8.3
MySQL for Visual Studio (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 matchingStops = context.stoppoints.Where(sp => sp.name.StartsWith("Lond"));

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 the generated SQL for LINQ queries such as .StartsWith() and .EndsWith() and .Contains() uses the 'LIKE... ' syntax so that MySQL text indices are used.

This may simply involve reversing a regression of bug #64935

Hi, I cannot reproduce this, 
With VS2013, Entity Framework 6.0.0 / 6.0.2, Connector/NET 6.9.3, DbFirst
(also tried Code First with both EF5 / EF6).

The query generated has like, as log sample with sakila's actor:

SELECT
`Extent1`.`actor_id`, 
`Extent1`.`first_name`, 
`Extent1`.`last_name`, 
`Extent1`.`last_update`
FROM `actor` AS `Extent1`
 WHERE `Extent1`.`first_name` LIKE 'Jo%'

-- Executing at 23/09/2014 08:50:23 p. m. -05:00

-- Completed in 3 ms with result: EFMySqlDataReader

Ok, found how to repro,

the argument to StartsWith must be a variable, not hardcoded.

sakilaEntities db = new sakilaEntities();
            string term = "Jo";
            //var q = db.actor.Where(p => p.first_name.StartsWith( "Jo" ));
            var q = db.actor.Where(p => p.first_name.StartsWith( term ));
            //string sql = q.ToString();
            db.Database.Log = p => Debug.WriteLine(p);
            foreach( var row in q )
            {

            }
            Console.ReadLine();

That's great, I'm glad you've been able to repro this, it seems like a regression of an earlier bug as detailed in the description.

Sounds like it's also an issue in connector 6.9.3 then, as you say that's what you've tested with?

Yes, also affects 6.9.3, working on a fix.

Posted by developer:
 
With the fix the query now uses correctly 'LIKE' predicates instead of locate functions for any of LINQ to Entities operators StartsWith, Contains and/or EndsWith.

This fix will appear in MySQL Connector/NET 6.7.6, 6.8.4, 6.9.5.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.7.6 / 6.8.4 / 6.9.5 releases, and here's the changelog entry:

Generated code that matched against the start of a VARCHAR/CHAR column now
correctly uses "LIKE" instead of location functions for the LINQ to Entity
operators "StartsWith", "Contains", and "EndsWith". Locate functions do
not make use of indexes placed on the VARCHAR/CHAR columns, which caused
significant performance degradation.

Thank you for the bug report.