Entity Framework Code First Development With WebMatrix

The database helper was introduced to provide an easy way for beginners to use SQL in their web pages. As such, it works well, but has its critics. Some of them resorted to developing their own data access libraries to be used instead (see Data Access Choices For WebMatrix). The Entity Framework, on the other hand, is a Microsoft technology. Specifically, it is an Object/Relational Mapping (ORM) tool. Most professional developers prefer to work with data as objects instead of using SQL to directly query databases. ORM tools take care of all the plumbing work required to get data from the database and to populate object properties, or to translate the value of object properties to SQL UPDATE and INSERT statements. For example, if you were working on an application that dealt with books, you would expect it to feature a Book object. That Book object would have a number of properties, such as a Title, an ISBN, perhaps, and an Author. These would be defined in the object's class definition, and they would map to tables and columns in a database somewhere. The Code First approach enables you to define your objects, and then get EF to create a suitable database for you. You can then query your objects in C# via Language Integrated Query (LINQ), which gets translated to SQL behind the scenes and executed against the database.

To get started, you need to download the Entity Framework package and include it in your web site. You can get this via the Package Manager. Make sure that Default - All is selected as the source, and search for "entityframework" (one word). The most recent version (4.1.10715.0) errored when I tried to install it, but the previous one (4.1.10331.0) works for this example just as fine.

It's time to consider the "object model". Since I have already introduced the notion of a Books application, that will do for this example. The object model will be a simple one - it will consist of a Book object or Entity, an Author entity and a Category entity, in keeping with the samples I have used before. An Author entity has the following properties:

• AuthorId (int)
• FirstName (string)
• LastName (string)
• Biography (string)

A Category is an even more simple object consisting of two properties:

• CategoryId (int)
• CategoryName (string)

Finally, a Book entity consists of the following properties:

• BookId (int)
• Title (string)
• Description (string)
• ISBN (string)
• DatePublished (DateTime)
• Author (Author)
• Category (Category)

Entities can be properties of other entities as evidenced by the Author and Category properties of the Book entity.

These entities need to be defined formally as classes in C# so that the EF can understand them and knows what to do with them. To do this, you need to add the Class (C#) file type to your application, but first you need to add a new folder called App_Code to your web site. All class files must go into this folder so that they get compiled when your site first runs. Here is a simple class definition for the Author entity as Author.cs:

public class Author
{
    public int AuthorId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Biography { get; set; }
}

And further classes for the Category and Book entities:

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }

}


public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string ISBN { get; set; }
    public DateTime DatePublished { get; set; }
    public Category Category { get; set; }
    public Author Author { get; set; }
}

These are fine as a basic class definitions, but they need some additional work done to them to make them suitable for Code First. By default, all strings are mapped to nvarchar(4000) data types. This is too large for most purposes and you would ideally want to constrain the size of a number of fields. Additionally, the Biography should map to an ntext field - not limited to 4000 characters. A one-to-many relationship needs to be defined between Authors and their Books, and between Categories and their Books. Here's how both issues are solved in code for the Author class:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

public class Author
{
    public int AuthorId { get; set; }
    [MaxLength(35)] public string FirstName { get; set; }
    [MaxLength(35)] public string LastName { get; set; }
    [MaxLength] public string Biography { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}

The bit in square brackets [ ] is an attribute. These come from System.ComponentModel.DataAnnotations which is why that namespace has been referenced with a using statement at the top of the class file. Data annotation attributes are used for providing metadata to the Entity Framework, and the MaxLength attribute will result in nvarchar columns being created with a limit of 35 characters for the first and last name. When you provide no limit, MaxLength translates to an ntext column - which is what is wanted for the Biography property. Finally, the Author is provided with an additional property - a collection of Book objects, hence the inclusion of the System.Collections.Generic namespace. The collection is marked as virtual, which ensures that EF lazy-loads the collection. In other words, books belonging to an author are only ever retrieved from the database when they are referenced in code, and not just because an Author object was instantiated. Following these changes, here are the revised definitions for the Category class and the Book class:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

public class Category
{
    public int CategoryId { get; set; }
    [MaxLength(50)] public string CategoryName { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}

using System;
using System.ComponentModel.DataAnnotations;

public class Book
{
    public int BookId { get; set; }
    [MaxLength(200)] public string Title { get; set; }
    [MaxLength] public string Description { get; set; }
    [MaxLength(20)] public string ISBN { get; set; }
    public DateTime DatePublished { get; set; }
    public int CategoryId { get; set; }
    public int AuthorId { get; set; }
    public virtual Category Category { get; set; }
    public virtual Author Author { get; set; }
}

The Book class includes a Category property and an Author property. These are marked as virtual as well, and will contain details of the related category and author. The relationship itself is formed via the AuthorId and CategoryId properties, which map back to the respective Category and Author objects.

A final class is needed. This class is responsible for managing communication between the entities and the database (once it has been generated). In EF parlance, the object that manages this is called a Context and it is represented by the DbContext class. You create your own Context class that inherits from DbContext. In the code below, you can see that I chose to name my Context as BookContext. It also includes a number of strongly typed DbSet properties. There is one for each entity, and they will map to tables in the database. Notice that they represent pluralised versions of the entities.

using System.Data.Entity;

public class BookContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
    public DbSet<Category> Categories { get; set; }
}

The final step at this stage is to tell the Context where the database should be. You do that by adding a connection string to the Web.Config file:

<?xml version="1.0"?>

<configuration>

    <system.web>
        <compilation debug="false" targetFramework="4.0" />
    </system.web>
    <connectionStrings>
        <add name="BookContext" connectionString="Data Source=|DataDirectory|Books.sdf" 
             providerName="System.Data.SqlServerCe.4.0" />
    </connectionStrings>
</configuration>

The name for the connection string entry is the same as the Context class - BookContext. That is the configuration required by EF Code First. Notice the use of the substitution string |DataDirectory| prior to the database file name - by default, this maps to the App_Data folder in an ASP.NET web site. You don't need any more at this stage. If you run the application - assuming you have a blank page to run - a SQL Compact 4.0 database will be created in the App_Data folder:

Notice that each of the DbSet objects is represented by a table, and see also that the Title for a Book is and nvarchar(200) column - as dictated by the DataAnnotation attribute that was placed on that property. There is also another table - EdmMetadata. This table provides a way for Code First to detect if any changes have occurred to the object model since the database was last generated. If the model changes, an exception is thrown when you next try to run the application. What you can do is to prevent the exception by adding the following to an _AppStart file:

@using EF = System.Data.Entity;
@{
    EF.Database.SetInitializer(new EF.DropCreateDatabaseIfModelChanges<BookContext>());
}

There is a potential clash between the Database classes of System.Data.Entity Database and WebMatrix.Data. Therefore the Database class needs to be referenced by its fully qualified name. To save some typing, the namespace is referenced in a using statement via an alias (EF), which is used in its place. The effect this has is to delete the entire database, data and all, and recreate it silently according to the changed model definition - without data. There is an alternative approach which allows you to provide seed data in the event that the database is dropped and recreated.

All kind of cool and groovy - but what about getting data into the database and displaying it? The following code does exactly that:

@{
    var db = new BookContext();
    var categories = new List<Category>{
        new Category{CategoryName = "WPF"},
        new Category{CategoryName = "JavaScript"},
        new Category{CategoryName = "ASP.NET"}
    };
    foreach(var c in categories){
        db.Categories.Add(c);
    };
    db.SaveChanges();
    
    var authors = new List<Author>{
        new Author{FirstName = "Mike", LastName = "Brind"},
        new Author{FirstName = "Imar", LastName = "Spaanjaars"}
    };
    foreach(var a in authors){
        db.Authors.Add(a);
    };
    db.SaveChanges();
    
    var book = new Book{
        Title = "Beginning ASP.NET Web Pages With WebMatrix",
        Description = "Buy this book!",
        ISBN = "978-1118050484",
        DatePublished = new DateTime(2011,10,18),
        AuthorId = 1,
        CategoryId = 3
    };
    db.Books.Add(book);
    db.SaveChanges();
    
    categories = db.Categories.OrderBy(c => c.CategoryName).ToList();
    authors = db.Authors.OrderByDescending(a => a.FirstName).ToList();
    book = db.Books.FirstOrDefault();
}
<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
        <h1>Books</h1>
        <h3>Categories</h3>
        @foreach(var c in categories){
            @c.CategoryName<br />
        }
        <h3>Authors</h3>
        @foreach(var a in authors){
            @a.FirstName @a.LastName<br />
        }
        
        <h3>@book.Title</h3>
        Description: @book.Description<br />
        ISBN: @book.ISBN<br />
        Publication Date: @book.DatePublished.ToLongDateString()<br />
        Author: @book.Author.FirstName @book.Author.LastName<br />
        Category: @book.Category.CategoryName
    </body>
</html>

Here's a view of the output from running this page:

All communication with the database is done via the DbContext object. New entity instances are attached to the relevant DbSet property, and then the DbContext.SaveChanges method is called, which causes the required SQL to be generated and executed against the database. Retrieving data from the database is also conducted via the DbContext object using LINQ to Entities. Multiple items are transferred to a List<T>, whereas singular items are obtained using the SingleOrDefault() extension method.

What are the benefits of this approach over, say, the Database Helper in WebMatrix 1.0? Probably none, as it stands. However, if you work with Web Pages via Visual Web Developer 2010 SP1, or Visual Studio 2010 SP1, you immediately benefit from strong typing. Object properties are provided through Intellisense:

This gives you compile-time checking which you simply do not enjoy from dynamic types. WebMatrix v 2.0 will include full Intellisense. That will make the Code First story a lot more compelling. Entity Framework is the recommended approach to data access according to Microsoft, which means that development effort will be put behind the framework. It supports the object-oriented approach to working with data that professional developers use, and is not too high in concept counts at a basic level.

A sample site to accompany this article is available at Github. The database will be created the first time you run the site.