12

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?

4
  • Did I miss something? Isn't Entity Framework only compatible with SQL Server? Commented Jan 4, 2012 at 14:08
  • @Yuck Entity framework is not only compatible with SQL Server, you can see the list here: en.wikipedia.org/wiki/… Commented Jan 4, 2012 at 15:22
  • But for code first it's a leaner list Commented Jan 10, 2012 at 4:38
  • @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. Commented Jan 12, 2012 at 7:56

1 Answer 1

12

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;
    }
}
Sign up to request clarification or add additional context in comments.

2 Comments

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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.