I have moved my active blog over to tumblr. I've maintained this blog for reference but will be posting to http://www.robustsoftware.co.uk instead. I've pointed my Feedburner feed to tumblr so if you're subscribed already you should already have switched with me.

Why LINQ to SQL is not a proper ORM

I’ve recently been involved in a new ASP.NET MVC project utilising LINQ to SQL as the ORM. I set up manual configuration of the mappings to try and avoid polluting the domain with all the rubbish LINQ to SQL likes to add to your domain. But a problem cropped up here that could not be overcome without tainting the domain objects: LINQ to SQL is unable to lazily load a one-to-many association without the use of EntitySet<T>. Ideally you would want to map this relationship to a List<T> or a similar data structure, but this wasn’t too bad so I did it and carried on.

Another problem with one-to-many mappings is that each child must have a property for the ID of the parent. Now this also went against my ideal as you should only have to have the parent object as a property of the child and reference the ID through that in an ideal world. Never mind, create a property for it that gets the ID from the parent object, mark it as obsolete to try and prevent people from using that property rather than the object and carry one.

Phew. This was starting to get a bit of a pain.

However, that was the end of the pain during the initial development. Everything was going pretty smoothly, people where getting to grips with LINQ queries. All was good.

But then scaling considerations reared their ugly head.

The problems we are trying to overcome revolve around returning a subset of the children through a mapping rather than the entire set. When you have a parent with a thousand children, retrieving them all just is not practical and indeed is wasteful. No-one in their right mind would try and display 1000 entries on a single web page.

Just return a subset via a LINQ query on the EntitySet<T> seemed like the easy way forward. Err, nope. EntitySet<T> doesn’t implement IQueryable so it actually retrieves every child and then uses LINQ to Objects to filter the set down to the ones you want. Hmm, what about changing the mapping in some way to filter the children there? Nope, that’s a no-go either. Seems the only option is to remove the property from the parent and retrieve the subset from a service via a proper LINQ to SQL query.

Did I mention it doesn’t do caching for you either?

Blergh, I want that domain knowledge integrated into my domain objects, not on the outskirts of it in a service. Perhaps I’m being too sensitive, but I think if you start down that path you’ll end up with next to nothing left in your domain, defeating the object of DDD. As far as I can see the only way forward is to change the ORM, which realistically leaves us with two choices. NHibernate and LINQ to Entities. NHibernate is more mature and fully featured, but would probably require rewriting all our queries to use the criteria method as there isn’t true support for LINQ as it stands. LINQ to Entities is from Microsoft (bonus to some), but it is pretty new and I’ve read about it polluting your domain quite badly.

Time to create a spike or two I think.