SubSonic3's SimpleRepository is a wonder to behold. It's simple, clean, effective, and represents a huge step forward in abstracting away the DAL and allowing developers to focus on what matters.
I've been using it in development for a while now, and I have found that it comes up short when dealing with foreign keys. For one thing, foreign relationships are not persisted to the database, so data integrity is not 100% guaranteed for less-than-meticulous programmers.
What is more crucial, however, and the subject of this post, is the difficulty in loading up foreign objects from their respective keys.
Consider a simple case of a Car and a Wheel:
public class Car
{
public int Id { get; set; }
}
public class Wheel
{
public int Id { get; set; }
public int CarId { get; set; }
}
In code, whenever you have an instance of a Wheel and want to reference the Car it belongs to, you have to do something like this:
// wheel is an instance of Wheel
SimpleRepository repo = new SimpleRepository("connection-string");
Car c = repo.Single<Car>(wheel.CarId);
Pretty simple! We can abstract away the need to always supply a connection string to the SimpleRepository by setting up a static method.
public class Repository
{
public static SimpleRepository GetRepository()
{
return new SimpleRepository("connection-string");
}
}
We can even abstract out the primary key for our models, knowing all our models will have a unique integer primary key.
public class Record
{
public int Id { get; set; }
}
public class Car : Record
{
}
public class Wheel : Record
{
public int CarId { get; set; }
}
That aside, let's get back to foreign object loading, and look at the reverse process. Given a Car, retrieve its Wheels.
// car is an instance of Car
SimpleRepository repo = Repository.GetRepository();
List<Wheel> wheels = repo.Find<Wheel>(w => w.CarId == car.Id).ToList();
Again, fairly simple.
So, what's the problem?
Well, there are two problems actually. The first is that there are performance issues. Consider a common case where the Car instance is passed around to a few methods. If any of those methods (or methods that they call, etc.) have to access the Wheels, they will have to make separate round-trips to the database. Ideally, once the Wheels have been loaded once, they will be cached with the Car instance. The second problem is that of code duplication. If the model changes, the expressions that describe the foreign key relationships will have to be updated everywhere.
So how do we fix it?
Ideally, we would use properties on the models to reflect the foreign key relationships. Something like:
public class Car : Record
{
public List<Wheel> Wheels { get; }
}
public class Wheel : Record
{
public int CarId { get; set; }
public Car Car { get; set; }
}
So that's what we will do :) By abstracting away the details of the foreign key lookups and caching the foreign key objects, we can write the process once and reuse it in every single one of our models. This is what the real-world implementation will look like:
public class Car : Record
{
public List<Wheel> Wheels
{
get { return GetForeignList<Wheel>(w => w.CarId == Id); }
}
}
public class Wheel : Record
{
public int CarId { get; set; }
public Car Car
{
get { return GetForeign<Car>(CarId); }
set { CarId = SetForeign(value); }
}
}
The GetForeign<T>, SetForeign<T>, and GetForeignList<T> methods are implemented as protected methods in the Record base class we built earlier. All the complexity is wrapped into these methods, including an in-memory cache, so the models can just be... models.
public class Record
{
public int Id { get; set; }
private Hashtable ForeignCache = new Hashtable();
protected T GetForeign<T>(int key) where T : Record, new()>
{
string relation = typeof(T).Name;
T foreign = ForeignCache[relation] as T;
if (foreign == null || foreign.Id != key)
{
foreign = Repository.Get<T>(key);
ForeignCache[relation] = foreign;
}
return foreign;
}
protected int SetForeign<T>(T foreign) where T : Record, new()
{
string relation = typeof(T).Name;
ForeignCache[relation] = foreign;
return (foreign == null) ? 0 : foreign.Id;
}
protected List<T> GetForeignList<T>(Expression<Func<T, bool>> expression) where T : Record, new()
{
return GetForeignList<T>(expression, false);
}
protected List<T> GetForeignList<T>(Expression<Func<T, bool>> expression, bool refresh) where T : Record, new()
{
string relation = "l-" + typeof(T).Name;
List<T> foreign = ForeignCache[relation] as List<T>;
if (foreign == null || refresh)
{
foreign = Repository.GetRepository().Find<T>(expression).ToList();
ForeignCache[relation] = foreign;
}
return foreign;
}
}