Many to Many with Data in the Joining Object

7 views
Skip to first unread message

tqwhite

unread,
Mar 12, 2010, 10:08:25 AM3/12/10
to nhusers
Is that a good way to title this? Do we have a better name for that
object that has both foreign keys?

Anyway, I am using a set of externally designed data types. Among them
is the connection between two of my entities, Schools and Students.
Each student can attend many schools. Each school has many students.
In addition, that period of attendance has a start and end date, etc.

I have constructed a mapping and it almost works. Two problems I need
help with:

1) I am getting an error, "Cannot insert the value NULL into the
column RefId." It specifies the join table. RefId is the <id> column
for this intermediate. I bet that the problem is that NH doesn't
expect there to be an id on the joining table.

2) I can't set said start and end dates because I can't find a way to
address that intermediate object.

I considered the idea that I should initialize that object in the
AddStudent() (and AddSchool()) method but the needed id keys don't
exist yet.

I spent last night poring over my brand, new copy of NHibernate in
Action (I LOVE THAT BOOK!), but can't find any advice.

Help! Please.

thanks,
tqii

Diego Mijelshon

unread,
Mar 12, 2010, 10:22:51 AM3/12/10
to nhusers
You'll need to create an intermediate entity, and one-to-many relationships to it from the School and Student classes.

   Diego



--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.


tqwhite

unread,
Mar 12, 2010, 2:58:06 PM3/12/10
to nhusers
Thanks. I have been wondering if that might not be the best way. I
htink I read something different someplace to get me on the wrong way.

I have implemented the "dual one-to-many" strategy. But, I get an
error message saying that I, "Cannot insert the value NULL into
column". That column is the foreign key in the join table. Basically,
NH is not getting the id from the objects I am trying to connect to
the join table.

I am loathe to ask anyone to read much code, but I am clearly not
getting some important thing, so please be patient.

Here is what I have done:

<class ... student
<!-- all kinds of stuff that works except for this problem, id is
RefId, a uuid.hex -->

<bag name="schools">
<key column="SchoolInfoRefId"/>
<one-to-many class="AssemblyName.Models.StudentSchoolEnrollment,
AssemblyName" />
</bag>

</class>

<class ... school
<!-- all kinds of stuff that works except for this problem, id is
RefId, a uuid.hex -->

<bag name="students">
<key column="StudentPersonalRefId"/>
<one-to-many class="AssemblyName.Models.StudentSchoolEnrollment,
AssemblyName" />
</bag>
</bag>

</class>

<class ... StudentSchools ...
<!-- id and one property that is not controversial, but I haven't
built a separate test, id is RefId, a uuid.hex -->

<many-to-one class="AssemblyName.Models.Student, AssemblyName"
name="school" column="StudentPersonalRefId"/>
<many-to-one class="AssemblyName.Models.School, AssemblyName"
name="student" column="SchoolInfoRefId"/>

</class>

Classes:
In School: public virtual IList<StudentSchoolEnrollment>
students{get;set;} //initialized in the contructor

In Student: public virtual IList<StudentSchoolEnrollment> schools
{ get; set; } //initialized in the contructor

In StudentSchool:

public virtual School school {get; set;}
public virtual Student student { get; set; }


To test it, I wrap the following in a session:

Student newOne = new Student();
NameOfRecordType newName = new NameOfRecordType();
newName.FirstName="TQ";
newName.LastName="White";
newName.FullName="TQ White II";

newOne.Name = newName;


School newSchool = new School();
newSchool.LocalId = "School #1";

StudentSchoolEnrollment newEnrollment = new
StudentSchoolEnrollment();
newEnrollment.SchoolYear = "2010 and beyond";

newOne.schools.Add(newEnrollment);
newSchool.students.Add(newEnrollment);

session.Save(newSchool);
session.Save(newOne);
session.Save(newEnrollment);

If I remove the enrollment code (not removing xml), the school and
student are persisted correctly. If this were my bad, old sql days,
I'd be trying to figure out how to get private keys for school and
student, but am pretty sure NH is supposed to do that for me.

Thanks for your help.

tqii

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >

Diego Mijelshon

unread,
Mar 12, 2010, 4:36:39 PM3/12/10
to nhusers
http://nhforge.org/doc/nh/en/index.html#collections-onetomany

Very Important Note: If the <key> column of a <one-to-many> association is declared NOT NULL, NHibernate may cause constraint violations when it creates or updates the association. To prevent this problem, you must use a bidirectional association with the many valued end (the set or bag) marked as inverse="true". See the discussion of bidirectional associations later in this chapter.


   Diego


To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

tqwhite

unread,
Mar 13, 2010, 1:07:22 AM3/13/10
to nhusers
I'm getting so beaten up and confused by this. I have never had this
much trouble getting something to work.

I actually considered the not-null issue before. At one point, I
asserted not-null="false" in all the places but it made no difference.
I just did it again and found that it generates the same schema either
way. Probably a bug, but it's true that I also didn't check. Arrgggh.

So, I read the bidirectional association. As best I can tell, the only
difference is that I didn't add the attribute, inverse="true" to the
many side, aka, bag. I tried this at one point before as a pure random
experiment. As with tonight, it didn't solve the problem.

Still, I did it again:

<bag name="students" inverse="true">
<bag name="schools" inverse="true">

And I still get the null violation.

So, I know these things:

1) My mapping basically works. When I manually remove "not null" and
the foreign key constraints from my database, I get the database
results I want.

2) Inverse="true" added to the bag, doesn't do it.

3) NH can do this, so I must have something wrong that I am can't
find.

Here is what the manual says, interleaved with notes:

<class name="Eg.Parent, Eg"> <!-- For me, Parent is the student and
school entities, one student/school has many enrollments-->
<id name="Id" column="id"/>
....
<set name="Children" inverse="true" lazy="true"> <!-- I use a bag
since I don't want them ordered and I couldn't get the type right when
I tried using a set for fun. The docs, and common sense, say this is
ok. Right?-->
<key column="parent_id"/> <!-- I use SchoolInfoRefId (or
StudentInfoRefId both here and in the many-to-one-->
<one-to-many class="Eg.Child, Eg"/>
</set>
</class>

<class name="Eg.Child, Eg"> <!-- for me, this is the StudentSchool
join table. Many enrollments can relate to a single student/school. --
>
<id name="Id" column="id"/>
....
<many-to-one name="Parent" class="Eg.Parent, Eg"
column="parent_id"/>
</class>

I just cannot see anything wrong with my code. I've been at this for
the last four hours. I'm dying.

tqii


On Mar 12, 3:36 pm, Diego Mijelshon <di...@mijelshon.com.ar> wrote:
> http://nhforge.org/doc/nh/en/index.html#collections-onetomany
> <http://nhforge.org/doc/nh/en/index.html#collections-onetomany>
>
> *Very Important Note:* If the <key> column of a <one-to-many> association is


> declared NOT NULL, NHibernate may cause constraint violations when it

> creates or updates the association. To prevent this problem, *you must use a
> bidirectional association* with the many valued end (the set or bag) marked


> as inverse="true". See the discussion of bidirectional associations later in

> this chapter.http://nhforge.org/doc/nh/en/index.html#collections-bidirectional

> > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Bunsubscribe@googlegroup s.com>>

Gustavo Ringel

unread,
Mar 13, 2010, 1:33:02 AM3/13/10
to nhu...@googlegroups.com
did you try idbag instead of bag, in general it is more suited for many to many relations, it lets you define de id of the connecting table.

In general i do not create both one to many relations for the connection if there is no specific value. If you need to have info in the middle, like date of subscription to the specific school, then you can use bags for sure.

Gustavo.

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Diego Mijelshon

unread,
Mar 13, 2010, 7:25:22 AM3/13/10
to nhusers
That's the title of the thread, Gustavo :-)

   Diego

Gustavo Ringel

unread,
Mar 13, 2010, 9:08:10 AM3/13/10
to nhu...@googlegroups.com
Yeah, now i see, it confused me the many to many, if you have data in the joining object then you have two one to many for sure.
I have a lot of working code with that configuration without any problem...

Can you write a test with the problem and send it to the group files?

Gustavo.

tqwhite

unread,
Mar 13, 2010, 9:10:23 AM3/13/10
to nhusers
Be nice to Gustavio, he's my new best friend You can't really expect
him to read the title when I've written enough in this thread to bore
anyone to tears. ;-)

I tried idbag briefly, Gustavio. It gave me a Null Exception at the
database, too. I didn't continue too far with it, because, at Diego's
suggestion, I have turned this many-to-many into a pair of one-to-many
relationships and it doesn't make sense to me to have two <id> tags
pointing to one database field (RefId in the joining table).

Truth is, my code above works (almost) correctly if I edit the
database. Then, I get a nice student record. I get a nice school
record. I get a nice enrollment record (the join table) with correct
foreign keys.

It was midnight when I wrote last night's post and, as I lay in bed
afterward worrying, I decided to declare defeat. I am going to have to
live, at least for now, without not-null protection, without foreign
key constraint and with the need to edit the automatically generated
schema (to get rid of the other two). A bitter pill to swallow but, I
have spent days on this and my constitution can't take it any more.

I note that this does NOT work if I tell the bags that inverse="true"
as suggested in the docs. If I do that, I do get a record in the
joining table (with it's specific data) but it does NOT have any
foreign keys.

If anyone gets any idea how I can fix this so I can keep my database
constraints, please let me know (tq [at] justkidding [dot] com). (It
would be nice if they had an email me when one of my threads change
function.)

Diego, I have to thank you for the instruction to change from many-to-
many between schools and students to one-to-many between school and
enrollment and between student and the same enrollment. This is
conceptually much more correct.

Now, get ready for the next step, retrieving this baby and sending it
in JSON. That's gonna hurt, you know it.

peace,
tqii

ps, For the record, I made two changes to the code in response to this
thread. The <key> columns were reversed from what they should have
been. Students was accidentally looking at the school foreign key and
vice versa. Also, the bag names are both changed to "enrollments"
reflecting the fact that that's what they are now.

On Mar 13, 6:25 am, Diego Mijelshon <di...@mijelshon.com.ar> wrote:
> That's the title of the thread, Gustavo :-)
>
>    Diego
>

> On Sat, Mar 13, 2010 at 03:33, Gustavo Ringel <gustavo.rin...@gmail.com>wrote:
>
>
>
> > did you try idbag instead of bag, in general it is more suited for many to
> > many relations, it lets you define de id of the connecting table.
>
> > In general i do not create both one to many relations for the connection if
> > there is no specific value. If you need to have info in the middle, like
> > date of subscription to the specific school, then you can use bags for sure.
>
> > Gustavo.
>

> >> > > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Bunsubscribe@googlegroup s.com>


> >> <nhusers%252Bunsubscribe@googlegroup s.com>>
> >> > > > > .
> >> > > > > For more options, visit this group at
> >> > > > >http://groups.google.com/group/nhusers?hl=en.
>
> >> > > --
> >> > > You received this message because you are subscribed to the Google
> >> Groups
> >> > > "nhusers" group.
> >> > > To post to this group, send email to nhu...@googlegroups.com.
> >> > > To unsubscribe from this group, send email to
> >> > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >
>

> ...
>
> read more »

tqwhite

unread,
Mar 13, 2010, 12:11:10 PM3/13/10
to nhusers
I take it back.

1) if I tell the <many-to-one> to be not-null="false", it creates a
correct schema that works.

This new schema demonstrates that I was also wrong about the foreign
key constraint.

2) With the new schema, the foreign key constraint doesn't need to be
removed.

I don't know what happened during my previous thrashing to cause me to
perceive things wrongly, but I'd rather be wrong and have things turn
out well, than right and, well, you know.

tqii

> ...
>
> read more »

Gustavo Ringel

unread,
Mar 13, 2010, 12:39:09 PM3/13/10
to nhu...@googlegroups.com
That's the reason i asked to write the test. 95% of the time trying to write the failing test i see i'm the failing part of the test :P.

Gustaov.

> ...
>
> read more »

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Fabio Maulo

unread,
Mar 13, 2010, 12:45:51 PM3/13/10
to nhu...@googlegroups.com
2010/3/13 Gustavo Ringel <gustavo...@gmail.com>

That's the reason i asked to write the test. 95% of the time trying to write the failing test i see i'm the failing part of the test :P.


AMEN!! 
--
Fabio Maulo

Reply all
Reply to author
Forward
0 new messages