Generating Database from Model

In This Topic

Entity Framework v4, v5, and v6 allow you to use Model First feature to generate database, and the SSDL, and MSL. In this article we consider how to use this functionality.

Note that this functionality is not supported in Entity Framework Core.

This feature provides a possibility to start from a conceptual model and then to generate the other parts of the EDM (Entity Data Model) and the database. Create Database from Model wizard can be used for database generation from an Entity Data Model in Visual Studio 2010 - 2022.

Entity Framework workflow for DDL generating from CSDL includes two actions. The first one generates SSDL and MSL from CSDL. It is common for all providers and cannot be customized independently. The second action is specific to Entity Framework provider and can be modified for specific DBMS.

Devart offers additional templates for database script generation.

The following steps will guide you through database generation wizard:

Create an EF model. The given example shows a CRM_DEMO schema.
Select a corresponding template in the model properties window. Then right-click on the diagram and run the Create Database from Model wizard by choosing Generate Database from Model from the popup menu.

The default schema name for Microsoft SQL Server database is "dbo". Don't forget to change it to the necessary one before the code generation.

Here is a DDL script generated for the current model.

As a result of these steps you will obtain a valid database-specific DDL script. You can run this script against your database to create a valid database schema.

Note:
Microsoft does not take into account the Oracle 30 symbol limitation on the length of database object names. That may result in problems in case your conceptual model contains objects with very long names. We are generating SQL using the storage model (SSDL). The SSDL generation in its turn is performed by EDM Designer. The procedure of generation is described here: \Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\TablePerTypeStrategy.xaml. Our generation is performed using the .tt templates: \Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\Devart SSDLToOracle.tt \Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\Devart SSDLToOracle.ttinclude \Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\Devart SSDLToSQL.ttinclude \Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\Devart SSDLToSQLite.tt 10.0 here is the Visual Studio version for Visual Studio 2010. For other Visual Studio versions, you need to place the following versions instead of 10.0: Visual Studio 2012 - 11.0 Visual Studio 2013 - 12.0 Visual Studio 2015 - 14.0 One can change these templates to modify the generated DDL. There is a limitation in Devart SSDLToOracle.ttinclude: IdentifierMaxLength = 30; If it is removed, it is changed to a greater value, DDL will be successfully generated, but there will be an Oracle exception while executing the script. The names of generated primary keys, foreign keys, triggers, sequences are cut to fit 30 symbols. We don't cut the names of tables and columns - this allow creating a valid DDL, but this will cause runtime errors because these names will still be full in the model. So, there can be two solutions for the problem: Shorten the names of classes and fields to 30 symbols or less. After SSDL is generated, open the model in XML Editor and shorten the names of long identifiers in the SSDL and MSL parts of the model, and generate DDL (without SSDL regeneration).

Note:

Microsoft does not take into account the Oracle 30 symbol limitation on the length of database object names. That may result in problems in case your conceptual model contains objects with very long names. We are generating SQL using the storage model (SSDL). The SSDL generation in its turn is performed by EDM Designer. The procedure of generation is described here:

\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\TablePerTypeStrategy.xaml.

Our generation is performed using the .tt templates:

\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\Devart SSDLToOracle.tt

\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\Devart SSDLToOracle.ttinclude

\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\Devart SSDLToSQL.ttinclude

\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\Devart SSDLToSQLite.tt

10.0 here is the Visual Studio version for Visual Studio 2010. For other Visual Studio versions, you need to place the following versions instead of 10.0:

Visual Studio 2012 - 11.0
Visual Studio 2013 - 12.0
Visual Studio 2015 - 14.0

One can change these templates to modify the generated DDL. There is a limitation in Devart SSDLToOracle.ttinclude: IdentifierMaxLength = 30; If it is removed, it is changed to a greater value, DDL will be successfully generated, but there will be an Oracle exception while executing the script.

The names of generated primary keys, foreign keys, triggers, sequences are cut to fit 30 symbols. We don't cut the names of tables and columns - this allow creating a valid DDL, but this will cause runtime errors because these names will still be full in the model.

So, there can be two solutions for the problem:

Shorten the names of classes and fields to 30 symbols or less.
After SSDL is generated, open the model in XML Editor and shorten the names of long identifiers in the SSDL and MSL parts of the model, and generate DDL (without SSDL regeneration).

Please also note that dotConnect for Oracle goes with its own ORM designer - Entity Developer. It provides advanced Model First support and a number of other features, not available in Visual Studio EDM designer.

See Also