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)”

 

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

 

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:

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.
 

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’)

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:

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.

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

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

Comments

• Anonymous
  February 08, 2010
  Since the release candidate appears to be only days away, is this going to be compatible without changes?

• Anonymous
  February 08, 2010
  I'll release a version that's RC-compatible that will most likely be compatible with RTM as well.

• Anonymous
  February 08, 2010
  After installation doing the walkthrough i see only the new workflows in (1), but not the dialog in (2)

• Anonymous
  February 09, 2010
  Hi Werner -- are you trying to install this over an RC build?

• Anonymous
  February 09, 2010
  no VS2010 BETA 2 the installation finished without any error or warning

• Anonymous
  February 10, 2010
  Hi Werner -- I wasn't able to reproduce this issue on a new Beta 2 build. Let's see if we can resolve this over email. My address is [firstname].[lastname]@microsoft.com. I have a few initial questions that we can address over email:

1. Are you installing this as an admin?
2. Can you proceed with the walkthrough if you select the "Generate Migration T-SQL and Deploy" workflow directly from the property window mentioned in step (5)? Does it proceed successfully without errors and do you see the progress bar?

• Anonymous
  February 10, 2010
  Has a version been released for RC?  I just upgraded to RC and then noticed this post and I'm cursing myself. I tried the release for Beta 2, but it doesn't seem to work (I don't see the dialog and even if I try to force it, no luck) Thanks, looks great!  Btw, this will work in VS 2010 Professional?

• Anonymous
  February 10, 2010
  Hi, Great addition. However when I tried to generate the DB from model within an ASP.NET website, I got the following message : "An Error occurred during the workflow configuration : object reference not set to an instance of an object" It seems to work with the Web Application project template, but the web application model has some other small complications when using dynamic data (due to the separate assemblies). For that reason, I prefer to use the Web Site template, but then I can't use the new workflow manager. Any suggestions ? Thanks! Yacine

• Anonymous
  February 11, 2010
  @Eric -- you can expect the RC update within the next two days. If you have some time, could you also please send me some details about your machine through email as well as answers to the questions I asked Werner before your comment? Are you using VS Pro currently? adi (dot) unnithan (at) microsoft.com. @ykhammal -- Thanks for reporting this. This issue is fixed in the RC version which will be released in the next day or two. For the time being, can you at least select the workflow through the designer's property window? (Database Generation Workflow - see step (5) in the walkthrough above)

• Anonymous
  February 11, 2010
  Hi, i using vs2010 RC  and Entity Designer Database Generation Power Pack Version 1.0.1 but after adding a Association between 2 entities , i got the following error : "Errors occurred while modeling the target database. Deployment can not continue." Database Generation Workflow property is set to “Generate Migration T-SQL and Deploy” workflow. in vs2010 beta2 all things are well !

• Anonymous
  February 11, 2010
  @siroos -- Currently the power pack is not supported for RC. An updated version will be released very soon, and it contains a fix for this specific issue with RC.

• Anonymous
  February 11, 2010
  The power pack has been updated for RC: http://visualstudiogallery.msdn.microsoft.com/en-us/df3541c3-d833-4b65-b942-989e7ec74c87 There are a few bug fixes and new features as well. Check out the release notes in the 'discussions'. Thanks for being patient -- we look forward to more feedback. -adi

• Anonymous
  February 11, 2010
  Hi adi, i installed the power pack 1.1 , it's working very well with vs2010 RC . Thanks

• Anonymous
  February 11, 2010
  @adiunni, yes I can select the workflow in the old property window, this is what I did to generate the DB using TPH. I noticed that workflow generates a new field called Disc in the DB and in the CSDL/MSL. I suppose that serves to automatically add a mapping condition for an inheritance relationship ?

• Anonymous
  February 12, 2010
  @ykhammal -- yes, this stands for 'discriminator' and will contain the value of the condition specified in the mapping (MSL). If you're interested in changing this, the logic is in T4 templates which you can find in %VSInstallationDirectory%Common7IDEExtensionsMicrosoftEntity Framework ToolsDBGen. I'll add support for easily bringing these T4 templates into the IDE to tweak in future versions. Also, regarding your original problem, I released the RC version which should fix this issue.

• Anonymous
  February 12, 2010
  @adiunni , thanks for your reply. Indeed I need to modiy the condition and mapping each time I use the DB generation TPH workflow : delete the default disc mapping and replace it with my own condition. I haven't checked out the RC yet, will do so shortly, thanks.

• Anonymous
  February 12, 2010
  Are you just changing the name? Would you prefer the column name is something else, like storesetname + "type"? i.e. if the CSDL EntitySet's name is "People", then maybe I could set it as "Peopletype"?

• Anonymous
  February 14, 2010
  Hi Adi, sorry for the delay - I'm not getting new post notifications for some reason. Yes name the column as you suggested would be better, but it would be even nicer to be able to specify a custom discriminator column instead of the default disc one, and choose among the properties specified for the derived entity. For instance in my model I have a Contact entity and a ExternalContact entity that inherits from Contact. I need to be able to specify the Source property of ExternalContact as the discriminator column : if sourceID is not null, this is an ExternalContact, otherwise a base Contact. Currently, I need to tweak the xml and the TSQL to remove disc and add the mapping condition I want. Yacine

• Anonymous
  February 14, 2010
  Following up on the topic, here's an issue I'm trying to deal with : if I add to ExternalContact the mapping "When souceID IS NOT NULL", when I validate the model, I get a message saying the SourceID property should be made non-nullable. Then, when I generate the DB from model, the SourceID column is generated with the NOT NULL constraint in the DB. This makes it impossible to use the SourceID as a discriminator column using the above condition (IS NOT NULL). So I need to manually change the TSQL script to allow nulls in the SourceID column. Thought you may be interested in this scenario.

• Anonymous
  February 27, 2010
  This does not work with my installation of VS2010 RC.  The BlogModel.edmx.sql file is generated as before; the database is not automatically created.  When I make the entity changes and generate the database, again the database is not modified.  You have to run the BlogModel.edmx.sql.  The second time you do this, it wipes all of the existing data.

• Anonymous
  February 27, 2010
  @Yacine - I also am sorry for the long delay. Looks like those notifications are only sent for the post, not the comments. Anyway, it sounds like SourceID is a key, and that's why it's complaining about it being nullable. Is this correct? Typically in this situation it's recommended that you have another non-key column that is used as the discriminator so that it can allow nulls. @John, it sounds like maybe the power pack didn't install correctly for you. Do you see the 'Workflow Manager' dialog when selecting 'Generate Database from Model'?  Do you see any new workflows specified in the property window as in step (5) above?

• Anonymous
  March 18, 2010
  Is it possible to run the db generation from run time code? I've been looking in reflector in the System.Data.Entity.Design namespace and examined the code of the EdmGen2 project, but am not coming up with anything.

• Anonymous
  March 18, 2010
  Hello, i am currently using EDMX designer which doesnt support TPC or mixed TPT-TPH mapping. I dont want (for productivity sake) to leave the designer and manipulate the XML Files, breaking the ability to go back to the designer and add features to my model. Will this feature help me to achieve that ? Is there any "generate inner SSDL and MSL into the EDMX" T4 templates ? thanks in advance. Digeo

• Anonymous
  April 12, 2010
  Can we expect a "rtm" version of this Powerpack for VS2010 RTM? Is it going to be ready in the short time?

• Anonymous
  May 13, 2010
  Hi Love this tool. But... Is there a T4 template that controls generation of the DDL when using one of the "Generate Migration T-SQL.." options? Many thanks.

• Anonymous
  September 24, 2010
  When using this tool does anyone get this error? "The source database schema provider Sql100DatabaseSchemaProvider can not be translated to provider Sql90DatabaseSchemaProvider." I am using VS 2010 and SQL 2008.

• Anonymous
  September 24, 2010
  Actually I was mistaken, the database is SQL 2005. According to the documentation on this power pack it only works with SQL 2008 R2. This must be the problem.

• Anonymous
  October 06, 2010
  Nice tool. I have one issue with it. Database Generation Workflow Manager doesn't display. The old Generate Databaase Wizard display instead. I'm using VS 2010 RTMRel and extension version 1.1. SQL generetaion works fine for all strategies. Any ideas how to fix it?

• Anonymous
  December 26, 2010
  The comment has been removed

• Anonymous
  February 13, 2011
  same as jarecky, Database Generation Workflow Manager doesn't display. The old Generate Databaase Wizard display instead. But manual selection of TPH xaml works. Any updates?

• Anonymous
  February 14, 2011
  When I follow your steps, the DGWM v1.1 does nothing but the UI shows "Successfully deployed to 'DBNAME'. Finished Workflow. When I look at the database, nothing has been done.

• Anonymous
  February 19, 2011
  I'm trying the "generate migration" option against a SQL Azure database, and I get the error "The target database schema provider could not be determined.  Deployment cannot continue."  Any advice?

• Anonymous
  February 25, 2011
  When using a database schema name other than dbo, migration does not work.

• Anonymous
  March 25, 2011
  I'm also getting "The target database schema provider could not be determined.  Deployment cannot continue" for a local instance of Denali.

• Anonymous
  March 27, 2011

1. Workflow Manager UI wont show up!
2. My problem: newid() default value setting of Id column is always deleted by the migration tool. VS2010 sp1 Ultimate, (Generate Migration T-SQL And Deploy.xaml (VS)) no error during migration. Can U pls help?

• Anonymous
  April 27, 2012
  Hi..  I have create clone for a existing workflow and manually edited the file. However, when I run the update wizard the manual update is lost. Can u please help me?

• Anonymous
  May 30, 2012
  are there any plans to bring this forword for Visual Studio 11 and Entity Framework 5?

• Anonymous
  August 20, 2012
  Wondering the same as sgeek7. Thanks!