Hi all

NHibernate version: 1.2.0RC1

Database: MSSQL2000

I have searched for solutions to this, but haven't found any, even though it doesn't seem too exotic:

I'm having two tables, for example like:


ZipCodes is related one-to-many to Persons, on the column named ZipCode (in both tables), but both tables has a surrogate key "Id" that is the primary key.

How do I map this in NHibernate (I'm using NHibernate.Mapping.Attributes, but since I can insert raw xml, a .hbm mapping file example will also do)?

Please don't ask me to change the database-schema, since I can't (the above example isn't the actual db-schema, but an example that points out the problem).

I have tried mappings along the lines of:


But that will map Person.ZipCode to ZipCode.Id (the primary key of the parent), and I can't figure out how to specify the column in the parent table (ZipCodes).

Any help is highly appreciated.

Best regards

/Kasper
[/code]

Did you ever find a solution to this problem? I'm facing the same problem and cannot find a solution.

So what I've found so far is that NHibernate does not support unidirectional one-to-many relationships on a non primary, foreign key. But supposedly it supports bi-directional ones, so theoretically you could setup your mapping like so, and it should work:

Person Class (many-to-one side):


ZipCode Class (one-to-many side):


According to what I read it this topic: Unidirectional one to many association on a FK You should be able to just add the property-ref="" attribute to the many-to-one side, and it should automatically link up the one-to-many side for you. But I can't get it to work for me... but I could be doing something else wrong.

Looks to me like the XML mapper example I gave should work, but there is a bug described here in NHibernate 1.2.

This pretty much makes NHibernate unusable for me, as I'm trying to build a Business Layer for a legacy system, that uses this technique pervasively through out the system.

Can anyone confirm if this assessment is correct?

Can we have some clarity on this problem.

I have seen it in the forum many times without a reasonable answer

Well, I didn't find any good answer either.

My workaround is that I simply doesn't map the one-to-many relation, and instead I implement a property IList<Whatever> (using C#, .NET and NHibernate) myself, and let the getter of that property query the database and return the list of related objects.

To avoid having every object now about the NHibernate session, I have a factory class knowing the session, exposing methods to get the relevant lists. Maybe not the most elegant solution, but it works, and have also proven useful in controlling when to load what (I'm not really satisfied with lazy-loading, but that is another story).

Hope this is clear, and helps someone.

Best regards

Kasper

I had to hack a solution as well, I'm lucky enough that I have the ability to add and change views in the system I'm working on. So I actually just changed the view for the Child table to include a sub select on the parent table grabbing it's Primary key and then I was able to do the join in NHibernate as though I really did have a Primary Key / Foreign key reference.

(BTW the reason it has to be a sub select and not a join is because SQL Server won't let you update / delete from a view that has more than one table specified in the from clause.)