All the technologies in the post name are rather hip & happening, right?. There was tons of web stack love shown off at the Microsoft MIX11 conference with MVC3 goodness & the amazing things Entity Framework 4.1 is capable of doing. Azure is off course a beast; but makes it incredibly easy to play with SQL Azure storage in the cloud. And, then there is off course, Windows Phone 7 .. nuf said :). So, could all 4 of these technologies play together? That is what I show in this post, in an oversimplified demo that hopefully gets the point across.

MVC3 Model:

So, let’s start with MVC3. Super nice web development toolset with Razor view engine & unobtrusive JavaScript goodness. Even MVC2 seems archaic with some of the cool stuff we can do with version 3. So, lets start with the intention of writing a simple MVC3 razor view that displays a list of Employees in a table.

Here’s our Model. Doesn’t get simpler, does it?

 
public class Employee 
{ 
    public int EmployeeID { get; set; } 
    public string EmployeeName { get; set; } 
    public string TwitterHandle { get; set; } 
} 

Entity Framework 4.1:

Next, let’s do something magical with EF; the guys on other frameworks could apparently do this for years now. So, just to introduce EF, it is an Object Relational Mapper that has existed in the .NET framework & gotten better with iterations. The goal is to reduce to friction between the database schema & object-relational structure needed in an Object-Oriented application. EF 4.1 further introduces this concept of Code First – let’s focus on the object hierarchy needed in our OOP application; the database side of mappings would be modeled after it. We use a new API called “DBContext” and define a custom one for our Employee entity to give the MVC application some context of how to get its data from a source. Essentially, we are looking for a data-source that presents a list of Employees.

 
public class EmployeeDBContext : DbContext 
{ 
     public DbSet Employees { get; set; } 
} 

EF 4.1 is available as a stand-alone installer or as a Nuget. You could learn more about EF 4.1 & the DBContext (here).

The Controller:

Now that we have a Model, we know exactly what we need to send down to the View for display. So, let’s define our controller. We check the option to create ActionResults for all the CRUD operations based on the Employee Model. The Index method, which displays the initial list of employees, is as below. Again, very simple .. gets a list of Employees from the context & displays them in a table.

 
public ActionResult Index() 
{ 
   using (var db = new EmployeeDBContext()) 
   { 
       return View (db.Employees.ToList()); 
   } 
} 

The view displaying the list of Employee is auto-generated using MVC3 Scaffolding and is tightly bound to the Employee Model. Essentially, an HTML Table displaying a list of Employee with Edit/Delete/Create links.

SQL Azure: 

So, we wanted to use the cloud for storage. Essentially, we want to have an Employee DB in SQL Azure and hit it from our MVC application. However, my SQL Azure server does not have a DB called Employee yet. So, will this work?

One thing that you may want to watch out for are Firewall settings in SQL Azure. For the MVC app to be able to hit Azure storage, you want to make sure that the present IP is allowed in the Firewall rules. Now, how does our app know where the heck to find the Employee DB Context? Glad you asked .. it is through a connection string defined in the Web.config file.

 
<connectionStrings> 
<add name="EmployeeDBContext" connectionString="Data Source=yourAzureServer.database.windows.net; Initial Catalog=Employee; User Id=yourID@yourServer.database.windows.net; Password=yourPwd; Encrypt=True; Trusted_Connection=false;" providerName="System.Data.SqlClient"/> 
</connectionStrings> 

As you notice, the connection string has the same name as our custom DBContext; that is what magically tells the app runtime to know where to find the data context, as the config file is read at runtime. Also, the connetion string mentioned an explicit DB Name of Employee, which is not present in SQL Azure yet. So, what happens when we run our application & navigate to the requisite controller?

Wait, it didn’t blow up!! We got an empty list with table headers as Employee properties, but everything seemed to work. How come? It is through the magic of EF4.1! The runtime figures out that it needs a DB Context in SQL Azure and sees that there is no such DB. So, it goes ahead and creates the needed DB itself! The schema – why off course you defined it in the Model itself and reflections off an Employee object should have a one-on-one match with the DB table schema. So, no more DBAs needed, right? :). There is off course always a price to pay with abstraction; but this is pretty sweet out of the box & let’s you fine-tune DB structures as needed.

Running our application just once meant that EF tried to create the requisite DB as it needed per the DB Context. So, if we head over to Azure, we shall surely see the newly created DB called Employee. If we connect to the DB, we see two tables – one metadata & one actual Employee table. The schema is shown below; notice how it matches our Model as is:

Now, we can implement the CRUD Action Results in our MVC Controller and our DB in Azure will allow for data manipulation. So, we can start adding, modifying & deleting records from the Employee table that was created in the DB.  So, couple of manipulations later, when we revisit the index view of our Employee controller, our view might look like the following, reflecting the data in the Azure Employee table:

So far we have a super-easy MVC3 app that creates a DB in SQL Azure using EF4.1 and then using CRUD Action Results to manipulate data.  How about accessing this data out of DB through other applications? Sure we can make up a connection string to hit up our Employee table in SQL Azure DB; but guess what’s cooler? Exposing our data through OData !!

Windows Phone 7:

Having our data in SQL Azure has the unique advantage of extremely easy consumption from mobile devices. Though CTP and somewhat limiting in granularity, any DB in SQL Azure can be exposed out as an OData feed to allow for data consumption & updates. Please see my previous post (WP7: Say Hello to OData from SQL Azure) on how to consume the Employee data out of SQL Azure in Windows Phone 7. Our simple demo app looks like this:

 

 

 

 

 

 

 

 

 

 

 

 

So, there you have it. A super-simple demo of having an MVC application use EF 4.1 to create & manage a DB in SQL Azure and then Windows Phone consuming the DB data using OData. Hope this was interesting.

Adios!