Asp Net MVC , Web API and Entity Framework(EF) application
Posted On June 11, 2015 by Sneha Latha filed under
But real world applications mostly use database tables to store data. So now we will see an application which stores the data in a MSSQL Server database –table. Here the Web API uses EF to get the above data and provide the Rest services .There were detailed discussions about the “ORM” EF from time to time. A number of artifacts are involved. Please look at the sketch below:
The ORM does a lot of work:
1) ORM maps the table and domain class so that we can think in terms of domain objects when we code or design.
2) It also provides an interface/context to store or retrieve domain objects.
3) Query –tool .When the client queries the interface, the query tool (here LINQ to Entities) is responsible for taking the object /LINQ query and translating it into a SQL query against the database. Once the query returns, Entity Framework is responsible for copying the data into instances of the classes that make up the model .i.e. the ORM populates the domain objects in the interface with data from the DB table.
4) The ORM provides tools to generate the necessary artifacts i.e.1) table 2) domain class 3) mapping information 4) the interface
But you have to manually create at-least one:
a) Table (Database-First) or
b) Code the domain class (Code-First) or
c) Draw an entity diagram for the domain class using the Tools in the IDE (Model-First).
The Tools will take care of the rest. You can furnish the mapping information or the ORM will follow conventions.
In short, the ORM enables OOP programmers to think in terms of objects and their attributes rather than tables and columns and provides an interface to enable him deal with only domain objects. The ORM uses a query tool like HSQL or LINQ to Entity to translate our object oriented queries into raw SQL. The ORM will interact with the database tables and furnish the data. Actually it populates the domain objects in the interface with the data from DB or stores the domain object from interface into the DB tables. In other words it takes care of what they call “impedance mismatch”. NoSQL people boast they can do without an ORM. But similar “Object Document Mappers (ODM) ” like Mongoose are used in all significant applications. Articles on Hibernate, NHibernate, EF and Mongoose are available in archives of DIQ site.
EF: Normally in EF we have to add or retrieve domain objects from the DbSet in the “Context” (sub-class of DbContext) and we will have no direct interaction with DB tables. EF also provides for direct interaction with DB tables but you are not expected to use the same, unless you feel the querying capacities of Query tools used “LINQ to Entity” and “Entity SQL” are not sufficient or not fast enough for your purpose and you need “raw” SQL.
Connect to MS SQL Server: We intend to follow “database-first” approach; i.e. will use the tools in Visual Studio to generate the other artifacts from a database table. We will create a db and a table using SQL Server Management Studio (SSMS). Connect to SQL Server from SSMS. Before that, I would like to give a word of warning. If you install MSSQL Server 2012 & SQL Server Management studio, please ensure that Visual Studio shell 2010 is not already in the system. Some of the components of SSMS are built upon VS shell 2010 and they may not be installed properly, if your system has separate VS Shell 2010.
When you connect to your SQL Server, your SSMS may look as in Figure1.Your server name may be different. You can click “ Connect”. You will get Figure-2.Right Click on Databases and choose New Database. You will get Figure-3.Give Database Name “EmployeeDb” and Click OK. If you expand databases now, you can see EmployeeDb. Expand it and right click on Tables and click on New Table. You will get Figure-4.
Give the Column Names and Types as under:
Column Name DataType
Selecting Id, click key Symbol in the tool bar to make it the primary key. As shown in Figure-5 under Column Properties navigate to “Identity Specification” and expand it clicking on it and Choose “Yes”-to auto-generate values for Id. The tool will fill up values as shown in Figure-5.Right click on dbo.Table1tab and choose Save Table1.You will get Figure-6.You can give the name as “Employee” and Click “OK”. If you expand Tables now there will be an entry “dbo.Employee”. You can right click on it and choose “Edit Top 200 Rows”. You will get Figure-7.Give values for Name and Department (Id need not be given, as it will be generated by DB) and use Tab to go to the next records. Add a few records. Now DB and table are ready for use.
Create a project and generate a Model class:
In the Visual Studio Express 2013 for Web, create a New Asp Net MVC4 with support for Web API as we did in our earlier sample. You can name it as “MvcWebEF”. The IDE will generate files and folders. Right Click “Models” folder and choose Add àADO.Net Entity Data Model. In the wizard that appears you can leave the Item name as “Model1” and click “OK”. You will get Figure-8.Click “Next”. In the Wizard that appears click on “New Connection” .In the next Wizard, give the Server name as you gave in SSMS (Earlier Figure-1) and expand “Select or Enter a Database Name”. You may get Figure-9.Select “EmployeeDb”. Please click OK. You may get Figure-10. If you can understand what is there you can feel elated as your knowledge of EF is good. Otherwise there is no problem. The IDE is there to help you in your job. Just remember the name “EmployeeContext”-entity connection settings in “Web.Config” .The IDE will generate the Context class- sub-class of DbContext- in this name. Please click “Next”. The next wizard will say that Entity Framework Version5.0 is used. You can click “Next”. In the wizard that appears check Tables and expand it and it may appear as in Figure-11.Click Finish. A number of files will be generated under “Models”. Actually the IDE uses two templates 1) Model.tt to generate the entity class and Model.Context.tt to generate the context. So if you expand Models you can see Employee. cs under Model1.tt and EmployeeContext.cs under Model1.Context.cs.The Code for the above two files are reproduced below:
There is nothing special in the above IDE generated code. The domain class “Employee” has been generated by the template from the table “Employee” and the column names have become automatic properties of the domain/entity class.
- As mentioned earlier, the name of this class corresponds to the Connection string setting in Web.Config, you gave in the Wizard-Figure-10.
Repository: Now the Web API can use EF and we can create the controller of our Web API. But to avoid tight coupling between EF and the controller, we will use a repository. i.e. create one more file “StaffManager.cs” . Add a “Manager” folder and under it create StaffManager.cs. The code, which is closely related to the database, has been put in this class instead of getting placed in the controller itself .It is given below:
Right clicking on Controllers folder you can choose Add à Controller. In the wizard that appears, give name as “EmployeesController” and choose “Empty API Controller”. You can click “Add”. Only a stub of the Controller- without any method- will be generated, as we have chosen Empty controller. It can be filled up as under:
Just remember the naming convention in Asp Net Web API.The name of a controller action has to contain a HTTP method name; otherwise you have to affix an attribute to an action to enable to framework to map an incoming request to the action. Here the HTTP Get request will go to GetAll().The action just invokes the GetEmployees() in StaffManager and returns the IEnumerable<Employee> it got from that method.
As in our earlier sample, Asp Net MVC will act as a client for the services provided by above Web API Controller. So you can replace the code in Views/Home/Index.cshtml with the following:
The code is the same as given in the earlier sample and you can see the explanation there.
Run the Project:
You can rebuild the project and Debug it. The Browser will open and Change as in Figure-12.When you click “Get” You can see the records from the table displayed.
This article is written by P. Madanasekaran. For more information you can send an email to firstname.lastname@example.org