Nhibernate - Persisting an Object with Dependent Tables
I have a User object as one of the properties has a Photos property which is of type am IList.
A foreign key is used in SQL database tables. i.e
User table {userid long NOT NULL .......} UserPhoto table {photoid long NOT NULL, long user IDs but not null references [User] .UserId ........}
Thus, you cannot insert a line into userphoto without a corresponding user line.
What I want to do is create a custom object in code, add a list of UserPhoto objects (which should contain an empty user id, just like the User object, since it was not saved and created with identity insert)
My mappings look like this
<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 to persist the User object using Nhibernate Session.Save (user), it throws an error saying that the foreign key constraint failed. So my guess is that it is trying to either insert custom photos in the list before the custom table, or not set the newly created user id property on the photos and try to store them with a null user id.
You must have something extra in the mapping to show that this is a foreign key column and what it refers to, so nhibernate knows how to insert dependent rows in the correct order so that I can accomplish this in a single insert?
source to share
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 photo batch
http://bchavez.bitarmory.com/archive/2007/10/06/nhibernate-and-inversetruefalse-attribute.aspx
Also changed the UserId element to a one-two-one user element.
source to share
You need to either add inverse = "true" to 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 null in the Photo table.
If you do the first option, you need to set up the communication between the objects yourself:
var user = new User();
var photo = new Photo();
photo.User = user;
user.Photos.Add(photo);
With inverse = "true" query NH will insert the user, update the user id based on the id value, and then insert each photo, setting the user id to the photo table with each insert.
Without inverse = "true", NH inserts photos with zero user id, selects all photo ids for the inserted photos, and then issues an update to set the user id after. This will generate an additional SQL statement.
source to share