Model First For SQL Azure

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

   

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

   

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.

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.

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:

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.

National Semiconductor National Instruments Motorola Moodys