MySQL Bugs: #93398: Can't filter with Contains in EntityFrameworkCore for Guid or Byte[] fields

Bug #93398	Can't filter with Contains in EntityFrameworkCore for Guid or Byte[] fields
Submitted:	29 Nov 2018 9:46	Modified:	22 Feb 2021 20:16
Reporter:	asdfas dfawef	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
When having a column of type binary(16) and the EntityFrameworkCore type is Guid or Byte[], we can't use the Contains extension to filter the records.

We have tested it on dotnet core 2.1 with the MySql.EntityFrameworkCore 8.0.13.

How to repeat:
Create a table:

CREATE TABLE `testtable` (
  `id` int(11) NOT NULL,
  `uuid` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
);

Create an entity in C#:

public class Testtable
{
    public int Id { get; set; }
    public Guid Uuid { get; set; }
}

Create a context:

public class MyContext : DbContext
{
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseMySQL("server=localhost;user id=myuser;password=mypassword;database=testdb;persistsecurityinfo=True;Charset=utf8mb4;old guids=True;");
            }
        }

...

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Testtable>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.ToTable("testtable");

                entity.Property(e => e.Id)
                    .HasColumnName("id")
                    .HasColumnType("int(11)");

                entity.Property(e => e.Uuid)
                    .HasColumnName("uuid")
                    .HasColumnType("binary(16)");
            });
        }
}

Now select a contains:

var filter = new [] { Guid.Empty };
var context = new MyContext();
var resultFilter = context.TesttableSet.Where(t => filter.Contains(t.Uuid)).ToArray();

This will result in the following exception:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[])' at line 3
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

This is because when checking the executed SQL will result in something along the lines of:
SELECT ... FROM `testtable` WHERE uuid IN (System.Byte[])

Suggested fix:
Please fix the contains extension to work as it should and as it worked in older versions.

I've written a PR to fix the issue:

https://github.com/mysql/mysql-connector-net/pull/27

However, I found that if I used OldGuids=True, I couldn't read the data, with the error:

System.InvalidOperationException: An exception occurred while reading a database value for property 'Testtable.Uuid'. The expected type was 'System.Guid' but the actual value was of type 'System.Guid'. ---> System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.Guid'.
   at System.Data.Common.DbDataReader.GetFieldValue[T](Int32 ordinal)
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue[T](Int32 ordinal)
   at lambda_method(Closure , DbDataReader )
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

So for now I removed the OldGuids=True and it works, I hope it won't affect my data.

Please accept this PR!
Thanks.

Thank you for the report. I see you have already submitted this pull request - https://github.com/mysql/mysql-connector-net/pull/27

Have you signed and submitted OCA? Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team - https://dev.mysql.com/community/

Once OCA is in place then you can attach patch using the "contribution" tab to this bug report.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hello!

Thank you for the report and contribution.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.24 release, and here's the proposed changelog entry from the documentation team:

Incomplete GUID mapping in Entity Framework Core caused an error when the
Contains method was used to filter records.

Thank you for the bug report.

Hi, I am still having the same exact issue as described here. I am using nuget package MySql.EntityFrameworkCore 5.0.5+MySQL8.0.26 published: Tuesday, July 20, 2021.
Any information would be very appreciated.
Thanks

This is still an issue on EF Core 6.0 with MySQL.Data 8.0.28.  Given the BINARY(32) column Operations.Hash and
```
IEnumerable<byte[]> hashes;
```
then both
```
source.Where(o => hashes.Any(h => o.Hash == h))
```
and
```
source.Where(o => hashes.Contains(o.Hash))
```
are both injecting the literal string "System.Byte[]" into the generated SQL:
```
SELECT `o`.`Hash`, `o`.`Id`
FROM `Operations` AS `o`
WHERE `o`.`Hash` = System.Byte[]
```
causing
```
MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[]' at line 3
```