NHibernate Forge
The official new home for the NHibernate for .NET community

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 ene 28 2011, 09:46 a.m. by Jose Romaniello

Comments

Fabio Maulo wrote re: How to use 0 instead of null for foreign keys
on 01-28-2011 10:39

You don't need to create the drop script.

Using foreign-key="none" is enough.

;)

Jose Romaniello wrote re: How to use 0 instead of null for foreign keys
on 01-31-2011 16:43

@fabio @apweb2010 i just updated the post. Thanks for the tip.

ExternalBlogs wrote NHibernate: utilizzare lo 0 per rappresentare l&rsquo;assenza di entit&agrave;
on 02-22-2011 10:32

Quando si ha a che fare con database legacy, potrebbe saltar fuori la necessità di non inserire nel database

MBDTeam wrote re: How to use 0 instead of null for foreign keys
on 03-16-2011 8:50

@Jose Thanks for charing this! I tried your solution with NH 3.1 GA. But I have a little problem. Reading Entities from the DB works as aspected. A entity with parentid=0 returns as nothing. But if I save an entity with parent=nothing I get an ADO exception claiming insert of null in parentid is not allowed. I tracked it down to AbstractSaveEventListener.cs -> PerformSaveOrReplicate. In line 'object[] values = persister.GetPropertyValuesToInsert(entity, GetMergeMap(anything), source);' the property parent is set to a dummy Object with id 0. This is ok. But a few lines later:

if (substitute)

{

persister.SetPropertyValues(entity, values, source.EntityMode);

}

the parent property is set to null again :-(

What can I do?

Michael

Jose Romaniello wrote re: How to use 0 instead of null for foreign keys
on 03-16-2011 9:04

Hi @MBDTeam, there last version of this code is on bitbucket; bitbucket.org/.../hotgazpachoeg

with some tests, please can you try that version and if it fail... try to write me a short test inside that solution.

I'll have a look ASAP.

Thanks

Jose Romaniello wrote re: How to use 0 instead of null for foreign keys
on 03-16-2011 9:09

@ExternalBlogs Sorry this is an english post. I don't speak italian; tried to translate your comment but i don't understand. May your write your comment in english? i'll kindly answer your questions.

Thanks

MBDTeam wrote re: How to use 0 instead of null for foreign keys
on 03-16-2011 10:47

@Jose, thanks for your quick response. A failing test is easy to get. Just add

public virtual Person Parent { get; set; }

public virtual IList<Person> Childs { get; set; }

to Person and

<many-to-one name="Parent"

class="Person"

column="ParentId"

not-null="true"

foreign-key="none" />

<bag name="Childs"

inverse="true"

lazy="true"

cascade="all">

<key column="ParentId" />

<one-to-many class="Person" />

</bag>

to person.hbm.xml and all your tests will fail. I think just adding a IList of Entity X will do it also, because then substitue in AbstractSaveEventListener.cs -> PerformSaveOrReplicate becomes true.

I have found a workaround for me. My base entity class has a property isTransient that is set to true if my dao creates the objects for me. And in my Interceptor -> PostFlush it is set back to false. So i can do the following in SetPropertyValues:

if (!entity.isTransient) {

values(i) = null;

}

Now everything works as aspected. But I would like to now if there is a cleaner way to detect if the entity in SetPropertyValues is Transient???

Michael

hotgazpacho wrote re: How to use 0 instead of null for foreign keys
on 03-22-2011 12:32

Yes, it seems that as soon as you add a collection of child entities, GetPropertyValues(object entity), and thus CreateFakeProxy(int i) does not get called.

Jose Romaniello wrote re: How to use 0 instead of null for foreign keys
on 03-22-2011 15:54

Thank you both! I'll have a look ASAP

hotgazpacho wrote re: How to use 0 instead of null for foreign keys
on 03-23-2011 16:42

OK, the way I'm going about solving this is eschewing the Tuplizer altogether and using Event Listeners. Specifically IPreInsertEventListener and IPreUpdateEventListeners. Ayende outlined their use in a blog post: ayende.com/.../nhibernate-ipreupdateeventlistener-amp-ipreinserteventlistener.aspx

Still need to figure out a way to abstract it a bit so that I am not hard-coding entity names and properties in the event listeners, but it's a good start, and my tests pass!

DsA wrote re: How to use 0 instead of null for foreign keys
on 04-27-2011 9:11

I have a problem. If I use NullableTuplizer in my project, then all of my object with null child are becoming dirty and NHibernate is sending UPDATE command for each object when Session.Flush()

digitalpacman wrote re: How to use 0 instead of null for foreign keys
on 07-27-2011 19:03

I only need this tuplizer for a single class. And I am using Fluent NHibernate. I can't figure out how to apply the tuplizer to it since i'm using fluent. Any help?

pvginkel wrote re: How to use 0 instead of null for foreign keys
on 11-15-2011 4:16

I solved this issue with a custom persister. I inherited from NHibernate.Persister.Entity.SingleTableEntityPersister and overriden just IdentifierType to return a custom implementation. In the identifier type, in the NullSafeGet and NullSafeSet methods, I convert null to the value I want (a space in my case, but this should work equally well for integers).

The only problem I couldn't solve through this method is that our database requires that "sometimes", null is converted, and "sometimes" it isn't.

online shopping invasion wrote online shopping invasion
on 10-17-2014 23:15

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

click the next website page wrote click the next website page
on 10-22-2014 1:48

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

Best Suitcase Brands wrote Best Suitcase Brands
on 11-04-2014 8:56

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

dentysta białystok wrote dentysta białystok
on 11-05-2014 2:04

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

best online shopping sites usa wrote best online shopping sites usa
on 11-06-2014 15:23

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

stock market for dummies wrote stock market for dummies
on 11-09-2014 9:46

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

simpsons tapped out iphone cheats wrote simpsons tapped out iphone cheats
on 11-10-2014 9:33

How to use 0 instead of null for foreign keys - NHibernate blog - NHibernate Forge

Powered by Community Server (Commercial Edition), by Telligent Systems