Build a Location API Using Entity Framework Spatial and Web API, on Windows Azure Web Sites

As announced by Scott Guthrie recently, Windows Azure Web Sites now supports the .NET Framework 4.5. Some awesome ASP.NET features are now available to web developers who want to host their ASP.NET applications on Windows Azure following the web sites offering getting support for .NET 4.5. One feature I’m especially excited about is Entity Framework Spatial support. Only available in .NET 4.5, EF Spatial is something that gives developers who want to build location-aware applications the ability to easily save and retrieve location data without having to invent crazy solutions using SQL code. I’ve implemented the Haversine formulausing a SQL stored procedure in the past, and I can speak from experience when I say that EF Spatial is about 10,000 times easier and more logical. Don’t take my word for it, though. Take a look at the sample code I’ll show you in this blog post, which demonstrates how you can develop a location-aware API using ASP.NET Web API, EF Spatial, and host the whole thing on Windows Azure Web Sites.

Creating the Site in Windows Azure

Before diving into code I’ll go out to the Windows Azure portal and create a new web site. For this API example, I create a site with a database, as I’ll want to store the data in a Windows Azure SQL Database. The screen shot below shows the first step of creating a new site. By simply selecting the new web site option, then selecting “with database,” I’m going to be walked through the process of creating both assets in Windows Azure in a moment.

The first thing Windows Azure will need to know is the URL I’ll want associated with my site. The free Windows Azure Web Sites offer defaults to [yoursitename].azurewebsites.net, so this first step allows me to define the URL prefix associated with my site.

Simultaneously, this first step gives me the opportunity to define the name of the connection string I’ll expect to use in my Web.config file later, that will connect the site to the Windows Azure SQL Database I’ll create in a moment.

The last steps in the site creation process will collect the username and SQL Server information from you. In this example, I’m going to create a new database and a new SQL Server in the Windows Azure cloud. However, you can select a pre-existing SQL Server if you’d prefer during your own setup process.

I specifically unchecked the “Configure Advanced Database Settings” checkbox, as there’s not much I’ll need to do to the database in the portal. As you’ll see in a moment, I’ll be doing all my database “stuff” using EF’s Migrations features.

Once I’ve entered the username I’d like to use, the password, and selected (or created) a SQL server, I click the check button to create the site and the SQL database. In just a few seconds, both are created in Windows Azure, and I can get started with the fun stuff – the c0d3z!

Preparing for Deployment

Just so I have a method of deploying the site once I finish the code, I’ll select the new site from the Windows Azure portal by clicking on it’s name once the site-creation process completes.

The site’s dashboard will open up in the browser. If I scroll down, the Quick Glance links are visible on the right side of the dashboard page. Clicking the link labeled Download Publish Profile will do just that – download a publish settings file, which contains some XML defining how Visual Studio or WebMatrix 2 (or the Web Deploy command line) should upload the files to the server. Also contained within the publish settings file is the metadata specific to the database I created for this site.

As you’ll see in a moment when I start the deployment process, everything I need to know about deploying a site and a database backing that site is outlined in the publish settings file. When I perform the deployment from within Visual Studio 2012, I’ll be given the option of using Entity Framework Migrations to populate the database live in Windows Azure. Not only will the site files be published, the database will be created, too. All of this is possible via the publish settings file’s metadata.

Building the API in Visual Studio 2012

The code for the location API will be relatively simple to build (thanks to the Entity Framework, ASP.NET, and Visual Studio teams). The first step is to create a new ASP.NET MVC project using Visual Studio 2012, as is shown below. If you’d rather just grab the code than walk through the coding process, I’ve created a public GitHub.com repository for the Spatial Demo solution, so clone it from there if you’d rather view the completed source code rather than create it from scratch.

Note that I’m selecting the .NET Framework 4.5 in this dialog. Previous to the 4.5 support in Windows Azure Web Sites, this would always need to be set at 4.0 or my deployment would fail. As well, I would have had compilation issues for anything relating to Entity Framework Spatial, as those libraries and namespaces are also only available under .NET 4.5. Now, I can select the 4.5 Framework, satisfy everyone, and keep on trucking.

In the second step of the new MVC project process I’ll select Web API, since my main focus in this application is to create a location-aware API that can be used by multiple clients.

By default, the project template comes with a sample controller to demonstrate how to create Web API controllers, called ValuesController.cs. Nothing against that file, but I’ll delete it right away, since I’ll be adding my own functionality to this project.

Domain Entities

The first classes I’ll add to this project will represent the entity domains pertinent to the project’s goals. The first of these model classes is the LocationEntity class. This class will be used in my Entity Framework layer to represent individual records in the database that are associated with locations on a map. The LocationEntity class is quite simple, and is shown in the gist below.

1234567

public class LocationEntity { public int Id { get; set; } public string Name { get; set; } public string Address { get; set; } public DbGeography Coordinates { get; set; } }

view rawLocationEntity.cs hosted with ❤ by GitHub

Some of the metadata associated with a DbGeography object isn’t easily or predictably serialized, so to minimize variableness (okay, I’m a control freak when it comes to serialization) I’ve also created a class to represent a Location object on the wire. This class, the Location class, is visible in the following gist. Take note, though, it’s not that much different from the typical LocationEntity class aside from one thing. I’m adding the explicit Latitude and Longitude properties to this class. DbGeography instances offer a good deal more functionality, but I won’t need those in this particular API example. Since all I need is latitude and longitude in the API side, I’ll just work up some code in the API controller I’ll create later to convert the entity class to the API class.

12345678

public class Location { public string Name { get; set; } public string Address { get; set; } public double Latitude { get; set; } public double Longitude { get; set; } public double Distance { get; set; } }

view rawLocation.cs hosted with ❤ by GitHub

Essentially, I’ve created a data transfer object and a view model object. Nothing really new here aside from the Entity Framework Spatial additions of functionality from what I’ve done in previous API implementations which required the database entity be loosely coupled away from the class the API or GUI will use to display (or transmit) the data.

Data Context, Configuring Migrations, and Database Seeding

Now that the models are complete I need to work in the Entity Framework “plumbing” that gives the controller access to the database via EF’s magic. The first step in this process is to work up the Data Context class that provides the abstraction layer between the entity models and the database layer. The data context class, shown below, is quite simple, as I’ve really only got a single entity in this example implementation.

12345678

public class SpatialDemoContext : System.Data.Entity.DbContext { public SpatialDemoContext() : base("name=SpatialDemoConnectionString") { }   public System.Data.Entity.DbSet<LocationEntity> Locations { get; set; } }

view rawSpatialDemoContext.cs hosted with ❤ by GitHub

Take note of the constructor, which is overridden from the base’s constructor. This requires me to make a change in the web.config file created by the project template. By default, the web.config file is generated with a single connection string, the name of which is DefaultConnection. I need to either create a secondary connection string with the right name, change the default one (which I’ve done in this example), or use Visual Studio’s MVC-generation tools to create an EF-infused controller, which will add a new connection string to the web.config automatically. Since I’m coding up this data context class manually, I just need to go into the Web.config and change the DefaultConnection connection string’s name attribute to match the one I’ve added in this constructor override, SpatialDemoConnectionString. Once that’s done, this EF data context class will use the connection string identified in the configuration file with that name.

During deployment, this becomes a very nifty facet of developing ASP.NET sites that are deployed to Windows Azure Web Sites using the Visual Studio 2012 publishing functionality. We’ll get to that in a moment, though…

EF has this awesome feature called Migrations that gives EF the ability of setting up and/or tearing down database schema objects, like tables and columns and all indexes (oh my!).  So the next step for me during this development cycle is to set up the EF Migrations for this project. Rowan Miller does a great job of describing how EF Migrations work in this Web Camps TV episode, and Robert Green’s Visual Studio Toolbox show has a ton of great content on EF, too, so check out those resources for more information on EF Migrations’ awesomeness. The general idea behind Migrations, though, is simple – it’s a way of allowing EF to scaffold database components up and down, so I won’t have to do those items using SQL code.

What’s even better than the fact the EF has Migrations is that I don’t need to memorize how to do it because the NuGet/PowerShell/Visual Studio gods have made that pretty easy for me.  To turn Migrations on for my project, which contains a class that derives from EF’s data context class (the one I just finished creating in the previous step), I simply need to type the command enable-migrations into the NuGet package management console window.

Once I enable migrations, a new class will be added to my project. This class will be added to a new Migrations folder, and is usually called Configuration.cs. Within that file is contained a constructor and a method I can implement however I want called – appropriately – Seed. In this particular use-case, I enable automatic migrations and add some seed data to the database.

123456789101112131415161718192021

internal sealed class Configuration : DbMigrationsConfiguration<SpatialDemo.Models.SpatialDemoContext> { public Configuration() { AutomaticMigrationsEnabled = true; }   protected override void Seed(SpatialDemo.Models.SpatialDemoContext context) { context.Locations.AddOrUpdate((x) => x.Name, new LocationEntity { Name = "The Wing Dome", Address = "7818 Greenwood Avenue North, Seattle WA", Coordinates = DbGeography.FromText("POINT(-122.35499 47.68609)") }, new LocationEntity { Name = "Kona Kitchen", Address = "8501 5th Ave NE, Seattle WA", Coordinates = DbGeography.FromText("POINT(-122.32338 47.69068)") }, new LocationEntity { Name = "Wing Central on the Ave", Address = "4524 University Way Northeast, Seattle WA", Coordinates = DbGeography.FromText("POINT(-122.31302 47.66182)") }, new LocationEntity { Name = "J. Michael's Pub and Eatery", Address = "15770 Redmond Way, Redmond WA", Coordinates = DbGeography.FromText("POINT(122.12975 47.67434)") }, new LocationEntity { Name = "Charlies Flame Broiled Burgers", Address = "1006 Lake Street South, Kirkland WA", Coordinates = DbGeography.FromText("POINT(-122.20642 47.6674)") }, new LocationEntity { Name = "The Wing Dome", Address = "232 Central Way, Kirkland WA", Coordinates = DbGeography.FromText("POINT(-122.19963 47.67861)") }, new LocationEntity { Name = "Jillian's Billiards Club", Address = "731 Westlake Avenue North, Seattle WA", Coordinates = DbGeography.FromText("POINT(-122.33939 47.62633)") }, new LocationEntity { Name = "Malarkey's Sports Grill", Address = "1025 Northwest Gilman Boulevard, Issaquah WA", Coordinates = DbGeography.FromText("POINT(-122.05334 47.54516)") } ); } }

view rawConfiguration.cs hosted with ❤ by GitHub

Enabling automatic migrations basically assumes any changes I make will automatically be reflected in the database later on (again, this is super-nifty once we do the deployment, so stay tuned!).

Quick background on what types of locations we’ll be saving… My wife and I moved from the Southeast US to the Pacific Northwest region recently. Much to our chagrin, there are far fewer places to pick up great chicken wings than there were in the Southeast. So, I decided I needed to use our every-Sunday-during-football snack of chicken wings as a good use-case for a location-based app. What a better example than to give you a list of good chicken wing restaurants listed in order of proximity? Anyway, that’s the inspiration for the demo. Dietary recommendation is not implied, BTW.

The API Controller Class

With all the EF plumbing and domain models complete, the last step in the API layer is to create the API controller itself. I simply add a new Web API controller to the Controllers folder, and change the code to make use of the plumbing work I’ve completed up to now. The dialog below shows the first step, when I create a newLocationController Web API controller.

This controller has one method, that takes the latitude and longitude from a client. Those values are then used in conjunction with EF Spatial’s DbGeography.Distance method to sort the records from closest in proximity, then the first five records are returned. The result of this call is that the closest five locations are returned with a client provides its latitude and longitude coordinates to the API method. The Distance method is used again to determine how far away each location is from the provided coordinates. The results are then returned using the API-specific class rather than the EF-specific class (thereby separating the two layers and easing some of the potential serialization issues that could arise), and the whole output is formatted to either XML or JSON and sent down the wire via HTTP.

123456789101112131415161718192021222324252627282930313233

public class LocationController : ApiController { SpatialDemoContext db;   public LocationController() { db = new SpatialDemoContext(); }   protected override void Dispose(bool disposing) { db.Dispose(); base.Dispose(disposing); }   public IEnumerable<Location> Get(string longitude, string latitude) { var qry = string.Format("POINT({0} {1})", longitude, latitude);   var results = (from s in db.Locations orderby s.Coordinates.Distance(DbGeography.FromText(qry)) select new Location { Name = s.Name, Address = s.Address, Longitude = s.Coordinates.Longitude.Value, Latitude = s.Coordinates.Latitude.Value, Distance = (s.Coordinates.Distance(DbGeography.FromText(qry)).Value * 0.000621371) }).Take(5).ToList<Location>();   return results; } }

view rawLocationController.cs hosted with ❤ by GitHub

At this point, the API is complete and can be deployed to Windows Azure directly from within Visual Studio 2012 using the great publishing features created by the Visual Studio publishing team (my buddy Sayed Hashimiloves to talk about this stuff, so ping him on Twitter if you have any questions or suggestions on this awesome feature-set).

Calling the Location API using an HTML 5 Client

In order to make this a more comprehensive sample, I’ve added some HTML 5 client code and Knockout.js-infused JavaScript code to the Home/Index.cshtml view that gets created by default with the ASP.NET MVC project template. This code makes use of the HTML 5 geospatial capabilities to read the user’s current position. The latitude and longitude are then used to call directly the location API, and the results are rendered in the HTML client using a basic table layout.

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253

<div id="body"> <section class="featured"> <div class="content-wrapper"> <hgroup class="title"> <h2>Where are all the good chicken wings in the Puget Sound Area?</h2> </hgroup> </div> </section> <section class="content-wrapper main-content clear-fix"> <table> <tbody data-bind="foreach: locations"> <tr> <td data-bind="text: name"></td> <td data-bind="text: address"></td> <td data-bind="text: distance"></td> </tr> </tbody> </table> </section> </div>   @section scripts{ <script src="~/Scripts/knockout-2.1.0.js"></script> <script type="text/javascript">   function pageViewModel() { var self = this; self.locations = ko.observableArray([]); self.handle_geolocation_query = function (position) { var url = '/api/location?latitude=' + position.coords.latitude + '&longitude=' + position.coords.longitude; $.get(url, function (data) { $(data).each(function (i, item) { var newLocation = new locationViewModel(item.Name, item.Address, item.Distance); self.locations.push(newLocation); }); }); }; self.initialize = function () { navigator.geolocation.getCurrentPosition(self.handle_geolocation_query); } };   function locationViewModel(nm, addr, dist) { this.name = ko.observable(nm); this.address = ko.observable(addr); this.distance = ko.observable(parseFloat(dist).toFixed(2) + ' miles'); };   var model = new pageViewModel(); ko.applyBindings(model); model.initialize(); </script> }

view rawindex.cshtml hosted with ❤ by GitHub

The final step is to deploy the whole thing up to Windows Azure Web Sites. This is something I wasn’t able to do until last week, so I’m super-stoked to be able to do it now and to share it with you on a demo site, the URL of which I’ll hand out at the end of this post.

One Last NuGet to Include

Entity Framework Spatial has some new data types that add support for things like… well... latitude and longitude, in this particular case. By default, these types aren’t installed into a Windows Azure instance, as they’re part of the database SDK. Most times, those assemblies aren’t needed on a web server, so by default you won’t have them when you deploy. To work around this problem and to make Entity Framework Spatial work on the first try following your deployment to Windows Azure, install the Microsoft.SqlServer.Types NuGet packageinto your project by typing install-package Microsoft.SqlServer.Types in the Package Manager Console or by manually finding the package in the “Manage NuGet References” dialog.

Thanks to Scott Hunter for this extremely valuable piece of information, which I lacked the first time I tried to do this. This solution was so obvious I hid in my car with embarrassment after realizing how simple it was and that I even had to ask. NuGet, again, to the rescue!

Once this package is installed, deploying the project to Windows Azure will trigger automatic retrieval of that package, and the support for the location data types in SQL Server will be added to your site.

Publishing from Visual Studio 2012 is a Breeze

You’ve probably seen a ton of demonstrations on how to do deployment from within Visual Studio 2012, but it never ceases to amaze me just how quick and easy the team has made it to deploy sites – with databases – directly up to Windows Azure in so few, simple steps. To deploy to a site from within Visual Studio 2012, I just right-click the site and select – get this – Publish. The first dialog that opens gives me the option to import a publish settings file, which I downloaded earlier just after having created the site in the Windows Azure portal.

Once the file is imported, I’m shown the details so I have the chance to verify everything is correct, which I’ve never seen it not be, quite frankly. I just click Next here to move on.

This next step is where all the magic happens that I’ve been promising you’d see. This screen, specifically the last checkbox (highlighted for enthusiasm), points to the database I created earlier in the first step when I initially created the “site with database” in the Windows Azure portal. If I check that box, when I deploy the web site, the database schema will be automatically created for me, and the seed data will be inserted and be there when the first request to the site is made. All that, just by publishing the site!

Can you imagine anything more convenient? I mean seriously. I publish my site and the database is automatically created, seeded, and everything wired up for me using Entity Framework, with a minimal amount of code. Pretty much magic, right?

Have at it!

Now that the .NET 4.5 Framework is supported by Windows Azure Web Sites, you can make use of these and other new features, many of which are discussed or demonstrated on at www.asp.net’s page set aside just on the topic of ASP.NET 4.5 awesomeness. If you want to get started building your own location API’s built on top of Entity Framework Spatial, grab your very own Windows Azure account here, that offers all kinds of awesomeness for free. You can take the sample code for this blog, or copy the gists and tweak them however you want.