Automatic Foreign Objects in SubSonic3 SimpleRepository

by anton.venema 30. December 2009 21:38

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;
    }
}

Tags:

Comments

1/28/2010 10:13:09 PM #

Adam

Anton, this is great. Would you be up for helping push this functionality into the core of SubSonic?

Adam United Kingdom | Reply

1/28/2010 10:20:06 PM #

jerod.venema

Hey Adam!

Anton's out for the week (he'll be back on Monday), but in the meantime, I feel safe saying if there's a good way this can be nicely integrated into SubSonic, we'd be all for it. We'll check into github, see if there's a way to at least throw this in as a possibility.

jerod.venema United States | Reply

1/30/2010 3:16:56 AM #

Adam

Thanks jerod/anton(in advance), we're looking at adding this feature into SubSonic 3.1. Our planning is happening at http://groups.google.com/group/subsonicproject all input welcome :)

Adam United Kingdom | Reply

2/13/2010 6:44:41 AM #

AdamW

Hi,

How would you go about inserting an Owner, with related Car, with related Wheels into the DB?

I have tried several combinations with no success yet.

Thanks, Adam

AdamW Australia | Reply

2/13/2010 10:01:51 AM #

anton.venema

@Adam, you still have to save related objects to the database individually as you make changes to them.  What this structure allows is the rapid traversal of foreign object relations by abstracting away the retrieval of related objects from the database.

anton.venema United States | Reply

10/22/2013 11:45:06 PM #

pingback

Pingback from ask.techwikihow.com

sqlite – SubSonic 3.0 Migrations Vs. SimpleRepository Auto-Migrations: Foreign keys support? | Ask TechwikiHow

ask.techwikihow.com | Reply

Add comment




  Country flag


  • Comment
  • Preview
Loading