Entity Framework - Database First Approach

Today, in this section, we'll see how the Database First approach works with Entity Framework. The idea is to use the existing database and create a new entity model that will interact with the EF designer. So, starting with this I will start with a class library as my data project just to be sure it can be easily reused in other applications also.
 
class liberary
 
So, it will generate the sample library with sample class file in there as shown below in the snapshot. But, I really don't want that class file. So, what I do is just go ahead and delete the class file.
 
delete the class file
 
And then add my model and it's pretty easy to find that. If we go to the Data tab and look for ADO.Net Entity Data Model then select it and provide the same edmx file a proper name and then add it.
 
Entity Data Model
 
Then, this will ask for alternatives, whether you would like to create from an existing database or from an empty one. I'll select the existing database as shown below.
 
generat from database
 
Since I already have the Server Explorer set up to my destined database, hence this will flag me the following message with the connection string. This will also go ahead and save the connection string in the App.config file.
 
select data connection
 
Now, in the next case, it will ask what tables, views, Stored Procedures, or functions you would like to select. I have selected those that I believe are OK for the explanation.
 
table
 
Now, one important point to note here is that I have three checkboxes and by default, all are checked. The first checkbox specifies whether to pluralize or singularize the names, in other words, if the wizard finds the table name is singular then the entities will be pluralized and vice versa. Now, the next option is to include the foreign keys column in the model. Again this is selected by default. So if you have relationships defined in the database then that means there is a primary key and foreign key, so the EF will recognize these and build subsequent relationships. Now, the last option specifies how the wizard handles the Stored Procedure and functions. When this option is selected (it is by default), the wizard is making it easy. Also, by offering you a choice you can use it in the code. I will now go ahead and click Finish. This will generate the Entity diagram with some warnings in it.
 
warnings
 
You might also see some messages and warnings in there. But these are OK. We should not be concerned about these. Basically, what happens is that there is a general rule in Entity Framework that every table should have a primary key and in this case, we have viewed also it without a primary key. So the wizard created a composite key on all the combinations of non-nullable in the views. So besides those warnings, there have been many tasks done by the wizard behind the scenes. So, the following is the snapshot. If you can see the lines between the objects, these are basically relationships that are being created based on the primary-foreign key relationships.
 
primary foreign key relationship
 
Now, in the meantime the designer has given me all the entities in one place, sometimes it is hard to identify the tables, views, and procedures. But, we have a way to check it by checking the model Browser by right-clicking on the designer as shown below.
 
model browser
 
By doing so, we'll have access to the model browser in the side pane of the Visual Studio as shown below.
 
explorer
 
Now, there are a couple of key points one should understand here, so the place where functions and Stored Procedures reside are in function imports. So these are converted to functions, in other words, functions that are executed will return results and what the wizard did for each of the functions is it returns it as a created complex type. There is one more thing that is known as scalar-valued functions, scalar-valued functions are something used in queries.
 
Now, the next thing we need to understand is the visual representation of the model that we are seeing is actually an XML file behind the scenes as shown below.
 
xml
 
Now, attached to the edmx file, we have the T4 template for generating the context and also for generating the model classes. So these model classes are basically Plain Object CLR Objects (POCOs) as shown below.
 
Plain Object CLR Objects
 
So, basically, it doesn't have any dependency on any EF API or any external APIs.
 
Now, to understand table mapping what we can do is right-click on the Table Designer and then click on table mapping for the desired table and it will give you the table mapping for it as shown below.
 
table maping
 
maping detail
 
However, what I can do in the entities is move up or move down the properties or rename them as well, and obviously, you can undo the changes that you have done so far. Also, one more interesting thing is the coloring of the entities, in other words by default all the entities are Blue in color but I would like to maintain some kind of distinction between a set of tables or between tables and views. So for that, we can set the fill color property for that specific entity with a different color just to differentiate the items as shown below.
 
properties
 
Another nice feature is that we can split a single diagram into multiple diagrams. The best way to do it is to add a new diagram in the model browser and then start dragging and dropping the desired models in there.
 
However, also there are many scenarios wherein we keep changing the table definitions or something in the database. So in that case we need to update our designer, so to do that we need to right-click on the designer surface and click on the updated model from the database. This will update the designer with the latest changed effects in the database.
 
update model
 
Now, one of the most awaited features in Entity Framework was Enums. Without Enums earlier people had workarounds. So, basically, if we want to use an Enum type we can use it against the property of integer type. Let me show you how to create an Enum type, we need to go to the model browser, then right-click the Enum and say add Enum type.
 
add new enum type
 
underlying type
 
So basically there could be these many types allowed for an Enum as is shown in the dropdown box. Now, let's go ahead and create it.
 
ternary
 
Now, keep the following flags unchecked. These are basically bitwise operators, in other words, if I have a requirement to check for a combination of members, then we need to use that. Now when I click OK, it will generate one type in the Enum category in the model browser as shown below.
 
enum generat
 
Now, when I save this browser one new model will be created for the Enum as shown below.
 
model generat for enum
 
enum quant
 
So, after defining my Enum I can go ahead and map the int type with a new Enum type as shown below.
 
Enum
 
new Enum type
 
So, now when the EF creates a query in a database it will actually transform the Enum into an actual value.
 
So, with this, I would like to wrap up this module for the Database First modeling approach. Now, to expose it in any application, you can use the same class library in the project and add its reference in there.
 
I have also attached the sample model code that I used in this demo as well. In the next session, we will see how to use the Code First approach, which is my favorite. So, until then stay tuned.
 
Download Link: Database First Approach.
 
Also, this is the visualization of my first app on e-commerce that can be viewed at http://www.ecommerce.rahulnivi.net/.
 
Thanks for joining me.