The Pragmatic DAL Architecture: Object Relational Mappers
— With the many ORM options available today, developers can choose between these or even create their own ORM to convert data objects.
Co-Authored by Bo Griffin
There are many Object Relational Mapper (ORM) options available on the market today. First, there are Full ORM options that offer rich feature sets and enable developers to get projects up quickly. Then there are Micro-ORM options that offer minimalism, simplicity, and performance. Developers can choose between these or even create their own ORM to convert data objects.
A Full-ORM is an object relational mapper with a rich feature set. Its primary goals are to minimize redundant work and provide the tools for all your data layer needs. Full-ORMs such as Entity Framework and NHibernate have many features.
Full-ORMs have tools to help get a project up and running quickly. If you like to work in a visual way, they include a Model Designer to build your models and their relationships. Once you have a model, you can generate your database and supporting code (Model-First). If you already have the database, you can use reverse-engineering tools that will generate code and data-models (Database-First). If you prefer to work with code, you can decorate your classes with attributes then use forward-engineering tools to generate your database tables (Code-First). Each strategy offers a POCO compatible solution. Proper tooling can accelerate a project significantly.
Hybrid approaches also work. You can design your databases first, and then generate your models, mappers, and context. Entity Framework’s Database-First approach is frequently used. This is fine for small databases. Unfortunately, it quickly becomes difficult with medium to large teams and projects. The designers have trouble handling 100+ tables while maintaining usability. Database-First is stored in an EDMX file. The EDMX file contains multiple concerns. When changes occur, the file is re-generated. A single change in the designer can update many sections and potentially re-arrange items in those sections. This makes source-control difficult and changes easily get lost. Code-First, on the other hand, aligns well with source control and larger projects.
Feature rich software libraries minimize the work for common scenarios. CRUD operations often do not require custom code. LINQ providers generate the SQL for you. Data-Models can have properties that match table relationships. These ‘navigational properties’ can be loaded automatically. This behavior enables an application to traverse a database easily.
Change Tracking handles the lifecycle of data from the database to the application and back. First, the original model is loaded from the database. The Change Manager creates a copy of the object before passing it along. The Unit of Work is responsible for collecting any pending changes. The application proceeds to make changes to the model. Then the Unit of Work uses the Change Manager to detect what changes have occurred. The SQL Generator uses the Unit of Work as a source of changes to produce SQL. The ORM gives the Unit of Work to the SQL Generator to produce SQL. Finally, the database executes the SQL, committing all changes.
Once you have changes to commit, you need to ensure that updates from multiple users are coordinated. Concurrency control support varies. Most vendors support at least ‘None’ and Optimistic, only a few support Pessimistic. Integration of concurrency control is usually as simple as adding an extra column and configuring the model. The SQL generator takes care of the rest. Code and SQL generation is a powerful thing. However, the default output may not align well with what the project requires. This is where templates come in. Full-ORMs include template-based code and SQL generators. The developer can customize the templates to the needs of the project.
The Object Mapper’s default mapping is to match each column to each property by name. This is good when you have full control or the naming conventions align. When there is a mismatch, the developer can provide an alternative mapper or add annotations. This will allow the application to work with models that are closer to an ideal representation instead of being bound to the definitions of the database tables.
Extensibility can allow a developer to extend or change the behavior of a component. Full-ORMs have multiple points where customization can occur. This can allow adding custom caching, logging, request routing, or even support for new database engines.
While there are many benefits, there are also some downfalls to a Full-ORM being used. Full-ORMs are often heavy weight and performance is not a primary goal. Startup time and query performance can often be less than ideal. To support all the capabilities that a Full-ORM has, they tend to be configuration heavy. Luckily, most of the modern frameworks support multiple ways to configure with either external files or fluent code.
LINQ is a robust tool for navigating database model. The Full-ORM will use a LINQ provider that converts the queries into SQL. Complex queries often result in slow SQL. The developer may need to review queries to see if they are a good fit for SQL generation.
A batch operation is one in which multiple changes need to be carried out. Full-ORMs with default change-tracking behavior often make one database call per operation. If the number of operations is small then the impact is marginal. However, as the number of operations increase, performance can degrade quickly. The developer may need to check the Full-ORM’s documentation for features that align well with bulk operations to get the performance desired.
A Micro-ORM is an object relation mapper with a limited feature set. Primary goals are simplicity, minimalism, and performance. Examples include Dapper and PetaPoco. They may do less, but they do it well.
Micro-ORMs are lightweight and to the point. Most are thin wrappers over ADO.NET code. To keep the code fast and light they have to keep abstractions and complexity to a minimum. Adding a Micro-ORM can be as simple as including a single code file to the project. Although we still recommend to use NuGet, to get the latest stable version easily. To promote simplicity, configuration (if any) is light. Dapper, for instance, does not have any configuration; it works with IDbConnection instances. Where the connection comes from is a caller concern. This small change allows it to use any database provider. One of the benefits of dealing with lower-level access is full control. If something is not performing the way you want, you can tweak it with minimal fuss. The developer controls how to group bulk database changes. Proper processing of bulk changes can increase database performance dramatically.
Micro-ORMs are fast and light, so what is the catch? For the most part, many of the more advanced features are not included. Most of the Micro-ORMs are open-source and supported by the community. The community contributes packages to help provide some additional features.
There are reasons to use Full-ORMs and Micro-ORMs and which one is best depends upon your particular project. In our next article, we will discuss the different DAL patterns and showcase a decision matrix that will help select the best pattern for your project.