Handling Model Changes

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.

Update Database from Model Wizard allows you to generate schema migration script for migrating a database to the latest model state. By using the wizard, you could generate either DDL for creating a database or DDL for migrating (updating) an existing database. This topic is focused on the second scenario, i.e. when you want to migrate your database to the latest model state.

Adding New Entities (Domain Classes)

Adding new entities and migrating your database is a straightforward task. Update Database from Model Wizard will detect the new domain class and generate DDL script for the table creation.

For example, if you add a new domain class named Customer with three properties - Id (primary key), Name and HireDate, the generated script should be similar to:

CREATE TABLE [customer] (
   [hire_date] datetime NOT NULL,          -- _hireDate
   [id] INT NOT NULL,                      -- _id
   [nme] varchar(255) NULL,                -- _name
   CONSTRAINT [pk_customer] PRIMARY KEY ([id])
)
GO

Adding New Scalar Properties

Scalar properties are properties of a persistent type that map to a simple type field (string, int, boolean) in the storage model. When you add a new property, Update Database from Model Wizard automatically detects the change and creates DDL script for updating your table.

For example, if you add a new property named Email for the Customer entity, the generated migration script should be similar to:

ALTER TABLE [customer] ADD [email] varchar(255) NULL
GO

Adding New Associations (Foreign Key Constraints)

Associations define relationships between persistent types in your domain model. Update Database from Model Wizard allows you to generate foreign key constraints in your database when you add new associations in Visual Designer.

This section demonstrates how to create foreign key constraints in a "migrate database" scenario. The specific thing here is that if you create the model with all associations and then create the database in a single trip, all the constraints will be created automatically. But, if you create the entities and the association in separate trips, this will not automatically lead to a foreign key constraint in the database.

Suppose, you have two entities - Customer and Order without associations between them. Both of the entities are mapped to corresponding tables in the database. You want to create a new association in Visual Designer and migrate your database. This will not automatically lead to a foreign key constraint to be created in the database. Telerik Data Access will work fine even with an association that does not match a constraint. However, you can explicitly add the matching constraint through the Constraint Editor.

You need to explicitly create the constraint. For a complete walkthrough, please refer to How to: Create Constraints in Round-Trip Scenario.

When you run Update Database from Model Wizard, the new constraint will be recognized by the wizard and DDL script will be created.

CREATE INDEX [idx_customer_order_id] ON [customer]([order_id])
GO
ALTER TABLE [customer] ADD CONSTRAINT [ref_customer_Orders] FOREIGN KEY ([order_id]) REFERENCES [Orders]([Id])
GO

For more information, please refer to the Working with Associations section.

Deleting Entities (Tables)

When an entity is deleted from the domain model (Visual Designer and Model Schema Explorer) or a database table was never included in it, Update Database from Model Wizard can generate DDL script for dropping the table in the database.

In order to permanently remove a table from the domain model and from the database, you can use the following steps:

Right-click on the domain class in Visual Designer and select Delete
Delete the table from Model Schema Explorer, the Tables node
Save the .rlinq file
Start Update Database from Model Wizard and select Migrate Database, Allow table removal
Select the tables to be removed

A sample DDL script is shown below:

-- dropping table [Country]
DROP TABLE [Country]
GO

Deleting Properties (Columns)

Update Database from Model Wizard will also detect the case when you delete properties/columns in the domain model. Two steps should be performed:

Delete the property from the Visual Designer.
Delete the column from the relational part of the domain model. In Model Schema Explorer, expand the Tables node and then expand the parent table. Right-click the corresponding column and click Remove column.

In Select Changes Page the table will be marked with the Modify operation. The deleted column will be marked with the Remove operation.

A sample DDL script is shown below:

ALTER TABLE [customer] DROP COLUMN [hire_date]
GO

Deleting Associations (Constraints)

Update Database from Model Wizard can detect the case when you delete associations/constraints in the domain model. Two steps should be performed:

Delete the association from Visual Designer.
Delete the constraint from the relational part of the domain model. In Model Schema Explorer, expand the Constraints node. Right-click the corresponding constraint and click Remove constraint.

In Select Changes Page the table that owns the constraint will be marked with the Modify operation. The deleted constraint will be marked with the Remove operation.

A sample DDL script is shown below:

ALTER TABLE [Customer] DROP CONSTRAINT [FK_Customer_Orders]
GO

Renaming Entities (Tables)

Renaming an entity in Visual Designer is not enough for Update Database from Model Wizard to generate DDL script for renaming the underlying table in the database. If you want to rename a table in the relational part of the domain model and then reflect the changes on the server, you need to perform the following steps:

In Visual Designer, right-click the target entity and select Edit Table.... This will open Table Editor.
Modify the name of the table and click OK.

In Select Changes Page the table will be marked with the Add operation. That means that a new table with the new name will be added on the server. The wizard cannot detect that the original table has been renamed so you need to select Allow table removal and mark the table with the old name for removal.

A sample DDL script is shown below:

-- dropping table [Customer]
DROP TABLE [Customer]
GO
-- creating table Customer_Changed
CREATE TABLE [Customer_Changed] (
[OrderID] int NOT NULL,                 -- _order
[nme] varchar(255) NULL,                -- _name
[Id] int NOT NULL,                      -- _id
[HireDate] datetime NOT NULL,           -- _hireDate
[Email] varchar(255) NULL,              -- _email
CONSTRAINT [pk_Customer_Changed] PRIMARY KEY ([Id])
)
GO
ALTER TABLE [Customer_Changed] ADD CONSTRAINT [ref_Customer_Changed_Order] 
FOREIGN KEY ([OrderID]) REFERENCES [Order]([Id])
GO
-- Index 'idx_Customer_Changed_OrderID' was not detected in the database. It will be created
CREATE INDEX [idx_Customer_Changed_OrderID] ON [Customer_Changed]([OrderID])
GO

Renaming Properties (Columns)

Renaming a property in Visual Designer is not enough for Update Database from Model Wizard to generate DDL script for renaming the underlying column in the database. If you want to rename a column in the relational part of the domain model and then reflect the changes on the server, you need to perform the following steps:

In Visual Designer, right-click the parent entity and select Edit Table.... This will open Table Editor.
Modify the name of the target column and click OK.

In Select Changes Page the table will be marked with the Modify operation. The original column will be marked with Remove, i.e. it will be removed from the database. The new (renamed) column will be marked with Add, i.e. it will be added in the table. In this example, the Name column of the Customers table is renamed to Name_Changed.

A sample DDL script is shown below:

-- add column for field _name
ALTER TABLE [Customer] ADD [Name_Changed] varchar(255) NULL
GO
-- dropping unknown column [Name]
ALTER TABLE [Customer] DROP COLUMN [NAME]
GO

Changing Property (Column) Type

Changing the property type in Visual Designer is not enough for Update Database from Model Wizard to generate DDL script for changing the SQL type of the underlying column in the database. If you want to change the SQL type of a column in the relation part of the domain model and then reflect the changes on the server, you need to perform the following steps:

In Visual Designer, right-click the parent entity and select Edit Table.... This will open Table Editor.
Modify the SQL Type of the target column and click OK.

In Select Changes Page the table will be marked with the Modify operation. The SqlType property of the column will be modified.

A sample DDL script is shown below:

-- Column was read from database as: [Email] varchar(255) null
-- modify column for field _email
ALTER TABLE [Customer] ALTER COLUMN [Email] INT NULL
GO

Changing Property (Column) Nullability

Changing the Nullable property in Visual Designer is not enough for Update Database from Model Wizard to generate DDL script for changing the nullability of the underlying column in the database. If you want to change the nullability of a column in the relation part of the domain model and then reflect the changes on the server, you need to perform the following steps:

In Visual Designer, right-click the parent entity and select Edit Table.... This will open Table Editor.
Modify the Allow nulls property of the target column and click OK.

In Select Changes Page the table will be marked with the Modify operation. The IsNullable property of the column will be modified.

A sample DDL script is shown below:

-- Column was read from database as: [OrderId] int null
-- modify column for field _orderId
UPDATE [Customer]
  SET [OrderId] = 0 -- Add your own default value here, for when [OrderId] is null.
WHERE [OrderId] IS NULL
GO
ALTER TABLE [Customer] ALTER COLUMN [OrderId] INT NOT NULL
GO

Changing the Primary Key

Changing the Identity property in Visual Designer is enough for Update Database from Model Wizard to generate DDL script for changing the primary key of the underlying table in the database. In Select Changes Page the table will be marked with the Modify operation. The old primary key constraint will be dropped. A new primary key constraint for the new identity column will be created.

A sample DDL script is shown below:

ALTER TABLE [Customer] DROP CONSTRAINT [pk_customer]
GO
ALTER TABLE [Customer] ADD CONSTRAINT [pk_Customer] PRIMARY KEY ([OrderId])
GO

Adding New Tables to a Schema

Consider a model-first or a round-trip scenario where you create several different schemas for the relational artifacts within the corresponding domain model. For example, you create a new table, which is part of a schema that does not exist in the database.

Update Database from Model Wizard will detect the new schema and generate DDL script.

CREATE SCHEMA [Production]
GO
-- MissingSchemaDemo.Location
CREATE TABLE [Production].[Locations] (
   [CostRate] DECIMAL(18) NULL,            -- _costRate
   [Id] INT IDENTITY NOT NULL,             -- _id
   [NAME] nvarchar(50) NULL,               -- _name
   CONSTRAINT [pk_Locations] PRIMARY KEY ([Id])
)
GO

Changing the Table Schema

Changing the table schema in Visual Designer is not enough for Update Database from Model Wizard to generate DDL script for changing the schema of the underlying table in the database.

In Select Changes Page the table will be marked with the Add operation. That means that a new table will be added on the server. The wizard cannot detect that the original table now belongs to a new schema. You have to select Allow table removal and mark the modified table for removal. Additionally, if the new schema does not exist on the server, the wizard will generate DDL script for it.

A sample DDL script is shown below:

-- dropping table [Customer]
DROP TABLE [Customer]
GO
CREATE SCHEMA [Sales]
GO
-- creating table Customer
CREATE TABLE [Sales].[Customer] (
[OrderID] int NOT NULL,                 -- _order
[nme] varchar(255) NULL,                -- _name
[Id] int NOT NULL,                      -- _id
[HireDate] datetime NOT NULL,           -- _hireDate
[Email] varchar NOT NULL,               -- _email
CONSTRAINT [pk_Customer2] PRIMARY KEY ([Id])
)
GO
ALTER TABLE [Sales].[Customer] ADD CONSTRAINT [ref_Customer_Order] 
FOREIGN KEY ([OrderID]) REFERENCES [Order]([Id])
GO
-- Index 'idx_Customer_OrderID2' was not detected in the database. It will be created
CREATE INDEX [idx_Customer_OrderID2] ON [Sales].[Customer]([OrderID])
GO