NHibernate with string primary key and relationships

Question

I've have just been stumped with this problem for an hour and I annoyingly found the problem eventually.

THE CIRCUMSTANCES

I have a table which users a string as a primary key, this table has various many to one and many to many relationships all off this primary key.

When searching for multiple items from the table all relationships were brought back. However whenever I tried to get the object by the primary key (string) it was not bringing back any relationships, they were always set to 0.

THE PARTIAL SOLUTION

So I looked into my logs to see what the SQL was doing and that was returning the correct results. So I tried various things in all sorts of random ways and eventually worked out it was. The case of the string being passed into the get method was not EXACTLY the same case as it was in the database, so when it tried to match up the relationship items with the main entity it was finding nothing (Or at least NHIbernate wasn't because as I stated above the SQL was actually returning the correct results)

THE REAL SOLUTION

Has anyone else come across this? If so how do you tell NHibernate to ignore case when matching SQL results to the entity? It is silly because it worked perfectly well before now all of a sudden it has started to pay attention to the case of the string.

Answer 1

I have the exact same situation on a ref table in my DB. I mapped the schema file the same way you did. In code, when I query for the record by Primary Key, I do the following using an instance of the NHibernate ISession:

return session.Get<T>(id);

In this statement, T is the type you are querying for, and id is the string id you are looking for (the Primary Key)

Here is an example of my mapping file:

    <class name="Merchant" table="T__MERCHANT">
        <id name="MerchantId" column="MERCHANT_ID" type="string">
            <generator class="assigned" />
        </id>

        <property name="MerchantStatusId" column="MERCHANT_STATUS_ID" type="Char" not-null="true" length="1" />
        <property name="MerchantStatusName" column="MERCHANT_STATUS_NAME" type="string" length="50" />
        <property name="MerchantName" column="NAME" type="string" not-null="true" length="50" />
 </class>
</hibernate-mapping>

And my C# code looks like this:

public Merchant GetMerchantById(string id)
{
     return session.Get<Merchant>(id);
}