Error storing Image in SQL CE 4.0 with ASP.NET MVC

问题:

I'm trying to store/save an image in an SQL Compact Edition (CE) database.

I declare the field in my Student model as:

[Column(TypeName = "image")]
public byte[] Photo { get; set; }

The database is created with the image data type for the Photo column as can be seen here:

The problem is:

When I run the app and try to save a Student with a Photo of 3 MB (for example), I get an exception:

validationError.ErrorMessage = "The field Photo must be a string or array type
with a maximum length of '4000'."

SQL Server CE supports these Data Types. In this comparison between SQL Express and SQL Compact Edition (CE) we have that SQL CE supports Binary (BLOB) storage through the use of image data type.

Image = Variable-length binary data with a maximum length of 2^30–1 (1,073,741,823) bytes. Storage is the length of the value in bytes.

Image should do the job I think.

What am I doing wrong here? Is this a bug?

Note:

I also tried the MaxLength data annotation:

[Column(TypeName = "image")]
[MaxLength(int.MaxValue)]
public byte[] Photo { get; set; } 

but I get this exception:

Binary column with MaxLength greater than 8000 is not supported.

Edit:

I found the post about the release of EF 4.1. It has the following:

Change of default length for non-key string and binary columns from ‘128’ to ‘Max’. SQL Compact does not support ‘Max’ columns, when running against SQL Compact an additional Code First convention will set a default length of 4000. There are more details about the change included in a recent blog post (link below).

Well well well... the only way I could get it working was doing what is described here, that is, setting DbContext.Configuration.ValidateOnSaveEnabled = false. This is a workaround as the post suggests.

回答1:

For those experiencing this problem, Erik Ejlskov Jensen posted a working console application which demonstrates the workaround to this bug. As the OP noted, a key part of the answer is:

    public StudentContext()
    {
        // Required to prevent bug - http://stackoverflow.com/questions/5737733
        this.Configuration.ValidateOnSaveEnabled = false;        
    }

A better solution has been found. Do not disable validation. [Updates from blog post] UPDATE: @DamienGuard, of LINQ to SQL and EF Code First fame, pointed out that a better and more provider agnostic solution is to use MaxLength rather than TypeName = “ntext”.

UPDATE 2: Using [MaxLength] prevents any validation errors, and disabling validation is not required.

回答2:

The way to specify no maximum length using the MaxLength data annotation is to provide no maximum value. For example:

[MaxLength]
public byte[] Photo { get; set; }

The SQL Compact provider will then map the property to "image" and EF validation will recognize that there is no max length specified and so does not need to be disabled. If you want to be explicit about mapping to an "image" column then you can do this:

[Column(TypeName = "image")]
[MaxLength]
public byte[] Photo { get; set; }

which will produce the same result when using SQL Compact.

回答3:

I know this is too late, but it could benefit other programmers.

You could add to the context class inside the OnModelCreating(DbModelBuilder modelBuilder) method

 modelBuilder.Entity<EntityName>().Property(p => p.Photo).IsMaxLength();