Hi Friends,
Today, in this section we’ll see how Database first approach works with Entity framework. The idea behind using the existing database and create a new entity model which will interact with EF designer. So, starting with this i’ll start with class library as my data project just to make sure the same can be easily reused in other applications also.
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.
and then i’ll add my model and it’s pretty easy to find the same. If we go in the data tab and look for ADO.Net Entity Data Model. what i’ll do is select the same and give the same edmx file a proper name and then add the same.
Then, this will ask for a option whether you would like to create from an existing database or from 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 below message with 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 which are the tables, views, stored procedures or functions you would like to select. I have selected those which i believe ok for the explanation.
now, one important point to note here is that i have three checkboxes and by default all are checked. the 1st checkbox tells the names are pluralized or singularized means if the wizard finds the table name with singular then entities will be pluralized and vice versa. Now, the next option is Include foreign keys column in the model, again this is by default selected. so if you got relationships defined in the database which means there is a primary key and foreign key, so EF will recognize these and build subsequent relationships. Now, the last option tells how the wizard handles the stored procedure and functions. so, when this option by default is selected, wizard is making your easy btw by giving you a choice that you can use the same in code. so, now i’ll go ahead and click finish. so, this will generate the Entity diagram with some warnings in there.
so, you could also see some messages and warnings in there. but, these are ok. we should not be concerned about these. Basically, what happens there is a general rule in entity framework that every table should have a primary key and in this case we have views also which is not having one primary key. so, wizard what did is created a composite key on all the combination of non nullables in the views. so besides on these warnings, there has been a lot of task has been performed by the wizard behind the scenes. so, below in the snapshot if you can see lines between the objects, these are basically relationships which are being created based on the primary-foreign key relationship.
Now, but in the mean the mean time designer has given me all the entities at one place, some times hard to identify between tables, views and procedures. but, we have a way to check the same by checking the model Browser by rt-clicking on the designer as shown below.
by doing so, we’ll have the access of model browser in the side pane of the Visual studio as shown below.
now, there are couple of key points one should understand here, so the place where functions and stored procedures reside are in function imports. so these have converted to functions means functions which are executed will return results and what the wizard did for each one of the functions returns it created a complex type. There is one more things which is known as scalar valued function, scalar valued function is something which is used in queries.
Now, the next thing which we need to understand the visual representation of model which we are seeing is actually xml file behind the scenes as shown below.
now, attached the edmx file, we have the T4 template for generating context and then also for generating the model classes. so, these model classes are basically POCO (Plain Object CLR Objects) as shown below
so, basically it doesn’t have any dependency on any EF Api or any external APIs.
Now, in order to understand table mapping what we can do is rt-click on the table designer and then click on table mapping for the desired table and it will give you the table mapping for the same like shown below.
however, what i can do in the entities are move up or move down the properties or rename the same as well and obviously you can undo the changes which you have done so far. also, one more interesting thing is coloring the entities means 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 in that case what we can do is go ahead and set the fillcolor property for that particular entity with a different color just to differentiate the items like shown below.
another nice feature here in that we can split a single diagram into multiple diagrams. the best way to do the same 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 where in we are keep changing table definitions or something in the database. so, in that case we need update our designer, so in order to do that we need to rt-click on the designer surface and click on update 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 was Enums. Without Enums in earlier people used to have work arounds. so, basically if we want to use an enum type we can use it against the property of integer type. Let me show you that how to create an enum type, we need to go in the model browser, then rt-click the enum and say add enum type.
so basically there could these many types allowed for enum what is shown in the dropdown box. Now, let’s go ahead and create the same.
Now, below flags i’ll keep these unchecked. these are basically bit wise operators means 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 under Enum category in the model browser as shown below.
Now, when i save this browser one new model will get created for the enum as shown below.
so, now when i got my enum defined i can go ahead an map the int type with new enum type as shown below.
so, now when the EF creates a query a database it will actually transform the enum into actual value.
so, with this i would like to wrap this module for Database 1st modeling approach. Now, to expose the same in any application, you can use the same class library in the project and add it’s reference in there.
I have also attached the sample model code which i used in this demo as well. In the next session we will see how to use code first approach, that is my favorite. So, till then stay tuned.
Download Link:- Database First Approach
Thanks,
Rahul
Happy Coding
