Reverse Mapping and Using Database Views with Telerik OpenAccess ORM

We are glad to announce that one of the major improvements for our 2009 Q2 release is the support for database views.

Views are stored queries accessed as virtual tables which represent the result of a query. To provide higher security, they can encapsulate the data into smaller result sets accessible from particular roles. Views are also used to combine data from many tables and allow the developer to expose the table data to the user in a more natural manner. Performance optimizations for fast searching and reading can be accomplished as well when using indexed views.

Now with Telerik OpenAccess ORM you can reverse map views to classes the same way you do with regular tables. And that is not all, the data exposed by the views can be even modified and stored back to the database.

This post is to guide you through the initial steps of mapping database views. We assume that your project is already “Enabled” to use Telerik OpenAccess ORM and the database connection is properly configured. If you are trying our product for the first time, this step-by-step how-to will help you to understand the process.

You can also use the already mapped Northwind model from our Code library. In order to run it, please remove and add again the Telerik.OpenAccess and Telerik.OpenAccess.Query references. Then run the Reverse mapping wizard and click the “Merge” button. This will add the information necessary to map the views available in the database.

We are now going to map the “Alphabetical list of products” view from the Northwind database. It contains columns from two related tables – Products and Categories.

Step 1: Open the Reverse Mapping wizard

The Simple View tab shows all available tables and views from the database along with some basic options needed to create the code. Each table/view can be mapped to a Class, Collection or Map. Usually collections and maps are used for mapping join tables. When mapping views, choose the Class option. Here you can also specify custom class and namespace names.

Step 2: Switch to Advanced View.

The Advanced View tab provides options that are not available in the Simple View but are necessary to map successfully a view. Select the Views node from the Treeview. A grid similar to the one from the previous step shows up. Now you have to choose which views to be mapped. To do this, enable the relevant “Generate” checkbox next to each. You may notice that the treeview icons have been changed for the views which are marked to be mapped.

Step 3: This step is specific for mapping views. They do not provide information about identity columns as the regular tables do. However, all persistent classes generated by Telerik OpenAccess ORM require an identity field. In this context, we have to manually set this identity for each class. Expand the view name in the treeview and select the id field (in our case “ProductID”). Then enable the “Primary Key” checkbox on the right. After setting an identity, the red warning about missing primary key field should disappear from the code preview window. Note that views without primary key columns are not currently supported. The uniqueness of each row is required for write operations and proper usage.

NOTE: It is user’s responsibility to set a real primary key – in the case where no real identity column is included in the view, you should use a composite key developed by including multiple columns from the available, trying to produce unique combination – usually meaning that all of the columns should be marked as such.

Step 4: When you have chosen identity fields for all classes, they are ready to be generated. Click the “Generate & Save Config” button.

After the classes are created you can use them to retrieve and modify the data as you have used to do with classes mapped to tables. To illustrate the basic read and update operations, we will use the already mapped “Alphabetical list of products” view. Currently create and delete operations are not supported for views containing columns from more than one table.

Both OQL and Linq queries can be used to fetch data:

Linq:

AlphabeticalListOfProduct product = (from x in scope.Extent<AlphabeticalListOfProduct>()
                                                            where x.ProductID == 1
                                                            select x).First();

OQL:

string queryStr = "SELECT * FROM AlphabeticalListOfProductExtent as a WHERE a.ProductID == 1";
AlphabeticalListOfProduct product = scope.GetOqlQuery<AlphabeticalListOfProduct>(queryStr).ExecuteEnumerable().First();

 

Updating data is easy as usual. Even the referenced object can be changed, just make sure changes are made within an active transaction:

scope.Transaction.Begin();
product.ProductName = "Cheese";
product.CategoryID = 4;
scope.Transaction.Commit();

We believe the ability to reverse map and work with views is crucial for many people and will be useful for the rest of them as well. We are eager to find out what you think of this feature and to have your feedback.