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

Sorry for the slow reply, mappings are below:

<?xml version="1.0" encoding="utf-8"?>
    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="App_Code.tqeub3fb" namespace="JohnHarmanLtd.Web.FineArtCompany.Models">
      <class name="Product" table="[Product]" xmlns="urn:nhibernate-mapping-2.2">
        <id name="Reference" column="Reference" type="String">
          <generator class="assigned" />
        </id>
        <property name="Publish" column="Publish" type="Boolean" not-null="true">
          <column name="Publish" />
        </property>
        <property name="ProductType" column="ProductType" type="JohnHarmanLtd.Web.FineArtCompany.Models.Product+enuProductType, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" not-null="true">
          <column name="ProductType" sql-type="string" length="50" />
        </property>
        <property name="Updated" column="Updated" type="DateTime" not-null="true">
          <column name="Updated" />
        </property>
        <property name="Created" column="Created" type="DateTime" not-null="true">
          <column name="Created" />
        </property>
        <property name="Username" column="Username" length="100" type="String" not-null="true">
          <column name="Username" />
        </property>
        <property name="Title" column="Title" length="300" type="String" not-null="true">
          <column name="Title" />
        </property>
        <property name="Keywords" column="Keywords" length="300" type="String" not-null="true">
          <column name="Keywords" />
        </property>
        <property name="ImageFilename" column="ImageFilename" length="150" type="String" not-null="true">
          <column name="ImageFilename" />
        </property>
        <property name="LimitedEdition" column="LimitedEdition" type="Boolean" not-null="true">
          <column name="LimitedEdition" />
        </property>
        <property name="SearchWeight" column="SearchWeight" type="Int32" not-null="true">
          <column name="SearchWeight" />
        </property>
        <property name="OriginalArt" column="OriginalArt" type="Boolean" not-null="true">
          <column name="OriginalArt" />
        </property>
        <many-to-one name="Artist" column="ArtistID" />
        <many-to-one name="Colour" column="ColourID" />
        <bag name="Lifestyles" lazy="true" table="ProductsWithLifestyles">
          <key column="Reference" />
          <many-to-many column="LifeStyleID" class="JohnHarmanLtd.Web.FineArtCompany.Models.Lifestyle, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" />
        </bag>
        <bag name="Subjects" lazy="true" table="ProductsWithSubjects">
          <key column="Reference" />
          <many-to-many column="SubjectID" class="JohnHarmanLtd.Web.FineArtCompany.Models.Subject, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" />
        </bag>
        <bag name="ArtTerms" lazy="true" table="ProductsWithArtTerms">
          <key column="Reference" />
          <many-to-many column="ArtTermID" class="JohnHarmanLtd.Web.FineArtCompany.Models.ArtTerm, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" />
        </bag>
        <bag name="Prices" table="PriceOption">
          <key column="Reference" />
          <one-to-many class="JohnHarmanLtd.Web.FineArtCompany.Models.PriceOption, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" />
        </bag>
        <bag name="Dimensions" table="Dimensions">
          <key column="Reference" />
          <one-to-many class="JohnHarmanLtd.Web.FineArtCompany.Models.Dimension, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" />
        </bag>
        <joined-subclass name="JohnHarmanLtd.Web.FineArtCompany.Models.SculpturePedestal, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null">
          <key column="Reference" />
          <property name="Material" column="Material" length="150" type="String" not-null="true">
            <column name="Material" />
          </property>
          <property name="FinishOrColour" column="FinishColour" length="150" type="String" not-null="true">
            <column name="FinishColour" />
          </property>
        </joined-subclass>
        <joined-subclass name="JohnHarmanLtd.Web.FineArtCompany.Models.Book, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null">
          <key column="Reference" />
          <property name="DatePublished" column="DatePublished" type="DateTime">
            <column name="DatePublished" />
          </property>
        </joined-subclass>
        <joined-subclass name="JohnHarmanLtd.Web.FineArtCompany.Models.Print, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" table="[Print]">
          <key column="Reference" />
          <property name="MainPrint" column="MainPrint" type="Boolean" not-null="true">
            <column name="MainPrint" />
          </property>
          <property name="NumberOfDifferentSizes" column="NumberDifferentSizes" type="Int32" not-null="true">
            <column name="NumberDifferentSizes" />
          </property>
          <property name="Type" column="Type" type="JohnHarmanLtd.Web.FineArtCompany.Models.Print+enuType, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" not-null="true">
            <column name="Type" sql-type="string" length="50" />
          </property>
          <component name="FrameSuggestion" insert="true" update="true">
            <property name="Glazing" column="FrameGlazingID" type="JohnHarmanLtd.Web.FineArtCompany.Models.Frame+enuGlazing, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" not-null="true">
              <column name="FrameGlazingID" sql-type="string" length="50" />
            </property>
            <many-to-one name="Frame" column="FrameSuggestionID" />
            <many-to-one name="MountColour" column="FrameMountColourID" />
            <many-to-one name="BorderColour" column="FrameBorderColourID" />
          </component>
        </joined-subclass>
      </class>
    </hibernate-mapping>

Answer 2

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);
}

Answer 3

There is no option for this but you can achieve the same using QBE

Example.create(parent).ignoreCase()

Answer 4

I don't understand your problem :

• when you query by the PK with Get method, the object returned has no value for the relationships
• when you query by another kind of query (an example will be useful), the relationships are not empty ?

Answer 5

For consistency sake, please revise this:

<joined-subclass name="JohnHarmanLtd.Web.FineArtCompany.Models.Book, App_Code.tqeub3fb, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null">

More specifically "App_Code.tqeub3fb". It looks like you're using temporary assemblies from an ASP.NET Website as part of the mapping for NHibernate?

Move your Business Entities out into their own library so the assembly name is always the same.