Hello,

Trying to map a many-to-many relationship like so,

table A:
A_id int
...

table B:
B_id int,
another_column varchar

A_B
A_id int,
another_column varchar

So, the join on table B should be done on another_column rather than B_id.

How does one map this?

Thanks,
Dan

If another_column has a UNIQUE constraint, then you might be able to get away with something using a "foreign-key" attribute somewhere.

If not, you map it as what it is... not a real many-to-many relationship. It's simply a table with zero-to-one (uni-directional one-to-one) with A and a column that happens to share a value with a column in B.

Don't fight the Object Relational Mapper by trying to model was isn't really there. Instead, model the reality of the system.

I would make a new class, AtoBStringMapping, with a one-to-one relationship to A and a string value for AnotherColumn.

Looking things up then becomes a matter of structuring a query that does a join on all the appropriate classes. It's more tedious than just accessing the many-to-many collections though object properties, but you're less likely to get into trouble down the road.

In my opinion, if the DB is not using real referential integrity constraints, then your mapping shouldn't pretend it is. You'll get away with it at first, but when some nutball goes in and changes a value in a column that everyone assured you was unique and could be used as a foreign key... well, your whole application will break in very intersting and dangerous ways. (And "but they said that would never happen!!! Inconceivable!!!" doesn't go over well with your boss)

If you can gaurantee that your app will be the only one that ever modifies the data, you can get away without DB-level constraints a little more easily.

Thanks for the help,

They actually went with this structure against my advice, and while I assumed nhibernate probably wouldn't support this it was worth a shot.

The issue is, unsurprisingly, integrating with legacy systems! Anyway, since it was a read only sort of relationship, I just added a method to the containing class to get the objects that were related to it in this way.

Thanks again,
Dan