Unexpected batch update commands after select in Nhibernate

Question

I found that NHibernate make unexpected updates after some select.

Entities:

public class BasePriceRule : BaseEntity
{
    public virtual string Name { get; set; }

    public virtual string RuleString { get; set; }

    public virtual TypePriceRule Type { get; }

    public virtual DateTime Date { get; set; } = DateTime.Now;
}

public class ProductGroupRule : BasePriceRule
{
    public virtual ProductGroup ProductGroup { get; set; }

    public override TypePriceRule Type => TypePriceRule.ProductGroup;
}

public class ProductGroup : BaseEntity
{        
    public virtual string Code { get; set; }

    public virtual string Name { get; set; }

    public virtual string Description { get; set; }

    public virtual UoM UoM { get; set; }

    public virtual CustomProductType Type { get; set; }
}

Mappings:

public class BasePriceRuleMap : ClassMap<BasePriceRule>
{
    public BasePriceRuleMap()
    {
        Table("PriceRule");
        Id(x => x.Id);

        Map(x => x.Name);
        Map(x => x.RuleString).Length(4096);

        DiscriminateSubClassesOnColumn("Type");
        Map(x => x.Type).CustomType<TypePriceRule>().ReadOnly().Access.None().Not.Nullable();

        Map(x => x.Date).Not.Nullable().Default("CURRENT_TIMESTAMP");
    }
}

public ProductGroupRuleMap()
{
    KeyColumn("Id");

    References(x => x.ProductGroup).Fetch.Join()/*.Not.Update()*/;
    DiscriminatorValue((int)TypePriceRule.ProductGroup);
}

public ProductGroupMap()
{
    Id(x => x.Id);

    Map(x => x.Code).Unique();
    Map(x => x.Name);
    Map(x => x.Description);
    Map(x => x.Type).CustomType<int>();

    References(x => x.UoM);
}

So when I run this simplest query:

_session.QueryOver<ProductGroupRule>();

Or this:

_session.QueryOver<ProductGroupRule>().Fetch(x => x.ProductGroup).Eager.List();

I receive this generated sql:

2016-11-18 17:17:39.3103 NHibernate.SQL  SELECT this_.Id as Id62_1_, this_.Name as Name62_1_, this_.RuleString as RuleString62_1_, this_.Type as Type62_1_, this_.ProductGroup_id as ProductG6_62_1_, productgro2_.Id as Id66_0_, productgro2_.Code as Code66_0_, productgro2_.Name as Name66_0_, productgro2_.Description as Descript4_66_0_, productgro2_.Type as Type66_0_, productgro2_.UoM_id as UoM6_66_0_ FROM PriceRule this_ left outer join [ProductGroup] productgro2_ on this_.ProductGroup_id=productgro2_.Id WHERE this_.Type='2'
2016-11-18 17:17:39.4474 NHibernate.SQL  Batch commands:
command 0:UPDATE [ProductGroup] SET Code = @p0, Name = @p1, Description = @p2, Type = @p3, UoM_id = @p4 WHERE Id = @p5;
command 1:UPDATE [ProductGroup] SET Code = @p0, Name = @p1, Description = @p2, Type = @p3, UoM_id = @p4 WHERE Id = @p5;
--Numbers of commands are equal to the amount of records in PriceRule table

I have tried:

Enable/disable lazy-loading
Change Cascade to None, Evict
Set Not.Update() for ProductGroup reference
Use Future<>

But still can't get rid of them.

Can anybody explain why those update commands appear?

score 2 · Accepted Answer · answered Nov 18 '16 at 16:35

In the ProductGroup definition, there is one property, which cannot be null - CustomProductType Type

public class ProductGroup : BaseEntity
{        
    public virtual string Code { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
    public virtual UoM UoM { get; set; }
    // this cannot be null
    public virtual CustomProductType Type { get; set; }
}

C# would set that into default value (usually the first enum)

But it seems, that in DB, related column contains null. For NHibernate it is a sign: 1) loaded was NULL, but when checking the object - 2) it has a value assigned.

So, the object is dirty and because Flush mode is by default AUTO ... it tries to keep DB and C# instance in sync

Just change the mapping

    public virtual CustomProductType? Type { get; set; }

It's a bit strange because actually I don't have ProductGroups with empty Type in th database. Anyway setting property to Nullable or in opposite changing mapping configuration to Not.Nullable remove update statements. Thank you — Roman Koliada, Nov 18 '16 at 16:57
Here's a link to the original code for ghostbuster. http://joseoncode.com/2010/02/05/nhibernate-ghostbuster-version-1-1/ it's a great tool for finding issues like this, not sure if it will run with the current version of nhibernate. — Fran, Nov 18 '16 at 17:59

score 0 · Answer 2 · answered Nov 18 '16 at 15:58

My guess is that there's an automatic flush happening and NH thinks that those ProductGroup objects are dirty for some reason. The best course of action, IMHO, is to enable logging via log4net and get some detailed diagnostics. NH's logging is excellent. You should be able to get to the bottom of this through that.

Unexpected batch update commands after select in Nhibernate

Entities:

Mappings:

2 Answers2