NHibernate emitting extraneous update statements regardless of the correct Inverse (smooth nhibernate) relationship settings

Question

The following classes represent, at a minimum, my real scenario with an outdated database. I can add new columns to it, but that’s all I can do, since over 300 table databases are used by many other legacy applications that won't be migrated to NHibernate (so switching from compound keys is not an option)

public class Parent { public virtual long Id { get; protected set; } ICollection<Child> children = new HashSet<Child>(); public virtual IEnumerable<Child> Children { get { return children; } } public virtual void AddChildren(params Child[] children) { foreach (var child in children) AddChild(child); } public virtual Child AddChild(Child child) { child.Parent = this; children.Add(child); return child; } } public class Child { public virtual Parent Parent { get; set; } public virtual int ChildId { get; set; } ICollection<Item> items = new HashSet<Item>(); public virtual ICollection<Item> Items { get { return items; } } long version; public override int GetHashCode() { return ChildId.GetHashCode() ^ (Parent != null ? Parent.Id.GetHashCode() : 0.GetHashCode()); } public override bool Equals(object obj) { var c = obj as Child; if (ReferenceEquals(c, null)) return false; return ChildId == c.ChildId && Parent.Id == c.Parent.Id; } } public class Item { public virtual long ItemId { get; set; } long version; } 

Here's how I mapped them to an existing database:

 public class MapeamentoParent : ClassMap<Parent> { public MapeamentoParent() { Id(_ => _.Id, "PARENT_ID").GeneratedBy.Identity(); HasMany(_ => _.Children) .Inverse() .AsSet() .Cascade.All() .KeyColumn("PARENT_ID"); } } public class MapeamentoChild : ClassMap<Child> { public MapeamentoChild() { CompositeId() .KeyReference(_ => _.Parent, "PARENT_ID") .KeyProperty(_ => _.ChildId, "CHILD_ID"); HasMany(_ => _.Items) .AsSet() .Cascade.All() .KeyColumns.Add("PARENT_ID") .KeyColumns.Add("CHILD_ID"); Version(Reveal.Member<Child>("version")); } } public class MapeamentoItem : ClassMap<Item> { public MapeamentoItem() { Id(_ => _.ItemId).GeneratedBy.Assigned(); Version(Reveal.Member<Item>("version")); } } 

This is the code I use to insert a parent with three children and one child with an element:

  using (var tx = session.BeginTransaction()) { var parent = new Parent(); var child = new Child() { ChildId = 1, }; parent.AddChildren( child, new Child() { ChildId = 2, }, new Child() { ChildId = 3 }); child.Items.Add(new Item() { ItemId = 1 }); session.Save(parent); tx.Commit(); } 

These are the SQL statements generated for the previous code:

 -- statement #1 INSERT INTO [Parent] DEFAULT VALUES; select SCOPE_IDENTITY() -- statement #2 INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_0 */, 1 /* @p1_0 */, 1 /* @p2_0 */) INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_1 */, 1 /* @p1_1 */, 2 /* @p2_1 */) INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_2 */, 1 /* @p1_2 */, 3 /* @p2_2 */) -- statement #3 INSERT INTO [Item] (version, ItemId) VALUES (1 /* @p0_0 */, 1 /* @p1_0 */) -- statement #4 UPDATE [Child] SET version = 2 /* @p0 */ WHERE PARENT_ID = 1 /* @p1 */ AND CHILD_ID = 1 /* @p2 */ AND version = 1 /* @p3 */ -- statement #5 UPDATE [Child] SET version = 2 /* @p0 */ WHERE PARENT_ID = 1 /* @p1 */ AND CHILD_ID = 2 /* @p2 */ AND version = 1 /* @p3 */ -- statement #6 UPDATE [Child] SET version = 2 /* @p0 */ WHERE PARENT_ID = 1 /* @p1 */ AND CHILD_ID = 3 /* @p2 */ AND version = 1 /* @p3 */ -- statement #7 UPDATE [Item] SET PARENT_ID = 1 /* @p0_0 */, CHILD_ID = 1 /* @p1_0 */ WHERE ItemId = 1 /* @p2_0 */ 

Statements 4, 5 and 6 are extraneous / redundant, since all this information has already been sent to the database in batch inserts in instruction 2.

This would be the expected behavior if the parent mapping did not set the Inverse property in relation to HasMany (one to many).

In fact, it gets even weirder when we get rid of the one-to-many relationship from Child to Item as follows:

Remove the assembly from the Child and add the child property to the element:

  public class Child { public virtual Parent Parent { get; set; } public virtual int ChildId { get; set; } long version; public override int GetHashCode() { return ChildId.GetHashCode() ^ (Parent != null ? Parent.Id.GetHashCode() : 0.GetHashCode()); } public override bool Equals(object obj) { var c = obj as Child; if (ReferenceEquals(c, null)) return false; return ChildId == c.ChildId && Parent.Id == c.Parent.Id; } } public class Item { public virtual Child Child { get; set; } public virtual long ItemId { get; set; } long version; } 

Change the display of Child and Item to remove HasMany from Item and add links to the composite key to Item back to Child:

 public class MapeamentoChild : ClassMap<Child> { public MapeamentoChild() { CompositeId() .KeyReference(_ => _.Parent, "PARENT_ID") .KeyProperty(_ => _.ChildId, "CHILD_ID"); Version(Reveal.Member<Child>("version")); } } public class MapeamentoItem : ClassMap<Item> { public MapeamentoItem() { Id(_ => _.ItemId).GeneratedBy.Assigned(); References(_ => _.Child).Columns("PARENT_ID", "CHILD_ID"); Version(Reveal.Member<Item>("version")); } } 

Change the code to the following (note that now we need to explicitly call the "Save" element):

  using (var tx = session.BeginTransaction()) { var parent = new Parent(); var child = new Child() { ChildId = 1, }; parent.AddChildren( child, new Child() { ChildId = 2, }, new Child() { ChildId = 3 }); var item = new Item() { ItemId = 1, Child = child }; session.Save(parent); session.Save(item); tx.Commit(); } 

The resulting sql statements:

 -- statement #1 INSERT INTO [Parent] DEFAULT VALUES; select SCOPE_IDENTITY() -- statement #2 INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_0 */, 1 /* @p1_0 */, 1 /* @p2_0 */) INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_1 */, 1 /* @p1_1 */, 2 /* @p2_1 */) INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_2 */, 1 /* @p1_2 */, 3 /* @p2_2 */) -- statement #3 INSERT INTO [Item] (version, PARENT_ID, CHILD_ID, ItemId) VALUES (1 /* @p0_0 */, 1 /* @p1_0 */, 1 /* @p2_0 */, 1 /* @p3_0 */) 

As you can see, there are no extraneous / unnecessary UPDATE statements, but the object model is not modeled in a natural way, since I do not want Item to have a link back to Child, and I NEED a collection of elements in Child.

I cannot find a way to prevent unwanted / unnecessary UPDATE statements except to remove any HasMany relationships from Child. It seems that since the “child” is already “a lot” from the “inverted” one-to-many relationship (he is responsible for preserving himself), he does not take into account the reverse installation when it is the “one” part from the other -to-many inverted attitude ...

It drives me crazy. I cannot accept these additional UPDATE statements without any well-thought-out explanations :-) Does anyone know what is going on here?

Answer 1

After struggling with this night and without hope of seeing an answer even here in Stack Overflow :-) I came up with a solution ... I began to think that perhaps it was a change in the Child objects, which were considered as a change in the parent collection, and then led to a change in the version of Entity. My assumption began to strengthen after reading this:

(13) optimistic-lock (optional - default is true): Views that change the state of the collection will increase the version for owners. (For many associations, it is often wise to disable this setting.) (Found here: http://nhibernate.info/doc/nh/en/index.html#collections )

Then I naively changed the display of the parent so as not to use the optimistic lock as follows:

  public MapeamentoParent() { Id(_ => _.Id, "PARENT_ID").GeneratedBy.Identity(); HasMany<Child>(_ => _.Children) .Inverse() .AsSet() .Cascade.All() .Not.OptimisticLock() .KeyColumn("PARENT_ID"); } 

This did not work. But then I noticed something interesting in extraneous updates:

 -- statement #1 UPDATE [Child] SET version = 2 /* @p0 */ WHERE PARENT_ID = 1 /* @p1 */ AND CHILD_ID = 1 /* @p2 */ AND version = 1 /* @p3 */ -- statement #2 UPDATE [Child] SET version = 2 /* @p0 */ WHERE PARENT_ID = 1 /* @p1 */ AND CHILD_ID = 2 /* @p2 */ AND version = 1 /* @p3 */ -- statement #3 UPDATE [Child] SET version = 2 /* @p0 */ WHERE PARENT_ID = 1 /* @p1 */ AND CHILD_ID = 3 /* @p2 */ AND version = 1 /* @p3 */ 

I was lucky to notice that the version was updated to 2! (Deviation: I used the DateTime version field, but since it does not have infinite precision, I intentionally changed it to the integral version when I started thinking that this is a version problem, so I could see every single step in the version, and don’t miss increments that occur in less than milliseconds that are not tracked by DateTime versions due to its accuracy or lack of it). So, before despairing again, I changed the parent HasMany to what it was before (to try to isolate any possible solution) and instead added Not.OptimisticLock () to the child's map (after all the objects that seemed to update their versions there were children!):

  public class MapeamentoChild : ClassMap<Child> { public MapeamentoChild() { CompositeId() .KeyReference(_ => _.Parent, "PARENT_ID") .KeyProperty(_ => _.ChildId, "CHILD_ID"); HasMany(_ => _.Items) .AsSet() .Cascade.All() .Not.OptimisticLock() .KeyColumns.Add("PARENT_ID") .KeyColumns.Add("CHILD_ID"); Version(Reveal.Member<Child>("version")); } } 

And he did a great job issuing the following SQL statements:

 -- statement #1 INSERT INTO [Parent] DEFAULT VALUES; select SCOPE_IDENTITY() -- statement #2 INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_0 */, 1 /* @p1_0 */, 1 /* @p2_0 */) INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_1 */, 1 /* @p1_1 */, 2 /* @p2_1 */) INSERT INTO [Child] (version, PARENT_ID, CHILD_ID) VALUES (1 /* @p0_2 */, 1 /* @p1_2 */, 3 /* @p2_2 */) -- statement #3 INSERT INTO [Item] (version, ItemId) VALUES (1 /* @p0_0 */, 1 /* @p1_0 */) -- statement #4 UPDATE [Item] SET PARENT_ID = 1 /* @p0_0 */, CHILD_ID = 1 /* @p1_0 */ WHERE ItemId = 1 /* @p2_0 */ 

NO SCREEN UPDATE APPLICATION FOR EVERYONE !!! :-)

The problem is that I still can’t explain why this did not work before. For some reason, when Child relates one to many to another object, external SQL transactions are performed. You should set the optimistic lock to false for these one-to-many collections of the Child object. I don’t know why all Child objects also changed their versions at the same time, only because the Child class had a one-to-many relationship with the Item added to it. It makes no sense to increase the version numbers of all child objects when only one of them changes!

My biggest problem is why ALL the children in the parent collection were updated, even if I did not add any items to any of the children. The only thing is that the child has a HasMany relation to Item ... (there is no need to add any element to any child in order to "receive" these additional updates). It seems to me that NHibernate is misunderstood here, but since I completely lack a deeper understanding of NHibernate, I can’t say for sure and definitely don’t know where the problem is, I don’t even categorically declare that this is really a problem, as it may well be my complete lack of strength of NHibernate's access to a real criminal !:-)

I hope someone more enlightened comes to explain what happened / is happening, but by setting an optimistic block on a false one-to-many relationship, as suggested in the documentation, and solved the problem.