Entity Framework converts StartsWith to MySQL's Locate, MySQL's Locate doesn't use index

Question

I'm using Entity Framework with MySQL, and my Linq Query:

db.Persons.Where(x => x.Surname.StartsWith("Zyw")).ToList();

..is producing the SQL:

SELECT PersonId, Forename, Surname
FROM Person
WHERE (LOCATE('Zyw', Surname)) = 1

...and it would seem that this doesn't make use of the index on Surname.

If LOCATE is replaced with the equivalent LIKE, the query speedily returns the required results. As it is it takes all afternoon.

Why is Entity Framework and its connecting drivers opting for this wierd LOCATE function / how can I make it use LIKE instead / why is MySQL making a poor index decision for the LOCATE function / how can I make it better?

Update:

I'm afraid I was guilty of over simplifying my code for this post, the Linq producing the error is in fact:

var target = "Zyw";
db.Persons.Where(x => x.Surname.StartsWith(target)).ToList();

If target term is hard coded, the SQL generated does indeed use LIKE, but with a variable term the SQL changes to use LOCATE.

This is all using the latest generally available MySQL for Windows as delivered by MySQL Installer 5.6.15.

Update:

A couple more notes to go with the bounty; am using:

• Visual Studio 2010
• EntityFramework 6.0.2
• MySQL Installer 5.6.15, which in turn gives:
  • MySql.Data 6.7.4
  • MySql.Data.Entities 6.7.4

The Entity Framework code is generated database first style.

I've also tried it with the latest connector from Nuget (MySql.Data 6.8.3) and the problem is still there.

Answer 1

It's likely your problem is caused by:

1. You are using an older connector with the bug.

2. You have a special case (using a variable to hold the .Contains search) described as a bug here

Does your case fall into any of those?

Answer 2

This looks like a regression of MySQL bug #64935 to me.

I can confirm that, using the same builds of EF6 and MySQL Connector, I'm getting the same SQL generated too:

context.stoppoints.Where(sp => sp.derivedName.StartsWith(stopName));

...logs as:

SELECT
`Extent1`.`primaryCode`, 
...
`Extent1`.`stop_timezone`
FROM `stoppoints` AS `Extent1`
 WHERE (LOCATE(@p__linq__0, `Extent1`.`derivedName`)) = 1

Entity Framework: 6.0.2 MySQL Connector.Net: 6.8.3

I have reported this as a MySQL bug regression.