Entity Framework Code First Development With WebMatrix

The existing data access story for WebMatrix is the Database Helper. Microsoft's recommended data access technology going forward is the Entity Framework (EF). This article explores how to use the Code First capability offered by EF within an ASP.NET Web Pages application.

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.

 

Date Posted: Monday, September 5, 2011 5:27 PM
Last Updated: Sunday, December 23, 2012 9:23 PM
Posted by: Mikesdotnetting
Total Views to date: 31643

11 Comments

Friday, September 9, 2011 3:02 PM - Robby

Mike:
Great stuff. It would really be nice if you had a companion article that illustrated DB-first use of EF in WebMatrix.

Also, hope your new book will come out in Kindle, too. When I am on the road, Kindle is my friend.

Friday, September 9, 2011 5:33 PM - hajan

Great post!

Friday, September 9, 2011 10:08 PM - Mike

@Robby,

The DB-first approach works well when using VWD/VS, because they have the design tools and code generation templates etc. WebMatrix doesn't have those tools. I doubt if it ever will.

Wednesday, September 14, 2011 10:18 AM - Ali

Great post: clever, clear and concise :)

Sunday, September 18, 2011 3:56 PM - reav

oh yeah, and with webmatrix 2 it goes so well!

Wednesday, September 21, 2011 1:00 AM - dc

Do you see any performance hits using codefirst? I've tried code first and it seems to be much slower than any other data access I've used. Especially compared to WebPage's database helper which seems really speedy.

Locally my sites using code first are fine, but when I put them up on hosts they slow down a lot. Many times it takes several times longer for a page to appear versus the same functionality using the database helper. I understand the first hit will be slower due to compiling, but every hit is slower than what I'd consider normal. I was thinking my code first seems either "heavy" or like it's trying to do something and then gives up.

Thursday, February 9, 2012 12:16 PM - Markus Strauss

great article!

but i have the problem that i get the following error when running the web app:

CS0234: Der Typ- oder Namespacename "Entity" ist im Namespace "System.Data" nicht vorhanden. (Fehlt ein Assemblyverweis?)

Translation: The type or namespace "Entity" was not found in "System.Data" (Missing assembly reference?)

Saturday, February 18, 2012 9:15 AM - Mike

@Markus

Make sure that the EntityFramework.dll file is in your bin folder. IF you downloaded EF via the Package Manager as described at the top of the article, this should have happened.

Wednesday, March 7, 2012 8:35 AM - Patrick K Tshiuma

WOW!
I just read this article and I can say it's very helpful since I am still learning how to use razor and webmatrix.

I also have a question:

Does the EntityFramework.dll has to be downloaded for every web application that I'd like to develop with web matrix or is it like VS where I can add reference whenever I need.?

Thanks a lot.

Tuesday, March 13, 2012 6:22 AM - Mike

@Patrick,

WebMatrix doesn't offer a way to add a reference, so you need to download the package. That might change with the next version though. Then again, it might not.

Tuesday, April 17, 2012 7:20 PM - Sulekh Verma

Thanks............
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Bino 11/27/2014 7:05 PM
In response to MVC 5 with EF 6 in Visual Basic - Async and Stored Procedures with the Entity Framework
Copy +...

Manas 11/27/2014 5:30 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, Thank you for awesome article. My concern is it might impact website if we use or is...

priya 11/26/2014 6:50 PM
In response to Create PDFs in ASP.NET - getting started with iTextSharp
very nice.....its save my time...

ransems 11/24/2014 12:29 AM
In response to Adding A Controller
Love the article. I dislike that the world thinks c# articles are the way to go. Love the VB, keep...

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...