CODEWITHSUNDEEP
c#nhibernate
NZJames
NZJames

Reputation: 5055

Nhibernate - Saving object with dependant tables

I have a User object that as one of the properties contains a Photos property, which is am IList type.

The tables in the SQL database are joined by a foreign key. ie

Table User { userid long NOT NULL....... } Table UserPhoto { photoid long NOT NULL, userid long not null references [User].UserId........ }

So you cannot insert a row in userphoto without the corresponding User row.

What I want to be able to do is build up a User Object in code, add an Ilist of UserPhoto objects (which must neccesarily contain a blank user id, as does the User object as it hasn't been saved and created by identity insert yet)

My mappings are as follows

 <class name="User" table="[User]">
    <id name="UserId" column="Id">
      <generator class="identity" />
    </id>
    <bag name="Photos" order-by="DisplayOrder asc" cascade="all">      
      <key column="UserId" />
      <one-to-many class="UserPhoto" />
    </bag>
 </class>
<class name="UserPhoto" table="UserPhoto">
    <id name="PhotoId" column="Id">
      <generator class="identity" />
    </id>
    <property name="UserId" />
</class>

But when I try and save the User object with Nhibernate Session.Save(user), it throws an error saying Foreign key constraint failed. So im guessing its trying to insert either the user photos in the list before the user table, OR its not setting the newly generated user id property in the photos and trying to save them with a null user id.

Do you have to have something extra in the mapping to show that this is a foreign key column and what it applies to, so nhibernate knows how to insert the dependant rows in the right order so I can accomplish this in one insert?

Upvotes: 3

Views: 2310

Answers (3)

reach4thelasers
reach4thelasers

Reputation: 26899

try this:

 <class name="User" table="[User]">
    <id name="UserId" column="Id">
      <generator class="identity" />
    </id>
    <bag name="Photos" order-by="DisplayOrder asc" cascade="all" inverse="true">      
      <key column="UserId" />
      <one-to-many class="UserPhoto" />
    </bag>
 </class>
<class name="UserPhoto" table="UserPhoto">
    <id name="PhotoId" column="Id">
      <generator class="identity" />
    </id>
    <many-to-one name="User" property-ref="UserId" column="UserId"/>
</class>

Inverse = true added to the Photos bag

http://bchavez.bitarmory.com/archive/2007/10/06/nhibernate-and-inversetruefalse-attribute.aspx

Also changed the UserId element to a many-to-one user element

Upvotes: 1

Trevor Pilley
Trevor Pilley

Reputation: 16393

You either need to add inverse="true" in the mapping for the Photo collection

<bag name="Photos" order-by="DisplayOrder asc" cascade="all" inverse="true">

or you will need to make the UserID column nullable in the Photo table.

If you do the first option, you need to configure the relationship between the objects yourself:

var user = new User();
var photo = new Photo();
photo.User = user;
user.Photos.Add(photo);

With inverse="true" set, NH will insert the user, update the userid based upon the identity value then insert each photo setting the userid in the photo table with each insert.

Without inverse="true" set, NH will insert the photos with a null userid, select all the photo ids for the inserted photos and then issue an update to set the userid after. This will result in an additional SQL statement being generated.

Upvotes: 0

Steve Czetty
Steve Czetty

Reputation: 6228

You need to add inverse="true" to the bag, or else NHibernate will try to do an insert followed by an update:

<bag name="Photos" order-by="DisplayOrder asc" cascade="all" inverse="true">      
  <key column="UserId" />
  <one-to-many class="UserPhoto" />
</bag>

Upvotes: 0

Related Questions