Build Your Own Data Access Layer: Hydrating Entities
Posted by Davy Brion on August 25th, 2009
Note: This post is part of a series. Be sure to read the introduction here.
In the previous post of this series, you saw that some of the DatabaseActions use the EntityHydrater to umm.. hydrate the entities with their values from the database. In this post, we’ll go over how this actually works. First, i’m going to post the code of the entire class and then we’ll go over the interesting parts.
So, here’s the entire code of the EntityHydrater class:
public class EntityHydrater
{
private readonly MetaDataStore metaDataStore;
private readonly SessionLevelCache sessionLevelCache;
public EntityHydrater(MetaDataStore metaDataStore, SessionLevelCache sessionLevelCache)
{
this.metaDataStore = metaDataStore;
this.sessionLevelCache = sessionLevelCache;
}
public TEntity HydrateEntity<TEntity>(SqlCommand command)
{
IDictionary<string, object> values;
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows) return default(TEntity);
reader.Read();
values = GetValuesFromCurrentRow(reader);
}
return CreateEntityFromValues<TEntity>(values);
}
public IEnumerable<TEntity> HydrateEntities<TEntity>(SqlCommand command)
{
var rows = new List<IDictionary<string, object>>();
var entities = new List<TEntity>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
rows.Add(GetValuesFromCurrentRow(reader));
}
}
foreach (var row in rows)
{
entities.Add(CreateEntityFromValues<TEntity>(row));
}
return entities;
}
private IDictionary<string, object> GetValuesFromCurrentRow(SqlDataReader dataReader)
{
var values = new Dictionary<string, object>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
values.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
return values;
}
private TEntity CreateEntityFromValues<TEntity>(IDictionary<string, object> values)
{
var tableInfo = metaDataStore.GetTableInfoFor<TEntity>();
var cachedEntity = sessionLevelCache.TryToFind(typeof(TEntity), values[tableInfo.PrimaryKey.Name]);
if (cachedEntity != null) return (TEntity)cachedEntity;
var entity = Activator.CreateInstance<TEntity>();
Hydrate(tableInfo, entity, values);
sessionLevelCache.Store(typeof(TEntity), values[tableInfo.PrimaryKey.Name], entity);
return entity;
}
private void Hydrate<TEntity>(TableInfo tableInfo, TEntity entity, IDictionary<string, object> values)
{
tableInfo.PrimaryKey.PropertyInfo.SetValue(entity, values[tableInfo.PrimaryKey.Name], null);
SetRegularColumns(tableInfo, entity, values);
SetReferenceProperties(tableInfo, entity, values);
}
private void SetRegularColumns<TEntity>(TableInfo tableInfo, TEntity entity, IDictionary<string, object> values)
{
foreach (var columnInfo in tableInfo.Columns)
{
if (columnInfo.PropertyInfo.CanWrite)
{
object value = values[columnInfo.Name];
if (value is DBNull) value = null;
columnInfo.PropertyInfo.SetValue(entity, value, null);
}
}
}
private void SetReferenceProperties<TEntity>(TableInfo tableInfo, TEntity entity, IDictionary<string, object> values)
{
foreach (var referenceInfo in tableInfo.References)
{
if (referenceInfo.PropertyInfo.CanWrite)
{
object foreignKeyValue = values[referenceInfo.Name];
if (foreignKeyValue is DBNull)
{
referenceInfo.PropertyInfo.SetValue(entity, null, null);
}
else
{
var referencedEntity = sessionLevelCache.TryToFind(referenceInfo.ReferenceType, foreignKeyValue) ??
CreateProxy(tableInfo, referenceInfo, foreignKeyValue);
referenceInfo.PropertyInfo.SetValue(entity, referencedEntity, null);
}
}
}
}
private object CreateProxy(TableInfo tableInfo, ReferenceInfo referenceInfo, object foreignKeyValue)
{
// NOTE: this will be covered in a later post, so i took out the spoiler code 
return null;
}
}
As you can see, there are 2 public methods: HydrateEntity and HydrateEntities. They are both pretty similar, except that the former only hydrates a single entity and the latter a list of entities. They both retrieve the values from the current position in a DataReader and store them in a Dictionary with the name of the column being the key and the value of the column being the value in the dictionary:
private IDictionary<string, object> GetValuesFromCurrentRow(SqlDataReader dataReader)
{
var values = new Dictionary<string, object>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
values.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
return values;
}
This dictionary is then passed to the CreateEntityFromValues method:
private TEntity CreateEntityFromValues<TEntity>(IDictionary<string, object> values)
{
var tableInfo = metaDataStore.GetTableInfoFor<TEntity>();
var cachedEntity = sessionLevelCache.TryToFind(typeof(TEntity), values[tableInfo.PrimaryKey.Name]);
if (cachedEntity != null) return (TEntity)cachedEntity;
var entity = Activator.CreateInstance<TEntity>();
Hydrate(tableInfo, entity, values);
sessionLevelCache.Store(typeof(TEntity), values[tableInfo.PrimaryKey.Name], entity);
return entity;
}
This method will first check to see if an entity instance for the row in the values dictionary already exists. If it exists already, it simply returns the entity instance and ignores the values in the dictionary. I’m not sure yet whether this behavior is correct or not. Theoretically speaking, it’s possible when using ReadCommitted isolation level that the values in the dictionary will be more recent than the entity instance in the session level cache. However, since this simple DAL has no change tracking, i also can’t deduce whether the instance in the cache has already had one or more of its properties updated by application code. Simply overwriting them with newly retrieved values of the database doesn’t seem like the right thing to do here. So the current options are to either ignore this possibility, or to rely on optimistic concurrency. Oh, and i don’t have support for optimistic concurrency either. A good DAL should however provide some optimistic concurrency strategies here, or be able to track the changes in the entity and throw an exception if it notices that the database contains more recent values AND the local entity has already been modified. If it hasn’t been modified yet, it could overwrite the values of the instances though i’m not sure everyone would agree with this behavior. Either way, this particular problem is definitely interesting enough to think about for a while 
Anyways, in this implementation, i’m completely ignoring this situation and i either return the already loaded entity of the session level cache, or i hydrate a new entity instance and return that instead. In the latter case, i also store the entity instance in the session level cache. Note that the actual implementation of the session level cache will be covered in the next post of this series.
The actual Hydrate method looks like this:
private void Hydrate<TEntity>(TableInfo tableInfo, TEntity entity, IDictionary<string, object> values)
{
tableInfo.PrimaryKey.PropertyInfo.SetValue(entity, values[tableInfo.PrimaryKey.Name], null);
SetRegularColumns(tableInfo, entity, values);
SetReferenceProperties(tableInfo, entity, values);
}
First the primary key value of the record is set in the primary key property of the new instance, and then we proceed with putting the regular column values in their properties, and after that, the reference properties. Filling the regular column properties is very straightforward:
private void SetRegularColumns<TEntity>(TableInfo tableInfo, TEntity entity, IDictionary<string, object> values)
{
foreach (var columnInfo in tableInfo.Columns)
{
if (columnInfo.PropertyInfo.CanWrite)
{
object value = values[columnInfo.Name];
if (value is DBNull) value = null;
columnInfo.PropertyInfo.SetValue(entity, value, null);
}
}
}
Dealing with the references is a bit more interesting though.
private void SetReferenceProperties<TEntity>(TableInfo tableInfo, TEntity entity, IDictionary<string, object> values)
{
foreach (var referenceInfo in tableInfo.References)
{
if (referenceInfo.PropertyInfo.CanWrite)
{
object foreignKeyValue = values[referenceInfo.Name];
if (foreignKeyValue is DBNull)
{
referenceInfo.PropertyInfo.SetValue(entity, null, null);
}
else
{
var referencedEntity = sessionLevelCache.TryToFind(referenceInfo.ReferenceType, foreignKeyValue) ??
CreateProxy(tableInfo, referenceInfo, foreignKeyValue);
referenceInfo.PropertyInfo.SetValue(entity, referencedEntity, null);
}
}
}
}
If we can’t find the referenced entity instance in the first level cache, what should we do? We obviously can’t load it automatically because that could in turn cause referenced entities’ references to be loaded automatically when they are hydrated. Which in turn could cause their referenced entities… Well, i’m sure you get the point. But those properties obviously can’t be set to a null reference either because the column actually does have a valid foreign key value in the database. Explicitly loading referenced properties leads to seriously ugly (and error-prone) code so that’s not an option i’m willing to consider either. The correct way to deal with this is to use lazy loading. To do that in an automated fashion, we need proxy classes. I’m not going to get into these proxy classes and the whole lazy loading thing just yet, since that will be covered in depth in a future post
So that’s pretty much it (for now) for our EntityHydrater class. As you can see, it’s still relatively simple but then again, the use cases that it supports are extremely simple as well. This current implementation is incapable of hydrating entities based on a SQL statement that selects data from more than just the entity’s table. And that is a pretty big shortcoming. For instance, with NHibernate you can execute queries where you can instruct NHibernate to fetch some (or all) of the entity’s references (associations in NHibernate) with just one SQL statement, using the join syntax. NHibernate can then hydrate the root entity, and populate its reference properties with the other values that were returned by the sql statement. While it wouldn’t be that complex to add this capability to this EntityHydrater class, it wouldn’t exactly be completely trivial either. Again, this is a limitation that many (maybe even most?) custom DAL’s have. This one probably makes it easy enough to still add this feature though
August 25th, 2009 at 10:54 pm
what value does session level cache provide is this scenario? you are not checking the cache until after the record is loaded from the database, so you’re not gaining preformance be decreasing trips to the database. Would you want to check the session cache before constructing/executing the sql statement to reduce trips to the database?
August 25th, 2009 at 11:14 pm
@Jason
in the previous post (http://davybrion.com/blog/2009/08/build-your-own-data-access-layer-out-of-the-box-crud-functionality/) i mentioned the following about the GetByIdAction:
“This will first check the session level cache to see if this instance has already been retrieved in the current session (i’ll discuss the session in a later post) and if so, it will return that instance. If it’s not in the cache, it will create a SqlCommand and fill its CommandText property with a SQL string that is provided by the relevant TableInfo class. After that, it passes the SqlCommand to the EntityHydrater so it can return an actual entity instance.”
As for checking the cache after the records have been retrieved, that is explained in the next post, which will be published tomorrow
August 25th, 2009 at 11:49 pm
[...] This is late but Davy Brion has started a series on Build Your Own Data Access Layer Series. Some posts include Out of the Box CRUD Functionality, Mapping Classes to Tables, and Hydrating Entities. [...]
August 26th, 2009 at 12:51 am
NHibernate light
August 26th, 2009 at 3:42 am
Ok, that makes more sense
August 26th, 2009 at 8:37 am
I’m curious as to why in the HydrateEntities() method, you load up a List<IDictionary>() with data from the reader first, and then create/load each entity.
My first guess is to shorten the time you have an open connection to the database.
August 26th, 2009 at 8:49 am
yeah, it shortens the time needed to keep the datareaders open, and i used a dictionary instead of an array because i didn’t want to deal with column positions and because it would be easy to retrieve specific values based on the column names (which i already have in the meta data model)
August 26th, 2009 at 8:56 am
AFAIK, “hydration” is frequently referenced as “materialization”: http://www.google.com/search?q=ORM+materialization
August 26th, 2009 at 9:02 am
And about CreateProxy(tableInfo, referenceInfo, foreignKeyValue): you can do this only if you exactly know the type mapped to referenceInfo, or this information is contained in foreignKeyValue. If you don’t know the type exactly (e.g. there is an inheritance hierarchy), you can’t do this.
August 26th, 2009 at 9:05 am
@Alex
read the other posts… this isn’t about building a full blown ORM and i don’t support the inheritance hierarchies which would cause problems with lazy loading
August 26th, 2009 at 11:15 pm
I have an interesting question, why not generate strong datasets from your table info metadata and use those instead of DataReaders. I think you can turn tracking off on your datasets and they will more or less function exactly as your dictionary does currently (with slightly added overhead but dramatically reduced complexity in your code).
August 26th, 2009 at 11:36 pm
i really dislike datasets, even the strong-typed ones… they really wouldn’t save that much code actually, and the overhead of using them as well as the time increase at application startup is just not worth it IMO
August 28th, 2009 at 8:48 pm
Fair enough, just curious. I wasn’t aware of an app startup performance hit. Could you provide some links to information regarding it. Or do you mean looking at the metadata and creating the dataset as a performance hit?
August 29th, 2009 at 12:01 pm
@Michael
the last one… i have to build the metadata model when the application starts up, and generating the datasets at that point can be a bit too expensive at that time when you have many tables. And considering the fact that the datasets don’t really offer any other real benefits, i don’t really see the point in doing that.
September 1st, 2009 at 10:51 am
Having used a similar approach myself a while ago, I ran into an issue converting integers to nullable enums. It looks like it might affect your code as well.
(Mine reflects “on the fly”, doesn’t have an identity map (as my scenario didn’t require one), and is generally more of a hack than yours. Still, it worked well enough.)