Logo

NHibernate

The object-relational mapper for .NET

How to use 0 instead of null for foreign keys

My twitter friend @hotgazpacho is having a nightmare with a legacy database, which has the following rule:

“0 represents the absence of an entity without an actual row in the database”

It is pretty interesting how many times I’ve seen this scenario on nhibernate forums.

The first thing people do is to add “not-found=ignore” to every relationship, but not-found ignore is an evil, because NHibernate need to know if the row exist when lazy load. So not-found ignore is like a lazy loading killer. Also, with not-found=ignore doesn’t work when you insert or update, nhibernate will persist a null value instead of 0.

We want to keep and follow the rule until no legacy applications use this database and we can fix the data, maybe never (or like the spanish saying “provisoriamente para siempre”).

NHibernate is bad in many aspects, but the only thing we can’t blame is extensibility. We can tweak NHibernate to work in this scenario and in many more.

First a test:

[TestFixture]
public class Fixture
{
    private ISessionFactory sf;
    private Configuration configuration;

    [TestFixtureSetUp]
    public void SetUp()
    {
        configuration = new Configuration().Configure();

        //export the schema
        var schemaExport = new SchemaExport(configuration);
        schemaExport.Execute(true, true ,false);
        sf = configuration.BuildSessionFactory();
    }

    [TestFixtureTearDown]
    public void TearDown()
    {
        var schemaExport = new SchemaExport(configuration);
        schemaExport.Execute(true, true, true);
    }


    [Test]
    public void WhenInsertingAPersonWithNullCountryThenInsert0ValueInCountry()
    {
        int personId;
        using(var s = sf.OpenSession())
        using(var tx = s.BeginTransaction())
        {
            var p = new Person {Name = "tito"};
            s.Save(p);
            tx.Commit();
            personId = p.Id;
        }
        using(var s = sf.OpenSession())
        {
            s.CreateSQLQuery("select CountryId from Person where id = :id")
                .SetInt32("id", personId)
                .UniqueResult<int?>()
                .Should().Be.EqualTo(0);
        }

    }

    [Test]
    public void WhenSelectingAPersonWithCountryWithIdEqualsTo0ThenCountryShouldBeNull()
    {
        int personId;
        using (var s = sf.OpenSession())
        using (var tx = s.BeginTransaction())
        {
            var p = new Person { Name = "tito" };
            s.Save(p);
            tx.Commit();
            personId = p.Id;
        }

        using (var s = sf.OpenSession())
        using (s.BeginTransaction())
        {
            s.Get<Person>(personId)
                .Country.Should().Be.Null();

        }

    }
}

The first test persist a Person with null Country, and goes to the database to test if the CountryId is equals to 0.

The second test, persist a Person with null Country, in other session executes Get<Person> and test if the Country is null.

The mapping for person is trivial:

<class name="Person">
<id name="Id">
  <generator class="hilo">
    <param name="max_lo">100</param>
  </generator>
</id>    
<property name="Name" />
<many-to-one name="Country" 
             class="Country" 
             column="CountryId" 
             foreign-key="none" />
</class>

Note: I am killing the constraint for this test  foreign-key=”none” tells the schema export to not create a foreign key, that is how the db must be on real life Guiño.

The solution is pretty simple:

public class NullableTuplizer : PocoEntityTuplizer
{
    public NullableTuplizer(EntityMetamodel entityMetamodel, PersistentClass mappedEntity)
        : base(entityMetamodel, mappedEntity)
    {
    }

    public override object[] GetPropertyValuesToInsert(
        object entity, IDictionary mergeMap, ISessionImplementor session)
    {
        object[] values = base.GetPropertyValuesToInsert(entity, mergeMap, session);
        //dirty hack 1
        for (int i = 0; i < values.Length; i++)
        {
            if (values[i ] == null && typeof (IEntity).IsAssignableFrom(getters[i ].ReturnType))
            {
                values[i ] = ProxyFactory.GetProxy(0, null);
            }
        }
        return values;
    }

    public override object[] GetPropertyValues(object entity)
    {
        object[] values = base.GetPropertyValues(entity);
        //dirty hack 2
        for (int i = 0; i < values.Length; i++)
        {
            if (values[i ] == null && typeof (IEntity).IsAssignableFrom(getters[i ].ReturnType))
            {
                values[i ] = ProxyFactory.GetProxy(0, null);
            }
        }
        return values;
    }


    public override void SetPropertyValues(object entity, object[] values)
    {
        //dirty hack 3.
        for (int i = 0; i < values.Length; i++)
        {
            if (typeof (IEntity).IsAssignableFrom(getters[i ].ReturnType)
                && ((IEntity) values[i ]).Id == 0)
            {
                values[i ] = null;
            }
        }
        base.SetPropertyValues(entity, values);
    }
}

We lie to nhibernate three times:

  • When getting the values for insert, we change a  “null” in the Country property, for a proxy of country with Id equals to 0. NHibernate assumes that such country exist and executes:
  • When getting the values for update , we do the same than for the insert.
  • When loading the values in the entity, we will get a nhibernate proxy or entity with Id = 0, we change this value to null.

To register the NullableTuplizer for all the mappings:

foreach (var persistentClass in configuration.ClassMappings)
{
    persistentClass.AddTuplizer(EntityMode.Poco, typeof(NullableTuplizer).AssemblyQualifiedName);
}

Posted Fri, 28 January 2011 02:46:00 AM by jfromainello
Filed under: relation Tuplizers EntityMode

comments powered by Disqus
© NHibernate Community 2016