MySQL Bugs: #74918: Incorrect query result with Entity Framework 6

Bug #74918	Incorrect query result with Entity Framework 6
Submitted:	18 Nov 2014 17:04	Modified:	19 Feb 2015 2:39
Reporter:	Alexei Ch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.7.6	OS:	Windows (Win7, x64)
Assigned to:	Gabriela Martinez Sanchez	CPU Architecture:	Any
Tags:	connector, EF6, like, linq, SQL, StartsWith

Description:
Query in entity framework 6 is returning incorrect results for the StartWith clause. This happens when passing a string reference as a parameter. Here is the sample( assume that required row exist in DB ):

// correct execution
var res1 = context.Cars.Where(e => e.Model.StartsWith("Camaro")).ToArray();

// incorrect execution
string str = "Camaro";
var err3 = context.Cars.Where(e => e.Model.StartsWith(str)).ToArray();

Previous version of connector 6.9.3.0 is working correctly.

How to repeat:
// below is a simple console project and app.config file to reproduce the problem

using MySql.Data.Entity;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Linq;

namespace MySqlConnectorTest
{
    [DbConfigurationType(typeof(MySqlEFConfiguration))]
    public class Parking : DbContext
    {
        public DbSet<Car> Cars { get; set; }
        public Parking(DbConnection existingConnection, bool contextOwnsConnection) : base(existingConnection, contextOwnsConnection)
        {
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }
    public class Car
    {
        public int CarId { get; set; }
        public string Model { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "server=localhost;User Id=alexei;Pwd=123456;;database=TestDB";

            MySqlConnectionFactory f = new MySqlConnectionFactory();

            using (DbConnection connection = f.CreateConnection(connectionString))
            {
                using (Parking contextDB = new Parking(connection, false))
                {
                    contextDB.Database.Delete();
                    contextDB.Database.CreateIfNotExists();
                }

                connection.Open();

                using (Parking context = new Parking(connection, false))
                {
                    List<Car> cars = new List<Car>();
                    cars.Add(new Car { Model = "Mustang" });
                    cars.Add(new Car { Model = "Camaro" });
                    context.Cars.AddRange(cars);

                    context.SaveChanges();

                    var res0 = context.Cars.Where(e => e.Model == "Camaro").ToArray();  // << OK
                    var res1 = context.Cars.Where(e => e.Model.StartsWith("Camaro")).ToArray(); // << OK
                    string str = "Camaro";
                    var res2 = context.Cars.Where(e => e.Model == str).ToArray();         // << OK
                    var err3 = context.Cars.Where(e => e.Model.StartsWith(str)).ToArray(); // << ERROR
                }
            }
        }
    }
}
// *******************************************************************
// ***** app config file content
/*

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"></provider>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

*/

Suggested fix:
Fix the problem

Same happend with Contains / Like

Totally unacceptable.

Bug#75137 marked as duplicate of this one.

Note : Bug#74943 marked as duplicate of this one.

Bug#75193 marked as duplicate of this one.

Looks like the change made for Bug #72058 did not include case where matching pattern is passed in an anonymous linq style function.

This is an enormous regression bug. Is there an ETR on this? I understand that 6.9.3 is okay?

Where can I find the repo for 6.9.3?

Bug#75567 marked as duplicate of this one.

Please note that the following sql...

WHERE `Extent1`.`QuoteNum` LIKE concat(@p__linq__0, '%')

...where cause a table scan regardless of indexes on the QuoteNum column. It appears that mysql needs the sql to look like this...

WHERE `Extent1`.`QuoteNum` LIKE 'mysearch%'

...in order to use indexes.

My comment "[21 Jan 6:48] Samuel Sippe" regarding tablescans was incorrect. A table scan only happens if the charset+collation of the variable and column in the where clause doesn't match. Providing they match an index will be used. See http://stackoverflow.com/a/28161625/176868

I don't know exactly how this could be possiblem but I worked-around this issue adding a .Substring(0) on string parameter into the not working code.

// not working, return an empty result
var SearchQuery = from tuple in context.tests
                  where tuple.Name.Contains(split)
                  select tuple;

// working
var SearchQuery = from tuple in context.tests
                  where tuple.Name.Contains(split.Substring(0))
                  select tuple;

Bug#75710 marked as duplicate of this one.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Net 6.7.7 / 6.8.5 / 6.9.6 releases, and here's the changelog entry:

With Entity Framework 6, passing in a string reference to the "StartWith"
clause would return incorrect results.

Thank you for the bug report.