Bug #65290 Cannot create an entity with a property of type byte[]
Submitted: 11 May 2012 21:24 Modified: 2 Jul 2012 22:33
Reporter: Brice Lambson Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.5.4.0 OS:Microsoft Windows
Assigned to: Assigned Account CPU Architecture:Any
Tags: DDL

[11 May 2012 21:24] Brice Lambson
Description:
The Connector/Net provider does not support creating an entity with a property of type byte[] (see Image class below).

During database creation, a MySqlException is thrown saying "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 'NOT NULL)' at line 5."

The DDL produced by the provider is as follows.

    CREATE TABLE `Images`(
            `Id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `Data` varbinary NOT NULL);

Fixing this is critical for the provider to work properly with Entity Framwork versions 4.3 and later since the __MigrationsHitory table (which replaces the EdmMetadata table) uses a property of type byte[].

I've posted a lengthy workaround at http://brice-lambson.blogspot.com/2012/05/using-entity-framework-code-first-with.html

How to repeat:
class Context : DbContext
{
    public DbSet<Image> Images { get; set; }
}

public class Image
{
    public int Id { get; set; }

    [Required]
    public byte[] Data { get; set; }
}

class Program
{
    static void Main()
    {
        using (var context = new Context())
        {
            // Throws
            context.Database.Initialize(force: false);
        }
    }
}

Suggested fix:
Since there is no MaxLength SSDL facet, the type of the column should probably be a BLOB type like MEDIUMBLOB.
[2 Jul 2012 22:32] Fernando Gonzalez.Sanchez
Thanks for your report, this has been fixed as part of http://bugs.mysql.com/bug.php?id=64216 fix; and will be released in upcoming 6.6.