Storing Large Binary Data (e.g. VARBINARY(MAX)) in SQL Server CE and EntityFramework

Unlike full-blown SQL Server, SQL Server CE (Compact Edition) restricts VARBINARY data types to a maximum size of roughly 8k. Fortunately, there’s an easy enough hack to get around this. According to the MSDN, SQL Server CE supports image data types up to a size of up to 1GB. Since images are just a specific binary format, they’ll work for any other binary data type. As such, all you have to do is decorate the desired model property as so:

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

Unfortunately, there’s also a bug with EntityFramework not properly detecting the maximum possible size for SQL Server CE. In order to get around this, you must also add the following code to your data context:

public class MyDbContext : DbContext
    public DbSet<DataClass> Data { get; set; }

    protected override bool ShouldValidateEntity(DbEntityEntry entityEntry)
        //Replace "DataClass" with the class that needs to store large data types
        if (entityEntry.Entity is DataClass)
            return false;
        return base.ShouldValidateEntity(entityEntry);

Thanks to Charles at StackOverflow and Erik for posting the solution to this frustrating problem.


Leave a Reply

Your email address will not be published. Required fields are marked *