Sneak Preview: Model First in the Entity Framework 4.0

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.

An exciting new Entity Framework Designer feature in Visual Studio 2010 is the ability to generate DDL that will create a database that can store your entity data model. This feature is not only exciting because it brings a long overdue capability to life, but because it is fully extensible: You can take control of the entire process, or plug into parts of it. For example, you can take over the DDL generation step to add support for your database of choice, or to customize the DDL generation process. Alternatively, you can take over the inheritance mapping step and replace our out-of-the box strategy (which is table-per-type) with your own. Or take over the entire mechanism and generate both stored procedure mappings and CRUD stored procedures for all your types. In a future post, we will show you exactly how to do this and provide an implementation of the table-per-hierarchy mapping for you to use or modify.

For now, let’s take a quick look at this feature by taking a very simple model:

We right click…and in the context menu we see, and click on, “Generate Database Script from Model”:

The database generation wizard asks for a database to target. This information is used by the wizard to determine which database-specific types to translate the Entity Data Model (EDM) types to, and how to render DDL that is appropriate for the target database. In this case, I just created a new Microsoft SQL Server database file:

Once we click on next, we are shown a preview of the generated DDL, which will be saved to disk once we click on “Finish”. Here is an excerpt from the DDL generated now for SQL Server:

 …
-- Creating table 'IngredientSet_Protien'
CREATE TABLE [dbo].[IngredientSet_Protien] (
    [Cut] nvarchar(100)  NOT NULL,
    [Type] nvarchar(70)  NULL,
    [Id] int  NOT NULL
);
GO
-- Creating table 'IngredientSet_Plant'
CREATE TABLE [dbo].[IngredientSet_Plant] (
    [IsFruit] bit  NOT NULL,
    [IsHerb] bit  NOT NULL,
    [IsVegetable] bit  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'RecipeSet'
ALTER TABLE [dbo].[RecipeSet] WITH NOCHECK
ADD CONSTRAINT [PK_RecipeSet]
    PRIMARY KEY CLUSTERED ([Id] ASC)
    ON [PRIMARY]
GO
…
-- --------------------------------------------------
-- Creating all Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [Chef_Id] in table 'RecipeSet'
ALTER TABLE [dbo].[RecipeSet] WITH NOCHECK
ADD CONSTRAINT [FK_ChefRecipe]
    FOREIGN KEY ([Chef_Id])
    REFERENCES [dbo].[ChefSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION
…

Note here that the default inheritance mapping strategy is to create a table for all subtypes: In the above DDL, you can see that we are generating tables for the Protien and Plant subtypes. These tables are named with the name of the EntitySet in which the types live, to which we append the name of the type itself.

Note also that we generate primary and foreign key constraints for the model.

Watch for our in-depth series on Model First with the Entity Framework that we are working on publishing in the coming weeks.

- Noam Ben-Ami
Program Manager, Entity Framework

Comments

• Anonymous
  May 12, 2009
  PingBack from http://asp-net-hosting.simplynetdev.com/sneak-preview-model-first-in-the-entity-framework-40/

• Anonymous
  May 12, 2009
  Thank you for submitting this cool story - Trackback from DotNetShoutout

• Anonymous
  May 12, 2009
  Thank you for submitting this cool story - Trackback from progg.ru

• Anonymous
  May 12, 2009
  You mean you are creating table per type. But do you support generation for TPH?! This is a cool feature! but I wonder if DBA or DB professional would love the generated tables out of the models! Maybe if we could later not to generate the tables, but map them to existing database tables would also be a good option. Waiting for the rest of the series!

• Anonymous
  May 12, 2009
  What if I add a new field to an entity, or alter the type of an entity, or add a relationship... is the designer capable of generating DDL SQL which updates the schema or does it generate a new complete schema? Because if I just remove a field for example, why would I want to re-generate a complete schema? I then have to wade through all the DDL SQL to see what's changed...

• Anonymous
  May 13, 2009
  I have started talking to developers about what they can expect in Entity Framework V2 such as in my

• Anonymous
  May 13, 2009
  What to be Expecting of Entity Framework in .NET 4 The ADO.NET team started to release a series of posts

• Anonymous
  May 14, 2009
  Windows Azure/Azure Service Framework (including .NET Services)/BizTalk David Pallmann has released 2.1 of Azure Storage Explorer with the new feature of modifying what is in storage including create or delete blob containers, blob items, queues, queue

• Anonymous
  May 19, 2009
  Hi; My question is the same as Frans question. When the model changes what steps are offered by EF4 to maintain the changes to the Dabase Schema and the Data as well?

• Anonymous
  May 20, 2009
  My question is the same of Frans and BenHayat.

• Anonymous
  May 21, 2009
  In response to the question from Frans, BenHayat and Alexnaldo on generating migration scripts:

1. We do not offer this capability from the designer. The intent is that existing Microsoft or third party tools be used to diff the DDL against the database.
2. The system is sufficiently extensible that this functionality can be added in. We may release bits that do this as a codeplex project (or some other public release mechanism.)
3. We have gotten sufficient feedback from many sources on this that we will investigate making this functionality a code part of the product as soon as is reasonably possible. Cheers, Noam

• Anonymous
  May 29, 2009
  Model-first (with Entity Designer) but what about programatically? The question has showed up a few times (how to programatically create a conceptual model for consumption by other tools) yet no clear-cut solution appears available in EFv2. Any news about this in EF4?

• Anonymous
  May 29, 2009
  Miguel, The model-first process is implemented using a XAML file which can be executed via code. We are using a T4 template to generate DDL, however, and that has a Visual Studio dependency. We will work on a walkthrough for showing how to run the process programmatically.

• Anonymous
  May 29, 2009
  Noam, If I interpret correctly that XAML file takes as starting point either (a) an .edmx file, or (b) a conceptual model already loaded in EF Designer. If that's the case, I'm having some difficulty in seeing how to programatically generate (a) or (b) in a type-safe manner (i.e., without resorting to piecing together XML strings). I guess I should have mentioned that I'm aiming for a batch migration of a datamodel into EDM, without the need to layout the resulting conceptual model in EF Designer. Miguel

• Anonymous
  June 06, 2009
  Will there be the ability to specify which columns will be indexed in the database? If not, then we still have to go in and edit the "auto-generated" sql file to add those. (and again anytime the model changes and the sql file is regenerated)

• Anonymous
  June 10, 2009
  There are currently two ways to get Entity Framework apps up and running, we call these Database First

• Anonymous
  June 10, 2009
    Entity Framework 4.0 Beta 1（又称EF V2）与 .NET 4.0 Beta 1 一起发布，包含了一系列的重要改进：自定义代码生成、延迟加载、N层支持、POCO支持

• Anonymous
  June 11, 2009
  Hi Noam, Possibly silly question.  How does this differ from gnerating DDL from visio, or other modeling tools?

• Anonymous
  June 12, 2009
  The comment has been removed

• Anonymous
  June 14, 2009
    There are currently two ways to get Entity Framework apps up and running, we call these Database

• Anonymous
  June 28, 2009
  あなたの精神年齢を占ってみよう！当サイトは、みんなの「精神年齢度」をチェックする性格診断のサイトです。精神年齢度には、期待以上の意外な結果があるかも？？興味がある方はぜひどうぞ

• Anonymous
  July 19, 2009
  The question has showed up a few times (how to programatically create a conceptual model for consumption by other tools) yet no clear-cut solution appears available in EFv2. Any news about this in EF4?

• Anonymous
  July 23, 2009
  I have question. What kind of classes you used for this example - automatically generated ones or POSOs? I tried this example out with POCOs and no primary and foreign keys were created.

• Anonymous
  October 15, 2009
  Hi Noam, Can we generate DDL for sql server from xml schema via Entity Framework. If so, how?  I can use WSCF.blue to create objects from xml schema, but i am not sure how to generate model from the objects created.