Fluent nhibernate SQL Server 2008 R2 Express very long string saving issue

Question

I have an Article object which has various properties:

public class Article {
    public virtual string Title { get; set; }
    public virtual string Body { get; set; }
}

I am using the fluent configuration to load the mappings:

configuration.Mappings(m => m.AutoMappings.Add((AutoMap.AssemblyOf<Article>())
                            .Conventions.Add(DefaultCascade.All())
                            .UseOverridesFromAssemblyOf<SchemaConfigurationController>())

The override is:

public class ArticleOverrideMapping : IAutoMappingOverride<Article>
{
    public void Override(AutoMapping<Article> mapping)
    {
       //mapping.Map(x => x.Body).CustomSqlType("NVARCHAR(4000)");
       //mapping.Map(article => article.Body).Length(10000);
       //mapping.Map(article => article.Body).Length(Int32.MaxValue);            
       //mapping.Map(article => article.Body).CustomSqlType("NVARCHAR(max)");           
       //mapping.Map(article => article.Body).CustomSqlType("NVARCHAR(max)").Length(Int32.MaxValue);           
       mapping.Map(article => article.Body).CustomType("StringClob").CustomSqlType("NVARCHAR(max)");
    }
}

I have tried each of the commented out lines (roughly in order of when I found a possible solution online). I can get SQL Server to create the nvarchar(max) column and if I use SQL management studio I can paste a LOT (185,602 words was largest test) into the Body column. My issue is trying to get it to save from the MVC site using NHibernate.

There are two main errors I get:

String or binary data would be truncated. The statement has been terminated.

This would occur if I didn't set the .Length(Int32.MaxValue) override.

The second Error that occurs (when the length is set):

The length of the string value exceeds the length configured in the mapping/parameter.

I am pretty confused as to what I should be doing at this point. My goal is to be able to store a very large string (an whole article) in SQL Server (and SQLite for testing, nvarchar(max) wasn't liked by SQLite) and get that back out, (and edit it) in an MVC site.

UPDATE

As per @Cymen's link I tried

.CustomSqlType("nvarchar(max)").Length(Int32.MaxValue).Nullable();

but this lead to the error:

The length of the string value exceeds the length configured in the mapping/parameter.

when I only tried to save 1201 words (all the word "test"). When I added the length on the end of the above mapping ".Length(Int32.MaxValue)" I still the same error.

update

wanted to confirm which versions I am using:

 FluentNHibernate.1.3.0.733
 NHibernate.3.3.1.4000
 Microsoft.AspNet.Mvc.4.0.20710.0

final update

Kieren had it correct, I had completely forgotten that I took that property and ran markdownsharp on it on the server and populated a second property on the server. So it was the second property that I hadn't mapped that was actually blowing up, sorry.

Could this be the solution?http://stackoverflow.com/questions/12708171/the-length-of-the-string-value-exceeds-the-length-configured-in-the-mapping-para Unfortunately, it doesn't say how to fix this when using fluent... — Cymen, Nov 14 '12 at 20:58
It looks like this should be a solution with fluent: http://serialseb.blogspot.com/2009/01/fluent-nhibernate-and-nvarcharmax.html — Cymen, Nov 14 '12 at 21:01
@Cymen thanks for the links, unfortunately I had already seen these two. updated my question to reflect this. — Jon, Nov 14 '12 at 21:24
after each option you tried, what length/size column does it create in the database? — Kieren Johnstone, Nov 15 '12 at 08:20
and, worth trying CustomSqlType("text")? Crap compared to nvarchar(max) but it shouldn't be limited to a length — Kieren Johnstone, Nov 15 '12 at 08:23
if I don't do any overrides it will create varchar(255), mapping.Map(x => x.Body).CustomSqlType("NVARCHAR(4000)") will create a nvarchar 4000. The rest are (nvarchar(max), null) — Jon, Nov 15 '12 at 08:35
Can you be sure it's that column that has the error, then? Maybe a different field is too long? — Kieren Johnstone, Nov 15 '12 at 08:45

score 1 · Accepted Answer · answered Nov 14 '12 at 23:54

1

this is how i normally handle it.

mapping.Map(x => x.Problem).CustomType("varchar(MAX)");

not sure what CustomSqlType is, but i've never used it, and this works.

answered Nov 14 '12 at 23:54

nathan gonzalez

11,817
4
41
57

unfortunately I still get "String or binary data would be truncated. The statement has been terminated." using: mapping.Map(x => x.Body).CustomType("varchar(max)"); – Jon Nov 15 '12 at 07:43

score 1 · Answer 2 · answered Mar 23 '14 at 17:09

Ok, folks. Same stuff, so i've did some tests.

It seems that int.MaxValue cannot be used as LENGTH for fluent. If you do that, the resulting create SQL will be nvarchar(255). So if nhibernate (fluent) still generates such a script, then he is awaiting 255 chars at max.

If you hovewer use int.MaxValue / 2, then everything is ok. At least in the script. Only reasonable explanation: unicode string, so for a single char fluent automatically takes 2 bytes. SO internally fluent hibernate may do a multiplication by 2. And if we get above 2GB space, who knows what fluent will do...

Please let me know if it works with data too.

FluentNHibernate isn't really involved in this. There is also no multiplication involved. However, NHibernate internally knows that for Unicode strings up to max 4000 characters it should use `nvarchar($length)` and for strings up to 1073741823 characters, it should use `nvarchar(max)`. What then (unfortunately) happens is that if you ask for a longer string, it won't be able to use any of those two, so it will fall back to the default, i.e. `nvarchar(255)`. — Oskar Berggren, Jul 02 '21 at 14:59

Fluent nhibernate SQL Server 2008 R2 Express very long string saving issue

2 Answers2