Model First

One of the most painful omissions from the Entity Framework V1 was Model First, which basically means creating a conceptual 'model first' and then deriving a storage model, database and mappings from that.

People ask for this scenario all the time in the forums.

Well Noam, a Program Manager on the Entity Framework Tools team, outlines what we are considering:

Generating Databases from Models

The next release of the Entity Framework will include the ability to generate database schemas from your model. The main entry point into this feature is via the Designer context menu, to which we will add a new option called “Create Database from Model”.

(Note: User interface elements in this walkthrough are not final user interfaces, as the design is still under review, so please provide feedback...)

Selecting this option will bring up the following warning:

We would like users to understand that this feature will regenerate all SSDL and all MSL from scratch.

If you have started from an empty model, you will be asked to specify the target database. This screen is identical to the one shown when reverse engineering a model from a database. The implication here is that you will need an available server and database, which the system will use to determine what “flavor” of DDL to generate.

Once you have selected the target database, you will be presented with a summary screen which will provide a preview of the DDL that will be generated, as well as a tree-view of the objects.

The tree view:

The DDL view:

The DDL in the above screenshot is there merely as a graphic used to show how what the window will look like and is not intended to be representative of the DDL that will be generated by the system when you are actually creating your database (more about this soon). The DDL will however be read-only: Since it is generated by a template, editing of the results should either be done in the template, or in a separate DDL file which will not get regenerated.

Two options will be available:

- Save the DDL (off by default). This option will add the DDL as a dependent file under your EDMX file.

- Deploy the DDL (on by default). This option will deploy the DDL to the specified target database.

The generated DDL will not migrate data or schema - by default your database will be recreated from scratch.

Out of the box, we will support the Table-per-Type mapping strategy, meaning that we will create a table for each of your types and subtypes. For example, for a model like this…

…the following schema will be generated:

Of interest here is that PK-to-PK constraint between the Customer and Persons table. This helps enforce the inheritance relationship and the creation of foreign key columns to represent the various associations. In addition, the engine will create clustered keys on primary keys, and indexes on foreign keys that represent associations.

Under the Hood

The model first process is implemented using a Windows Workflow Foundation workflow that looks like this:

Here is what the stages do:

Stage

Purpose

Stage	Purpose
CSDLtoSSDL	Creates the mappings (MSL) and database store model (SSDL) in the EDMX file.
SSDLtoDBSchema	Converts the SSDL to the format used by the Microsoft.Data.Schema APIs. This format is used as a “universal” database description format and includes physical information not present in the Entity Frameworks store model, such as indexes.
GenerateDDL	Uses the Microsoft.Data.Schema APIs to convert the universal format to store-specific DDL. In this release, we will support a minimum of SQL 2005 and SQL 2008. A provider model is in place, however, and we hope to add support for additional databases.
SuspendToConfirm	This activity pauses the workflow to allow the wizard to display the DDL.
DeployToDatabase	Deploys the DDL to the target database.
OutputDDL	Writes the DDL to the file system.

These stages are expressed in a XAML file which will be placed underneath your EDMX, to allow for customization: You can add your own steps or replace ones we have created with steps that you write.

Templates

Several of the steps above make use of templates to provide an additional point of control for users, and this is where we expect most of the customization to happen. These templates use the T4 Engine that is included in Visual Studio. We are currently working on making these templates as simple as possible by providing a set of supporting APIs that provide metadata collections that are designed for artifact generation – for example, a collection of all inherited properties for a type, or a collection of both inherited and defined properties. There are three templates:

Template

Purpose

Template	Purpose
CSDL to SSDL	Creates the SSDL for the target database.
CSDL to MSL	Creates the MSL for mapping the CSDL to the generated SSDL.
SSDL to DBSchema	Creates the physical database description, including elements such as foreign keys and indexes.

So, for example, if you need all table names to start with “_tbl”, you would modify the SSDL generation template to give all tables the appropriate name, and also modify the MSL template to provide the appropriate mappings. The DBSchema template will not need to be modified as it will automatically pick up the changes made to the SSDL which is its input.

As another example, if you wish to change the mapping strategy from Table-per-Type to Table-per-Hierarchy, you would need to change this same pair of templates.

Finally, if you needed to support a database for which no Microsoft.Data.Schema provider is available, you could replace the GenerateDDL step with your own template-driven activity which could them transform the schema “manually” to your store’s DDL format.

We hope this gives you enough information to understand the design and intent of this feature.

We would love to hear your comments.

Alex James
Program Manager,
Entity Framework Team

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at this post .

Comments

• Anonymous
  September 10, 2008
  PingBack from http://hoursfunnywallpaper.cn/?p=5583

• Anonymous
  September 10, 2008
  This feature is really needed and I'm waiting the release like numerous EF users ! But without testing things it's really hard to give a useful feedback. What is shown in this post seems to be a good starting point (templates seem to be very cool, but need to be tested to see how it will be hard/easy to use..). So, the best is to release a beta as soon as possible and we'll be able to give you a true feedback. Of course, thanks for sharing the info about this new feature.

• Anonymous
  September 10, 2008
  Looks great so far, would love to see this feature callable from code to recreate the database for things like integration tests and / or part of an msbuild task. Also with this being a designer feature how would it with full POCO? Cheers

• Anonymous
  September 10, 2008
  Olivier: We are putting extra effort to make the templates as simple as possible. Given your feedback, perhaps we should consider putting a significant number of comments in them to explain what each piece does and how/why to customize it. Nigel: Yes, we would like to make this feature both callable from code and not dependent on Visual Studio assemblies being present. Regarding POCO, you will still need to have an EDMX file for many scenarios, and so you should be able to generate your database. If you are running POCO without a model file, then that scenario is a little more tricky and we are still working out what will and will not be supported. Thank you, Noam

• Anonymous
  September 10, 2008
  After I've changed my schema, how would I get for my database updated?  This is quite important for updating databases on customers machines in versions after the original deployment.  It will be even more important using poco without a model file. ...Stefan

• Anonymous
  September 10, 2008
  The comment has been removed

• Anonymous
  September 10, 2008
  Ce n'est pas un scoop, avec EF V2, il sera possible de générer la base à partir d'un EDM. Alex James

• Anonymous
  September 11, 2008
  This is great! I've had many fellow developers ask about this option. Glad to see the design team taking the feedback from the community and acting on it. Good job!

• Anonymous
  September 11, 2008
  I'd definitely like to see this, and the WF approach is a nice touch.  I would like to cast a vote for some migration approach, though.  Here's the deal: every database app needs some sort of migration ability, but not every team can afford the $10,000 price tag for Team Suite.  Diff tools work ok, but I'd like to see it built into the design tools.  Built in to the Model Designer would be awesome.  For example, if I rename a field, it should track that and gen ddl for renaming the field in the db. While I'm wishing, I'd like to see more non-1:1 support.

• Anonymous
  September 11, 2008
  It's good to see that our calls are heard :)

• Anonymous
  September 11, 2008
  The comment has been removed

• Anonymous
  September 11, 2008
  I don't understand the need or reasons for re-generating the SSDL. I would prefer to see the designer have a "logical view" (CSDL) and "physical view" (SSDL) and the ability to switch between the two. Generating the db should be done from the SSDL but without the first "generate SSDL from CSDL" step - that step effectively nullifies the whole idea of having a two layered model. The SSDL will also need to incorporate definitions of basic db side objects such as indexes, constraints (besides the already existing FKs), table- and column attributes, etc etc. If you are going to generate the SSDL from the CSDL and then the db from the generated SSDL you are effectively throwing away all potential benefits of having a layered model, and will also end up with a database with no indexes, no check constraints etc etc (and might as well use flat files for storage rather than a relational database engine in that case). Just my humble opinion...

• Anonymous
  September 12, 2008
  This is a great step! How would many to many associations be handled? I should be able to assign a M:M between two entities and you guys would gen a table with the combined names of each entity. Also, for a future blog, can you guys please explain how you're going to tackle the problem of model complexity. We really, really need the ability to divided up the model into domains so it's digestable and usable amongst multiple teams. Sorry for the off topic post but please address this issue in V2.

• Anonymous
  September 12, 2008
  Kristofer, Thank you for the feedback. We respectfully disagree that the advantages of a layered system are lost with this approach: Model-First in this release is there to help you get started. Once your schema has stabilized, we expect most database changes to go through a dba or some mechanism that does not involve the model. In addition, since the DDL generation is template-driven, you can change the way we generate indexes, add constraints, and so on: Annotations from the CSDL will be available for you to use while generating DDL. If you would like to actively "author" SSDL, the best way to do that is to use an existing database authoring tool, and then to reverse engineer that database into a model that you can then customize.

• Anonymous
  September 12, 2008
  Travis, Yes, you are correct, we will create a new table for each m:m association, as well as the necessary foreign key constraints. Model complexity is a big topic, but the first step towards dealing with it is enabling users to create multiple views of their models - much like the multiple diagram support in SSMS. At this time, however, this feature will not make it into the next release. This was a painful decision, but had to be made given the resources at hand.

• Anonymous
  September 12, 2008
  EFDesign, Re. "Once your schema has stabilized, we expect most database changes to go through a dba or some mechanism that does not involve the model." Nice in theory, but is that how it works in the real world? I'm sure that's the way it is done in some organizations, but I am also sure that those organizations account for a minority of the Visual Studio users. In most small and medium size dev shops the developers are also the DBAs. You're right that the best way to do this today is to use a data modelling tool such as erwin, erstudio, or visio and then reverse engineer the model from the db. But you now have the chance to "do away" with those tools by building that functionality into the EF Designer. Having a half-baked design where the EF can somehow build "half" of the data model can only achieve two things: a) database aware developers will not use that feature and will instead take your advice to use one of the traditional data modelling tools for their model b) database un-aware developers will use it to generate databases with poor/inefficient/incomplete schemas Instead, I would like to see the EF designer not only support database modelling but also assist the developer when it comes to data modelling. Now - for the first time - the modelling tool can have "all information at hand", from db schema to also being aware of the application side of things; what queries will run against the database etc etc. The SSDL today already describes the tables, columns (with sql data type) foreign keys and the primary key for each table. Besides time constraints for the EF dev team, I can not imagine one good reason for not including the rest. Indexes etc is on one hand good for the "generate database" feature, but even more important during development to assist developers in writing optimal queries, or at least queries that will not go off on a 10 million page table scan in search for a single record...   (I have a couple of good real-world examples of that - all that could have been prevented already in the development tool with a bit more intelligence.) I fully understand the time constraint factor leaving these out for v1, v2, and possibly v3. But I also think you should leave out the "generate database" thing until the basic parts of the model are in place, and instead focus your efforts on more needed basic modelling functionality. Oh, and I know - I'm like an old record player repeating the same things over and over. But that's just because I am convinced that a couple of small changes can improve the usefulness of the EF designer [by a factor of magnitudes]. Here are a couple of links to the same thing but in more detail and with a few more suggestions: forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3794390&SiteID=1 forums.microsoft.com/Forums/ShowPost.aspx?PostID=3758534&SiteID=1 forums.microsoft.com/Forums/ShowPost.aspx?PostID=3758604&SiteID=1

• Anonymous
  September 12, 2008
  Great to see that work on the designer is progressing!! So far, this feature looks excellent. The integration of templates and the workflow are great, and should provide the kind of flexability and adaptability we developers need in tools from Microsoft. I'm excited at the direction the EF team is taking. I have a couple questions about this feature, if you guys would be so kind enough to answer them.

1. Are you only intending to support databases from an initial generation standpoint? Or will this feature also generate change scripts to modify tables after the initial generation as well? If so, what kinds of configurable options will we have so we can, say, prevent deletion of columns that exist even if they are deleted from the model, when they may still be used by some other processes or applications?
2. Will there be any kind of support for managing the schema somewhat independantly from the conceptual model? Currently the designer only supports editing the conceptual half when in reality there are two halves to O/R mapping. If we are moving into the arena of generating/updating a schema from the model (which is awesome, something DDD fans will LOVE)...it seems it would be important to allow both halves of the model to be edited visually. I am not asking for full-blown DBA features like index management, constraints, etc. However, we do need the ability to manage keys for views or SSDL entities created with DefiningQueries (so, if afte our initial generation, we happen to do an Update-From-Database, we don't loose our key definitions). We also need better control over specifying store-generated patterns (on that note, Identity and Computed are fairly missleading...GenerateOnInsert and GenerateAlways might be better terms that accurately reflect the two kinds of store-generated patterns), and some way to create an SSDL entity from a SQL query visually. Well, enough of my questions for now. EF v2 is shaping up pretty nicely I think, and it looks like you guys are really aiming to meet some of the most-requested needs of the developer community. I look forward to see what other capabilities you guys add to the designer, and hope to hear more about POCO/PI in the near future (here is to hoping that the designer will support POCO classes, so we don't have to edit the xml manually if we choose to take that route).

• Anonymous
  September 14, 2008
  Will there be an option to generate tables using singular names?  It's a big turn-off I can't do that easily.

• Anonymous
  September 14, 2008
  @AW2008, While waiting for a singularization feature from MSFT you may want to try out my add-in that can do that for you. You can download it and get a trial license from www.huagati.com/dbmltools (it adds functionality both to the EF designer and the L2S designer)

• Anonymous
  September 15, 2008
  "Will there be an option to generate tables using singular names?  It's a big turn-off I can't do that easily." The current plan is for table names to match the entity set name, so if you do not pluralize those, you will get singular table names. In addition, you will be able to edit the DDL generation template, and if you want to use the entity type names as the table names, that should not be a problem. HTH.

• Anonymous
  September 16, 2008
  "The generated DDL will not migrate data or schema - by default your database will be recreated from scratch." I am excited about this feature as I use other tool which provides this function. I am worried about this statement. Will this mean that I will not be able to add any objects to my model? Will there be option just to update existing DB with new/modified objects? Cheers

• Anonymous
  September 16, 2008
  "The current plan is for table names to match the entity set name, so if you do not pluralize those, you will get singular table names." Lack of proper pluralization/depluralization support in the generator is a huge problem. Not just for entity names, but for navigation properties too. When you have a 1 or 0..1 multiplicity, but the property name is plural by default, thats extremely missleading. It's also highly undesirable to have plural entity names, ever...its a singular instance taken from a collection, they should be singular by default. Conversely, collections represent multiple entities, and should by default be plural. Matching the names is the worst possible solution, because they do not produce names that accurately represent the item of interest. From what I have been told in the past on the EF forums at forums.microsoft.com/msdn, better pluralization support was planned for the next release. Has that changed? Because currently, having to dig through my model to find entities with incorrect pluralization is my greatest pet peeve, and I know I am not alone on that. Entity set names and entity names should NOT be matched...of all the possible solutions, that is the worst one to choose. :'(

• Anonymous
  September 17, 2008
  Regarding to Message:

Noam Ben-Ami said on September 10, 2008 10:00 PM:

" If you are running POCO without a model file, then that scenario is a little more tricky and we are still working out what will and will not be supported." If you dont suppoty database schema generation from a POCO (persistance ignorance) like Nhibernate does. Entity Framework DON'T support persistance ignorance. Am i wrong? Or im missing some point?

• Anonymous
  September 17, 2008
  Persistence Ignorance is a very big topic meaning many different things to different people.  Probably the first and most important part of that is whether or not your POCO classes need to have any knowledge of their persistence mechanisms.  We're working to make it so that in v2 this won't be required (if you do add some knowledge or modify the structure of your classes slightly here or there, then things can be faster, but it's not required).   A separate step is the question of whether or not other code on the outside of your entity classes needs configuration or anything else.  Those are things we would also like to support at some point, but it's just not clear that they will make it in v2.  We're doing lots of things in v2, and I think folks will see considerable progress, but just like with v1 there are many features that we are balancing across a tight schedule (we started v2 a lot later than most of the .net / vs folks because we were so focused on getting EF v1 out the door with sp1).  The result is that v2 will be the next installment on the multi-release path to where we want to get the product.

• Danny

• Anonymous
  September 20, 2008
  Jrista, Pluralization and depluralization should be supported in v2. That should include the wizard, so that plural table names will be singularized. For database generation, we will match the names of the sets, and we are planning on providing context menus that will allow you to batch pluralize set names as well as singularize type names. HTH,   Noam

• Anonymous
  September 25, 2008
  I'm thinking of throwing an exception after changing entities key. I wonder how can an association in a many-to-many relationship be changed as far as Assignment table almost always has a compound primary key. Should I delete an old record first and insert a new one into the same table for implementing this task?

• Anonymous
  September 25, 2008
  If your many-many link table has only the two foreign keys and no other columns, then it should be optimized out of your model so that you just interact with collections on the two outside entity types. If, however, you are simulating a many-many relationship with a payload by having a middle entity that has two associations to the two outside entities, and if that entity's key is a compound key made up of the foreign keys to the other entities (so that you have an RI-constraint in the conceptual model), then yes you will need to delete the middle entity and add a new one if you want to change the relationship.

• Danny

• Anonymous
  September 29, 2008
  Daniel, thanks for your reply. Now I have an issue with three tables referenced with many-to-many relationship and the approach you've described before doesn't seems to work as referential exception is thrown while deleting and inserting a new record into the link table. Maybe I have some kind of misunderstanding? Could you please explain.

• Anonymous
  September 30, 2008
  Maksim, It would be better to continue this discussion in the entity framework forum (http://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=533&SiteID=1) rather than here in the EF Design blog comments.  That way more people will see your question both to help answer it and to benefit from the answer. Would you please repost there? Thanks, Danny

• Anonymous
  October 06, 2008
  The comment has been removed

• Anonymous
  November 12, 2008
  Hey, it looks like this has not made it into the VS 2008.. Is this correct?

• Anonymous
  December 02, 2008
  Unfortunately in the current version of the Entity Framework, which ships in .NET 3.5 SP1, we don't make

• Anonymous
  December 08, 2008
  This will be a great improvement. However I think it isn't a good idea to define the inheritance mapping strategy in a template. Different mapping strategies are often necessary within one schema. As Kristofer, I would prefer to have the storage schema editable in the designer and the DDL generated from this storage schema.

• Anonymous
  January 02, 2009
  Hello! I use Visual Studio 2008 with SP1 in Vista Business SP. I was created a model from empty model, but I can´t see the option "Create Database from Model". Please, there's something more I must have in my computer?

• Anonymous
  January 02, 2009
  @Lucio, This is a feature we will be putting into the next release of the Entity Framework which ships with VS 2010 and .net 4.0.  It's not yet available in VS2008 SP1.  Sorry.

• Danny

• Anonymous
  March 02, 2009
  So if you've been reading the Entity Framework Design Blog you will have heard us talk about T4 . It

• Anonymous
  March 16, 2009
  Background A number of months ago we asked whether Foreign Keys (FKs) in Conceptual and Object models

• Anonymous
  March 19, 2009
  Thanks for the update. Are there plans to be able to automatically update an existing database from the entity model without heaps of steps through a wizard? What we want to be able to do is send out our updated application to our clients that contains a modified entity model and when they run the app up for the first time it should check the entity model against the SQL Server database that they use and insert/update any new fields/tables without any fuss. What are my chances? :o) Thx.

• Anonymous
  April 07, 2009
  Sorry for the DPMud hiatus.  Here’s another installment for those interested in the process. 

• Anonymous
  May 01, 2009
  How did I miss this ? Jonathan Carter (aka LostInTangent ) has done a Channel 9 video on our upcoming

• Anonymous
  June 03, 2009
  Background A number of months ago we asked whether Foreign Keys (FKs) in Conceptual and Object models

• Anonymous
  June 21, 2009
  So if you've been reading the Entity Framework Design Blog you will have heard us talk about T4