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 to interact with the EF designer. So, starting with this I will begin with a class library as my data project just to be sure it can be easily reused in other applications.

So, it will generate the sample library with a sample class file in there as shown below in the snapshot. But, I don't want that class file. So, what I do is just go ahead and 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.

Then, this will ask for alternatives, whether you would like to create from an existing database or an empty one. I'll select the existing database as shown below.

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.

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.

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 a 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 makes 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.

You might also see some messages and warnings in there. But these are OK. We should not be concerned about these. 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 relationships that are being created based on the primary-foreign key relationships.

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.

By doing so, we'll have access to the model browser in the side pane of the Visual Studio as shown below.

Now, there are a couple of key points one should understand here, so the place where functions and Stored Procedures reside is 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 an XML file behind the scenes as shown below.

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 Plain Object CLR Objects (POCOs) as shown below.

So, 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.

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 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.

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.

Now, one of the most awaited features in Entity Framework is Enums. Without Enums earlier people had workarounds. So, 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.

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.

Now, keep the following flags unchecked. These are 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.

Now, when I save this browser one new model will be created for the Enum as shown below.

So, after defining my Enum I can go ahead and map the int type with a new Enum type as shown below.

So, now when the EF creates a query in a database it will 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 there.