23 07 2009
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 newGenerate 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.
- Click right mouse button on model and select Generate Database from Model, as shown on picture below.
- Specify database connection and click Next.
- Copy generated SQL script to SQL Server Management Studio and run it.
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):
- new store schema definition (SSDL) will be generated,
- new mapping specification (MSL) will be generated,
- EDMX file will be updated using previously generated SSDL and MSL,
- script with SQL will be saved to location you show,
- 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):
- 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,
- no primary keys were generated,
- 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:
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 🙂