I was at first a little skeptical about the Entity Framework Model First development in Visual Studio 2010 beta 2. I was a little worried that the database that was generated wouldn't be something I would want to use in production. I have used modeling tools before that did a good job and some that didn't. I decided to take a look at it and was pleasantly suprised at the final generated database. The process produces a .sql file that you execute on your database that will create the tables and relationships from the model. Any tables that already exist are dropped and re-created. If you execute this generated script against a blank compare database, you can use a sql compare tool to get a set of alter scripts to upgrade a dev database. More on that in a later blog post. Right now I want to show you how you can use Visual Studio 2010 beta 2 as a modeling tool to create a database than you can then use from Visual Studio 2008, effectively giving you Model First development in v1 of Entity Framework.
To start, open up Visual Studio 2010 beta 2 and create a new project, I chose a Console Application, but it really doesn't matter since we are using the project to access an EF data model and no more. Once you have the project open, add a new file and select "ADO.Net Entity Data Model".
Select "Empty Model" from the next dialog:
Once you click finish, you will be taken to an empty data model screen:
Now that you have a clean slate, it's time to start adding some entities. You can either right click in the entity model surface or drag an entity over from the toolbar. I prefer right-clicking because it gives you a dialog to set some basic options. You can set those options from the properties window, but to me the dialog is a bit faster. For this exercise, I'll right click. You will want to select Add -> Entity:
The following dialog will appear:
I am going to add a "Person" table so I will fill out this dialog as follows:
I entered "Person" into the "Entity name" field and the "Entity Set" field was automatically set to "People". The "Entity Set" is used to set the table name in the physical model and it automatically pluralized. I changed it to Person to match the "Entity Name". I have been using singular table names for several years, but you can certainly use plural names if that is your convention as it won't affect your outcome. I changed the "Property name" field under "Key Property" to "PersonId" because I personally do not like to leave the primary key as simply "Id". How you do it is up to you, but that is my preference.
The "Key Property" creates the model's primary key. Note: the "Key Property" is not automatically set as an Identity. Once you click "Ok", you can set the Identity property. Click on the "PersonId" in the data model and change the "StoreGeneratedPattern" property to "Identity":
The "Person" entity will get created as the "Person" table when the database gets created. It's time now to add some properties to get us some columns in the database. To do this right click on the entity and select Add -> New Scalar Property:
We'll set the name to "FirstName" and have the following:
If you click on the "FirstName" property, you will see the following in the Properties Window:
Note the "Type" is String and "Max Length" is none. This will create a database table of nvarchar(MAX). If you set the "Max Length" to "50", the produced column will be a nvarchar(50). If you want the column type to be a nchar, set "Fixed Length" to true. The default value for "Fixed Length" is false.
We will go ahead and create "LastName" and "DateOfBirth" properties. The "LastName" property will be a string, but the "DateOfBirth" will obviously be a date. In the property settings for the "DateOfBirth" property, set the "Type" to DateTime:
Now I will create a new Entity for "Address" with a few standard properties. Our model will not look like this:
We need a way to create an relationship between Person and Address so we will create an "Association". To do this right click on Person and select Add -> Association. It's important to click on "Person" when doing this as it is our starting point and we want the foreign key to be on "Address".
This will bring up a dialog:
We want to have a 1 to many relationship between Person and Address so we will leave these settings. There are checkboxes for "Navigation Property" on both ends of the association. When these boxes are checked, the model will include navigation properties to allow you to get a collection of Addresses off of a Person entity by accessing Person.Addressees and Address.Person to get back to the Person entity from an Address. Checking the "Add foreign key properties..." checkbox will include a property for the foreign key to the model. Clicking "Ok" will create the association and leave our model like this:
If you will notice the Navigation properties on both entities and that Address has a PersonPersonId property. The property name is comprised of the table name and key property. Since our key on Person is PersonId, the property is generated as PersonPersonId. We can simply rename this to just PersonId.
We are now ready to generate a physical database from our conceptual model. To do this, build the project and then right click on the model surface and select "Generate Database from Model...":
This will give us a dialog:
Since we are just using this to export, you do not need to actually setup a connection here. You can click "Next". If you were going to use this model in this project, you would want to make sure you setup a database connection so that it gets linked properly. Clicking "Next" will give you a preview of the generated sql file:
All you need to do here is click "Finish". This will produce a confirmation dialog:
You want to click "Yes" to continue. When finished, you will have a new .sql file in your project:
Create a new database and run this .sql file against it to create your database. Any time you wish to update the database, you can change the model and generate using the above instructions. The generation database functions will only generate create scripts and will drop all tables and recreate them. I would recommend running this against a blank database that can be used for a compare and use a sql compare tool to generate an upgrade script. Hopefully in the future the database generation tool will be able to do that for you and eliminate this step.
We have now used Visual Studio 2010 beta 2 as a data modeling tool and we can now take this newly created database back to Visual Studio 2008 to use the currently released version of the Entity Framework.
Open Visual Studio 2008 and create a new Console Application and add a new "ADO.Net Entity Data Model" like we did above in Visual Studio 2010 beta 2. This time however we will not start with a blank model, but generate it from the database:
Select "Generate From Database" and click "Next":
If you have already added a connection to the Server Explorer in Visual Studio, the connection will be in the drop down. If you have not, you can create a new connection. Once you have the proper connection selected click "Next":
This will give the option to select what objects you want in the model. As you can see the tables to import are identical to the ones we created in the data model. Click "Finish" and the data model will get created. You will be taken back to the model surface and as you can see, the model is identical to the one we created using Visual Studio 2010 beta 2. The only difference is that the Address table doesn't have the PersonId property on it. This is due to the foreign keys not being directly available on the model in v1.
From here, it's Entity Framework as usual with v1 in Visual Studio 2008 SP1.
Model First development is a very cool new feature and as you can see, it's one you don't necessarily have to wait for Visual Studio 2010 to be released to use it. If you use Visual Studio 2010 beta 2 as a data modeling tool, you can use Model First development with v1 of the Entity Framework. Enjoy!