ASP.NET Hosting

Choosing an object-relational mapping tool

My latest article is now available in English: "Choosing an object-relational mapping tool".

When developing an application, it is almost unavoidable to write data access and data manipulation code. Many object-relational mapping tools are available to help. Choosing the right one is important.

This article presents the criteria for selecting an object-relational mapping tool.

Version originale en fran├žais


  • "Mapping tools use reflection, which is slower than the compiled code produced by code generators"

    This is not always true, some use code generation ( :)) others use IL code generation. Also reflection can be slow, but you can cache the reflection results, keeping the dip low.

    Earlier on I said:

    "We're the only one supporting this. The main reason that no other o/r mapper does this is that it destroys any inner table an O/R mapper will have for uniquing, identity map and cache: you can't know which entities delete from table where x=1 will delete, it just runs on a set. The set definition itself, required for the cache maintenance, is not available."

    We support this because we have defined the habitat of the entity differently and work completely disconnected, without a context/session which holds references to objects, which frees us from the out-of-sync problem when you delete some rows from a table and you still keep entities in cache like there was no delete at all.. :) (just to clear something up. )

    I must say, long list you put up! :) What I missed a bit is the relation with how people look at data-access in the windows/.net world. It's written IMHO more from a Java pov, which doesn't really match with how the average .net/windows developer looks at data-access.

  • Frans, thanks for the long and detailed comments!

    You certainly have a deeper knownledge on the subject than me... for some reason ;-)

    I'll try tro digest everything you wrote and eventually update the article.

  • "Inheritance plays a key role in OOP, thus its support in ORM tool is actually very important. "

    True, but there is a difference between supertype/subtype hierarchies in entities and superclass/subclass hierarchies in code. The main point is that data can be easily migrated between types, objects can't. This can cause problems in code, for example in my manager-executive hierarchy.

    Inheritance support for extending entity objects to add behavior: ok, but that can be done in code without bothering the O/R mapper (IMHO).

    - I agree on the query caching argument you have, I didn't think of that.

    - about your caching explanation: if I fire a random query, it always has to access the db to know if it doesn't miss any object and you say it fetches version + ID only, am I correct? (you have to do this, for example if the app is used on 2 desktop boxes targeting the same machine). Ok, it then determines it needs to fetch 1001 objects of the 1200 to fetch, as these aren't in the cache. You then have to requery the db with an IN (...) query, with 1001 parameters, which will break on Oracle and will be pretty slow.

    The other way is to refetch simply the 1200 objects and drop the 199 already in the cache. Nevertheless, you need to fetch 2 times the data (in the RDBMS, once it sends the ID's and versions along), and update your cache as well.

    Next time you again need the 1200 objects, you fetch again teh id's and versions, match them, you see nothing is changed, so you grab the objects from the cache, correct? Ok, in that case you save a bit of data to be read, agreed.

    On the expense of having to have a version column and 1 field PK's. But you already have to have that anyway with your solution.

  • > Its a Detailed list of criteria to select a ORM Tool for .NET.

    > But Most of the Tools do not meet all of it.

    Who needs a tool that does everything plus coffee?!

    That's not the point.

  • "Be able to use inheritance, create hierarchies between entities, and use polymorphism (we are using objects!)."

    'Often you don't need this. Especially if you have a solid relational model. You want to promote a loaded manager object. How? You can't cast. Copy data? perhaps. It gets messy in that scenario.'

    I agree with the last bit and having to promote to an Executive might be a pain. But I disagree with the first bit because it depends on the rules you have implemented in the Business model. Reading a base class (Manager) and getting both Managers and Executives can be a live saver if the business demands a different calculation for the bonus these guys get.

    "Bulk updates or deletions. When we want to update or delete thousands of records at a time, it's not possible to load all the objects in memory, while this can be easily and quickly done with a SQL query (DELETE FROM Customer WHERE Balance < 0)."

    Is this really want you want? What if deleting a customer also invokes other businessrules? Obviously it is good to have a choice in the case where there are no rules.

  • On Lazy Loading: I would like to add that being able to specify that for a particular relation is even better.

    On cascade updates in combination with the next point (Bulk deletions)-> In order to implement this properly the tool needs to know the cases that are handled by the backend. Usually this does not involve setting the parent relation to some default parent, but let's assume this is required. If you have a model where A 1 contains (aggregate) n Bs and also has a unidirectional relation to 1 B then what should happen when an A is deleted? In Sql server this would result in a circular update path so enforcing constraints on both relations is not possible. Also from the model it's unclear if A.B.A == A unless you specify additional contraints.

  • Also I would really like to know how the tool providers rate there conformance to the stated 'features', is there any body who has done a comparion chart?

Comments have been disabled for this content.