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?

Answer 1

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;
    }
}