We have discovered a potential issue with either our code or NHibernate. It appears to be within NHibernate since I have not found a setting for how to handle the case sensitivity of object keys.

This sytem is built using ASP.NET 1.1 with SQL Server 2000 (With case-sensitivity turned off).

We have migrated a good portion of an existing system to NHibernate (currently using 1.0.2). The existing system had a user table with the userid being their e-mail address which is used as the primary key for the user. The user enters the userid into a textbox in order to log in and we then use that information in order to retrieve the user.

The issue we ran into is basically that it appears that NHibernate will populate the object with the provided key when performing an ISession.Get or ISession.Load. It then appears to populate the properties of the object based on the results of the Get/Load, which makes sense.

The issue comes into play when NHibernate loads the roles for the user. Since SQL Server 2000 is set to be case insensitive all role objects are returned (using the userrole table) from the database query for the roles, but none of the roles match the user which we are retrieving the roles for. Since the user is not found NHibernate marks the roles (or userroles actually) for deletion and our returned user has 0 roles. Of course the bigger problem is that the user no longer has any roles, and can no longer use the system.

Note that this only happens when the user enters their id in a different case than which it is stored in the database. However, if the userrole entries had multiple cases for the userid this issue would again show it's face.

As a temporary solution we have stopped using Get/Load for objects which use a string for the primary key. We instead run a general query which could not possibly load the object prior to population and must rely on the database's userid property to populate the userid in the object. Now since we don't have a User object with the primary key that the user entered, but rather the primary key of the database the roles match up. Again, I don't like this approach since if at any time someone manually changes the data and uses a different case we can have serious problems. The database won't notice, and the result is an issue which becomes very hard to trace.

Is there an option somewhere within NHibernate to take this into account? And if there isn't, is there a way I can make a request that there is at least some way to handle these situations within a future release of NHibernate?

Thanks

you can avoid all of these problems if you use a pure fabricated primary key instead of your domain specific primary key (usually an autonumber). Its also a best practice in database design. Otherwise your problem sounds interesting maybe you can post a test case.

cheers,
radu

I started by stating that this is an existing system, and as such I do not have the opportunity to make a change like that. That's why this is so critical. I knew I could do that, and for most of our tables (we have over 410 tables) this is not an issue.

If what you stated was the only resolution, then we would be talking about an issue with NHibernate which should be probably be addressed in some way or form. I just hope there is an option or a switch somewhere which I can set to change how key comparisons are done. It seems like a relatively easy modification. I know that this could possibly make cross database support relatively harder, but some users would probably prefer to work safely with one database than working safely in every possible database.

I think you should build a simple test case to exihibit the behaviour and post it in jira as a bug.

cheers,
radu

You can write a user type that would convert all such strings to upper or lower case on load/store and use it for the case-insensitive identifiers.

While the user type may work to satisfy this scenario, it may not satisfy all such requirements. If the case of the key was meant to be preserved for a presentation purpose we would lose this information using the custom user type. Is there any likely hood that this is something that could be addressed when using a case insensitive database? I am willing to put together a bug report if that would help.

With 410 tables, I can't say for certainty that this will satisfy all of our requirements.

Is NHibernate's behavior in this case considered a bug? It seems like it should be, as the current behavior is highly unexpected, and can lead to unpredictable mismatches between SQL joins and NHibernate mappings.

I'm facing the same issue with Hibernate 3.1.2.
We also have a case-insensitive database instance (SQLServer 2000).

This is an application that has been used for more than a year now. I discovered the problem when trying to fix some weird bugs.

We've managed all the cases for the PK creation or search by forcing upper case input.

The problem comes from other entities FK pointing on those case-sensitive PK.
I was horrified when looking up in the db for those FK column and realizing that for a same ID, say #C300 I could find references like #c300 and #C300.
This works perfectly when firing HIbernate generated queries on the SQLServer instance.
But Hibernate itself is case-sensitive and check this in the AbstractType class method:



The question is how come some FK became lower-case when the entity ID itself is using upper-case???? Some table containing those FK are index table for NtoN relationships, meaning entirely controled by Hibernate.
So, it seems that Hibernate itself would be responsible for this behavior. I haven't be able to reproduce the behavior yet.

For now the easiest solution to fix my bug would be to change is isEqual method to be case-insensitive. I won't dare to do that since I have no idea on how it is used, but if possible, that'd be a very quick fix.

The only other way is to find all lower case references in the DB and change them. That's going to be a HUGE amount of work. Then make the DB itself case-sentitive and track the errors...

The kind of exception i get from this lower case FK:

What's the status of this? I'm running into the same issue...

I think a bug should be filed, as this behavior is WAY unexpected. I'm using a legacy schema that I cannot change, so I think my best route is to create a custom string type that is case insensitive, but I'd like some input.

Anyone know if this has been filed as a bug? I am having a similar issue in 1.2.0.GA.

Example:

Area a = Session.Get<Area>("2a"); // 2A in database

The object I get back has an id of "2a", not the value in the database.

It looks like I am subsequently ending up with two objects in Session for the same record, one has "2a" and one has "2A".

In the meantime, I'll probably go the user type route. If anyone has a better workaround, I'd appreciate hearing about it.

Thanks.

Your approach of the User type is the right one, but a short term workaround could be to not use the Get/Load methods for items with string. Instead use HQL. Then NHibernate will not first create the object with your primary key. It will have to perform the query and then it will construct the object based on the returned results. It's probably not the cleanest approach, but it does work. We wrote our own get/load methods which run HQL if the primary key value is a type string (information which is available in the ClassMappings of NHibernate).

The approach I'm now taking to get around this is to implement a CaseInsensitiveStringType that is exactly the same as the NHibernate StringType implementation except for the Equals and GetHashCode methods that I've changes as below. This doesn't help the fact that I may still end up with different cases in the database, but will prevent getting multiple objects in session for the same row. Anyone aware of any gotchas of doing it this way?

public override bool Equals(object x, object y)
{
return ObjectUtils.Equals(x, y) || (x != null && y != null && x.ToString().ToUpper().Trim() == y.ToString().ToUpper().Trim());
}

public override int GetHashCode(object x, NHibernate.Engine.ISessionFactoryImplementor factory)
{
return base.GetHashCode(((string)x).Trim().ToUpper(), factory);
}

The only point I would make is to use a Case Insensitive string comparer instead of the String.ToUpper(). I'm not 100% sure that there is a performance different, but I would guess there would be. Plus, the code looks cleaner. The easiest way to do it is just use the static method String.Compare(strA, strB, ignoreCase);.