Bug #70812 MySql Connector 6.7.4 - Entity Framework 5 - Wrong query using Contains()
Submitted: 4 Nov 2013 17:52 Modified: 11 Nov 2013 10:19
Reporter: Soul Blaster Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.7.4 OS:Any
Assigned to: Francisco Alberto Tirado Zavala CPU Architecture:Any
Tags: Contains(), Entity Framework 5, MySql Connector 6.7.4

[4 Nov 2013 17:52] Soul Blaster
Description:
Using .Contains() with Entity Framework 5 produces the wrong query.

How to repeat:
Something like this:

var l = users.Where(u => u.Name.Contains("foo")).OrderBy(u => u.Id).ToList()

Will produce this:

SELECT 
`Project1`.`Id`,
`Project1`.`Name`
FROM `User` AS `Project1`
WHERE (LOCATE(@p__linq__0, `Extent1`.`Name`)) > 0
ORDER BY `Project1`.`Id` ASC

And cause an error:

Note that it uses Extend1 and Project1.
Also note that it is using LOCATE instead of LIKE.

Suggested fix:
I don't know if the docs are wrong but, http://dev.mysql.com/doc/relnotes/connector-net/en/connector-net-news-6-7.html, says that bug #16974405 and #69409 are already fixed.
[4 Nov 2013 22:19] Francisco Alberto Tirado Zavala
Hello.

Hi did some testing using EF5 and Connector/Net 6.7.4, but I was not able to reproduce the issue.
I tried doing the linq query directly to the DB:
 var movies = dbContext.Movies.Where(mov => mov.Title.Contains("Matrix")).OrderBy(mov => mov.ID).ToList();

And also doing it to a object list:
 var movies = dbContext.Movies.Select(mov => mov);
 var matrix = movies.Where(mov => mov.Title.Contains("Matrix")).OrderBy(mov => mov.ID).ToList();

The code runs without problems, and the query generated looks like:
SELECT
`Extent1`.`ID`, 
`Extent1`.`Title`, 
`Extent1`.`ReleaseDate`, 
`Extent1`.`Genre`, 
`Extent1`.`Price`, 
`Extent1`.`Data`, 
`Extent1`.`Director_ID`
FROM `Movies` AS `Extent1`
 WHERE `Extent1`.`Title` LIKE '%Matrix%'
 ORDER BY 
`Extent1`.`ID` ASC

Can you provide more details about your problem? 
Or if you can share with us a project with the problem will be great.

Thanks for your time.
[5 Nov 2013 11:02] Soul Blaster
Francisco,

Thanks for your fast reply, and sorry, my example was too simplistic. 

I figured it out the problems: Using a variable to hold the .Contains search.
(Note that this problems also happens with .StartWith and .EndWith)

Here is the test implementation:

[CODE]
using System.Linq;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations.Schema;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            string search = "a"; //This is causing the problems

            var query = new SimpleDatabase().Users.Where(u => u.Name.Contains(search)).OrderBy(u => u.Id);

            /*SELECT
            `Project1`.`Id`, 
            `Project1`.`Name`
            FROM `User` AS `Project1`
             WHERE (LOCATE(@p__linq__0, `Extent1`.`Name`)) > 0
             ORDER BY 
            `Project1`.`Id` ASC*/

            var users = query.ToList(); //Buggy: Unknown column 'Extent1.Name' in 'where clause'
        }
    }

    public class SimpleDatabase : DbContext
    {
        public DbSet<User> Users { get; set; }
    }

    [Table("User")]
    public class User
    {
        public int Id { get; set; }

        public string Name { get; set; }
    }
}
[/CODE]

I'll be waiting for a reply.
[6 Nov 2013 17:56] Fernando Gonzalez.Sanchez
Hi,

Confirming this only happens in Code First, not DbFirst, I am marking it as duplicate of http://bugs.mysql.com/bug.php?id=70527.
[8 Nov 2013 16:24] Fernando Gonzalez.Sanchez
Hi,

The fix for http://bugs.mysql.com/bug.php?id=69751 was released, but only applies for Entity Framework Model First & Database First.

Something is different in the DbCommandTree passed to the SQL generator in Code First that causes the code for the fix to not be invoked, thus is broken.

We are currently working in a solution for Code First (see the link for duplicated bug for progress).

As a side note, in Entity Framework 6, Microsoft finally fixed this as part of https://entityframework.codeplex.com/workitem/245

Connector/NET 6.8.0 (EF6) already implements the mentioned EF6 feature and the problem does not happen (in none of dbfirst, modelfirst, codefirst).

Of course we will still provide a fix for EF5 / Cnet 6.7.x
[8 Nov 2013 19:05] Soul Blaster
Hello,

This bug does not exists in 6.6.4 (a custom build to work with EF5: https://bitbucket.org/kirk/mysql-data-connector-net-ef5)

I am currently using EF5 and I can't rollback to that custom build because official version 6.6.5 fixed another bug. I don't know what to do now, maybe hand write the query :s
[11 Nov 2013 10:19] Soul Blaster
Hello,

This is a temporary solution:

var query = new SimpleDatabase().Users.Where(u => u.Name.Contains(search) ).OrderBy(u => u.Id).GroupBy(u => u.Id).Select(u => u.FirstOrDefault() );

Again, a temporary solution!!!
[12 Feb 2014 2:15] Kevin Hollingshead
Using cnet 6.7.4

This extension method code:

loggedMessages = theDb.messages
   .Where(msg => msg.from_phone.Contains(pp.phone_number))
   .OrderBy(msg => msg.C_id)
   .Take(1000);

gives:

{SELECT
`Project1`.`_id`, 
`Project1`.`ts`, 
`Project1`.`uri`, 
`Project1`.`message_sid`, 
`Project1`.`from_phone`, 
`Project1`.`to_phone`, 
`Project1`.`body`
FROM `messages` AS `Project1`
 WHERE (LOCATE(@p__linq__0, `Extent1`.`from_phone`)) > 0
 ORDER BY 
`Project1`.`_id` ASC LIMIT 1000}

I really don't want to upgrade right now as I'm about to deploy to production and this is an optional function (although the customer will be slightly pissed).