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
Tags: connector, linq, NET

[17 Mar 2014 11:03] Carl Partridge
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
[24 Sep 2014 1:54] Fernando Gonzalez.Sanchez
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
[24 Sep 2014 17:17] Fernando Gonzalez.Sanchez
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();
[24 Sep 2014 18:39] Carl Partridge
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?
[24 Sep 2014 19:13] Fernando Gonzalez.Sanchez
Yes, also affects 6.9.3, working on a fix.
[29 Sep 2014 16:39] Fernando Gonzalez.Sanchez
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.
[19 Oct 2014 23:23] Philip Olson
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.