ASP.NET MVC Example Application over Northwind with the Entity Framework


Over last month over the holidays, Lance Olson and I spent some time porting ScottGu’s excellent MVC example over the Entity Framework.  I thought I’d share the results here and get your thoughts\feedback.


 


12-11-08:  I updated a few links and the example to match latest bits. 


For this example, you will need:



 


If you are the type that likes to eat your desert first, you can skip to the end and just download the full working solution (Update: download this version of the sample that works with latest bits). 


 


Getting Started


File/New Project – Select ASP.NET MVC Web Application and Test


image


This creates a single solution with both a web application project as well as a project we can use for unit testing.   Both are pre-populated with the basic stuff you need to get going.


 


 


Creating the Routes


One of the powerful new features that ASP.NET MVC brings to the table is the ability to customize the URLs that access your application. The URL routing feature explicitly breaks the connection between physical files on disk and the URL that is used to access a given bit of functionality.   This is important for Search Engine Optimization as well as general usability of the website.    For example, rather than access http://localhost/Products/ItemDetails.aspx?item=42 you can now very give a pretty URL such as http://localhost/Products/CodFishOil 


This is done by creating a route table in the global.asax file in the MVC Application.   Luckily for us, the defaults included in the template work perfectly this application. 

RouteTable.Routes.Add(new Route
{
Url = “[controller]/[action]/[id]”,
Defaults = new { action = “Index”, id = (string)null },
RouteHandler = typeof(MvcRouteHandler)
});

This code spells out the format of the URLs we want to use for our site.  In particular, a URL of the format


http://localhost/Products/Details/CodFishOil


would translate to the ProductsController class (notice we add the “Controller” suffix to the class name to make these classes stand out in the object model at design time).  Then the Action is a method on that class called Details and finally the argument to that details method is CodFishOil. 


It is of course possible to have other formats, simply by changing he regular expression in the URL pattern string.


 


Creating the Model


The model represents the data you are going to use in the application.   In our case, this is a pretty good place to start the core of the application. 


In the App_Data direction of the MVCApplication copy the Northwind.mdf file.  Northwind is likely the most common example database we have for SqlServer… You can download it from the official location, or for just the raw file, feel free to grab it from here.


image


Next, we need to create a LINQ model on top of northwind to make it easier to work with… You can do this with NHibernate, LinqToSql, Entity Framework , or any other .NET OR-Mapping technology.  As long as it results in .NET Object, the ASP.NET MVC framework can work with it.  In this case I am going to use the Entity Framework.


Right click on the Models directory and select add new item


image


In the dialog select ADO.NET Entity Data Model.


In the wizard, select “Generate from Database” and then go with the default “Northwnd” connection string.


For our demo, we are only going to use the Categories, Products and Suppliers tables, but you could of course extend this demo to include a richer feature set.  But for now, unselect the Views and Stored Procedures and all the tables except those three..


image


When you click finished, VS will create a set of .NET classes that are custom built for accessing this database and we get a nice designer for visualizing the data relationships.


image


Notice, that the default names given to these classes still use the plural names from the database, but in our OR-Mapping they represent single instances, so to make the code easier to read, go in and change all the table names to their appropriate singular form: Category, Product and Supplier.   Then Navigation properties on Product needs to be singular as there is only one Category and Suppler for each product.


image


Next we need to cleanup the namespace so things look pretty in our code… Right click on the design surface then set the properties such that namespace is “NorthwindModels” and the Entity Container name to “NorthWindEntities”


image


 


While we are not 100% done with the model, we have gotten the lion’s share of it complete.. Let’s switch over and look at the controller.


Creating the Controller


Right click on the Controller directory and select “Add new Item”.  In the dialog find MVC Controller and be sure to give it a name that ends in the Controller suffix.  In our case we are writing the ProductsController. 


image


OK, we are ready to start working in  ProductsController.cs


The goal of the controller is to prepare the model for the view.    We want to bring as much logical as possible out of the view because it is so hard to test in the view.  So in the controller, we are going to be accessing the Model and getting it all set up so all the view has to do is spit out some data. 


The first thing we need, is access to our database. 


1. Add the right namespaces… Linq and a reference to our OR mapping.

using System.Linq;
using NorthwindModel;

2. Next, we create an instance of the NorthwindEntities container class.  Nearly all of our actions will access this class.

    public class ProductsController : Controller
{
NorthwindEntities Northwind = new NorthwindEntities();

 


OK, now we are ready to create our first action.. Listing all the categories.  Remember the job of the controller is to prepare the model for the view..   When defining a new action, I like to start off by putting a comment that reminds me what the URL is that access this functionality. 


The next thing we do is access the Categories table from the model.  I snap the results to a generic collection class (you may have to add a reference to System.Collections.Generic) then we pass the results to the view named “Categories”.    This is a very simple example, later we will see more complex logic here. 


//URL: http://localhost/Products/Categories
[ControllerAction]
public void Categories()
{
    List<Category> categories = Northwind.Categories.ToList();
    RenderView(“Categories”, categories);
}


Next step, we need to create the “Categories” view..


Creating the View


Right click on the Views folder, add new Directory “Products”.  This so we can organize all our views cleanly.  The Right click on Views/Products folder and add new item MVC View Content Page.  We are going to leverage the Master Page that comes with the default project to make this look a little more pretty. 


image


Call it Categories.aspx… It is important that the view name matches the first argument to the RenderView method above.


The default project puts the master page in Views/Shared/Site.Master


image


In order to get the strongly typed access to the ViewData we are passing in from the controller, we need to tell the View Page, the type to expect.  This is done by opening up the codebehind (Categories.aspx.cs) and changing the derived type from:

    public partial class Categories : ViewPage
{
}
to:

public partial class Categories : ViewPage< List<Category> >
{
}

Then you simply write clean, simple, designable HTML.  Notice here I am looping over all the items returned in ViewData and pushing them out as a link.  I use the MVC helper method Html.ActionLink to create the URL for the List action with the appropriate product ID. 


 

    <h2>Browse Products</h2>

<ul class=”categorylisting”>

<% foreach (var category in ViewData) { %>

<li>
<%= Html.ActionLink(category.CategoryName, new { action=“List”, id=category.CategoryName }) %>
</li>

<% } %>

</ul>


OK, we are finally ready to run it! 


Hit F5, and navigate to the controller action we just wrote on http://localhost:64701/products/Categories 


image


Clicking on any of the links give you an error, because we haven’t written the List action yet.. we will do that next. 


As an aside, if like me, you are in the habit of using “View in Browser” on aspx pages you develop, you are likely to see this error.  To reproduce, right click on Categories.aspx and select View in browser. 


image


You get an error. Why?  Well, remember with the MVC model, all execution goes through the controller, the views themselves are not runnable.   Future tooling will make this better, but in the mean time, use F5 or you can “run in browser” with default.aspx.. just be sure you have built the solution first!


 


The List Action, View


OK, let’s now go back and add the List action that we are missing.  What we need to do here is find all the products with a give Category.   First I need to get all the products out of the model, then I need to ensure that the Category references are loaded.  The entity framework offers an explicit loading model by default.  As such you have to explicitly load any tables you need.  And finally we render the view. 


 

//example URL:http://localhost:64701/products/List/Confections
[ControllerAction]
public void List(string id)
{
List<Product> products = Northwind.GetProductsByCategory(id);

//prepare the view by explicitly loading the categories
products.FindAll(p => p.Category == null).ForEach(p => p.CategoryReference.Load());

RenderView(“ListingByCategory”, products);

}


Notice, i am calling a custom method on the NorthwindDataContext class… Personally I like the idea of keeping all the data access logic encapsulated in that class.  So to define this method, Right click on the Model, add new Item, select CodeFile and name it NorthwindDataContext.cs and give it the implementation.

using System;
using System.Collections.Generic;
using System.Linq;

namespace NorthwindModel
{
public partial class NorthwindEntities
{

}
}


 


Now you can easily add data access methods to this class… such as the GetProductsByCategory() method we use above. 

public List<Product> GetProductsByCategory(string category)
{
return Products.Where(p => p.Category.CategoryName == category).ToList();
}

Next, we need to add the ListingByCategory view… We follow the same steps from above to add a ListingByCategory.aspx page in the Views/Products/ directory. 


This time, we should make the ViewData be of type List<Products>

public partial class ListingByCategory : ViewPage< List<Product> >
{
}

Next, to implement the view, we simply loop over the view data and spit it out in the right format.


<%–Print out the catagory name–%>
<% foreach (var product in ViewData) { %>
<% if (product.Category.CategoryName != null) { %>
<h2> <%=product.Category.CategoryName %></h2>
<% break; %>
<%} //end if %>
<%}//end foreach %>

<ul class=”productlist”>

<% foreach (var product in ViewData) { %>

<li>
<
img src=”/Content/Images/<%=product.ProductID%>.jpg” alt=”<%=product.ProductName %>” />
<
br/>
<
a href=”/Products/Detail/<%=product.ProductID %>“> <%=product.ProductName %> </a>
<
br />
Price: <%=String.Format(“{0:C2}”, product.UnitPrice)%>
<span class=”editlink””>
(<%= Html.ActionLink(“Edit”, new { Action=“Edit”, ID=product.ProductID })%>)
</span>

</li>

<% } %>

</ul>


Once you add the /Content/Images directory from the sample project you get this:


image


 


Unit Testing


One of the primary  reasons for using the MVC model is to enable Test Driven Development and more generically Unit Testing.  Unit testing UIs is notoriously difficult.  Automation is fragile and hiring an army of testers to click on buttons is not always cost effective.  The model MVC is going for is to separate out as much code (and therefore potential bugs) as possible into the Model and the Controller where they are testable.


So, we are going to write Unit Tests for the two control methods we just created and the corresponding logic in the model. 


First, we need to setup our testing environment.  The default template already created a Test project we can start to customize. 


1. In the MvcApplicationTest project, create a new class called ProductControllerTest.cs


2. Add a method for testing the Categories action we defined above.

namespace MvcApplicationTest.Controllers
{

[TestClass]
public class ProductsControllerTest
{

[TestMethod]
public void Categories()
{

}
}


3. We want to test the controller we wrote, but we don’t want to load IIS and all of ASP.NET… we want to have the leanest, most simple system we can to avoid test breaks, etc.   To that end, I created a test specific subclass of the ProductsController we defined above.  In this subclass I override the RenderView method.  Instead of calling out to ASP.NET, my test specific implement simply captures the arguments so they can be queried later in the test.

class TestProductsController : ProductsController
{
public string ViewName { get; set; }
public string MasterName { get; set; }
public new object ViewData { get; set; }
protected override void RenderView(string viewName, string masterName, object viewData)
{
ViewData = viewData;
MasterName = masterName;
ViewName = viewName;
}
}

 


4. Now we implement the test!  First we create an instance of our test controller, then I create some dummy  (or mock) data.  The idea here is that we don’t want to hit the production database (or any database for that mater) in our tests.  We want exactly the data we need for the test, no more, no less.  Then we set up the NorthwindDataContext to use our data rather than the data from the database.  Then we call the controller action we are trying to test.  Finally, we assert a few things..  that the type of the ViewData is right, that the number  of items returned is right, etc.   Notice, you will need to add a reference to the EntityFramework (System.Data.Entity.dll) in your test project to use these EF generated types like Product, Category, etc. 


 

[TestMethod]
public void Categories()
{
// Create Products Controller
TestProductsController controller = new TestProductsController();

List<Category> l = new List<Category>();
l.Add(new Category() { CategoryName = “Drink” });
l.Add(new Category() { CategoryName = “Food” });
l.Add(new Category() { CategoryName = “Cheese” });

controller.Northwind.TheCategories = l.AsQueryable();

// Invoke Controller Action
controller.Categories();

// Verify Detail Action Behavior
Assert.AreEqual(typeof(List<Category>),
controller.ViewData.GetType(),
“Product object Passed to View”);

Assert.AreEqual(3,
((List<Category>)controller.ViewData).Count,
“Correct Number of Catagories returned”);

Assert.AreEqual(“Food”,
((List<Category>)controller.ViewData)[1].CategoryName,
“Correct Product Object Passed to View”);

Assert.AreEqual(“Categories”,
controller.ViewName,
“Correct View Rendered”);

}


5. Set the connection string in the test project so that it access the metadata from EF model, but does not open the database.  In MVCApplicationTest add an app.config file to the root of the project with the this connection string.

<?xml version=1.0?>
<
configuration>
<
connectionStrings>
<
add name=NorthwindEntities
connectionString=metadata=res://*/;provider=System.Data.SqlClient;
providerName=System.Data.EntityClient/>
</
connectionStrings>
</
configuration>

 


Now, we need to go back to the EDM designer and set it to embed the metadata for the model as part of the assembly.  This makes our connection string above much more simple.  Double click on the edmx file, then click on the conceptual model in the model browser and in the property sheet you change the property Metadata Artifact Processing from “Copy to Output Directory” to “Embed in  Output Assembly”


 image


 


6. Now we need to do just a bit of setup in the MVCApplication to enable a great testing experience… There are a few tricks here and what I am showing is NOT the definitive model (there isn’t a definitive model yet)… but I like it, so what the heck!


MVCApplication/Model/NorthwindDataContext.cs add the following encapsulation of the Categories property. 


 

private IQueryable<Product> savedProducts = null;
public IQueryable<Product> TheProducts
{
get
{
if (savedProducts == null) savedProducts = this.Products;
return savedProducts;
}
set
{
savedProducts = value;
}
}

Then, make sure that all access go through this abstraction.  For example:

public List<Product> GetProductsByCategory(string category)
{
return TheProducts.Where(p => p.Category.CategoryName == category).ToList();
}

Finally, we need to make sure the NorthwindEntities is accessible from the test classes, so in ProductsController.cs add the “public” modifier.

public NorthwindEntities Northwind = new NorthwindEntities();

 


Now, we are able to run our test.  You can do this by setting the test project as being the “startup project” (right click on the test project) or you can select Test/Run menu option. 


image


 


Well, that is the lap around MVC+EF… There is a lot more to the sample I posted.  If there is interest and I get the time, I will walk through the rest in future blog posts, but all the source code is there, so you can have fun with it on your own!


 


Enjoy!


You can download the full working example here (Updated to work with latest bits).

Comments (41)

  1. Carmelo Lisciotto says:

    Some very good information here!

    Carmelo Lisciotto

  2. Carmelo Lisciotto says:

    Some very good information here!

    Carmelo Lisciotto

  3. Carmelo Lisciotto says:

    Some very good information here!

    Carmelo Lisciotto

  4. ASP.Net MVC Sample Application using ADO.Net Entity Framework By now, you already know that I like walkthoughs

  5. You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  6. mike says:

    Interesting read, very ‘edgy’ stuff: MVC + EF.

    The tests in this example seem pretty useless to me, it’s like your defining the outcome and then testing it, ofcourse it’s good. Also, testing if the correct view is rendered is going to be Tedious!

    Can you post a more realistic example, maybe something from your real work? Testing the ‘hello world’ scenarios is not really convincing me.

    Lastly, the part about Metadata Artifact Processing could use some more explanation.

    Thanks!

  7. Thomas says:

    This is an excellent article on MVC however it would be great if you could include more specific example for instance how do you upload an image of the product using MVC architecture, and how to use thrid party controls such as Telerik or Infragistics.

    Thank you

  8. zbrong says:

    too complex

  9. rocky says:

    Cool……MVC+Entity Framework + Linq + Unit testing

  10. SoftMind says:

    Hi,

    Any idea, when will it be possible to run such MVC applications on VWD 2008….?

    Since VWD is an IDE, just any one can afford to download, i think, full support for this IDE is a must.

    Thanks

  11. Etienne says:

    Greetings,

    I have been watching and noticing that you and increasingly many other developers are switching from VB to C# and it’s variants.

    Do you think there’s that much future left for VB?

    cheers,

    Etienne

  12. ASP.NET ASP.NET Hang and OutOfMemory exceptions caused by STA components [Via: Tess ] Code Camps New…

  13. Link Listing – January 29, 2008

  14. Corey says:

    Great examples, although I would love to see more samples with user controls, and ajax. Two things I can’t seem to wrap my mind around with in the MVC world.

  15. PK says:

    Very good post 🙂 I think the opening paragraphs need to explain (in layman’s terms) the differences between LinqToSql and EF and why it might be appropriate to go EF or not. To me, this looks very very similar to LinqToSql so i’m not sure why i would use EF.

    Secondly :-

       "5. Set the connection string in the test project so that it access the metadata from EF model, but does not open the database".

      It think this needs to be explained a bit more -> what does it mean, why would you do that?

    Nice guys 🙂

  16. PK says:

    Oh – one more thing to add.

    Please show us how we could do a simple MVC View User Control that has some ‘actions’ associated to it. Eg, a listing all the products, and the action could be a ‘sort by’ or ‘order by’ action or a filter action .. all wrapped up in a resusable MVC View User Control.

    Cheers!

  17. Sylvain says:

    Hello, i can’t download the full solution, my browser wrote adress not found, maybe link dead, or need some registration ?

  18. Kikil says:

    Hello,

    Same problem here, impossible to download the full solution archive.

    It would be very kind if you could fix the link.

    Thank you very much !

  19. jnw says:

    Very cool!  In my humble opinion, MVC+EF (+TDD) can really change the game, so the unit testing part is my favorite here. Agree with what PK said, there seems to be extremely little document on EF, so distinguishing it from LingtoSQL will be good. More importantly, I would like to know unit testing without connecting to the database. What did it really do when you change from “Copy to Output Directory” to “Embed in  Output Assembly”? If I have a Entity data model created from scratch (not from a database), would it enable me to use the EDM independently as an in-memory store?

    Thanks and great work!

  20. jnw says:

    Change to "Embed in Output Assembly" doesn’t seem to disconnect the EDM from the underlying database. Just tried:).

    Played around for a couple of hours now, but am still confused on how to access the model without opening the DB. Any suggestions?

    On why I want to do it? Besides making the testing simple as mentioned in the post; I also want to design the model ahead of the DB tables. Just want to get the program running, then later map the model to a database (manually? is it possible to generate schema from an entity model?).

    Thanks,

  21. @PK – Check out this article on MSDN: Introducing LINQ to RElational Data [http://msdn2.microsoft.com/en-us/library/cc161164.aspx] to get an idea of when you would use LINQ to SQL vs. when you would use EF.

    @jnw – When you embed, you are embedding the schema files so you don’t have to copy them around. Mike Pizzo recently demo’d starting from an empty model in this webcast [http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032364889&CountryCode=US]. It’s the first demo.

    hth

    julie

  22. jnw says:

    Thanks a lot Julie.

    In case someone is also curious about testing without "opening the database", the answer lies in the step 6 (not step 5) of the Unit testing part.

    Thanks for the great post.

  23. Guru Stop says:

    It started an email Mohamed Hossam (AKA, Bashmohandes) sent to my company’s local office here in Egypt

  24. Resumo da semana – 07/02/2008

  25. For the past year, there seems to be an overwhelming community outcry for getting back to a simpler separation

  26. TerryLee says:

    概述春节后的第一期推荐系列文章,共有10篇文章:1.ASP.NETMVCExampleApplicationoverNorthwindwiththeEntityFramework…

  27. Jacky_Xu says:

    概述 春节后的第一期推荐系列文章,共有10篇文章: 1.ASP.NETMVCExampleApplicationoverNorthwindwiththeEntityFr…

  28. Q.Lee.lulu says:

    首先,谢谢TerryLee的推荐系列,本文就是在他的推荐系列看到的.

    原文地址:ASP.NETMVCExampleApplicationoverNorthwindwiththeEnt…

  29. Q.Lee.lulu says:

    导读:来自于Brad Abrams 的一篇很棒的入门文章,使用ASP.NET MVC Framework和ADO.NET Entity Framework开发一个简单的示例,延续了Brad Abrams 的一贯风格,图文并茂,相信对于初学者朋友有很大的帮助。(引用一下TerryLee的导读,^_^)

  30. What about validation?  Validation Application Block?

    As some else suggested, what about ajax?  Customers are increasing asking about the UI and Ajax.  How does MVC deal with this?

  31. Chris Kinsman says:

    Looks like Html.ActionLink has changed in the March CTP.  Your samples break pretty consistently there.  It isn’t mentioned in the release notes…

  32. He adaptado para Visual Web Developer Express una de las aplicaciones de ejemplo para ASP.NET MVC que

  33. MinSou says:

    Great example and a good introduction to EF and ASP.net MVC !

  34. MinSou says:

    I upgrade your sample to works with the latest version of ASP.net MVC (ASP.Net MVC Preview 2) and publish it on code.msdn.microsoft.com

    http://code.msdn.microsoft.com/AspNetMvcAndEFSample

  35. Keith Patton says:

    whilst asp.net mvc affords testability, the entity framework DOES NOT! POJO is not supported and you cannot swap out the EF’s control over the persistence mechanism. I don’t see the point of using the MVC for testability, when your tests will still have and end to end reliance on the EF with DB access. It is possible tobreak this dependency which much work, and it simply looks like EF has not been designed with testability in mind.

  36. Dating says:

    Over last month over the holidays, Lance Olson and I spent some time porting ScottGu’s excellent MVC example over the Entity Framework . I thought I’d share the results here and get your thoughtsfeedback. For this example, you will need: VS2008 (Fre

  37. Weddings says:

    Over last month over the holidays, Lance Olson and I spent some time porting ScottGu’s excellent MVC example over the Entity Framework . I thought I’d share the results here and get your thoughtsfeedback. For this example, you will need: VS2008 (Fre

  38. XGU_Winner says:

    先来发一通牢骚。 其实这是一篇迟发布近2个月的文章。事实上在ASP.NETMVCPreview2发布之前我就已经将这篇文章的所有内容准备完毕了。当时想,就等Preview2发布吧,而真一…

Skip to main content