Top 10 Must-Have Features in O/R Mapping Tools

O/R mapping tools are becoming more popular each day and people are realizing the productivity gain they provide to developers. Yet, many people don’t know enough about O/R mapping to consider using these tools and many others are weary of using any code generators (including O/R mapping tools).

In this article, I will try to educate you about the various important features that a good O/R mapping tool would provide you and how it can be beneficial to you. I am not discussing any particular O/R mapping tool, but rather all tools in general.

What Is O/R Mapping?

If you’re developing an object-oriented application that requires a relational database, you’ll need to develop persistence objects that are modeled against your database design and know how to interact with different tables in the database. You can either develop these objects by hand or use an O/R mapping tool to map and generate them quickly. Hopefully, after reading this article, you’ll be convinced that developing by hand is a bad idea in most situations.

An O/R mapping tool connects to your database and reads its schema; then lets you map persistence objects to database tables and views, specify single-row transactional operations, queries, and stored procedure calls as methods to these objects. And, it lets you define one-to-one, one-to-many, many-to-one, and many-to-many relationships between objects based on relationships in the database. It then generates fully working persistence objects code for you. Below is a simple example of some persistence objects. Please note that in this persistence design pattern as explained in Domain Objects Persistence Pattern for .NET, the persistence objects are broken up into “Domain Objects” and “Factory Objects.” You can read more about this design pattern if you want.

// Note one-to-many self relationship thru "reports_to"
CREATE TABLE t_employees (
employee_id int IDENTITY (1, 1) NOT NULL,
name nvarchar (40) NOT NULL,
               birth_date datetime NULL,
               photo image NULL,
               reports_to int NULL,
// Domain object class for t_employees table

public class Employee
   Int32        _employeeId;
   String       _name;
   DateTime     _birthDate;
   Byte[]       _photo;
   Int32        _reportsTo;
   ArrayList    _subordinates;
   Employee     _supervisor;
   Int32 EmployeeId {
      get { return _employeeId;} set { _employeeId = value; }
   String Name { get { return _name; } set { _name = value; }
   DateTime BirthDate {
      get { return _birthDate; } set { _birthDate = value; }
   Byte[] Photo { get { return _photo; } set { _photo = value; }
   Int32 ReportsTo {
      get { return _reportsTo; } set { _reportsTo = value; }
   ArrayList Subordinates {
      get { return _subordinates; } set { _subordinates = value; }
   Employee Supervisor {
      get { return _employee; } set { _employee = value; }

// Persistence object for Employee class
public interface IEmployeeFactory
   void Load(Employee object, int nDepth);
   void Insert(Employee object);
   void Update(Employee object);
   void Delete(Employee object);
  // Query methods
ArrayList FindSomeEmployees(); <![endif]>
   // Relationship methods
void LoadSupervisor ( Employee emp);
void LoadSubordinates(Employee emp, int nDepth);

Below is an example of how a client application will use this code:

public class NorthwindApp
   static void Main (string[] args) {
      Employee emp = new Employee();
      EmployeeFactory empFactory = new EmployeeFactory();

      // Let's load an employee from the Northwind database.
      emp.EmployeeId = 10045;

      // empList is a collection of Employee objects
      ArrayList empList = empFactory.FindSomeEmployees();
      // subList is a collection of Employee's subordinates objects
      ArrayList subList = empFactory.LoadSubordinates(emp, 1);

      // supervisor is Employee's supervisor object
      Employee supervisor = empFactory.LoadSupervisor(emp);

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:

Feature 1: Flexible Object Mapping

  1. Tables & 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 that you 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 list of rows that span multiple tables (a common occurrence in Web applications), you’ll 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 your database table is named t_employees, your object may need to be named Employee.
  4. Attribute mapping: There are a number of features that the tool should support:
    • Primary key: Your object must distinguish the primary key from other columns. It also should let you use a single-column or multi-column primary key.
    • 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.
    • Read-only columns: Some columns are not meant to be set by the client but instead their values are system generated (for example, creation_dtime column using the getDate() function in SQL Server). Your object must have appropriate code to fetch these system-generated values.
    • 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.
    • 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 persistence objects so you can save an unnecessary trip to the database just to receive an error message.
    • Formula Fields: There are many situations where, when you fetch data from the database, you use a regular expression rather than a column (for example, Annual Salary object attribute might be a formula field monthly_salary * 12).
    • Data type mapping: Sometimes, you want to map one data type from the database to another data type in your object. For example, a datetime type might be converted 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 persistence 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 and instead 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; therefore, they don’t want them changing frequently through subsequent code regenerations. But, they don’t mind generating the “factory” objects because 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 either all operations succeed or none of them succeed. 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’ll be working in one of two main transactional environments and your O/R mapping tools need 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 and generate your persistence objects to 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.

More by Author

Must Read