Hibernate version: 1.2.0.GA

Our management insists that we use NCHAR instead of NVARCHAR for string-based database columns, because then when our customers manually insert and edit data directly to the database (which they say is a good thing and must be available to our customers) resulting in mixed formats (both with and without trailing spaces in string-based columns), SQL queries will work regardless of whether the data in the database or the criteria in the query have trailing whitespace or not.

Unfortunately, this policy is causing us grief with NHibernate in two ways:

1. I haven't found a way through any standard or custom NHibernate.UserTypes.IUserType to trim off the trailng whitespace on NCHAR-based primary key columns without upsetting NHibernate. I did create a custom user type that I use for non-identifier properties, but NHibernate throws exceptions, saying the identifier value changed, if I try to use it on identifier properties.

2. Since I haven't found a way to trim identifier properties through mapping, my identifier columns all have trailing whitespace. this is not a problem for tables with non-business keys, since they are generated to their full length and don't ever contain whitespace. Unfortunately, management also insists that some tables have user-defined keys, which then usually end up with trailing whitespace. Attempts to call ISession.Load() with trimmed versions of these keys fail.

How can we set up mapping (either through a standard or custom user type) so that the identifier column in the database is NCHAR and therefore padded with trailing whitespace, but the entity property is always trimmed and so that trimmed identifier values passed to ISession.Load() still return the entity for the row with the non-trimmed primary key column in the database?

Can't you just use the Equals method of the IUserType interface to show that the two strings are equal regardless of trailing whitespaces? I know that your application would still need to handle it appropriately where it matters but at least internal to NHibernate should work correctly, right?

I do that in my user type for non-identifier properties, it works fine there, but if I try to use that type for identifier properties, NHibernate gets upset when I trim (or otherwise modify) the key value.

Here's what I do in in my implementation of NHibernate.UserTypes.IUserType.NullSafeGet:



NHibernate throws exceptions when you do this for identifier properties ...

I'm saying, can't you leave the NullSafeGet alone and don't trim the white space at the end. Instead just use an Equals method on the IUserType to compare the values without the trailing whitespace (to simulate what is being done in the database). You'll still have trailing whitespace in your business object which you will have to deal with, but at least your queries and internal NHibernate operations should behave consistently right?

I realize you won't be able to trim the value as it is returned. I don't know if that is the correct NHibernate behavior or not, but it seems like my approach should at least let you work around it right?

I can try that. BTW, is that what the AnsiString (or AnsiStringFixedLength) type is for? It isn't documented as to how it is different from the String type, and since it doesn't trim values, we considered it worthless and didn't look into any further ...

Since we only use field-based mapping, we can also have our Id properties always trim the field before returning it in the getter, and always pad the value before assigning it to the field in the setter. At least that way it will always appear trimmed to us, and always padded to NHibernate.

AnsiStringFixedLength is used to map a database column of type CHAR (in Sql Server at least), AnsiString is used to map to VARCHAR, String is used to map to NVARCHAR and StringFixedLength is used to map to NCHAR. I don't believe NHibernate changes too much in how these are handled besides using ansi types or unicode types when talking with the database. I believe the main point in using these types is to allow NHibernate to generate the database schema, I may be wrong about that.

I don't believe NHibernate handles the 4 types much differently in the NHibernate types.



That works, but if that is too ugly you could also try another approach. I don't know if you would find this more offensive or not. You could also create an interface which defines the identifier of the class. Then implement the interface explicitly on the class. You can have NHibernate access the property using the explicit interface but your developers won't see that reserved property unless they explicitly cast your object as the interface. To prevent that from happening I'll mark the property on the interface as obsolete, which would stop it at compile time (provided they weren't also using reflection).

It's really up to you if that approach is uglier or not. I think all of the 3 options should work. I may be wrong though.