Roman Koliada Roman Koliada - 21 days ago 5
SQL Question

Unexpected batch update commands after select in Nhibernate

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?

Answer

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; }