Dynamic Database Creation in Entity Framework

Entity Framework 4 RC allows you to create and drop databases in run-time using SSDL for DDL generation. Now ObjectContext has CreateDatabase(), DropDatabase(), and CreateDatabaseScript() methods. They appeared in Entity Framework v4 CTP for Code Only and only for SQLClient initially but later they became available for other EF-providers.

In this article we describe implementation of these methods in Devart data providers. We are using dotConnect for Oracle as a data provider in the following examples. Northwind is used as a sample database.

DDL generation

We have supported functionality for DDL script generation not only for Entity Framework v4 but also for Entity Framework v1 in the following ADO.NET data providers: dotConnect for Oracle, dotConnect for MySQL, dotConnect for PostgreSQL, and dotConnect for SQLite. This functionality is useful when you have a ready EF-model, but don’t have a DDL script. You can use it for simple database deployment also.

During database creation tables, primary keys, and foreign keys are specified. If database server supports Id autogeneration, than identity specification will be created for primary keys. The views, functions, and tables for the entities from SSDL having DefiningQuery are not created.

Please note, fully qualified names for a table will be generated if the table has a Schema attribute. So if you don’t want to link to a single schema, it is better to delete these attributes (Entity Developer allows you to edit storage model for edml models conveniently). If your model was created by EDM Wizard and Visual Studio Entity Designer, you can edit it with the help of any XML Editor.

Entity Framework v4

Use the following code to create a database:

NorthwindEntityModel ctx = new NorthwindEntityModel();
ctx.CreateDatabase(); // The database structure was created on the server

To generate a database script use the following code:

NorthwindEntityModel ctx = new NorthwindEntityModel();
string ddlScript = ctx.CreateDatabaseScript();
// The script of database creation was generated

You can delete a database with the help of the following code:

NorthwindEntityModel ctx = new NorthwindEntityModel();
ctx.DeleteDatabase();

Note. We don’t support the new public bool DatabaseExists() method of ObjectContext because it is ambiguous.

DeleteDatabaseScript

ObjectContext has the CreateDatabaseScript method, but it does not have a corresponding DropDatabaseScript() method. This method is required to receive database dropping script. You can do it manually:

1. Add the Devart.Data.Oracle.Entity.dll for .NET run-time v4.0 reference to the project
2. Extend the ObjectContext partial class with the following method:

   using System.Data.EntityClient;
   using System.Data.Metadata.Edm;
   using Devart.Data.Oracle.Entity;
   
   partial class NorthwindEntityModel {
   
       public string DeleteDatabaseScript() 
       {
           EntityConnection entityConnection = this.Connection as EntityConnection;
   
           StoreItemCollection store = entityConnection.GetMetadataWorkspace().
             GetItemCollection(DataSpace.SSpace)
             as StoreItemCollection;
   
           OracleEntityProviderServices oracleEntityProviderServices
             = new OracleEntityProviderServices();
   
           return oracleEntityProviderServices.DeleteDatabaseScript(null, store);
       }
   }

3. Use it in run-time:

   NorthwindEntityModel ctx = new NorthwindEntityModel();
   string ddlScript = ctx.DeleteDatabaseScript();

Entity Framework v1

You can generate DDL for Entity Framework v1 too. To do this perform the following steps:

1. Add Devart.Data.Oracle.Entity.dll for .NET run-time v2.0 reference to the project
2. Extend the ObjectContext partial class with the following methods:

 using System.Data.EntityClient;
 using System.Data.Metadata.Edm;
 using Devart.Data.Oracle.Entity;

  partial class NorthwindEntityModel {

     public void CreateDatabase() {

       EntityConnection entityConnection = this.Connection as EntityConnection;

       StoreItemCollection store =
          entityConnection.GetMetadataWorkspace().GetItemCollection(DataSpace.SSpace)
          as StoreItemCollection;

       OracleEntityProviderServices oracleEntityProviderServices =
         new OracleEntityProviderServices();

       oracleEntityProviderServices.CreateDatabase(entityConnection.StoreConnection,
         null, store);
     }

     public void DeleteDatabase() {

       EntityConnection entityConnection = this.Connection as EntityConnection;

       StoreItemCollection store =
         entityConnection.GetMetadataWorkspace().GetItemCollection(DataSpace.SSpace)
         as StoreItemCollection;

        OracleEntityProviderServices oracleEntityProviderServices =
          new OracleEntityProviderServices();

        oracleEntityProviderServices.DeleteDatabase(entityConnection.StoreConnection,
          null, store);
     }

     public string CreateDatabaseScript() {

       EntityConnection entityConnection = this.Connection as EntityConnection;

       StoreItemCollection store =
         entityConnection.GetMetadataWorkspace().GetItemCollection(DataSpace.SSpace)
         as StoreItemCollection;

       OracleEntityProviderServices oracleEntityProviderServices =
          new OracleEntityProviderServices();

       return oracleEntityProviderServices.CreateDatabaseScript(null, store);
    }

     public string DeleteDatabaseScript() {

       EntityConnection entityConnection = this.Connection as EntityConnection;

       StoreItemCollection store =
         entityConnection.GetMetadataWorkspace().GetItemCollection(DataSpace.SSpace)
         as StoreItemCollection;

       OracleEntityProviderServices oracleEntityProviderServices =
          new OracleEntityProviderServices();

       return oracleEntityProviderServices.DeleteDatabaseScript(null, store);
   }
}

The functionality of the provider-specific DbProviderServices class is used for the DDL generation. Here is a full list of the provider-specific classes and assemblies: