Ok I have a POS legacy DB that was designed by monkeys. I know this :)

I have two classes Account, User

Below are the classes and their class variables. Also the .net type and the sql types

Account
int id : int id
guid account_id : uniquieidentifier account_id
string name : varchar(200) name

User
int id : int id
guid account_id : varchar(50) account_id
string username : varchar(200) username

There is a one to many association from account to user using account_id for the key on both sides.

Surprisingly to me nHibernate handles this with aplomb. It does conversion on user.account_id to a uniqueidentifier when querying.

Now we throw another wrench in the works. The **#* who set this up used '0' in user.account_id sometimes. So when the sql statement tries to do a conversion from varchar(50) to uniqueidentifier it bombs.

This mismatch is handled in various different places of this legacy app with dynamic sql. There are 3 dozen tables with the wrong type for account_id and 100's if not 1000's of places where this is handled. So just changing the column type is not really an option currently. Our hope is to use nHibernate to get a usable set of domain objects and rewrite sections as we go and then all we have to do at the end is change the mapping in nHibernate for it to handle it correctly.

Below is the relevant mappings.

<class name="account" table="accounts">
<id name="id">
<generator class="native"/>
</id>
<property name="account_id"/>
<property name="name"/>
<set name="users" cascade="all-delete-orphan">
<key column="account_id" property-ref="account_id"/>
<one-to-many class="user"/>
</set>
</class>

<class name="user" table="account_users">
<id name="id">
<generator class="native"/>
</id>
<property name="name"/>
<property name="account_id"/>
</class>