Entity Designer Database Generation Power Pack


The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.


The "Entity Designer Database Generation Power Pack" is a downloadable addition to Visual Studio 2010 Beta 2 which includes some new database generation technologies. The original Database Generation feature in the Entity Designer in VS 2010 is extensible via Windows Workflows and T4 Templates. This Power Pack builds on these extensibility mechanisms and introduces the following:

  1. Basic Table-per-Hierarchy support. This is represented by the “Generate T-SQL via T4 (TPH)” workflow.
  2. The SSDL and MSL generation pieces can now be tweaked through T4 templates, both in TPH and TPT strategies through the “Generate T-SQL via T4 (TPT)” and “Generate T-SQL via T4 (TPH)” workflows.
  3. Direct deployment and data/schema migration are available through the “Generate Migration T-SQL and Deploy” workflow. This workflow will use the Team System Data APIs to diff our default T-SQL script against the target database and create a new script which will perform non-invasive ALTERs and data migration where necessary.
  4. A new user interface will now display when “Generate Database from Model” is selected – this acts as a “workflow manager” which will present to you our default workflows and allow you to create your own, customizable workflows based on your own strategy, script generation, and deployment requirements.

In addition, there are a few other things:

  1. Data-Tier Application support. This feature, first introduced in SQL Server 2008 R2, provides a new, consolidated way of managing SQL Server databases across multiple servers. You can think of it as an MSI installer package for your database server. For more information, see Sanjay Nagamangalam’s latest PDC session: https://channel9.msdn.com/posts/elisaj/Developing-Data-tier-Applications-using-Visual-Studio-2010/. The Database Generation Power Pack 1.0 supports generation of DACPACs and direct deployment of DACPACs to a SQL Server 2008 R2 CTP3 instance.
  2. Database Project support. Rather than just creating a T-SQL script, it may be more useful for a T-SQL developer to interact with the T-SQL inside a database project. This Power Pack can automatically ‘sync’ with a database project by importing our generated script into it – the project must be added to the same solution as the project containing the EDMX file and it must have the same name as the EDMX file – this requirement will go away in the future.
  3. Another small user interface in the form of a progress bar that replaces the “DDL” textbox in the last page of the “Generate Database from Model” wizard and can be declaratively created within the XAML of a Windows Workflow.

In this walkthrough, I will demonstrate how to install the Database Generation Power Pack 1.0.1, some of the key features in the new user interface, and how to achieve data and schema migration.

Installation

The only pre-requisite is Visual Studio 2010 Beta 2. Note that this extension will not work with Express editions. The Database Generation Power Pack is an MSI package that consists of a VSIX extension, a set of XAML flies, and T4 templates.

You can download the package from here: https://visualstudiogallery.msdn.microsoft.com/en-us/df3541c3-d833-4b65-b942-989e7ec74c87

Click on the “Download” link which will download the MSI. Launch the MSI, note the pre-release license and finish installing the setup package.

Setup

  1. Download the attached EDMX file locally (see end of this post)
  2. Save the attached SQL script locally (see end of this post)
  3. Open Visual Studio and create a new C# Console Application project.
  4. Right-click the project node and Add -> Existing Item -> BlogModel.edmx.
  5. Make sure you have CREATE DATABASE permission in the [master] database.
    https://blogs.msdn.com/dparys/archive/2009/09/17/create-database-permission-denied-in-database-master-my-fix.aspx

Walkthrough

1.  Open BlogModel.edmx in your project. Click on an area of the designer surface, look at the “Database Generation Workflow” property in the Properties window and note that we’ve added six new workflows. We can select a new one from here but for now, keep the default workflow: “TablePerTypeStrategy.xaml (VS)”

Untitled 

2. Right-click on the designer surface and select “Generate Database from Model…”. You will be presented with the new user interface:

Untitled 

3. You can think of this user interface as a more advanced version of the property window selector. On the left pane is a list of all workflows available which we aggregate from three locations:

  • VS directory: %vsinstalldir%\common7\ide\extensions\microsoft\entity framework tools\dbgen

  • User directory: %localappdata%\microsoft\visualstudio\10.0\extensions\microsoft\entity framework tools\dbgen

  • The project that owns the current EDMX file

    The right is a ‘details’ view which shows the options you can select, which options are available for the selected workflow, and buttons for deleting, cloning, and selecting the workflow. Making any changes to the workflow will automatically save it (as of version 1.0.1).

4. All of the workflows in the left pane are installed in the VS directory. We discourage you from editing these workflows since we may update them in the future. Therefore, the only operation available to you for these default workflows is cloning. The two buttons on the right of the toolbar allow you to clone the workflow to your user directory and to your project, respectively:
Untitled

5. Click on the “Generate Migration T-SQL and Deploy” workflow in the list and click “Select Workflow for Database Generation”. Notice the green tick mark in the left pane now indicates that the “Generate Migration T-SQL and Deploy” workflow is selected. If your properties window is open, also notice that “Generate Migration T-SQL and Deploy.xaml” is selected in the “Database Generation Workflow” property.

6. Hit the “Next >” button at the bottom, and now you’re presented with the data connection page that you’re familiar with in basic Model First.

7. Select a ‘New Connection’, choose ‘.\sqlexpress’ as your Server name, and type in ‘BlogDb’ as the database name. Hit OK. VS will prompt you to create this database – select Yes. If you run into an error like “Permission denied in master”, make sure you have CREATE DATABASE permission (see the link above in ‘Setup’). Click ‘Next’ out of the data connection page.

8. You will see the Summary Page, but the “DDL” textbox has been replaced by a new progress bar which will track the progress of the overall workflow. If everything proceeds successfully, the progress bar should finish.
Untitled 

9. Hit Finish out of the Summary page, and the required store model (SSDL) and mappings (MSL) are created. Note that the T-SQL was never shown in the database generation process and it never opened in the editor afterwards. If you would like to tweak this workflow so that it follows the usual database generation process (not directly deploy, but open the migration script in the T-SQL editor) then using the new ‘Workflow Manager’, you can clone the workflow into your user directory, rename it, set the ‘Script Generation’ option to ‘Migration T-SQL’, set the ‘Deployment’ option to ‘None’, and select it for database generation.

10. Now open the Server Explorer (View -> Server Explorer) and notice that we’ve directly deployed to the database (if it’s already open, then right-click on the ‘Tables’ folder and select ‘Refresh’)

Untitled

11. Now let’s insert some data – open up the [blogmodel.sql] script in Visual Studio and choose Data -> Transact SQL Editor -> Execute SQL. Connect to ‘.\sqlexpress’, and execute the script. You should see ‘Query executed successfully.’ Under the ‘Messages’ pane in the T-SQL editor.

12. Go back to the model – now let’s say we want to add comments to the posts in our blog and add a description to each post in our blog.

  • Add in a ‘Comment’ Entity Type with ‘Id’ (of Type: non-nullable Int32), ‘Text’ (String) and ‘Rating’ (Int32) properties.
  • Add a nullable (this is important) String property to Post called “Description” and then add a 0..1:* association between Post and Comment. You can choose to include foreign keys/navigation properties or not, it won’t matter. Without navigation properties and with foreign keys, it should look like this:

Untitled

13. Right-click on the designer surface again, Generate Database from Model. Click ‘Next’ past the Workflow Manager. Since the connection string is now present in app.config, you will not see the data connection dialog, and the workflow should immediately directly deploy the new T-SQL. Select ‘Finish’ out of the Summary and Settings page. Go back to Server Explorer, right-click on the ‘Tables’ folder and select ‘Refresh’. Notice that we’ve added the ‘Comments’ table.

Untitled

14. Right-click the Posts table and select “Show Table Data”:

Untitled

We have preserved the existing data and added a new, nullable column for the description. If you do not see the ‘Comments’ table and the new ‘Description’ column, check to make sure that the ‘Description’ property in your ‘Post’ entity is Nullable. A script that is generated with a non-nullable Description property will look to see if there is any data in the ‘Post’ and halt the script to prevent data loss.

Conclusion

In this walkthrough we:

  • Looked briefly at the new user interface to manage workflows and select them for database generation.
  • Demonstrated how the designer can generate a migration script and deploy it directly.
  • Demonstrated data and schema migration from the Entity Designer.

You can expect more functionality in future version such as the ability to handle “renames” (although this Power Pack provides migration, it cannot identify a rename of an EntityType, for example – this will translate into a drop/create), a new Workflow Manager, the ability to manage T4 templates, better database project support, and more.

Finally, we’re presenting these options and new UI affordances as a way of gauging what is most important to you for the next version of our tools so we value your feedback greatly!

Thanks,

Adi Unnithan
Software Design Engineer

DatabaseGenerationPowerPack.zip