How to: Work with Views and Classes Without Primary Key

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

Telerik Data Access always needs to associate its persistent types with a primary key. If such is not supplied, Telerik Data Access uses internal mechanism for creating IDs. This mechanism relies that a certain table (voa_keygen) is presented in your database. If the table is not presented, you will receive SQLException with message Invalid object name 'voa_keygen' every time you try to execute queries against your model.

Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'voa_keygen'.

Suppose you have the following domain model:

The model contains two persistent types - Category and CategorySalesFor1997. The Category entity is mapped to a database table and it provides a primary key (the CategoryID property). The CategorySalesFor1997 entity is mapped to a view. However, you cannot define primary keys for views. That's why the CategorySalesFor1997 entity is generated without primary key.

Whenever you try to execute queries against your domain model (querying either the Category entity or the CategorySalesFor1997 entity) you will receive the following exception:

Invalid object name 'voa_keygen'. Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'voa_keygen'. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeQuery

EntitiesModel dbContext = new EntitiesModel();
int catCount = dbContext.Categories.Count();

Dim dbContext As New EntitiesModel()
Dim catCount As Integer = dbContext.Categories.Count()

There are several solutions:

Setting a Primary Key Directly in the Domain Model

The most straightforward solution is to specify a primary key for the entities without primary key directly in the Visual Designer:

Open the Visual Designer.
Select the entity without primary key.
Select a property that is of type Byte, Int16, Int32, Int64, Guid, Char or String and press F4 to open the Property Window. Set the Identity property to True. Now you should be able to continue your work.

The type of the identity field must be System.Byte, System.Int16, System.Int32, System.Int64, System.Guid, System.Char or System.String.

Using the Validation Dialog

Furthermore, if your domain model contains an entity without a primary, you should see an error saying that your entity has no primary key specified in the Error List pane.

Note that validation errors are not fatal errors; a project will build successfully even when your model has validation errors.

Double-clicking on the error will open the Validation dialog. Here you could select the property you want to be a primary key.

Creating a voa_keygen Table in the Database

This approach applies only to entities that are mapped to tables !!!

The third possible solution, and the recommended one, is to create an additional voa_keygen table in your database and add a primary key column for the table without primary key. That primary key will be used internally by Telerik Data Access. In order to do that:

Right-click on an empty area in the Visual Designer and select Update Database from Model.
In the Select changes page dialog, you need to select the Create database option. The tricky moment here is that the wizard cannot add an internal primary key column and create the voa_keygen table through the Migrate Database option. The only way to do that is through the Create Database option. In case your database schema is already generated, you have to perform some additional work manually. Click Next.

In the Summary dialog, you should see a preview of the generated sql script. Do not execute it, just save it in a separate script file (using the Create script file option). The generated script should be similar to the following one. It is supposed that MyTable is the table without a primary key.

CREATE TABLE [MyTable] (
   [MyTable_id] INT NOT NULL,              -- <internal-pk>
   [NAME] NCHAR(10) NULL,                  -- _name
   CONSTRAINT [pk_MyTable] PRIMARY KEY ([MyTable_id])
)
GO

-- OpenAccessRuntime.Relational.sql.HighLowRelationalKeyGenerator
CREATE TABLE [voa_keygen] (
   [TABLE_NAME] varchar(64) NOT NULL,
   [last_used_id] INT NOT NULL,
   CONSTRAINT [pk_voa_keygen] PRIMARY KEY ([TABLE_NAME])
)
GO

Two things should be pointed out. The wizard generates a script for the creation of the voa_keygen table. The second thing is that the wizard is trying to add a primary column named MyTable_id. The problem here is that if the MyTable table already exists in the database, the execution of the script will fail. If MyTable exists in the database, you need to modify the script, so it alters the table, not creates it:

-- add column for field <internal-pk>
ALTER TABLE [MyTable] ADD [MyTable_id] INT NULL
GO
UPDATE [MyTable] SET [MyTable_id] = 0
GO
ALTER TABLE [MyTable] ALTER COLUMN [MyTable_id] INT NOT NULL
GO
ALTER TABLE [MyTable] ADD CONSTRAINT [pk_MyTable] PRIMARY KEY ([MyTable_id])
GO
-- OpenAccessRuntime.Relational.sql.HighLowRelationalKeyGenerator
CREATE TABLE [voa_keygen] (
   [TABLE_NAME] varchar(64) NOT NULL,
   [last_used_id] INT NOT NULL,
   CONSTRAINT [pk_voa_keygen] PRIMARY KEY ([TABLE_NAME])
)
GO

Execute the script. The script will modify the table without a primary key - it will add an internal primary key column. Now you should be able to continue your work.

High-Low Identity Mechanism