Monday, April 26, 2010

Updatable Views Vs ORM tools (ADO.NET Entity Framework)

Recently during one of our design brainstorming discussions, our database developer was having a hard time trying to understand the benefit of using a ORM tool such as NHibernate or ADO.NET Entity framework. For our example scenario, he said that he could easily create database views that would serve the same purpose - abstracting the logical schema and helping the developer avoid complex joins for simple business queries.

For the use-case we were working on, his statement made sense. But there are a lot of cases and reasons why database views would not be the best choice. Here is a snippet from a MSDN article.

An obvious question at this point would be why not just use traditional database views for this. While database views can abstract many of the mappings, often that solution won't work for several process and functional reasons: (a) many of the views are simply too complex to be generated and maintained by developers in a cost-effective way, even for some simple conceptual to logical mappings, (b) the classes of views that have the property of being automatically updatable at the store are limited, and (c) databases for core-systems in medium and large companies are used by many central and departmental applications, and having each individual application create several views in the database would pollute the database schema and create significant maintenance workload for the database administrators. In addition, database views are limited to the expressivity of the relational model, and typically lack some of the more real-world concepts of the Entity Data Model, such as inheritance and complex types.

ADO.NET client-views work entirely on the client, so each application developer can create views that adapt the data to a shape that makes sense for each particular application without affecting the actual database or other applications. The class of updatable views supported in the Entity Framework is much broader than those supported by any relational store.