I've spent the last day or two mulling over how to implement a relation in our database using NHibernate and can't seem to come up with something that works. So I'm hoping there is someone out there who can give me some advice on how best to go about doing this.

I'm dealing with a legacy database so I can't change the schema. I'm trying to model a relationship between a user and location that is defined by a PermissionItem object. The PermissionItem object defines what type of access a user has to the location (eg. read, write, control, etc.). In UML speak, it would look like this:




In our database, this has been modeled as a four tables: A User table, PermissionItem table, Location table, and a join table that joins these.

Could someone tell me how I would setup my mappings so that I can create, store and manipulate these relationships?

Thanks
Dave.

I am having the same problem

In this case I have the following


A -> AB <- B

I want Object A to have a 1-1 relationship with B via AB using an associating class/table.

I will continue resarch and if i discover how, I will let you know. In the meantime i am crossing my fingers that someone will have the answer we seek.

Thanks,

Brad

Brad,

Is this a legacy db your dealing with? I'm assuming it must be as the solution is too simple.

The only way I can see you doing this is to create a ManyToMany relationship and then fake out the OneToOne in code.

Dave.

That is what i thought.

When you make the many-to-many relationship you are stuck with a set but the set only contains one entry. I just fetch the first entry.

In B there is a getAs() getter to get the set. I added a getA() getter that gets the first element of the the list of A and vis-versa in A for B.


Now The only issue i have with this is the A_B table contains 1 extra column (call it X) that is not A_ID or B_ID that cannot be null. If i want to persist a new B and A with the relationship A_B, hibernate complains that this column X cannot be null. Column X is also contained in A if that helps with the problem.

Any help with this issue would be greatly appreciated!

Thanks,

brad

I have discovered a similar problem with our db. All the join tables have primary keys on them so Save operations cack with an integrity constraint error. We can probably solve this by adding a trigger but at this point, we don't know the effect on the rest of the system without a lot of testing.

However, in the case where you have an extra column in the join table that is not a primary key, it likely indicates this is probably not a simple join table and likely should be treated as some kind of Entity that requires its own class. That's the road I'm going down.

Dave

Daver,

Yeah thanks for the help. I totally agree with you. Perhaps someone will see this that knows of a solution that could help us both. I am going to dig through all the forums and if i find something I will let you know.

thanks,

Brad