Building a relational data model in ASP.NET MVC w/EF Code First

Just about every application uses some sort of data model, and .NET developers have been using POCOs (Plain Old CLR Objects) for some time now. You can use either new or existing POCOs in MVC 3 applications and still take advantage of EF (Entity Framework), and in particular, EF’s Code First feature. EF’s Code First feature allows you to base both an application and a database from your data model. EF additionally carries features for database and model first development.

Data Models, ORMs & Entity Framework

As with most business apps of any type, data models are at the heart of an application. When developing ASP.NET MVC applications it’s best to go with an ORM (Object-Relational Mapper) such as EF, as ORMs alleviate many pains in dealing with databases and their objects. Since MVC is extensible and pluggable, you also have the choice of using 3rd party or open source ORMs, for example, the widely used nHibernate.

That’s where things can get complex, and that’s where EF fits in with multiple strategies to help alleviate common pains in modeling. There are many ways you can model data to represent and manipulate it the way you need to, either visually with a designer, or by using code. EF supports these data access options:

  • Database first
    • You can use Visual Studio to connect to a data source and visually generate a data model from an existing database(s).
  • Model first
    • Using model first you can build brand new databases from models you’ve created using the designers in Visual Studio.
  • Code First
    • With code first, you start with your own POCOs, just add a small amount of code, and voilà, instant ORM.

In addition to these features, EF has more advanced features, such as a XML based schema mappings and fluent APIs. EF is flexible enough to cover the spectrum of applications from the smallest of web sites to enterprise applications.

Before coding EF classes, you must add a NuGet package reference to EntityFramework by selecting “Add Library Package Reference” from the Project menu, and before using SQL CE 4.0 you’ll need to add a regular reference to the System.Data.SqlServerCe library.

Building the basic data model

Code First is a feature of EF that maps POCOs containing classes, relationships, data annotations, etc…, to database tables, columns, constraints, and relationships in physical databases. EF CF creates the database by examining the code in the data model, then building the corresponding physical database and its objects. You have the option to merge or drop/recreate any databases generated by EF Code First, and you can additionally seed the database with  data (coming in a later post).

Since the controller returns the model to the view, you can send validation information to the view by using Data Annotations. Data annotations are attributes that you can apply to a model to perform common types of validation at the property level, as shown below in two basic POCO classes.

public class Category

{

    public int Id { get; set; }

 

    [DisplayName("Category")]

    [Required(ErrorMessage = "The product category is required.")]

    public string Name { get; set; }

}

public class Product

{

    public int Id { get; set; }

 

    [DisplayName("Delicious Treat")]

    [Required(ErrorMessage = "The product name field is required.")]

    public string Name { get; set; }

 

    [Required(ErrorMessage = "The product description field is required.")]

    public string Description { get; set; }

 

    [DisplayName("Sale Price")]

    [Required(ErrorMessage = "The Sale Price field is required.")]

    public decimal Price { get; set; }

 

    [DisplayName("Made fresh on")]

    [Required(ErrorMessage = "The Freshly Baked On field is required.")]

    public DateTime CreationDate { get; set; }

 

    [DisplayName("Don't Sell After")]

    [Required(ErrorMessage = "The Expiration Date field is required.")]

    public DateTime ExpirationDate { get; set; }

 

    [DisplayName("Qty Available")]

    [Required(ErrorMessage = "The Qty Available field is required.")]

    [Range(0, 120,ErrorMessage="The Qty Available must be between 0 and 120.")]

    public int QtyOnHand { get; set; }

 

    [DisplayName("Product Image")]

    public string ImageName { get; set; }

}

Now that you have a model, you can add a few lines of code to tell EF to generate the database for you, and even seed it with data if you want to. In order for EF to know what classes in a project it should use as a model to generate the database, you need to tap into two classes from the System.Data.Entity namespace:

  • DBContext class.
    • The DbContext is a lot like a connection, except it manages database connectivity for you automatically (i.e., no need to explicitly call open/close on connections). Additionally, there are many properties methods on the DbContext class to work directly with the model and/or database, such as the SaveChanges method or the ValidateEntity method.
  • DbSet class
    • This object knows how to deal with CRUD operations on the entity class itself (i.e., Category or Product), and works in tandem with the DbContext object to perform those operations.

A data model is not just a set of POCO classes, though. Models need a class to manage the POCOs, perform connection management, or other duties that an ORM would do. These management classes are often called a context or repository[1]. Rather than writing the database and connection management code yourself, you can take advantage of EF and receive features provided automatically by inheriting from the DbContext class in your repository class.

The DbContext also needs to know what POCO collections to work with. You can do this by adding properties of type DbSet<T> for each of your collection types, as the code below demonstrates.

public class FourthCoffeeWebContext : DbContext

{

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }

}

At this point, the two POCO classes and DbContext are all you need to see EF in action, and you can move onto running the application and generating the physical database.

Generating the database from the model

Assuming you have the controllers and views in place to perform CRUD operations on the Categories and Products collections, running the MVC application will create a SQL (Express or CE 4.0) database automatically for you. Of course, there’s no data, just the schema, but the site and database are both up, running, and completely functional. Navigating to the create action on the products controller in a browser renders the create view like the one below (including validation!):

 

image_6

 

Once you save a product, the app takes you back to the listing of products, where you can see the results.

image_8

Although you can see the data on in the browser, you probably can’t find the database in the Solution Explorer. Checking out the customary App_Data folder reveals nothing, and examining the Web.Config file comes up empty. Since EF could find no information about the database, it created a SQL Express[2] database with the a default name and path shown here:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\FourthCoffee.Web.Models.FourthCoffeeWebContext.mdf.

If you want to name your database something different, modify the <connectionStrings> section of the Web.Config file to point to the preferred database name and location. This connection string connects to the \App_Data\bakery.sdf database.

<connectionStrings>  

  <add name="FourthCoffeeWebContext" connectionString="Data Source=|DataDirectory|bakery.sdf" 

  providerName="System.Data.SqlServerCe.4.0" />

</connectionStrings>

By convention, the name of this connection string needs to match the name of your context/repository class, in this case, FourthCoffeeWebContext, so EF can identify the connection string to use. Notice that the connection string points to the data directory (App_Data) and it’s a SQL CE 4.0 database. If you wish to use a different edition of SQL, feel free to change the connection string and provider name to point to other locations.

Once the changes are in place, run the application, enter some data, and return to Visual Studio where you can see the App_Data folder now contains the bakery.sdf database.

image_10

Since the option to drop and recreate the database exists, you can design your data model through code and tests to verify that it works in a repetitive cycle, so relating the classes and regenerating the database is easy.

Relating classes in the data model

The Category and Product classes relate to each other in a one-(zero or)many relationship. You can express this relationship in code by adding a property in the Category class to represent the collection of related products, as shown here:

public virtual ICollection<Product> Products { get; set; }

The Product class needs two lines of code, so you’ll need to add the following properties below to the Product class.

public int CategoryId { get; set; }

public virtual Category Category { get; set; }

While the data model is complete, the code in controllers and views does not reflect the updates made to the model. The Edit and Create views associated with the Product need to include a dropdown of the categories, with the current product’s category selected, similar to the code below:

<div class="editor-label">

    Category

</div>

<div class="editor-field">

    @Html.DropDownListFor(model => model.CategoryId, 

    ((IEnumerable<FourthCoffee.Models.Category>)ViewBag.PossibleCategories)

        .Select(option => new SelectListItem {

        Text = (option == null ? "None" : option.Name), 

        Value = option.Id.ToString(),

        Selected = (Model != null) && (option.Id == Model.CategoryId)

    }), "Choose...")

    @Html.ValidationMessageFor(model => model.CategoryId)

</div>

Since the view is counting on the controller to pass in a ViewBag.PossibleCategories, you’ll now need to wire up the Create/Edit action methods of the Products controller so the view can use it. Just create a dynamic property on the ViewBag object and set it to the context’s Categories property.

// the context variable is of type FourthCoffeeWebContext (Inherits from DbContext)

ViewBag.PossibleCategories = context.Categories;

Generating an app from the finished model

The application at run time produces 100% supported CRUD operations, but now with relational operations and validation as well. This is evident in the Product’s Edit or Create view.

image_11

Note: Before running the application, you’ll need to delete the database or you’ll receive an error message.

Summary

MVC with Entity Framework Code First enables you to rapidly build sites with easy to understand data models.

Resources:

Code First w/existing DB

Code First

 

[1] Dear purists: I am not going to be pedantic about the repository pattern in this blog post.

[2] If you don’t have SQL Express installed, you might get an error. If that’s the case modify the web config to use SQL CE 4.0 instead, as it does not need to be installed.

Creating Helpers for ASP.NET Web Pages with WebMatrix

If you are unfamiliar with helpers and haven’t read my blog post on “Getting Started with ASP.NET Web Helpers for WebMatrix”, you should start reading there.  Otherwise, we’ll move on to create helpers for use with the Razor View Engine and WebMatrix.  In a later post I’ll describe how to create helpers using Visual Studio 2010 for use in WebMatrix.

There’s four steps to creating helpers for Razor views.

1. Create the helper file.

The helper file belongs in the \App_Code folder located under your site’s root.  If there is no \App_Code folder in the project, go ahead and create one.  You can name it whatever you’d like, but note that the name of the helper file without the extension will be used in the calling syntax and the extension must be .cshtml. In MVC with VS 2010, helper are stored as .cs or .dll files instead.

cshtml_2

 

2. Add the @helper directive to the top of the helper file.

Here’s what the signature for a helper looks like:

@helper HelperName([DataType arg], [DataType arg]…) { // helper code }

 

You can store multiple helpers in a single .cshtml file, as well as functions which we’ll look at in the next section of this post.  Each helper works syntactically as if it’s a static method when called (more on that below).

3. Add code, markup and text.

Start adding elements that you want to render and don’t forget that Razor code is case sensitive.  In this example, a helper named  RenderDiscountedPrice that accepts a decimal input and renders out a message displaying the featured product’s final price with the discount.

@helper RenderDiscountedPrice(decimal price)

{

    Buy now for only <strong>$@(GetDiscountedPrice(price))</strong> with $1.00 off!

}

Alternatively, if you want to also use functions in a helper for doing calculations, providing logic, etc.…you can.   Here’s a code sample showing the same code but rewritten to include a function:

@helper RenderDiscountedPrice(decimal price)

{

    Buy now for only <strong>$@(GetDiscountedPrice(price))</strong> with $1.00 off!

}

 

@functions {

    public static decimal GetDiscountedPrice(decimal price)

    {   

        return price-1// just a dollar off, we’re cheap

    }

}

All that’s needed for a function library is the @functions declaration with braces to signify the beginning and ending of the code block.  Many functions can be placed here and the function signatures have the exact syntax as if they were in a standard .cs file.  The calling syntax,HelperFileName.helperName([args…]); is a combination of the filename without the extension but with the name of the helper instead. Using default.cshtml located at the root of the web site, locate this block of code…

<div id=”featuredProduct”>

    <img alt=”Featured Product” src=”@HrefAttribute(“~/Images/Products/” + featured.ImageName) />

    <div id=”featuredProductInfo”>

        <div id=”productInfo”>

            <h2>@featured.Name</h2>

            <p class=”price”>$@string.Format(“{0:f}”, featured.Price)</p>

            <p class=”description”>@featured.Description</p>

        </div>

        <div id=”callToAction”>

            <a class=”order-button” href=”@HrefAttribute(“~/order”, featured.Id) title=”Order @featured.Name“>Order Now</a>

        </div>

    </div>

</div>

and replace it with this block of code (the highlighted code is the modifications necessary)…

<div id=”featuredProdcut”>

    <img alt=”Featured Product” src=”@HrefAttribute(“~/Images/Products/” + featured.ImageName) />

    <div id=”featuredProductInfo”>

        <div id=”productInfo”>

            <h2>@featured.Name</h2>

            <p class=”price”>

                @{var price = featured.Price;}

                $@string.Format(“{0:f}”, price) <br/>

                @Pricing.RenderDiscountedPrice(price) // HelperFile.HelperName syntax/behaves like a static

            </p>           

            <p class=”description”>@featured.Description</p>

        </div>

        <div id=”callToAction”>

            <a class=”order-button” href=”@HrefAttribute(“~/order”, featured.Id) title=”Order @featured.Name“>Order Now</a>           

        </div>   

    </div>

</div>

The code’s been changed to declare a variable named price and set it to the featured product price, which is then to be used in the following two lines of code, including the existing line that renders the price.  The variable is available to all parts of the web page below where it’s declared by default. (you can modify a variable’s scope if need be).  Anywhere in the Bakery site that you want to include a message about the discount, you can use the same single line call to produce the same message about the discount.

4. Verify the Results

Run the default.cshtml file and check that the sales message has been rendered.

image_8

When using WebMatrix as the tool for creating helpers, you can release helpers only as .cshtml files into \App_Code folders across projects (at this point in time, and subject to change).  If you need to release .dll files, you can create helpers in Visual Studio 2010 and package them with NuPack if you’re creating something sharable or as a private .dll otherwise.

Why ASP.NET MVC is different than Classic ASP

There’s still a lot of Classic ASP developers out there, maintaining and adding features to existing ASP sites. Many want to migrate or upgrade to ASP.NET, and particularly ASP.NET MVC. During my MVC presentations, the most frequently asked question I get is: “This looks like Classic ASP code, are we just going backwards with MVC stuff?”. If we dig into MVC a bit deeper, we’ll see that the answer is most definitely, NO. Although at first glance, the mixture of HTML and script in the views may appear to be similar to Classic ASP, upon further investigation, there are some very remarkable differences.

Note: When speaking of Classic ASP in this post, I’m referring to the ASP found in .asp pages, not .aspx Web Forms.

MVC Models & Classic ASP Data Access

MVC:

There are many options for dealing with data in MVC models; EF (db or code first),  LINQ to SQL, POCOS, Service Layers or even DataSets and DataReaders. No matter what the choice, all data model code is partitioned away from the views and controllers, and that’s the important factor to consider. MVC convention and application templates guide the developer to add models to a \Models folder or use a library reference (preferably) the data access layer. As a developer you get full control over where and how you create the data access layer and business objects so it doesn’t interfere with UI code.

Classic ASP:

There’s two primary ways of dealing with data in ASP. Developers often place data access code directly in the .asp page and other times in a COM component. Data returned from the data access layer is returned into ADODB.Recordset objects which represent the data as a rectangle, and that data is directly sent to the ASP page.

Because of the tight coupling, code in the data access model is harder to test and maintain.

MVC Views & Classic ASP Pages

MVC:

Views are the reason that prompts developers to look at MVC and think it’s similar to ASP, and that’s because in the view both HTML and code exist side by side. However, peering into views a bit deeper reveals that views contain only enough markup and syntax to render pages from data that is passed to views from a controller. That means the code in MVC isn’t a bunch of large gobs of script interlaced with markup, they’re generally just code nuggets. This also means that the most noticeable difference is the lack of actual code itself (hopefully!), as views should be clean and not cluttered.

Classic ASP:

Pages in ASP often contain server script, client script, markup, and the kitchen sink thrown in for good measure. JavaScript obtrusively pilfers the markup throughout entire pages. All too often, the ASP page contains large amounts of business logic code, and everything gets stuffed into big .asp pages.

Although there are techniques for working with ASP to get some separation of concerns, that concept gets offset since most all of today’s ASP development is in maintenance mode so many aren’t investing in enhancing Classic ASP technology, particularly when there are other alternatives.

MVC Controllers & Classic ASP

MVC:

Rather than mapping URL addresses to individual static pages living on a web server, MVC uses Routing to map the HTTP request to methods, not complete pages, in controllers called action methods. Action methods retrieves an entity in the model to match the model to a corresponding view based on routing information in the URL. These URL patterns, called Routes, are defined and added to an application wide RouteTable object so the routes can identify and execute controllers automatically. This gives tremendous flexibility, as the controller can behave as a traffic cop, directing requests to a specific code location which is natively more testable than a whole page. This also gives us a nice separation of concerns as well.

Classic ASP:

There’s nothing to compare to a controller in ASP as there’s nothing out of the box that supports routing in any similar way to MVC. URLS are directly mapped to a an .asp file and there’s nothing in the ASP platform itself to help out. You could write an ISAPI filter or some sort of module to create your own routing system, but it’s more work and maintenance compared to MVC, as MVC has this functionality built in.

Summary

MVC isn’t just a rehash of Classic ASP and we’re definitely not going backwards in technology with it. MVC gives you a way to write much cleaner, separated,  and robust code. The MVC pattern and ASP.NET MVC project templates in Visual Studio also assist in guiding the developer to do better things with their code, such as unit testing or implementing other practices such as dependency injection (DI), which are hard to near impossible to do in older versions of ASP. The good news: If you’re writing ASP applications now, the move from Classic ASP to MVC doesn’t have to be such a huge learning curve.