NHibernate emitting extraneous update statements r

问题:

The following classes represent, in a bare-minimum way, my real-world scenario with a legacy database. I can add new columns to it, but this is all I can do, since the 300+ hundred table database is used by many other legacy applications which won't be ported to NHibernate (so migrating from composite keys isn't 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;
}

This is how I've mapped these to the "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'm using to insert a Parent with three children and one children with an item:

        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/superfluous since all that information was already sent to the database in batch inserts in statement 2.

This would be the expected behavior if the Parent mapping hadn't set the Inverse property on the HasMany (one-to-many) relationship.

In fact, it becomes stranger still when we get rid of the one-to-many relationship from Child to Item like this:

Remove the collection from Child and add a Child property into Item:

   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 mapping of Child and Item to remove the HasMany from Item and add a References on the composite key on 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 (notice that now we need to call save Item explicitly):

        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 are:

-- 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/superfluous UPDATE statements, but the object model is not modeled naturally as I don't want Item to have a link back to Child and I NEED a collection of Items in Child.

I can't find any way to prevent those unwanted/unneeded UPDATE statements except to remove any HasMany relations from Child. It seems that since Child is already the "many" from a "inverted" one-to-many relationship (it is responsible from saving itself), it don't respect the Inverse setup when it is the "one" part from another one-to-many inverted relation...

This is driving me nuts. I can't accept those extra UPDATE statements without any sort of well thought explanation :-) Does any one knows what is going on around here?

回答1:

After struggling with this through the night and no hope in sight for an answer even here in Stack Overflow :-) I've come up with the solution... I started to think that maybe it was a change in the Child objects that was being considered as a change in the Parent's collection and then resulted in a change to the Entity version. My guess started to solidify after reading this:

(13) optimistic-lock (optional - defaults to true): Species that changes to the state of the collection results in increment of the owning entity's version. (For one to many associations, it is often reasonable to disable this setting.) (Found here: http://nhibernate.info/doc/nh/en/index.html#collections)

I then naively changed the mapping on the Parent to not use 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 didn't work. But then I've noticed something interestingly in the 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 being updated to 2! (Digression: I was using a DateTime version field, but since it has not an infinite precision I've intentionally changed it to an integral version when I started to think it was a versioning issue, so that I could see every single increment in version, and do not miss increments that occur in less than milliseconds, which are not traceable by DateTime versions due to it's precision, or lack of). So, before despairing once again, I've changed the Parent's HasMany back to what it was before (to try to isolate any possible solution) and added the Not.OptimisticLock() to the Child's map instead (after all the entities that seemed to have their versions updated were the 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 it worked perfectly 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 EXTRANEOUS UPDATE STATEMENTS AT ALL!!! :-)

The problem is that I still can't explain why it didn't work before. For some reason when Child has a one to many relation to another entity the extraneous SQL statements are executed. You have to set optimistic lock to false on these one-to-many collections on the Child object. I don't know why ALL the Child objects had their versions changed simultaneously either, just because the Child class had a one-to-many relation to Item added to it. It makes no sense to increase the version numbers of all Child objects when only one of them is changed!

My biggest issue with this is why ALL Child objects on the Parent's collection were being UPDATED even if I didn't add any Item to any of the Child objects. It was happening for the fact alone that Child has a HasMany relation to Item... (no need to add any item to any Child to "get" those extra updates). It seems to me that NHibernate is figuring out things wrongly here, but since I completely lack any deeper understanding of NHibernate I can't say for sure, nor pinpoint exactly where the problem is, not even affirm categorically that it is indeed a problem as it may well be my complete lack of NHibernate grokking power the real culprit! :-)

I'll hope that someone more enlightened comes to explain what was/is happening, but setting the optimistic-lock to false on a one-to-many relationship as suggested by the documentation solved the issue.