10/4/12

Database First Development with ASP.NET MVC Scaffolding and Entity Framework

With ASP.NET MVC and Scaffolding, we are able to code first and create the database objects from the model. This approach basically allows us to create the model classes (implementation). We can then use Scaffolding to create the controller, view and database objects with code generation.  This however is not as straight forward when we use the Database first approach in which we design our database first and then we want to create the controller and view. We are going to walk through this scenario to see how we can achieve this with Scaffolding and the Entity Framework.

Database Model:

We first start by creating our database object model.  Create a database on SQL Server and add the following table and a few records:

CREATE TABLE [dbo].[VehicleMake](
      [MakeId] [int] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NOT NULL,
      [Description] [varchar](500) NULL,
 CONSTRAINT [PK_VehicleMake] PRIMARY KEY CLUSTERED
(
      [MakeId] ASC
)
) ON [PRIMARY]


--SQL SERVER 2008 SYNTAX
insert into [VehicleMake](Name,[Description])
values ('Ford','Ford Motor'),
       ('Nissan','Nissan Motor'),
        ('Toyota','Toyota USA')

This is the table that we will use for our application. We will create the entity model, controller and views without writing a line of code.

Entity Model:

Open Visual Studio 2010 and create an MVC project with these options:
  • ASP.NET MVC 4 Web Application
  • Template: Internet Application
  • View Engine: Razor

Now that we have our project created, we need to add the database entity model first. This is basically the concept of Database first development. Add the entity model from the database by following these steps:
  • Right click the Models folder and select Add New Item
  • On the right, select Data and click on ADO.NET Entity Data Model.  Enter VehicleModel in the name field
  • Select Generate from database
  • Select the database connection string or create a new connection to make sure you can connect to your database and select your database name
  • Select the table that we created before and click finish

Convert Entity from ObjectContext to DbContext:

This has created the Entity model, and we should be able to see the object diagram with the one class.  If we inspect the designer class under the edmx file (solution explorer), we can see that the Entities class inherits from ObjectContext. This causes a problem for Scaffolding because it needs a context object of type DbContext.   We will make this change with the following steps:
  • Right click on the edmx class diagram and select Add Code Generation
  • Click on Code and select EF DbContext Generator. If you do not see this option, click on Online Templates and install the template by selecting Entity 4 or 5  DbContext Generator. This is determined by the version of the Entity framework you have installed.

We just replaced the code in the edmx designer class with code generated in tt files. You should have two files one with ModelContext.tt and Model.tt.  The TT extension stands for Text Template. This is the T4 technology that allows us to generate code using templatesa, and this is not just for MVC projects.  The model context file contains the database context class of type DbContext.  The model tt file contains the entity model for the table.

public partial class CommerceEntities : DbContext
    {
        public CommerceEntities()
            : base("name=CommerceEntities")
        {
        }
   
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
   
        public DbSet<VehicleMake> VehicleMakes { get; set; }
    }

public partial class VehicleMake
    {
        public int MakeId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }

Scaffolding Controller and View:

We are now ready to start creating our controller and view. Let’s start by installing the Scaffolding package to our project by following these steps:
  • On Visual Studio, select tools->Library Package Manager->Package Manager Console
  • At the PM> prompt, type this command:  INSTALL-PACKAGE MVCSCAFFOLDING

That should install the T4 templates for code generation and the MVCScaffolding reference to our project.  Let’s now create our vehicle make controller and view by typing this command at the PM> prompt:
  • Scaffold Controller VehicleMake  -force -repository -DbContextType "CommerceEntities"

In this command, we are telling our code generation to create a controller and view based on the VehicleMake model. The –force switch is used to overwrite any previous files. The –repository switch is to create a repository interface that would be used by the controller. This facilitates for a dependency injection approach which avoids having a tightly couple implementation of the entity object in the controller. The –DbContextType is to tell our code generation that when the repository class is created it should use the DbContextType that was created when we converted the entity classes to type DbContext. In our case, it is the CommerceEntities. If this parameter is not used, the code will be generated with a type of MVCContext which is not our entity context.

The output for this command should look like this:

PM> scaffold controller VehicleMake -force -repository -DbContextType "CommerceEntities"
Scaffolding VehicleMakesController...
CommerceEntities already has a member called 'VehicleMakes'. Skipping...
Added repository 'Models\VehicleMakeRepository.cs'
Added controller Controllers\VehicleMakesController.cs
Added Create view at 'Views\VehicleMakes\Create.cshtml'
Added Edit view at 'Views\VehicleMakes\Edit.cshtml'
Added Delete view at 'Views\VehicleMakes\Delete.cshtml'
Added Details view at 'Views\VehicleMakes\Details.cshtml'
Added Index view at 'Views\VehicleMakes\Index.cshtml'
Added _CreateOrEdit view at 'Views\VehicleMakes\_CreateOrEdit.cshtml'

*Note: If you get this error:

Get-PrimaryKey : Cannot find primary key property for type 'demo.Models.VehicleMake'. No properties appear
 to be primary keys

Check your model class and make sure the primary key field is annotated accordingly by adding the [Key] attribute and the System.ComponentModel.DataAnnotation namespace. The model class should look like this:
using System.ComponentModel.DataAnnotations;
   
    public partial class VehicleMake
    {
        [Key]
        public int MakeId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }

In our code, we should now have all the files listed in the output of the package manager console.  The repository file should have this code: (notice the context object is of type CommerceEntities)

public class VehicleMakeRepository : IVehicleMakeRepository
    {
        CommerceEntities context = new CommerceEntities();

        public IQueryable<VehicleMake> All
        {
            get { return context.VehicleMakes; }
        }

The controller file should look as follows: 

public class VehicleMakesController : Controller
    {
       private readonly IVehicleMakeRepository vehiclemakeRepository;

       // If you are using Dependency Injection, you can delete the following constructor
        public VehicleMakesController() : this(new VehicleMakeRepository())
        {
        }

        public VehicleMakesController(IVehicleMakeRepository vehiclemakeRepository)
        {
                    this.vehiclemakeRepository = vehiclemakeRepository;
        }

You should notice how the controller uses the repository interface as the parameter in the constructor. This allows us to use dependency injection and separate the model operations from the controller. The controller uses the repository for all the data/model tasks.

For the views, we have the index, edit, create, delete and details views which can be used for the CRUD operations. These views and controller can be enhanced to meet your application requirements.

Run the project:

We can now compile the project and run it. The application should load in the browser and display the home page. We can type the controller name on the url to something like: (add the port number when using Visual Studio)


This should load the following views:

Index View

Edit View

Details View

These views may not have a great design, but the objective here was to show how we can create a running application with no implementation effort at the application level.  We can see the records that were added during our database creation step. From these views, we should be able to add, edit and delete records, and the changes will be reflected in our table.

I hope I was able to show you how to leverage Scaffolding and the Entity framework to accelerate your development process. We started with a database model, use some code generation templates and created a simple application in a short time.