In this post we will cover how to use Entity Framework by model first. This means we will create the ADO.NET entity data model first that will contain menu’s from the restaurant, with the according dishes linked to it. After we have set up our data model in visual studio, we will create the database afterwards to match the model we created in visual studio. The most common way to the derive your ADO.NET entity data model from an existing database.
If you are looking for Entity Framework 4.1 Code First instead of Model First, you can find it here:
Entity framework Code First with Entity Framework 4.1 and System.Data.Entity.DbContext
We start by creating a Console Application called “EntityFramework.CodeFirst”.
We will add an ADO.NET Entity Data Model to our project, called Restaurant.edmx
Press Add and select an Empty Model
After this you should get an empty data model generated. Obviously we need an empty data model as we will not generate it from any database. We will create the model first untill it meets our requirements, from a developer perspective. After finishing the model, we will generate the database structure from our conceptual model we defined in Visual Studio.
Our solution looks like this:
Go to the model view and right click in the model and add a new Entity:
We will create a simple scenario where we create some entities which holds the restaurants information about it’s menus. We will use a simple console application to output the available menus of the restaurant.
We start by creating an “Menu” Entity, which will hold our different menu’s:
We call our Entity “Menu” and our Entity Set “Menus”. We create a key property Id which is an integer. Entities should have a primary key to use them with entity framework, otherwise it would not be able to distinguish the items in your database table if no primary key is set.
We should now have an Menu Entity defined with an primary key Id:
You can notice the property Id is a primary key by the key image at the property. If you click the Id property in the Menu entity, you can see it’s properties in the properties detail window:
Note the StoreGeneratedPattern is set to Identity, which means this is an identity field, so it will get it’s valued auto assigned at insert. You can also set whether the property can be null or not, what isolation the property should have by the Setter and Getter property. Notice you also have a Documentation drop down at the property, just as you have at the entity. This is information that will not be propagated to the database tables, but is used for the entity framework intellisense.
If we set a Summary at the documentation of our Menu entity:
If you get to the Menu entity in your code, the intellisense will show the summary you defined, as you can see on the following image:
Summaries and descriptions are not required, but can be of use if any other people will be working on the ADO.NET Entity Data Model later on.
Let’s add a few properties to our Menu entity as following by adding a Scalar Property: (you need to right-click on the Menu entity)
Call the new property “Name”. You can set the properties of the Name property by selecting it and changing the values at the properties window:
We set the Max Length of the property to 50, meaning the length of the nvarchar field to the database will be set to a maximum of 50.
Create another Scalar Property named “Price”, with a type of double:
Our Menu Entity looks as following now:
If you want to be able to see the Type of the properties directly in your model view, right-click in your model background:
Your entity will now show the property type in the model:
Now you know how to create a new entity and how to create basic properties on the entity, create the following entities and properties:
We have a Menu entity, a Dish entity and a DishCategory entity. We created the basics of the entities. Only thing remaining is that we need to create the associations between the entities.
To create entity associations in the Entity Framework, you do this by adding associations between entities:
We will add an association between Menu and MenuDish. The relation is 1 to many : 1 menu can have multiple MenuDishes linked to the menu.
(The reason we don’t link Dish directly to menu is because a Dish can be linked to multiple menu’s)
We leave the Navigation Property in the Add Association window checked. This means adding this association will create a Navigation property at both the Menu entity and the MenuDish entity. At the Menu entity a navigation property “MenuDish” will be created. Actually call the navigation property “MenuDishes” instead of “MenuDish”. On our Menu entity there will be a property available called “MenuDishes” which will give us a list of MenuDish entities that are linked to that Menu entity. Same idea behind the MenuDish entity as a navigation property will be added called Menu. We will leave this in singular as a MenuDish only can point to 1 Menu entity. Make sure the Add foreign key properties is checked on, as we need the foreign key property in the other entity to be able to make the relations between the 2 entities.
These navigation properties are ideal for retrieving associated data with Entity Framework. Our Model will look like this:
You will notice that an 1 to many association has been added between the Menu and MenuDish entity.The MenuDish entity also has a property “MenuId” added which is the foreign key from the MenuDish to the Menu entity. Both the entities have now a Navigation Property added to the entity, that allows you to browse to the associated data from the entity out. By code you can use navigation properties like this:
You see you can get the MenuDishes collection directly from the Menu entity, which will be retrieved through lazy loading when you access the MenuDishes property. If you do not know what lazy loading is, you can find more information here: http://msdn.microsoft.com/en-us/library/bb896272.aspx
Create the remaining associations, so your model looks as following:
Associations between our entities:
- A menu can have multiple MenuDishes. For example the Valentine’s menu will have 3 dishes, which are linked through the MenuDish entities
- A MenuDish is linked to 1 Dish and 1 dishCategory. The category is linked to the MenuDish and not to the Dish entity, that way one and the same Dish can be used as an entrée or as a main dish in different menu’s
- A DishCategory can have multiple MenuDishes
- A Dish can have multiple MenuDishes
For demo purposes I created navigation properties at all the entities, though it might be useful to skip some of the navigation properties as you might not need them.
An Entity Data model exists of 3 parts:
- The Conceptual Model (the structure of the entities defined in our data model) (CSDL = Conceptual store definition language)
- The database schema model (the structure of the tables in the database) (SSDL = Schema store definition language)
- The CS mappings which holds the mappings between the conceptual model entity and the database table schema
You can view this Entity Data Model XML by right clicking the Entity Model and click “Open With” and select XML (Text) Editor.
You can see the CSDL has content defined in the XML, which makes sense. The CSDL contains the information about the entities we have defined in our data model.
The SSDL on the other hand is empty. This should also make sense, since there is not database created yet. So the physical structure of our database tables can not be present yet in the model as the database is not created yet, nor is the model build from an existing database. You will also see the CS mappings are also empty, which of course is normal aswell as there is no SSDL, so mappings between the SSDL and CSDL are not possible yet. Only when the SSDL and CSDL is complete, there can be mappings between the database table and our entity in our model.
We will now generate the database from this model, instead of the usual other way around. Right click in the model background:
Generating the database from the model does not happen automatically. The wizard will generate a list of SQL scripts which you have to execute on your SQL server to create the structure. Knowing the database creation does not happen automatically, let’s already create an empty database called “Restaurant” in our SQL server:
Click the Generate Database from Model and select a new connection:
Select the name of the empty database you created and continue the wizard untill it is finished. And the wizard is finished, an SQL command file will be visible in Visual studio that contains the SQL command to create the database schema to match the structure of our entity model:
Now that you have the script to create the database schema, copy the script, go to your SQL server and right-click on your newly created database Restaurant and click “New Query”. Copy the content of the SQL query in there and execute it. After the command is finished, refresh your tables in your database and it should look like this:
Note the table names match the entity set names we defined in our model.
If you would now view the Entity Data Model by the XML editor, you would see the XML for the SSDL changed:
After we generated the script, the physical schema structure got added to our model, equal to what was written into the SQL script to create the schema. You can see the Properties defined at the SSDL match the database schema, as the Type of Name is a nvarchar, which are database type.
If you scroll down in the file, you will also find content to the C-S mapping:
The C-S mapping defines the mappings between the properties of the entity in the data model (defined in the CSDL) and the columns in the database schema (defined in the SSDL). You can also see this mapping and change this mapping between the entity and the database table with the Mapping Details:
You can change the mapping details between entities and database table columns like this. You can change the mappings, remove them and so forth. You can easily rename a property in your entity, let’s say from Price to Total. The Database would still be Price:float, but it would be mapped to Total:Double. So at your entity you would be using Total at the Menu entity, but in the database it would be saved under the Price column. The entity framework will do this mapping for you automatically.
Let’s add some simple data to our tables for testing purposes:
You can write some code in your console application to retrieve the menu’s with the dishes that are linked to it:
(This is not ideal code as this code will generate a lot of separate T-SQL queries to the database. You can also write a LINQ query that gets the result in a list of combined anonymous objects in a single query).
When we run the client console application:
Any suggestions, remarks or improvements are always welcome.
If you found this information useful, make sure to support me by leaving a comment.
Cheers and have fun,
Robbin
Be nice to nerds. Chances are you'll end up working for one … 
You must be logged in to post a comment.