Top 10 Must Have Features in O/R Mapping Tools

Feature 1: Flexible Object Mapping

Everything in O/R mapping starts with mapping your objects to your relational tables. Here are some specific features in this area that you should know:

1. Tables and views mapping: The tool should let you map objects to both tables and views in your relational database. Mapping to views is important because many real-life applications prefer to use views instead of tables.
2. Multi-table mapping: The tool should let you map an object not only to a single table but also to multiple tables and specify a join between these tables. If your application needs to fetch a list of rows that span multiple tables (a common occurrence in Web applications), you will need this feature.
3. Naming convention: The tool should let you use a different naming convention in objects and their attributes than in relational databases. If you name your database "t_employees," you may need to name your object "Employee."
4. Attribute mapping: There are a number of features that the tool should support:
   
   1. Primary key: Your object must distinguish the primary key from other columns. It should also let you use a single-column or multi-column primary key.
   2. Auto generated columns: Some columns are auto generated (IDENTITY or SEQUENCE) and your object must have code to handle fetching the generated values after an insert.
   3. Read-only columns: Some columns are not meant to be set by the client but instead their values are system generated (e.g. the creation_dtime column using getDate() function in SQL Server). Your object must have appropriate code to fetch these system-generated values.
   4. Required columns: Your object must do data validation for required columns at the time of insert or update operations. This is much more efficient than wasting a trip to the database just to get an error message back.
   5. Validation: In most cases, you have defined various constraints on your database columns. It would be nice to have the same validations done in your persistent objects so you can save an unnecessary trip to the database just to receive an error message.
   6. Formula Fields: There are many situations where when you fetch data from the database, you use a regular expression rather than a column (e.g. Annual Salary object attribute might be a formula field monthly_salary * 12).
   7. Data type mapping: Sometimes, you want to map one data type from the database to another data type in your object. For example, converting a datetime type into a string. Your object must have the logic to do this automatically in both directions (read and write).

Feature 2: Use Your Existing Domain Objects

As you saw, a popular design pattern separates persistent objects into "domain" and "factory" objects. One important O/R mapping feature is to let you decide whether you want to generate both domain and factory objects or use your existing domain objects and only generate factory objects that know about your domain objects.

Some people do not want to generate "domain" objects preferring instead to develop them by hand and only generate the "factory" objects. The reason behind this is that their domain objects are being used in almost all subsystems of their application and therefore they do not want them changing frequently through subsequent code regenerations. But, they do not mind generating the "factory" objects since their use is localized to a few places (for load and save operations).

Therefore, the O/R mapping tool should let you use your existing domain objects and map, and generate only the factory objects. It should use .NET Reflection to read your domain object definition and after you have done the mapping, it should generate the factory objects in such a way that these factory objects use your domain objects to hold all the data.

Feature 3: Transactional Operations (CRUD)

A database transaction allows you to group multiple operations as one atomic operation so that either all operations succeed or none succeeds. Transactional operations include create, read, update, and delete (also called insert, update, load, and delete). Each transaction operation is performed only on one row of data in a table.

You will be working in one of two main transactional environments and your O/R mapping tools needs to know both of them so it can generate code accordingly. The options are:

1. COM+/MTS: Microsoft Transaction Server (MTS) manages all transactions of an application. Your objects do not start, commit, or rollback a transaction. They only return success or failure from their methods and MTS figures out when to do "BeginTrans," "Commit," or "Rollback." Additionally, all your factory objects are stateless so MTS can do object pooling on them. This is a specific design pattern that your O/R mapping tool must understand t0 generate persistent objects that comply with it. Most common applications for this environment are ASP.NET applications and .NET Web Services.
2. Stand-alone: This is the environment where your application manages all the transactions itself. It needs to know where to go "BeginTrans," "Commit," and "Rollback." And, your O/R mapping tool needs to be aware of this environment and generate code to comply with it. Most common situations for this are Windows forms based client/server applications that directly talk to the database server.