NHibernate - Wrong Columns on Queries

Question

I'm getting an intermittant problem with NHibernate where it generates a query for an entity, but replaces one of the columns with a column from completely different (and unrelated) entity.

It only ever replaces a single column, and is generally solved by restarting the application (though sometimes it takes a couple of attempts).

• ASP.NET application (.NET 4.0)
• SessionFactory created during Application_Start
• NHibernate 3.3.1- All mappings/configuration done via Mapping By Code
• Using Nhibernate Criteria

Any input on this would be much appreciated!

Entity

public class LiquiditySourceItem : RunDataEntity, IEntity<int>
    {
        public virtual int Id { get; protected internal set; }
        public virtual int IdentID { get; protected internal set; }
        public virtual string Portfolio { get; protected internal set; }
        public virtual string ProfitCentre { get; protected internal set; }
        public virtual DateTime? MaturityDate { get; protected internal set; }
        public virtual string Curr1 { get; protected internal set; }
        public virtual string Curr2 { get; protected internal set; }
        public virtual decimal Reval { get; protected internal set; }
        public virtual string ContractType { get; protected internal set; }
        public virtual string ContractType2 { get; protected internal set; }
        public virtual string ContractCode { get; protected internal set; }
        public virtual decimal AmountSignedTradeUnit { get; protected internal set; }
        public virtual decimal Amount2Signed { get; protected internal set; }
        public virtual decimal SpotDelta { get; protected internal set; }
        public virtual string TradeRevalCurr { get; protected internal set; }
    }

Entity Mapping

public LiquiditySourceItemMap()
{
    Id(x => x.Id, map => map.Column("RowId"));
    Property(x => x.IdentID, map => map.Column("IdentID"));
    Property(x => x.Portfolio, map => map.Column("Portfolio"));
    Property(x => x.ProfitCentre, map => map.Column("ProfitCentre"));
    Property(x => x.MaturityDate, map => map.Column("Con_Expiry"));
    Property(x => x.BuySell, map => map.Column("BS"));
    Property(x => x.Curr1, map => map.Column("Curr1"));
    Property(x => x.Curr2, map => map.Column("Curr2"));
    Property(x => x.Reval, map => map.Column("Reval"));
    Property(x => x.ContractType, map => map.Column("ContractType"));
    Property(x => x.ContractType2, map => map.Column("ContractType2"));
    Property(x => x.ContractCode, map => map.Column("ContractCode"));
    Property(x => x.AmountSignedTradeUnit, map => map.Column("AmountSignedTradeUnit"));
    Property(x => x.Amount2Signed, map => map.Column("Amount2Signed"));
    Property(x => x.ValSpot, map => map.Column("Val_Spot"));
    Property(x => x.SpotDelta, map => map.Column("SpotDelta"));
    Property(x => x.TradeRevalCurr, map => map.Column("Traderevalcurr"));
    Property(x => x.SourceReport, map => map.Column("SourceReport"));
    ManyToOne(x => x.RunContext, map => map.Column("RunContextID"));
    Table("Staging.vw_Liquidity");
}

Report Entity

public class BusinessBreakdownStandardPosition : ReportRunDataEntity, IEntity<long>
    {
        public virtual long Id { get; set; }
        public virtual decimal FinalNettingAmountUSD { get; set; }
        public virtual decimal InitialChargeAmountUSD { get; set; }
        public virtual BusinessBreakdownInitialPrr InitialPrr { get; set; }
        public virtual IEnumerable<FinalInstrumentPosition> FinalInstrumentPositions { get; set; }
        public virtual decimal CreditEventPaymentUSD { get; set; }
        public virtual decimal ValuationChangeIncreaseUSD { get; set; }
        public virtual decimal ValuationChangeDecreaseUSD { get; set; }
        public virtual string ReportKey { get; set; }
        public virtual decimal USDCharge { get; set; }
        public virtual decimal USDChargeICG { get; set; }
        public virtual string InstrumentType { get; set; } 
}

Report Entity Mapping

public class BusinessBreakdownStandardPositionMap : ClassMapping<BusinessBreakdownStandardPosition>
    {
        public BusinessBreakdownStandardPositionMap()
        {
            Id(x => x.Id,
               m =>
                   {
                       m.Column("BusinessBreakdownStandardPositionID");
                       m.Generator(Generators.HighLow,
                                   g =>
                                   g.Params(
                                       new
                                           {
                                               table = "dbo.HiValue",
                                               max_lo = 10000,
                                               Where = string.Format("EntityName = 'BusinessBreakdownStandardPosition'")
                                           }));
                   });
            Property(x => x.FinalNettingAmountUSD, map => map.Column("FinalNettingAmountUSD"));
            Property(x => x.InitialChargeAmountUSD, map => map.Column("InitialAmountUSD"));
            Property(x => x.CreditEventPaymentUSD);
            Property(x => x.ValuationChangeDecreaseUSD);
            Property(x => x.ValuationChangeIncreaseUSD);
            Property(x => x.USDCharge);
            Property(x => x.USDChargeICG);
            Property(x=>x.InstrumentType);
            ManyToOne(p => p.RunContext, map => map.Column("ReportRunContextID"));
            ManyToOne(p => p.InitialPrr, m =>
                {
                    m.Column("InitialPrrID");
                    m.Cascade(Cascade.All);
                });
            Property(x => x.ReportKey);
            Bag(x => x.FinalInstrumentPositions, collectionMapping =>
                {
                    collectionMapping.Table("Reporting.BusinessBreakdownFinalInstrumentPositionStandardPositionMap");
                    collectionMapping.Cascade(Cascade.All);
                    collectionMapping.Key(k => k.Column("StandardPositionID"));
                }, mapping => mapping.ManyToMany(y => y.Column("FinalInstrumentPositionID")));
            Table("Reporting.BusinessBreakdownStandardPosition");
        }
    }

SQL Query, Generated By NHibernate

SELECT
    this_.RowId AS RowId47_0_,
    this_.IdentID AS IdentID47_0_,
    this_.Portfolio AS Portfolio47_0_,
    this_.ProfitCentre AS ProfitCe4_47_0_,
    this_.Con_Expiry AS Con5_47_0_,
    this_.BS AS BS47_0_,
    this_.Curr1 AS Curr7_47_0_,
    this_.Curr2 AS Curr8_47_0_,
    this_.Reval AS Reval47_0_,
    this_.ContractType AS Contrac10_47_0_,
    this_.ContractType2 AS Contrac11_47_0_,
    this_.ContractCode AS Contrac12_47_0_,
    this_.AmountSignedTradeUnit AS AmountS13_47_0_,
    this_.Amount2Signed AS Amount14_47_0_,
    this_.Val_Spot AS Val15_47_0_,
    this_.SpotDelta AS SpotDelta47_0_,
    this_.InitialAmountUSD AS Initial17_47_0_,
    this_.RunContextID AS RunCont18_47_0_,
    this_.SourceReport AS Sou19_47_0_
FROM Staging.vw_Liquidity this_

Exception

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'InitialAmountUSD'.

As you can see, nhibernate has replaced the LiquiditySourceItem column 'Traderevalcurr' with 'InitialAmountUSD', which belongs to the BusinessBreakdownStandardPosition entity. These entities have no relationship whatsoever. Otherwise, the SQL is exactly as you'd expect( including column order).

Observations

• The wrong column is always a valid column in a different mapped entity
• The wrong column will replace an existing one
• The issue sometimes ocurrs between other entities. Again, there's no relationship between these

Any thoughts?

Kosygin

Does this problem always affect the same entities and columns, or is this is it a widespread issue? If the former, I'd expect some random misconfiguration somewhere... – Nonprofessional

There's at least one more entity which it happens to. Again, it's intermittant, and I've yet to be able to recreate the problem consistently. – Kosygin

It also affects both of the entities I've included above, i.e. sometimes it replaces a column from 'LiquiditySourceItem' with another column from 'BusinessBreakdownStandardPosition', and sometimes the other way round. – Kosygin

Does mapping by code allow you to generate the hbm.xml files? If so, examine those to verify the mapping. – Foreknowledge

Yes, it does. When you look at the generated hbm.xml files though, all mappings are correct, even after this mapping bug has ocurred. Also, having run the application several times, including both sucessful and failed runs, the generated mapping files are all identical (i.e. all were correct). This suggests that NHibernate isn't actually using these generated files to perform the db/entity mapping, rather doing it programatically, which I imagine is where the issue is? – Kosygin

Is it significant that the capitalisation for this field differs between the DB and the property? i.e. Have you tried changing... Property(x => x.TradeRevalCurr, map => map.Column("Traderevalcurr")); to read... Property(x => x.TradeRevalCurr, map => map.Column("TradeRevalCurr")); – Atavistic

Have you looked for random .hbm.xml files on the environment or more likely in the solution? – Stonecutter

Do the classes have a common ancestor in RunDataEntity and ReportRunDataEntity? – Pottery

@Pottery No, the only common ancestor is an interface which only contains an Id field: – Kosygin

public interface IEntity<T> { T Id { get;} } – Kosygin

Are you working with SQL Server? If so, "Staging" and "Reporting" are schemas? – Daltondaltonism

Yes, they are both schemas. Any thoughts?? – Kosygin

This sounds very odd. Could it be a bug in NHibernate? I see that it is now on version 3.3.3. Have you looked at the release notes? – Marven

@SoWieLie, I've not looked at the release notes, but that's a good idea. However, I noticed that somebody else on SO had had the same problem in 3.3.3.400, which suggests it's still not been fixed. #16992338 – Kosygin

Answer 1

I asked the same question on the NHibernate Users Google Groups forum, and someone thinks they have worked out the root cause (and have also proposed a solution):

https://groups.google.com/forum/#!topic/nhusers/BZoBoyWQEvs

The problem code is in PropertyPath.Equals(PropertyPath) which attempts to determine equality by only using the hash code. This works fine for smaller code bases as the default Object.GetHashCode() returns a sequential object index. However, after garbage collection, these indices get reused as finalized objects are removed and new objects are created...which results in more than one object getting the same hashcode...Once garbage collection kicks in, property paths have a chance to share the same hashcode which means they will ultimately mix up their customizers for the colliding properties, thus the wrong column names...

If you want to fix this the bug, you can patch the NH source code:

If you have your own copy of the NH source, you can fix the bug by changing NHibernate/Mapping/ByCode/PropertyPath.cs line #66 from:

return hashCode == other.GetHashCode();

To:

return hashCode == other.GetHashCode() && ToString() == other.ToString();

Please check out the Google Group for full details of the issue.

Kosygin