Model First Data Base generation in the Entity Framework 4.0

An exciting new Entity Framework Designer feature in Visual Studio 2010 is the ability to generate DDL that will create a database that can store your entity data model. This feature is not only exciting because it brings a long overdue capability to life, but because it is fully extensible: You can take control of the entire process, or plug into parts of it. For example, you can take over the DDL generation step to add support for your database of choice, or to customize the DDL generation process. Alternatively, you can take over the inheritance mapping step and replace our out-of-the box strategy (which is table-per-type) with your own. Or take over the entire mechanism and generate both stored procedure mappings and CRUD stored procedures for all your types. In a future post, we will show you exactly how to do this and provide an implementation of the table-per-hierarchy mapping for you to use or modify.

For now, let’s take a quick look at this feature by taking a very simple model:

We right click…and in the context menu we see, and click on, “Generate Database Script from Model”:

The database generation wizard asks for a database to target. This information is used by the wizard to determine which database-specific types to translate the Entity Data Model (EDM) types to, and how to render DDL that is appropriate for the target database. In this case, I just created a new Microsoft SQL Server database file:

Once we click on next, we are shown a preview of the generated DDL, which will be saved to disk once we click on “Finish”. Here is an excerpt from the DDL generated now for SQL Server:

…
-- Creating table 'IngredientSet_Protien'
CREATE TABLE [dbo].[IngredientSet_Protien] (
    [Cut] nvarchar(100)  NOT NULL,
    [Type] nvarchar(70)  NULL,
    [Id] int  NOT NULL
);
GO
-- Creating table 'IngredientSet_Plant'
CREATE TABLE [dbo].[IngredientSet_Plant] (
    [IsFruit] bit  NOT NULL,
    [IsHerb] bit  NOT NULL,
    [IsVegetable] bit  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'RecipeSet'
ALTER TABLE [dbo].[RecipeSet] WITH NOCHECK
ADD CONSTRAINT [PK_RecipeSet]
    PRIMARY KEY CLUSTERED ([Id] ASC)
    ON [PRIMARY]
GO
…
-- --------------------------------------------------
-- Creating all Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [Chef_Id] in table 'RecipeSet'
ALTER TABLE [dbo].[RecipeSet] WITH NOCHECK
ADD CONSTRAINT [FK_ChefRecipe]
    FOREIGN KEY ([Chef_Id])
    REFERENCES [dbo].[ChefSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION
…

Note here that the default inheritance mapping strategy is to create a table for all subtypes: In the above DDL, you can see that we are generating tables for the Protien and Plant subtypes. These tables are named with the name of the EntitySet in which the types live, to which we append the name of the type itself.

Note also that we generate primary and foreign key constraints for the model.