From DB to RAM: WellKnowInstanceType

How many times we are putting something in DB even if it is not needed ? No one ? sure ?

In these days I’m reviewing an application after four year rolling. I have checked the persistent states of some entities and they are in exactly the same state they was four years ago; why entities should not persist in compiled code ?

The enum is a special kind of these entities. If the value of the enum is enough for your needs all is fine. If you need something more than the value, as for example de description, some workaround may be enough. If you need something more than a value to persist and a “in memory description” ?

Example

A classic example of this situation is the entity Country. A possible simplification is:

public class Country
{
public int Id { get; protected set; }

public string Name { get; set; }
public short PhonePrefix { get; set; }
}

In this case, and let me say in various others cases in a real world application, an enum is not enough and a “DB persisted” entity is too much.

How many times you add a Country in your DB ? How many times you are changing the persistent state of a State/Province ? How many times you are deploying your application ?

From DB to RAM

This is the state of the art before change it

public class User
{
public virtual string Name { get; set; }
public virtual Country Country { get; set; }
}

<class name="User">
<id type="int">
    <generator class="hilo"/>
</id>
<property name="Name"/>
<many-to-one name="Country"/>
</class>

To transform the entity Country in a well-know-instance, maintaining the logical relationship but simplifying all access to DB (remove possible N+1, lazy-loading, joins and so on), the best candidate, using NHibernate, is an implementation of IUserType but… a special one because I have a Identifier stored in the DB and I need some others properties.

the Countries repository

First of all I need a little change to the Country implementation

public class Country
{
internal Country(int id)
{
    Id = id;
}

then a repository of countries.

public class Countries : ReadOnlyCollection<Country>
{
public static Country Argentina =
    new Country(1) {Name = "Argentina", PhonePrefix = 54};
public static Country Italy =
    new Country(2) {Name = "Italy", PhonePrefix = 39};

public Countries() : base(new[] {Argentina, Italy}) {}
}

the custom type

Here I will show only the ctor:

protected WellKnowInstanceType(
IEnumerable<T> repository,
Func<T, int, bool> findPredicate,
Func<T, int> idGetter)

The implementation of IUserType need:

• The full repository (Countries)
• The Func<T, int, bool> to convert an actual value stored in DB to an instance of the entity (Country)
• The Func<T, int> to get the value to store in DB (the Country id)

My custom implementation of IUserType is:

public class CountryType : WellKnowInstanceType<Country>
{
public CountryType() : base(new Countries(), (e, k) => e.Id == k, e => e.Id) {}
}

The new mapping is:

<typedef name="Country"
     class="uNhAddIns.Test.UserTypes.CountryType, uNhAddIns.Test"/>

<class name="UserMitaMita">
<id type="int">
    <generator class="hilo"/>
</id>
<property name="Name"/>
<property name="Country" type="Country"/>
</class>

Implications

Perhaps you will need to change some queries removing “join fetch” or “FetchMode.Eager” but believe me that your RDBMS will be happy for that (I don’t know how happy will be your DBA).

This is the example of queries extracted from uNhAddIns tests:

[Test]
public void Queries()
{
sessions.EncloseInTransaction(s =>
{
    s.Save(new UserMitaMita { Name = "Fabio", Country = Countries.Argentina });
    s.Save(new UserMitaMita { Name = "Maulo", Country = Countries.Italy });
});

sessions.EncloseInTransaction(s =>
{
    var l = s.CreateQuery("from UserMitaMita u where u.Country in (:countries)")
        .SetParameterList("countries", new[] { Countries.Argentina, Countries.Italy })
        .List<UserMitaMita>();
    l.Should().Have.Count.EqualTo(2);
});
sessions.EncloseInTransaction(s =>
{
    var l = s.CreateQuery("from UserMitaMita u where u.Country = :country")
        .SetParameter("country", (object)Countries.Argentina)
        .List<UserMitaMita>();
    l.Should().Have.Count.EqualTo(1);
    l[0].Name.Should().Be.EqualTo("Fabio");
});
sessions.EncloseInTransaction(s => s.CreateQuery("delete from UserMitaMita").ExecuteUpdate());
}

The implementation of WellKnowInstanceType<T> is available in uNhAddIns (if you prefer copy&paste the code is available here).

The phrase

“The database only exist because we have not found a way to secure energy, in a permanent manner, to our servers” (by Angel java Lopez)