Store byte array using Entity Framework 4, MySQL and code first?

Question

Hey I'm trying to store a simple byte[] using EF 4 MySQL (newest connector) and code-first approach.

Simply doing:

public byte[] Thumbnail {get; set;}

gives me the following error upon creation:

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

And then it points to what comes right after my byte[] declaration.

Anyone got any quick tips for me?

Did I miss something? Isn't Entity Framework only compatible with SQL Server? — Yuck, Jan 04 '12 at 14:08
@Yuck Entity framework is not only compatible with SQL Server, you can see the list here: http://en.wikipedia.org/wiki/ADO.NET_Entity_Framework#Entity_Framework_ADO.NET_providers — King Chan, Jan 04 '12 at 15:22
@Jani but mysql should still be fully supported - as far as I can tell. I chose instead to save my thumbnail as a file, and only store the url - although the other method would be preferred, so I'm leaving the question open. — Dynde, Jan 12 '12 at 07:56

Skyqula · Accepted Answer · 2018-01-16T10:12:01.617

You need to use the MaxLength Attribute.

[MaxLength(16)]
public byte[] test { get; set; }

Note that the above turns it into a tinyblob datatype, wich can have indexing/primary key problems. When using migrations it turns into this:

AddColumn("dbo.testDB", "test", c => c.Binary(storeType: "tinyblob"));

You can use the attribute column and set TypeName to "Binary" if you need indexing/Primary Key.

[MaxLength(16), Column(TypeName = "Binary")]
public byte[] test { get; set; }

Although the above results in a Binary(1) column for me (it's how I got here).

Edit: To get a correct length binary array, simply add (16) after binary in the migration file:

AddColumn("dbo.testDB", "test", c => c.Binary(storeType: "binary(16)"));

Unfortunatly, adding it the the typename of the Column attribute doesn't work.

Edit2: It is possible to get a correct database without needing to edit the migration file by creating a custom MySqlMigrationSqlGenerator.

internal class CustomMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
{
    protected override MigrationStatement Generate(CreateTableOperation op)
    {
        MigrationStatement statement = base.Generate(op);

        foreach (ColumnModel column in op.Columns)
        {
            if (column.MaxLength.HasValue)
            {
                statement.Sql = statement.Sql.Replace($"`{column.Name}` binary", $"`{column.Name}` binary({column.MaxLength.Value})");
            }
        }

        return statement;
    }
}

Wow... 4 year old post. Uhm, I have no way of validating this is right, so I'm just gonna take your word for it — Dynde, Feb 23 '16 at 17:21
This issue still presents itself (7 years later) in EF Core 2.1 using the first party MySQL Provider. Pomelo fixes this issue, but if it is not a option, one can use the `text` type to store binary data. — chakeda, Jan 22 '19 at 23:20

Store byte array using Entity Framework 4, MySQL and code first?

1 Answers1