Entity Framework 4.0: Generating SQL script from model

Entity Framework 4.0 is able to generate database schema based on model. If you built your model first and you now want to create database for it you can use new Generate Database Script from Model feature. Let’s see how it works.

I will use my example gallery model. Let’s assume we have model opened in Visual Studio 2010.

  1. Click right mouse button on model and select Generate Database from Model, as shown on picture below.

    Entity Framework 4.0: Generate Database Script from Model

  2. Specify database connection and click Next.

    Entity Framework 4.0: Choose Data Connection

  3. Copy generated SQL script to SQL Server Management Studio and run it.

    Entity Framework 4.0: SQL script

When you decide to save changes when clicking on Finish button then the following happens (taken from Gil Fink blog posting Model First in Entity Framework 4):

  1. new store schema definition (SSDL) will be generated,
  2. new mapping specification (MSL) will be generated,
  3. EDMX file will be updated using previously generated SSDL and MSL,
  4. script with SQL will be saved to location you show,
  5. new connection definition will be added to application configuration file.

I found some issues with POCOs I use (read more from my blog entry Entity Framework 4.0: How to use POCOs):

  1. table names are not correct: instead of gallery_item, album and photo I got gallery_item, gallery_item_Album and gallery_item_Photo as result,
  2. no primary keys were generated,
  3. no foreign keys were generated.

It is possible that this is limitation of Entity Framework 4.0 because it is still in beta. But it may also be some hard-to-trace problem deep inside my model definition. I will update this posting as soon as I find out why I got those problems with POCOs.

2009-08-07 Update I got the following very promising answer from Microsoft Connect to my question:

Thank you for your feedback. At this point in time we are not able to make significant changes to the system. So instead, I will explain what is going on. Specifically, in response to this part of the bug filing:
"One thing that occurs always is problem related to table names - they are incorrect in generated SQL. Example is here:

1) Correct: album, Generated: gallery_item_Album
2) Correct: photo, Generated: gallery_item_Photo"

What happens here is that we take the name of the set associated with the root type in your hierarchy, in this case "gallery_item", and we prepend it to the name of the type.

The idea was (and it may not have been right, but it is what we have now) is to communicate what is actually happening. In this case, the "gallery_item_Album" table holds all members of the "gallery_item" set that are of type Album. So if you rename your set to, for example "GalleryItems" then you will get three tables:

GalleryItems,
GalleryItems_Album
GalleryItems_Photo

All instances of Photo are created by joining GalleryItems with GalleryItems_Photo.

Note that you can always write your own strategy or download someone else Database Script Generation Workflow file. We, and others, are working on other workflows that will, for example, generate a single table for all members of a set, which obviates this problem. For now, this behavior is most likely what will be shipped.

So, guys, cool things are coming, we just have to wait a little bit :)

kick it on DotNetKicks.com pimp it Progg it 顶 Shout it

4 Comments

  • This is a cool feature of EF 4.0. Not sure how well does it work in a real project. Thank you for providing the info.

  • Thank you for feedback, Nam!

    I think this feature is very new and not 100% stable to use. But it works some how. I think it works correctly with automatically generated classes. I hope that with first release of VS2010 EF4.0 has 100% support for POCOs implemented correctly. But this is just my hope :)

  • Unless I'm missing something it appears EF is setup to generate islands of data, not a complete data model. If this is the case, it will fail as a tool. It needs to be able to take multiple entities that are not directly tied together and generate a normalized data model from it similar to how Deklarit works. Deklarit operates on the principle of Universal Relation Assumption that assumes that attributes between entities with the same name are the same. And with URA it is able to generate a complete and normalized data model. I use Deklarit for data modeling and to generate normalized database schemas, but use my own code base to build applications.

  • Thanks for feedback, John :)

    EF is able to generate the whole model as far as I know. You define your classes and their database relations on model designer and it is able to generate database creation script. I am not very sure if database field and table names are required in final version of EF4.0

Comments have been disabled for this content.