Building A Razor Web Pages Site With Database First And Entity Framework

Since I published an item covering Code First development with Entity Framework and WebMatrix, I have had a few requests to provide a guide to working with the Entity Framework from a database first point of view. This post answers those requests and provides a step by step guide to getting up and running with a WebMatrix web site based on the Northwind sample database that comes with SQL Server CE 4.0.

Entity Framework (EF) is a technology that attempts to alleviate the friction caused when developers have to move between the conceptual representation of their application (the entities that represent the application's domain) and the logical or relational representation of the data (typically the database itself). On the one hand, a developer will work with their entities, such as Products or Employees using C# to instantiate them, bind them to grids, change them and so on. On the other hand, the developer will use another language (SQL) to persist entities to a data store (the database), or to retrieve them from persistent storage. EF's role is to map entities to database schema (tables, views etc) and to allow the developer to stick to C# alone - letting EF take care of transitioning C# Linq expressions to SQL to be executed against the database. EF does more than that. It is also a code and database generation tool. In the previous article, EF generated a database, including all tables, relationships etc, based purely on the defintion of some C# classes. In this article, EF will generate class definitions and mappings based purely on the schema of an existing database.

It is possible to generate the various files that EF needs by using a command line tool - edmgen.exe. However, this article looks at the wizard and design tools that are freely available for this task instead. The EF wizard comes as a standard part of both Visual Studio 2010 and Visual Web Developer Express 2010 (VWD). It is not available in WebMatrix 1.0 and there are no indications at the moment that it will be added to WebMatrix in version 2.0 - the Beta of which is available now. So, that leaves VWD as the remaining free option. If you already have VWD installed, you must make sure that you also have Service Pack 1 as this contains the tooling for generating Razor Web Pages sites. If you download VWD, you automatically get SP1 anyway.

Once you have started VWD, choose New Web Site, and then select ASP.NET Web Site (Razor) from the options available

This generates a site based on the Starter Site template. You can remove the Account folder if you like, as well as the StarterSite.mdf file in App_Data

If you remove the database file, you must also comment out or delete the first line in _AppStart.cshtml

Now you need a database. Right click on the App_Data folder in Solution Explorer and choose Add Existing Item.

Navigate to the location of Northwind.sdf. This is typically C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Samples. (x86) will only feature in the file path on 64 bit operating systems, so if you are working with 32 bits, leave that part out. If you cannot find a copy of Northwind.sdf on your system, download this sample from one of my previous articles and borrow the copy from the App_Data folder there. Once you have organised a copy of the database, you are ready to start the EF wizard.

Right click in the Solution Explorer and choose Add New Item. From the options available, select ADO.NET Entity Data Model and name it Northwind.edmx.

You will receive a warning that special files such as those that comprise and ADO.NET Entity Data Model should go in an App_Code folder. Click Yes.

Select Generate From Database when you are prompted to choose the content source for your Model

Click Next and choose New Connection. Select Microsoft SQL Server Compact 4.0 as the Data Source, then browse to the Northwind.sdf file that you have in your App_Data folder

Select that so that the connection string is generated and leave the name of the connection string as NorthwindEntities.

Click Next so that you are taken to the dialog shown below where you specify the objects to be used in the Model. Choose Tables and leave the Model Namespace as NorthwindModel. Then click Finish.

This results in an EDMX file, which in design view, shows the various entities in the model and how they relate to each other, much like a database diagram in SQL Server Management Studio.

The EDMX file is an XML file that defines the storage model - that is the database itself, the conceptual model - that is the entities as classes, and how the two map to each other. The final part of the XML document is an XML representation of the design view that you see in the preceding image.

Once that has been done, you are ready to work with EF. To test, alter the code in Default.cshtml as follows, making sure that you add the using directive at the top of the page:

@using NorthwindModel;
@{  
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Welcome to my Web Site!";
    var db = new NorthwindEntities();
    var products = db.Products.Where(p => p.CategoryID == 2);
    var grid = new WebGrid(products);
}
<div>
    @grid.GetHtml()
</div>

Launch the page in a browser. The result should look similar to the following image.

Microsoft introduced the Database helper with WebMatrix to make data access easy for beginners or people who know a little SQL, and that's fine. However, the Web Pages framework is built on top of ASP.NET and has the whole of the .NET framework behind it, so the Entity Framework, and indeed LINQ to SQL is available to Web Pages developers who want to use these technologies. WebMatrix doesn't include the tooling to make this happen, but Visual Web Developer Express 2010 SP1 does, and it also includes tooling for working with SQL Server Compact databases - and it's free.

 

Date Posted: Friday, December 30, 2011 3:25 PM
Last Updated: Tuesday, March 27, 2012 4:59 PM
Posted by: Mikesdotnetting
Total Views to date: 32460

7 Comments

Sunday, January 1, 2012 2:11 PM - reav

Now maybe its time for Dapper article and rabbit framework?

Saturday, April 14, 2012 8:59 PM - Robby

I have been using EF 4.0 with my SQL 4.0 DB and I love it.

However, I was led to believe that LINQ-to-SQL would not work with SQL CE 4.0.

I am using LINQ-to-Entities and it works fine. If your database design is moderately complex and involves a lot of relationships (resulting in a lot of JOINs), EF really makes things easier and, assuming you're using VWD 2010 SP1, the intellisense you get from the strongly-typed entities (vs the non-typed dynamic records) is a blessing.

Sunday, May 27, 2012 2:17 AM - Jason

Hi Mike, I am just following the tutorial and where you wrote: "...choose Add New Item. From the options available, select ADO.NET Entity Data Model..." - I did that, but I don't have ADO.NET Entity Data Model showing up in my list. I have Visual Studio Ultimate 2010 - I'm going to try and see if I can download it?

Monday, May 28, 2012 7:42 AM - Mike

@Jason,

You should find ADO.NET Entity Data Model as an option in the Data section of the menu

Monday, November 26, 2012 4:13 PM - faisal

its a great example for a beginner but could you add CRUD methods too by using Database First and EntityFramework

that would help alot of people other than me

Saturday, July 6, 2013 3:34 AM - Matt

I discovered that when using EDMX (with MVC4) you have to create a "buddy class" if you want to add data annotations to your entities, since the EDMX file can be over-written if you refresh from the database. None of the posts I read mentioned this, but it works easiest if your EDMX and buddy class are in the same namespace. Since files grab the namespace of the folder they are in, I placed them both in the Models folder. Here are a couple links about using the MetadatType attribute and buddy classes.

http://stackoverflow.com/questions/8689375/mvc-model-from-edmx-file-add-custom-annotations-to-it-and-make-them-stay

http://hartzer.wordpress.com/2010/01/26/mvc-buddy-class/

Sunday, July 7, 2013 8:41 PM - Mike

@Matt,

Recommended practice is to use of ViewModels and to apply attributes to their properties.
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

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!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...