Orchard migration with byte[] column throws NHibernate length mapping error

Question

I've created a data migration for a new table in my custom Orchard module. This table needs to store uploaded file data in one of the columns. Here is the migration code for the column in question (other columns removed for brevity):

SchemaBuilder.CreateTable("Attachment", table => table
    .Column<byte[]>("Content", col => col.WithLength(2147483647).WithType(DbType.Binary).Unlimited())
);

I've tried adding/removing both the WithLength and the Unlimited methods, but neither stops the error from occurring. Here's the NHibernate exception:

The length of the byte[] value exceeds the length configured in the mapping/parameter.
  at NHibernate.Type.AbstractBinaryType.Set(IDbCommand cmd, Object value, Int32 index)
  at NHibernate.Type.NullableType.NullSafeSet(IDbCommand cmd, Object value, Int32 index)
  at NHibernate.Persister.Entity.AbstractEntityPersister.Dehydrate(Object id, Object[] fields, Object rowId, Boolean[] includeProperty, Boolean[][] includeColumns, Int32 table, IDbCommand statement, ISessionImplementor session, Int32 index)

EDIT: I've been playing around with things like adding a Binary Length Convention (see here: NHibernate Image Storage - The length of the byte[] value exceeds the length configured), which hasn't helped either, but it looks as if DbType.Binary has a limit of 8000 bytes, which is the ceiling that I'm hitting. From the MSDN docs: DbType.Binary - A variable-length stream of binary data ranging between 1 and 8,000 bytes. The answer would be to use SqlDbType.Image, which doesn't suffer from this restriction, but it's not usable by the Orchard Schema Builder.

EDIT 2: I forgot to add - the error occurs when I call IRepository<T>.Create() or IRepository<T>.Update() to create or update the domain object that is stored in the Attachment table.

Is there a way to override/extend the SchemaBuilder so that I can use SqlDbType.Image as a column type? I'm using Orchard 1.6

What is the length of the data that it fails to insert? It looks like whatever configures NHibernate fails to let it know what the length of the Content column is. — Oskar Berggren
I've been experimenting and it'll allow a file that's just under 8k but not one that is 11k, so I think it's the 8000 char limit — levelnis
Did you start from a fresh database each time you tried different col.Unlimited()/.WithLength() variations? Could be that you need to delete App_Data/Site/Default/mappings.bin and wipe the database and revert the migrations between each attempt. Mappings.bin may be caching the nhibernate mappings from prev. attempts. — Giscard Biamby
I did go for that approach to fix another Orchard issue I was having - will go through each combination resetting the database and report back — levelnis
Resetting the database and trying each different combination made no difference. I also tried changing the column type from byte[] to object but that also made no difference — levelnis

levelnis levelnis · Accepted Answer · 2013-01-08T10:45:00

So it looks as though the issue stems from the fact that Orchard has to support both SQL Server and SQL Compact, and VARBINARY(MAX) is not supported in Compact Edition. The only way forward that I can see is to create my own schema builder on top of the stuff that's already in Orchard. The approach I am going to take is to create a custom CreateColumnCommand and a custom DataMigrationInterpreter (plus whatever else I need that's related) to perform the schema generation for me. I'll post relevant bits of code once it's working. If anyone thinks there's flaws in this approach, please shout!

EDIT: However, on closer inspection the Image type is supported by SQL Server CE so I'm still at a loss as to why this isn't possible with Orchard. Will keep looking at my custom solution in the absence of an alternative.

Orchard migration with byte[] column throws NHibernate length mapping error

1 Answers