NHibernate sets the foreign key in a secondary update, not in the original insert that violates the Not-Null Constrain on the key column

Question

I have a problem with what should be a fairly simple (I would think) use case for NHibernate.

I have a classic parent and child like:

public class Parent { public virtual int ParentId { get; set; } public virtual string Name { get; set; } public virtual IList<Child> Children { get; set; } } public class Child { public virtual int ChildId { get; set; } public virtual Parent Parent { get; set; } public virtual string Name { get; set; } } 

And the mappings are as follows:

 public class ParentMap : ClassMap<Parent> { public ParentMap() { Id(x => x.ParentId).GeneratedBy.Native(); Map(x => x.Name); HasMany(x => x.Children).KeyColumn("ParentId").Cascade.SaveUpdate(); } } public class ChildMap : ClassMap<Child> { public ChildMap() { Id(x => x.ChildId).GeneratedBy.Native(); Map(x => x.Name); References(x => x.Parent).Column("ParentId").ReadOnly().Not.Nullable(); } } 

Finally, I have simple tests:

  [Test] public void Test_save_family() { var parent = new Parent(); var child = new Child {Parent = parent}; parent.Children = new List<Child>{child}; SessionManager.WithSession( session => { session.Save(parent); session.Flush(); }); } 

Test failed with System.Data.SqlClient.SqlException: Cannot insert a NULL value in the "ParentId" column. This is true that the column is not null, but why does it insert zero?

If I remove the null constraint, the save will work because NHibernate first inserts the parent, then inserts the child, and then updates the ParentId column of the child record, as shown in this output:

 NHibernate: INSERT INTO [Parent] (Name) VALUES (@p0); select SCOPE_IDENTITY();@p0 = NULL NHibernate: INSERT INTO [Child] (Name) VALUES (@p0); select SCOPE_IDENTITY();@p0 = NULL NHibernate: UPDATE [Child] SET ParentId = @p0 WHERE ChildId = @p1;@p0 = 2, @p1 = 1 

This seems strange to me, because in almost all cases, columns of this type of foreign key are declared invalid and therefore the foreign key must be provided in the insert. So why doesn't NHibernate set the foreign key in the initial insertion of the child row, and how to fix it?

Answer 1

A few problems with your mapping ... You have a bi-directional relationship, and NHibernate needs to know which way to update it. In the OO world, links go only in one direction, and NHibernate does not know that Parent-> Children is the same FK as Child-> Parent. You now have Child-> Parent set to ReadOnly (). This tells NHibernate not to update this property. So he tries to insert Child (with a zero parent) and then update the FK from the parent side. This does not work if you have a non-zero restriction on your FK. The usual way to match this is to use Inverse = true on the parent side and let the child worry about persistence. (This is your work in the OO model to ensure that the Parent-> Children collection contains the same set of links as the Child-> Parent relationship set.)

 public class ParentMap : ClassMap<Parent> { public ParentMap() { Id(x => x.ParentId).GeneratedBy.Native(); Map(x => x.Name); HasMany(x => x.Children).KeyColumn("ParentId").Inverse().Cascade.SaveUpdate(); } } public class ChildMap : ClassMap<Child> { public ChildMap() { Id(x => x.ChildId).GeneratedBy.Native(); Map(x => x.Name); References(x => x.Parent).Column("ParentId").Not.Nullable(); // Removed ReadOnly() } } 

SQL statements sent to the database on save now:

 INSERT INTO [Parent] (Name) VALUES ('P1' /* @p0 */) select SCOPE_IDENTITY() INSERT INTO [Child] (Name, ParentId) VALUES ('C1' /* @p0 */, 1 /* @p1 */) select SCOPE_IDENTITY()