When dealing with a legacy database one often encounters the situation that the database schema defines one-to-one relations between two entities. A typical example might be the following schema fragment
with a one-to-one relation between person and address, that is: each person can have an address and an address can only belong to a single person. To guarantee that a single person can only have zero or one address the foreign key column PersonId in the Address table is set to be unique.
Such a construct is not straight forward to map in NHibernate! I want to show you one possible solution. The solution works but is not free from certain hick-ups! Let's first model the domain
The domain model
the code for the above model is straight forward. Nothing magic
public class Person
{
public virtual int Id { get; set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
public virtual Address Address { get; private set; }
public virtual void AssignAddress(Address address)
{
Address = address;
address.Owner = this;
}
}
public class Address
{
public virtual int Id { get; set; }
public virtual string AddressLine1 { get; set; }
public virtual string AddressLine2 { get; set; }
public virtual string PostalCode { get; set; }
public virtual string City { get; set; }
public virtual Person Owner { get; set; }
}
Note that I have defined the setter method of the Address property in the Person entity as private such as that the consumer of the code is forced to use the AssignAddress method which sets up the bi-directional relation.
The mapping
And now we try to find a working mapping. I use the Fluent NHibernate framework for the mapping (please refer to my previous posts for an introduction to this framework: part 1, part 2, part 3, part 4)
public class PersonMapper : ClassMap<Person>
{
public PersonMapper()
{
LazyLoad();
Id(x => x.Id);
Map(x => x.FirstName);
Map(x => x.LastName);
HasOne(x => x.Address)
.PropertyRef(p => p.Owner)
.Cascade.All()
.FetchType.Join();
}
}
public class AddressMapper : ClassMap<Address>
{
public AddressMapper()
{
LazyLoad();
Id(x => x.Id);
Map(x => x.AddressLine1);
Map(x => x.AddressLine2);
Map(x => x.PostalCode);
Map(x => x.City);
References(x => x.Owner)
.WithUniqueConstraint()
.TheColumnNameIs("PersonId")
.LazyLoad()
.Cascade.None();
}
}
Unit Tests
What does the above mapping derive as database schema? Let's implement a test. First I have to define what is my model (or: where are my mappings to be found)
public class TestModel : PersistenceModel
{
public TestModel()
{
addMappingsFromAssembly(typeof(Person).Assembly);
}
}
I then use the following code to generate the schema
[TestFixture]
public class when_creating_the_schema
{
[SetUp]
protected void Context()
{
var model = new TestModel();
var config = new Configuration();
config.Configure();
model.Configure(config);
var factory = config.BuildSessionFactory();
var session = factory.OpenSession();
new SchemaExport(config).Execute(true, false, false, false, session.Connection, null);
}
[Test]
public void smoke_test()
{
true.ShouldBeTrue();
}
}
when using an SQLite database (in-memory mode) the output generated by the smoke test is
let me reformat the create table scripts a little bit
create table [Person] (
Id integer,
LastName TEXT,
FirstName TEXT,
primary key (Id))
create table [Address] (
Id integer,
AddressLine1 TEXT,
AddressLine2 TEXT,
PostalCode TEXT,
City TEXT,
PersonId INTEGER unique,
primary key (Id))
we can clearly see that the schema created from the model is indeed equal to the schema in the legacy database. Especially note that the PersonId foreign key in the Address table is set to be unique. Now that is a good start...!
Let's see how the creation of a new person with an associated address works (if ever). I have implemented the following unit test
[TestFixture]
public class when_adding_a_new_person_with_an_address : Person_Fixture
{
private Address address;
private Person person;
protected override void Context()
{
base.Context();
address = new Address
{
AddressLine1 = "Some Street 1",
PostalCode = "8000",
City = "Zurich"
};
person = new Person {FirstName = "Gabriel", LastName = "Schenker"};
person.AssignAddress(address);
Session.Save(person);
Session.Flush();
Session.Clear();
}
[Test]
public void smoke_test()
{
true.ShouldBeTrue();
}
}
in the context I create a new person entity and assign it a new address. Then I save the person, flush and clear the session. The smoke test just verifies that the context can be set up without throwing an exception. Let's run the test and analyze the output
and indeed this works as expected. First a person record is created and the the associated address is created.
Now let's have a look what happens when we query for this person. I add this test to the above test class
[Test]
public void should_add_person_to_database()
{
var fromDb = Session.Get<Person>(person.Id);
fromDb.ShouldNotBeNull();
fromDb.ShouldNotBeTheSameAs(person);
fromDb.LastName.ShouldEqual(person.LastName);
fromDb.FirstName.ShouldEqual(person.FirstName);
}
In the first line I load the person entity which I have previously created from database. I then assert that the entity does indeed exist and has the expected properties. The following test output is generated
The interesting part is the one outlined by the red rectangle. When I load a person two select statements are generated. One to load the person record and the second to load its associated address.
Problems with this implementation
When loading a single person entity this is no problem but it starts to be a problem when I try to load a list of persons, as I do in the following test
public void can_load_all_persons()
{
var list = Session.CreateQuery("from Person").List<Person>();
}
the output generated is
In this test I have 3 persons (each having an address) in the database. The problem is that although only one select statement is generated for the Person table there are 3 select statements for loading the corresponding addresses. We have a typical select (n+1) problem which is BAD.
A possible solution to avoid the select n+1 problem
A possible solution to this problem is shown below
[Test]
public void can_load_all_persons_revisited()
{
var list = Session.CreateQuery("select p.Id, p.LastName from Person p").List();
}
here I explicitly select the fields I want to retrieve from database. And indeed the result of the test is only one select statement
We can use this method when we need a lookup list with all or some persons in the database.
Code
The code accompanying this post can be found here.
Summary
I have shown a possible solution how one can map a one-to-one relation implied by a pre-existing legacy database with NHibernate. For the mapping I have used the Fluent NHibernate framework. There is a select (n+1) problem with this implementation. But this problem can be avoided by using customized queries.
Enjoy.
