Model First For SQL Azure - SQL Azure Team Blog - Site Home - MSDN Blogs

Model First For SQL Azure

Model First For SQL Azure

  • Comments 9

One of the great uses for ADO.NET Entity Framework 4.0 that ships with .NET Framework 4.0 is to use the model first approach to design your SQL Azure database. Model first means that the first thing you design is the entity model using Visual Studio and the Entity framework designer, then the designer creates the Transact-SQL for you that will generate your SQL Azure database schema. The part I really like is the Entity framework designer gives me a great WYSIWYG experience for the design of my tables and their inter-relationships. Plus as a huge bonus, you get a middle tier object layer to call from your application code that matches the model and the database on SQL Azure.

Visual Studio 2010

The first thing to do is open Visual Studio 2010, which has the 4.0 version of the Entity Framework, this version works especially well with SQL Azure. If you don’t have Visual Studio 2010, you can download the Express version for free; see the Get Started Developing with the ADO.NET Entity Framework page.

Data Layer Assembly

At this point you should have a good idea of what your data model is, however you might not know what type of application you want to make; ASP.NET MVC, ASP.NET WebForms, Silverlight, etc.. So let’s put the entity model and the objects that it creates in a class library. This will allow us to reference the class library, as an assembly, for a variety of different applications. For now, create a new Visual Studio 2010 solution with a single class library project.

Here is how:

  1. Open Visual Studio 2010.
  2. On the File menu, click New Project.
  3. Choose either Visual Basic or Visual C# in the Project Types pane.
  4. Select Class Library in the Templates pane.
  5. Enter ModelFirst for the project name, and then click OK.

The next set is to add an ADO.NET Entity Data Model item to the project, here is how:

  1. Right click on the project and choose Add then New Item.
  2. Choose Data and then ADO.NET Entity Data Model

    image

  3. Click on the Add Button.
  4. Choose Empty Model and press the Finish button.

    image

Now you have an empty model view to add entities (I still think of them as tables).

Designing You Data Structure

The Entity Framework designer lets you drag and drop items from the toolbox directly into the designer pane to build out your data structure. For this blog post I am going to drag and drop an Entity from the toolbox into the designer. Immediately I am curious about how the Transact-SQL will look from just the default entity.

To generate the Transact-SQL to create a SQL Azure schema, right click in the designer pane and choose Generate Database From Model. Since the Entity Framework needs to know what the data source is to generate the schema with the right syntax and semantics we are asked by Entity Framework to enter connection information in a set of wizard steps.

Since I need a New Connection to I press the Add Connection button on the first wizard page. Here I enter connection information for a new database I created on SQL Azure called ModelFirst; which you can do from the SQL Azure Portal. The portal also gives me other information I need for the Connection Properties dialog, like my Administrator account name.

image

Now that I have the connection created in Visual Studio’s Server Explorer, I can continue on with the Generate Database Wizard. I want to uncheck that box that saves my connection string in the app.config file. Because this is a Class Library the app.config isn’t relevant -- .config files go in the assembly that calls the class library.

The Generate Database Wizard creates an Entity Framework connection string that is then passed to the Entity Framework provider to generate the Transact-SQL. The connection string isn’t stored anywhere, however it is needed to connect to the SQL Azure to find out the database version.

image

Finally, I get the Transact-SQL to generate the table in SQL Azure that represents the Transact-SQL.

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Entity1'
CREATE TABLE [dbo].[Entity1] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Entity1'
ALTER TABLE [dbo].[Entity1]
ADD CONSTRAINT [PK_Entity1]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

 

This Transact-SQL is saved to a .sql file which is included in my project. The full project looks like this:

image

I am not going to run this Transact-SQL on a connection to SQL Azure; I just wanted to see what it looked like. The table looks much like I expected it to, and Entity Framework was smart enough to create a clustered index which is a requirement for SQL Azure.

Summary

Watch for our upcoming video and interview with Faisal Mohamood of the Entity Framework team to demonstrate a start-to-finish example of Model First. From modeling the entities, generating the SQL Azure database, and all the way to inserting and querying data utilizing Entity Framework.

Make sure to check back, or subscribe to the RSS feed to be alerted as we post more information. Do you have questions, concerns, comments? Post them below and we will try to address them.

  • Steve, thanks for the post.

    Have you tried a more complex scenario, like indexing columns or default values?

    The reason I ask is because for my current project, I'm doing the reverse (build the schema first, then gen up the models), which feels more natural for me.  However, calling CreateDatabase() from the ObjectContext doesn't build the indeces and the default values into the model, so I'm guessing that you can't do it the other way around either.

    Thoughts?

    Thanks,

    Jayson

  • Hi,

    Things like adding indexes are interesting when calling CreateDatabase - they are things we are thinking about. One of the ways to think about it though is that adding indexes and such are something that could be done after the fact (and in fact is not a modeling concern but a physical db concern).

    Model First surely allows you to do this by extending the template that is used but we might make this possible in the future through the use of annotations or something like that via Code First.

    Also, regarding default values, we are thinking about scenarios where default values can be specified in the model itself, which will then translate to defaults in the DDL.

    Hope this helps.

    Faisal Mohamood | Program Manager | Entity Framework

  • This is an approach that interests me and scares me all at the same time. Its interesting because, like Jayson - I prefer to build the database and tables first then model, then wire it all up. It scares me because I have just handed control over to something else. Whilst the database schema that is generated matched the model, I do wonder how more complex scenarios would work.

    As for the index creation etc. On approach could be to let the EF model create your database, then run the SQL profiler against the database when it is in use every week. Then use the tuning wizard to create indexes and stats. The first week will capture enough data to build the majority of indexes that are required, the following week will begin the fine tuning.

  • We're leveraging the latest "bleeding-edge" technologies released from Microsoft, specifically Code-First development with Entity Framework 4, MVC 3 Preview 1, and SQL Azure.

    One of the repeating questions about CF-EF with SQL Azure is how best to handle the retry logic for connection failures.

    There is a recently published code snippet from MS on how to write such retry logic and exception handling (some ugly code in my opinion) using raw ADO.NET (SqlConnection, SqlCommand, etc), but we want to remain "clean" leveraging DbContext and DbSet entities provided within the Code-First pattern (ie, new DbContext, context.SaveChanges(), etc).

    Unfortunately, there do not seem to be any public examples or "best practices" documentation on how best to handle this scenario, while there remain many outstanding questions from other developers in the community as to whether an upcoming release of EF will handle this internally (which I think it should), or whether we should craft our own metholodogy for handling this in an elegant way.

    I mean, who knows better the intricacies of Entity Framework, Connection Pooling, and SQL Azure than Microsoft, right?  We need that guidance.

    I'd love to see someone present a real-world, tested, example of using Code-First Entity Framework 4.0 with a production-quality, scalable solution running on Azure with SQL Azure as the back-end and overcoming these issues.

    If not a quality example, at least entertain us with a documented roadmap of Entity Framework and SQL Azure so we can plot a course in our own development cycle.

  • If you try to generate the azure db from an EF model that contains many to many relationships, the process doesnt work. The generated t-sql script doesnt create pk's for the mapping tables. Thats why you get the error "clustered index required" when you try to insert data in such a n:m mapping table :/

  • Do you have any guidance on how to do data migrations with EF model first & SQL Azure as you extend and change the schema?

  • Steve,

    Can you comment on my question above?

  • @Aaron

    There is not a built-in way to do data migrations through the EF model first tooling. However, EF model first works by creating SQL scripts, and you can use these scripts with database tools that ship with VS 2010 to do things like SQL Server schema comparisons and database syncronizations. Here is a link to get started:

    msdn.microsoft.com/.../dd193250.aspx

    We have been doing a lot of thinking around how to get schema evolution support as a built in feature of the Entity Framework and designer. Here is some of the content we have published on that:

    blogs.msdn.com/.../code-first-database-evolution-aka-migrations.aspx

    We'd welcome your feedback on anything specail you are trying to do so we can tune the experience and functionality.

  • We would love to have data migrations, particularly during development when making non-breaking changes (adding a column that has a default or is nullable).  This seems like a pretty common use case, right?  It seems that data migrations are even more important than creating a database from an EF model (you create a db once, you change it many times), and you have done a lot of work in that area.  Is there a plan to add data migrations for model first or code first (we are currently using code first)?  Thanks for the responses!

Page 1 of 1 (9 items)
Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post