Look-up tables with Linq-to-Sql

Question

Hi all,

I'm working on a car dealer website at the moment, and I've been working on a 'Vehicle' model in my database. I've been making extensive use of lookup tables with FK relationships for things like Colour, Make, Model etc.

So a basic version could be something like:

Vehicle {
    Id
    MakeId
    ModelId
    ColourId
    Price
    Year
    Odometer
}

Which then uses a simple 2-column look-up table, for example Colour would have a ColourId column, and ColourText column; nothing unusual.

This is all good and well, however I've found my generated Linq-to-Sql classes become more complex when you start using look-up tables. To get the colour I now have to do things like Vehicle.Colour.ColourText. Adding new vehicles requires looking up all the various tables to ensure the values are there, etc. So I don't really want to be passing this Linq-to-Sql model around the rest of my application code.

So my current approach implements a couple of methods to convert this linq model into a pure domain model, which is nearly an identical object, but just replaces the Id fields with their actual textual values (strings). This is all wrapped up in a repository, so the rest of the app is only aware of these straight 'domain' objects, and not the data access objects. If the app needs to insert a new record, I pass this domain model back in to the repository, which then converts it back to the Linq-to-Sql variant, ensuring all the lookup values are in fact valid.

Is this a decent idea? I feel a little dirty doing this conversion, it seems to go against one of the reasons for using Linq-to-Sql in the first place. Then again, I guess it would be even worse passing around objects exposing look-ups and the like to the rest of the app. Is this why more fully-fledged O/RMs are more widely used?

Using the domain objects over the L2S ones also makes it easier for JSON serialisation for use with AJAX and the like too.

I guess I'm just asking if the approach I've taken is reasonable? Cheers.

Answer 1

What you have done is created low level objects from LINQ and then built your own Business Objects (or View Model) on top of them.

There is nothing wrong with this: in fact, it can help isolate the application from the relational model and bring it more fully into the Object realm. You see this done explicitly when people build a ViewModel to bind to the UI, where the ViewModel actually loads and saves through the low level entities.

The downside is more coding. The upside is that your object collection actually reflects your application use-cases better. I recommend continuing to explore this avenue. Perhaps a look here help you along: http://blogs.msdn.com/dphill/archive/2009/01/31/the-viewmodel-pattern.aspx