none
create database from Entity Data Model

    Question

  • Is it possible once I have created my ADO.NET Entity Data Model to automatically generate the appropiate database (with all the required tables and associations) out of this Data Model Definition (edmx) ? Is there maybe a tool for this?

     

    It would seems quite tedious to me to setup my datamodel twice. Once in SQL and once in edmx.

    Friday, August 15, 2008 11:26 AM

Answers

All replies

  • This capability does not exist in version 1 of Entity Framework.

     

    Folks are starting to use T4 Templates to generate TSQL from the model . HEre's an example

    http://blogs.rev-net.com/ddewinter/2008/04/26/entity-framework-stored-procedure-generation/

     

    Maybe someone will do the same eventually for creating a whole database. (or has already done so, but I just don't know about it. :-)).

     

    hth

    julie

    Friday, August 15, 2008 3:39 PM
  • We are working on providing this capability in the next release of the tools. In the meantime, T4 templates seem like a good approach: It is the primary one that we are evaluating internally right now.

    Friday, August 15, 2008 4:37 PM
  • Since the EDMX / SSDL / designer is totally index-unaware (except for PKs) this is a feature that would be not-so-good for the database. If they add index definitions to the SSDL (and designer) then it would be a useful feature.

     

    And if they add all of: index support, udf support, logical/physical separation (at the SSDL layer), _and_  (most importantly) multi page/multi tab support in the designer then it could even replace traditional data modelling tools (ErWin/ErStudio/Visio/...). I won't hold my breath waiting for that though...

     

    Saturday, August 16, 2008 3:10 AM
  • KristoferA,

     

     You make a good point: The CSDL does not provide all of the information required to construct a fully optimized physical layer. Additionally, SSDL is not a physical layer description format, nor, I would argue, should it be: There are enough such formats out there, DDL being a great db-specific one, and DataDude's dbschema being a great db-neutral one.

     

     That aside, once you've generated the DDL, you should be able to add another DDL file which specifies indexes and other physical artifacts to the deployment process.

     In addition, the template-based approach to DDL generation will give you a great deal of flexibility in terms of how the DDL is generated, you could even modify the templates to generate your physical layer concerns, if you were so inclined.

     

     For the sake of openness, we've got a design in place for multi-diagram support and we share your desire for it. Right now we are prioritizing things like DDL generation from models, complex type support, and better sproc support over that feature. The reason being that we feel that multiple diagram support is a usability issue, whereas the others are more fundamental enablers.

    Saturday, August 16, 2008 11:17 PM
  • Hi Noam,

     

    Thanks for your reply.

     

     Noam Ben-Ami // MSFT wrote:

     You make a good point: The CSDL does not provide all of the information required to construct a fully optimized physical layer. Additionally, SSDL is not a physical layer description format, nor, I would argue, should it be: There are enough such formats out there, DDL being a great db-specific one, and DataDude's dbschema being a great db-neutral one.

     

    I think you got CSDL and SSDL mixed up in that paragraph. SSDL defines the database schema [or..at least some portions of it]. Anyway, whoever defined the SSDL format has apparently not done a lot of work with databases as they left out many important things; indexes being the most serious blunder in case you are going to use this thing in reverse (i.e. generate ddl from the edmx/ssdl)

     

     Noam Ben-Ami // MSFT wrote:

     That aside, once you've generated the DDL, you should be able to add another DDL file which specifies indexes and other physical artifacts to the deployment process.

     In addition, the template-based approach to DDL generation will give you a great deal of flexibility in terms of how the DDL is generated, you could even modify the templates to generate your physical layer concerns, if you were so inclined.

     

    Index definitions should be available already in the early design process, and thereafter through all iterations. If using this designer for data modelling it should be a basic requirement. Manually adding them "in another ddl file" is a bit contradictory to the whole concept of doing any form of modelling/design work. Thankfully there are plenty of data modelling tools out there that can be used to model and generate databases from. At the moment the EDMX designer is not up to the task [in my opinion].

     

     Noam Ben-Ami // MSFT wrote:

     For the sake of openness, we've got a design in place for multi-diagram support and we share your desire for it. Right now we are prioritizing things like DDL generation from models, complex type support, and better sproc support over that feature. The reason being that we feel that multiple diagram support is a usability issue, whereas the others are more fundamental enablers.

     

    Openness is great. Do you have a URL to share to the design you mention?

     

    Some of the things you mention sound a bit contradictory. From "SSDL is not a physical layer description format, nor, I would argue, should it be" to "Right now we are prioritizing things like DDL generation from models". So... which one is it?

    Monday, August 18, 2008 3:31 AM
  • I think you got CSDL and SSDL mixed up in that paragraph. SSDL defines the database schema [or..at least some portions of it]. Anyway, whoever defined the SSDL format has apparently not done a lot of work with databases as they left out many important things; indexes being the most serious blunder in case you are going to use this thing in reverse (i.e. generate ddl from the edmx/ssdl)

     

    [Noam:] No, SSDL is a runtime artifact used by the runtime to generate SQL. It is not intended to be a physical database description. It contains features that are entity-framework specific, such as CommandText and DefiningQuery.

     

    Index definitions should be available already in the early design process, and thereafter through all iterations. If using this designer for data modelling it should be a basic requirement. Manually adding them "in another ddl file" is a bit contradictory to the whole concept of doing any form of modelling/design work. Thankfully there are plenty of data modelling tools out there that can be used to model and generate databases from. At the moment the EDMX designer is not up to the task [in my opinion].

     

    [Noam:] The designer's purpose is not, and probably never will be, to describe the physical aspects of a database. There are other tools for doing this and the vision is to make them work well with the designer. This will take a bit of time. So the workflow that we envision is: Create your ER model in the designer. Generate the DDL or DBSchema and expand on that in a tool designed to do physical database work.

     

    Openness is great. Do you have a URL to share to the design you mention?

     

    [Noam:] Given the fact that we are not yet putting resources to work on that feature, that would be premature. That said, the efdesign blog is where the team has been posting designs for features we are working on.

    Now, that said, the current proposed approach to multiple diagrams is quite similar to the one that SSMS has taken, so that should tell you just about everything you could want about our thinking on this.

     

    Some of the things you mention sound a bit contradictory. From "SSDL is not a physical layer description format, nor, I would argue, should it be" to "Right now we are prioritizing things like DDL generation from models". So... which one is it?

     

    [Noam:] I see no contradiction here: We are providing a quick way to create a baseline database schema from the CSDL (not the SSDL, although we do generate the DDL from the SSDL) with the expectation that users will customize the results for production work. Hope this is making a bit more sense...just remember, we're still at v1 here.

     

    So my question to you is - given that the designer is an EDM designer and does not contain physical database information, and given the existence of other products for the latter, such as SSMS and Datadude, how would you want the database generation workflow to function? Your opinion would be much appreciated!

    Monday, August 18, 2008 4:36 AM
  •  Noam Ben-Ami // MSFT wrote:

    [Noam:] No, SSDL is a runtime artifact used by the runtime to generate SQL. It is not intended to be a physical database description. It contains features that are entity-framework specific, such as CommandText and DefiningQuery.

     

    Ok, I have probably misunderstood something then.

     

    I think my misunderstandings are based on a few things:

     

    a) SSDL was the only place in the edmx where I could find anything that even remotely described - from a database perspective - tables, columns etc. CSDL looks to me like class and property definitions.

     

    b) I misinterpreted the first couple of sentences in the EDM SSDL docs over at MSDN as the SSDL being a db schema description.

    "The storage metadata schema is a formal description of the database that persists data for an application built on the Entity Data Model (EDM). The entities and associations declared in this schema are the basis for mapping entities and associations in the conceptual schema to the corresponding entities in the storage model."

     

    c) I also misinterpreted the first couple of sentences in the EDM CSDL docs as the CSDL being class definitions for the object model rather than a database schema definition.

    "The conceptual schema is a design template for the object model that will be used by applications built on the Entity Data Model (EDM). The namespace name declared in the conceptual schema will be the namespace name that qualifies the entity and association classes in the generated object model."

     

    Sorry for my misinterpretation - it is easy to do when trying out new tools.

     

     Noam Ben-Ami // MSFT wrote:

    [Noam:] The designer's purpose is not, and probably never will be, to describe the physical aspects of a database. There are other tools for doing this and the vision is to make them work well with the designer. This will take a bit of time. So the workflow that we envision is: Create your ER model in the designer. Generate the DDL or DBSchema and expand on that in a tool designed to do physical database work.

     

    Does your design process incorporate use-case-studies (peeking over the shoulders of developers to see how they use the tools)? If not, I would recommend doing that at a number of different dev shops to get an idea of how these things are used in the real world. Of course this couldn't be done pre-RTM, but now that it has been RTMed there should be plenty of opportunity to do this...

     

    Also, playing around a bit with Visio for Enterprise Architects, ErWin, and ErStudio might give some feature ideas for the designer. Visio for EA is a Microsoft product so I'm sure you'll find that one somewhere on your network. The latter two are third party products, but they do have 14-day trial versions that you can use just for trying them out.

     

     Noam Ben-Ami // MSFT wrote:

    [Noam:] I am afraid that I see no contradiction here: We are providing a quick way to create a baseline database schema from the CSDL (not the SSDL, although we do generate the DDL from the SSDL) with the expectation that users will customize the results for production work. Hope this makes sense.

     

    I'm now even more confused. You generate DDL from the SSDL? But the database schema from the CSDL? What - besides the ddl - makes up the definition of the database schema?

     

    I stick to my point that indexes is a vital part early on in the design process, so if generating the database schema / ddl from the EF designer it must - at a minimum - allow indexes to be defined. How else are you going to keep track of what indexes already exist or what indexes may be needed as you are writing code? How else can you know if your where clauses and joins match existing indexes when writing queries against large tables? Jot it down in MS Visual Notepad on the side? Or SQL Profiler looking for table scans and queries doing excessive IO once the software hits production?

     

    For now, the only workflow approach that make any sense to me is:

    a) Define your database schema in a data modelling tool (ErWin, ErStudio, Visio, ...).

    b) Import into the EF designer.

    c) Keep the data modelling tool open while writing code, for reference and for making any schema changes deemed necessary while writing code [e.g. adding indexes etc]
    d) Update the EF designer from the database whenever the database has been updated from the data modelling tool.

     

    It would be nice/desirable to get rid of the external database modelling tool and instead define both the database schema and object model inside visual studio, but I can't see how to practically do that today...

    Monday, August 18, 2008 5:55 AM
  • 1. If you think of the SSDL as an artifact that allows the EF runtime to generate SQL, then you will understand why it doesn't include things like indexes and why it includes things that aren't in database schemas, such as CommandText.

     

    2. The description in the docs is...um. Not what I would have written. The CSDL is a description of the Entity Data Model, along with anotations used by systems layered on top of the EDM, such as the code generation system. IT IS NOT A DESCRIPTION OF THE CLASSES. There is, in fact, a Conceptual->Object mapping layer that is part of the Entity Framework. This is an important point: You can interact with/query the entity data model without ever creating classes by using the EntityClient layer of the system.

     

    3. Sorry for the confusion about how DDL gets generated, here is one database generation workflow that we envision:

     

    a. User authors the CSDL using the designer.

    b. Using a strategy such as TPH, the SSDL is generated for the CSDL.

    c. The SSDL is converted into a dbschema file using a 1:1 "mapping".

    d. The dbschema is converted to DDL.

    e. The DDL is deployed to the database.

     

    So, there are a few options here for adding indexes:

     

    a. You can add your own DDL file that defines indexes and deploy it as part of the DDL deployment process.

    b. You can use annotations to indicate which entity type properties should have indexes and modify the dbschema file to generate indexes when it sees these annotations.

     

    If indexes are so critical, then we could try to add them to the designer - there are some other more important "physical" properties we do need to add, so a boolean "Is Indexed" property might be a small enough feature that we could fit it in. I will bring this up for discussion.

     

    Thank you!

      Noam

    Monday, August 18, 2008 7:18 AM
  • I would like to apologize to the original poster that started this thread for "hijacking" the thread. By now it is probably time to spin off the discussion between me and Noam in a separate thread so I'll do just that. (And this thread is once again about _how_ to generate DB schemas from EDMX files)

     

    Continued here: http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3758604&SiteID=1

    Monday, August 18, 2008 11:35 AM