Creating MongoDB-backed tables in Azure Mobile Services with .NET backend

  • Article

[This post originally appeared in https://blogs.msdn.com/b/azuremobile/archive/2014/04/14/creating-mongodb-backed-tables-in-azure-mobile-services-with-net-backend.aspx . I’ll be writing Azure Mobile Service related posts in the Azure Mobile blog primarily, and cross-posting them here for people who have feeds linked to this blog only]

One of the advantages of the .NET backend for Azure Mobile Services is that it has out-of-the-box support for multiple data storages, not only SQL Azure. In the node.js runtime you could bypass the SQL operations and use some other storage option (as in this post by Chris Risner for Azure Table Storage), but it’s not a built-in functionality and you’d need to write a lot of code yourself. With the .NET runtime, a lot of the support is built-in, and there’s no need to create a “dummy” table just to have the requests to it redirected. In this post I’ll talk a little about the MongoDB support, and how we can create tables for which the CRUD operations are directed to a MongoDB collection.

Database setup

If you already have a MongoDB account with some collection created, feel free to skip this step (just make sure to take a note of your connection string, we’ll need it later). I’ll also use a collection named “orders” in this post – you don’t need to create one, the backend will create one if it doesn’t exist.

If you’re starting from scratch, for this post I’m using a Mongo Labs database, which we can get for free (for the sandboxed, developer edition) on the Azure portal. To create an account, you can go to the Azure portal, select “New” –> “Store”, and select the MongoLab add-on, where you can sign up for an account.

001-NewMongoLabAddOn

Once you have the account set up, click the “Connection info” button to copy the URI to connect to the database and save that value. The name you gave to the account is the name of the database, which we’ll also use later.

002-ConnectionInfoAndManage

Now the Mongo database is set; we don’t need to create a collection, it will be created when we first try to access it via the backend.

Service setup

Since we don’t have a complete Visual Studio tooling story for non-Entity Framework backend types, let’s begin with an empty web project. We’ll start as I did on my previous post about creating a .NET backend from scratch, but instead of adding the Azure Mobile Services .NET Backend Entity Framework Extension NuGet package, we’ll add the Azure Mobile Services .NET Backend Mongo Extension package instead. Let’s also add the Microsoft.Owin.Host.SystemWeb package so that we’ll be able to run it locally for easier debugging.

With the two packages (and all of its dependencies) installed, let’s add the bootstrapper class by adding a WebApiConfig static class with a default Register method:

  1. public static class WebApiConfig
  2. {
  3.     public static void Register()
  4.     {
  5.         ServiceConfig.Initialize(new ConfigBuilder());
  6.     }
  7. }

And also add a Global Application Class to invoke the bootstrapper when running locally:

  1. public class Global : System.Web.HttpApplication
  2. {
  3.     protected void Application_Start(object sender, EventArgs e)
  4.     {
  5.         WebApiConfig.Register();
  6.     }
  7. }

Next let’s define our object model which we’ll store in the database collection. For this example, let’s define the order which contains a series of items.

  1. public class Order : DocumentData
  2. {
  3.     public DateTime OrderDate { get; set; }
  4.  
  5.     public string Client { get; set; }
  6.  
  7.     public List<OrderItem> Items { get; set; }
  8. }
  9.  
  10. public class OrderItem
  11. {
  12.     public string Name { get; set; }
  13.  
  14.     public double Quantity { get; set; }
  15.  
  16.     public double Price { get; set; }
  17. }

Notice that, like on EF-backed data the models to be used in tables need to implement the ITableData interface. For the EF scenario we provide the EntityData base class which can be used, and similarly for the MongoDB case we provide the DocumentData class which implements that interface, leaving the model class to define the domain-specific properties only.

Defining the table

Tables for MongoDB-backed data are really similar to the EF-based ones. The operations can be implemented exactly like in that scenario, except that we need a new domain manager implementation, for which we can use the MongoDomainManager class which comes with the Azure Mobile Services .NET Backend Mongo Extension NuGet package. Notice that you can always use the types from the MongoDB driver (or any other Mongo client) directly to implement the operations, but for common scenarios, the base class TableController<T> provides the implementation necessary.

  1. public class OrderController : TableController<Order>
  2. {
  3.     protected override void Initialize(HttpControllerContext controllerContext)
  4.     {
  5.         base.Initialize(controllerContext);
  6.         var connStringName = "mongodb";
  7.         var dbName = "MyMongoLab";
  8.         var collectionName = "orders";
  9.         this.DomainManager = new MongoDomainManager<Order>(connStringName, dbName, collectionName, this.Request, this.Services);
  10.     }
  11.  
  12.     public IQueryable<Order> GetAllOrders()
  13.     {
  14.         return base.Query();
  15.     }
  16.  
  17.     public Order GetOneOrder(string id)
  18.     {
  19.         var result = base.Lookup(id).Queryable.FirstOrDefault();
  20.         if (result == null)
  21.         {
  22.             throw new HttpResponseException(HttpStatusCode.NotFound);
  23.         }
  24.         else
  25.         {
  26.             return result;
  27.         }
  28.     }
  29.  
  30.     public Task<Order> PostOrder(Order order)
  31.     {
  32.         return base.InsertAsync(order);
  33.     }
  34.  
  35.     public Task DeleteOrder(string id)
  36.     {
  37.         return base.DeleteAsync(id);
  38.     }
  39.  
  40.     public Task<Order> PatchOrder(string id, Delta<Order> patch)
  41.     {
  42.         return base.UpdateAsync(id, patch);
  43.     }
  44. }

The first parameter in the MongoDomainManager constructor is the name of an element in the <connectionStrings> section in the configuration which contains the actual connection string for the database (we may add an option to pass the actual connection string to the constructor later). If we add the appropriate section to the web.config file (use the connection string you got from the Azure portal):

  1. <connectionStrings>
  2.   <add name="mongodb" connectionString="mongodb://MyMongoLab:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-@dsNNNNNN.mongolab.com:PPPPP/MyMongoLab"/>
  3. </connectionStrings>

We should be able to run the project now. If we press CTRL+F5 the service should start running.

Testing the service

To test the service, let’s fire up Fiddler to send some requests to the service. First, let’s see what we GET from it:

 GET https://localhost:54524/tables/order HTTP/1.1 
User-Agent: Fiddler 
Host: localhost:54524 

=-=-=-=-=-=-=-=-=-

HTTP/1.1 200 OK 
Cache-Control: no-cache 
Pragma: no-cache 
Content-Length: 2 
Content-Type: application/json; charset=utf-8 
Expires: 0 
Server: Microsoft-IIS/8.0 
X-Powered-By: ASP.NET 
Date: Mon, 14 Apr 2014 15:43:31 GMT 

[]

Nothing unexpected here (unless you already had an “orders” collection in your database). Let’s add some orders to our collection:

 POST https://localhost:54524/tables/order HTTP/1.1
User-Agent: Fiddler
Host: localhost:54524
Content-Length: 211
Content-Type: application/json

{
    "client":"John Doe",
    "orderDate":"2014-04-13T00:00:00Z",
    "items":[
        { "name": "bread", "quantity": 1, "price": 1.99 },
        { "name": "milk", "quantity": 2, "price": 2.99 }
    ]
}

=-=-=-=-=-=-=-=-=-

HTTP/1.1 200 OK
Content-Length: 383
Content-Type: application/json; charset=utf-8
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 15:53:13 GMT

{
  "orderDate": "2014-04-13T00:00:00Z",
  "client": "John Doe",
  "items": [
    {
      "name": "bread",
      "quantity": 1.0,
      "price": 1.99
    },
    {
      "name": "milk",
      "quantity": 2.0,
      "price": 2.99
    }
  ],
  "id": "534c0469f76e1e10c4703c2b",
  "__createdAt": "2014-04-14T15:53:12.982Z",
  "__updatedAt": "2014-04-14T15:53:12.982Z"
}

And one more

 POST https://localhost:54524/tables/order HTTP/1.1
User-Agent: Fiddler
Host: localhost:54524
Content-Length: 216
Content-Type: application/json

{
    "client":"Jane Roe",
    "orderDate":"2014-02-22T00:00:00Z",
    "items":[
        { "name": "nails", "quantity": 100, "price": 3.50 },
        { "name": "hammer", "quantity": 1, "price": 12.34 }
    ]
}

=-=-=-=-=-=-=-=-=-

HTTP/1.1 200 OK
Content-Length: 387
Content-Type: application/json; charset=utf-8
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 15:53:21 GMT

{
  "orderDate": "2014-02-22T00:00:00Z",
  "client": "Jane Roe",
  "items": [
    {
      "name": "nails",
      "quantity": 100.0,
      "price": 3.5
    },
    {
      "name": "hammer",
      "quantity": 1.0,
      "price": 12.34
    }
  ],
  "id": "534c0471f76e1e10c4703c2c",
  "__createdAt": "2014-04-14T15:53:21.557Z",
  "__updatedAt": "2014-04-14T15:53:21.557Z"
}

Now we can try to resend the GET request to see what we've got:

 GET https://localhost:54524/tables/order HTTP/1.1 
User-Agent: Fiddler 
Host: localhost:54524 

=-=-=-=-=-=-=-=-=-

HTTP/1.1 200 OK
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 239
Content-Type: application/json; charset=utf-8
Expires: 0
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 15:55:12 GMT

[
  {
    "id": "534c0469f76e1e10c4703c2b",
    "client": "John Doe",
    "orderDate": "2014-04-13T00:00:00Z"
  },
  {
    "id": "534c0471f76e1e10c4703c2c",
    "client": "Jane Roe",
    "orderDate": "2014-02-22T00:00:00Z"
  }
]

We got the items which we added, but we don’t get the complex property (items) in the object. The issue we have here is that the return type of the operation (IQueryable<Order>) only returns complex properties if the client explicitly asks for it (by passing the $expand=<propertyName> query string parameter). Having the method return a queryable object is useful because it also adds the additional filtering and ordering capabilities (via $filter and $orderby parameters, respectively), so we must make a decision on whether to continue using the queryable and pass the $expand to retrieve the items, or move to another return type. For the latter, the change is fairly small:

  1. public List<Order> GetAllOrders()
  2. {
  3.     return base.Query().ToList();
  4. }

For the former, there are some alternatives. The easier (on the server) is to make the client send the $expand header, and nothing needs to be changed in the server. In this case, we can send the request and get the full document back:

 GET https://localhost:54524/tables/order?$expand=items HTTP/1.1 
User-Agent: Fiddler 
Host: localhost:54524 

=-=-=-=-=-=-=-=-=-

HTTP/1.1 200 OK
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 663
Content-Type: application/json; charset=utf-8
Expires: 0
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 17:52:26 GMT

[
  {
    "id": "534c0469f76e1e10c4703c2b",
    "client": "John Doe",
    "orderDate": "2014-04-13T00:00:00Z",
    "items": [
      {
        "name": "bread",
        "quantity": 1.0,
        "price": 1.99
      },
      {
        "name": "milk",
        "quantity": 2.0,
        "price": 2.99
      }
    ]
  },
  {
    "id": "534c0471f76e1e10c4703c2c",
    "client": "Jane Roe",
    "orderDate": "2014-02-22T00:00:00Z",
    "items": [
      {
        "name": "nails",
        "quantity": 100.0,
        "price": 3.5
      },
      {
        "name": "hammer",
        "quantity": 1.0,
        "price": 12.34
      }
    ]
  }
]

Another alternative is to use an action filter which will change the incoming requests to always add the expand query parameter. One possible implementation is given below:

  1. [AttributeUsage(AttributeTargets.Method, AllowMultiple = true)]
  2. class ExpandPropertyAttribute : ActionFilterAttribute
  3. {
  4.     string propertyName;
  5.  
  6.     public ExpandPropertyAttribute(string propertyName)
  7.     {
  8.         this.propertyName = propertyName;
  9.     }
  10.  
  11.     public override void OnActionExecuting(HttpActionContext actionContext)
  12.     {
  13.         base.OnActionExecuting(actionContext);
  14.         var uriBuilder = new UriBuilder(actionContext.Request.RequestUri);
  15.         var queryParams = uriBuilder.Query.TrimStart('?').Split(new[] { '&' }, StringSplitOptions.RemoveEmptyEntries).ToList();
  16.         int expandIndex = -1;
  17.         for (var i = 0; i < queryParams.Count; i++)
  18.         {
  19.             if (queryParams[i].StartsWith("$expand", StringComparison.Ordinal))
  20.             {
  21.                 expandIndex = i;
  22.                 break;
  23.             }
  24.         }
  25.  
  26.         if (expandIndex < 0)
  27.         {
  28.             queryParams.Add("$expand=" + this.propertyName);
  29.         }
  30.         else
  31.         {
  32.             queryParams[expandIndex] = queryParams[expandIndex] + "," + propertyName;
  33.         }
  34.  
  35.         uriBuilder.Query = string.Join("&", queryParams);
  36.         actionContext.Request.RequestUri = uriBuilder.Uri;
  37.     }
  38. }

And if we decorate our method with that attribute

  1. [ExpandProperty("Items")]
  2. public IQueryable<Order> GetAllOrders()
  3. {
  4.     return base.Query();
  5. }

We can send requests which use other queryable attributes while still having the expanded items in our order

 GET https://localhost:54524/tables/order?$orderby=client HTTP/1.1 
User-Agent: Fiddler 
Host: localhost:54524 

=-=-=-=-=-=-=-=-=-
HTTP/1.1 200 OK
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 663
Content-Type: application/json; charset=utf-8
Expires: 0
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 18:37:27 GMT

[
  {
    "id": "534c0471f76e1e10c4703c2c",
    "client": "Jane Roe",
    "orderDate": "2014-02-22T00:00:00Z",
    "items": [
      {
        "name": "nails",
        "quantity": 100.0,
        "price": 3.5
      },
      {
        "name": "hammer",
        "quantity": 1.0,
        "price": 12.34
      }
    ]
  },
  {
    "id": "534c0469f76e1e10c4703c2b",
    "client": "John Doe",
    "orderDate": "2014-04-13T00:00:00Z",
    "items": [
      {
        "name": "bread",
        "quantity": 1.0,
        "price": 1.99
      },
      {
        "name": "milk",
        "quantity": 2.0,
        "price": 2.99
      }
    ]
  }
]

Deployment

Now that the service runs locally correctly, we’re ready to publish to Azure. After downloading the publishing profile from the portal, right-click the project in VS and select “publish”, the service will be live. And if we fire up Fiddler again, we should be able to retrieve our two orders straight from Azure:

 GET https://blog20140413.azure-mobile.net/tables/order HTTP/1.1
User-Agent: Fiddler
Host: blog20140413.azure-mobile.net

=-=-=-=-=-=-=-=-=-
HTTP/1.1 500 Internal Server Error
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 43
Content-Type: application/json; charset=utf-8
Expires: 0
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 18:50:22 GMT

{
  "message": "An error has occurred."
}

Something is wrong. By default the runtime doesn’t return any error details (for security purposes), so we can go to the portal, in the logs tab, to see what is going on, and the error is listed there:

 Exception=System.ArgumentException: No connection string named 'mongodb' could be found in the service configuration.
   at Microsoft.WindowsAzure.Mobile.Service.MongoDomainManager`1.GetMongoContext(String connectionStringName)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.WindowsAzure.Mobile.Service.MongoDomainManager`1..ctor(String connectionStringName, String databaseName, String collectionName, HttpRequestMessage request, ApiServices services)
   at MongoDbOnNetBackend.OrderController.Initialize(HttpControllerContext controllerContext)
   at System.Web.Http.ApiController.ExecuteAsync(HttpControllerContext controllerContext, CancellationToken cancellationToken)
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.SendAsyncCore(HttpRequestMessage request, CancellationToken cancellationToken)
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__0.MoveNext(), Id=6133b3eb-9851-41d1-9e17-38f196e41f6b, Category='App.Request'

The problem is that the local web.config file which we were using when running the service locally is not used when the service is running in the cloud. We need to define the connection string some other way. Unfortunately due to a current bug we have there’s no easy way to define the connection string (it should be easily done in the portal, but this is not there yet), so we have to work around it for now.

To do that, first go to the “configure” tab of the mobile service in the portal, and add a new app setting whose value is the connection string which we had defined in the web.config file before:

004-AppSettingWithConnString

And when initializing the table controller, we change the connection strings in the service settings based on that value which we can read from the app settings.

  1. static bool connectionStringInitialized = false;
  2.  
  3. private void InitializeConnectionString(string connStringName, string appSettingName)
  4. {
  5.     if (!connectionStringInitialized)
  6.     {
  7.         connectionStringInitialized = true;
  8.         if (!this.Services.Settings.Connections.ContainsKey(connStringName))
  9.         {
  10.             var connFromAppSetting = this.Services.Settings[appSettingName];
  11.             var connSetting = new ConnectionSettings(connStringName, connFromAppSetting);
  12.             this.Services.Settings.Connections.Add(connStringName, connSetting);
  13.         }
  14.     }
  15. }
  16.  
  17. protected override void Initialize(HttpControllerContext controllerContext)
  18. {
  19.     var connStringName = "mongodb";
  20.     var dbName = "MyMongoLab";
  21.     var collectionName = "orders";
  22.  
  23.     // Workaround for lack of connection strings in the portal
  24.     InitializeConnectionString(connStringName, "mongoConnectionString");
  25.  
  26.     base.Initialize(controllerContext);
  27.     this.DomainManager = new MongoDomainManager<Order>(connStringName, dbName, collectionName, this.Request, this.Services);
  28. }

And now when we publish it again we should be able to consume the table from Azure.

 GET https://blog20140413.azure-mobile.net/tables/order HTTP/1.1
User-Agent: Fiddler
Host: blog20140413.azure-mobile.net
x-zumo-application: cOFQkbaAmffuVRBJRpYDKHbNHbtDYG97

=-=-=-=-=-=-=-=-=-
HTTP/1.1 200 OK
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 663
Content-Type: application/json; charset=utf-8
Expires: 0
Server: Microsoft-IIS/8.0
X-Powered-By: ASP.NET
Date: Mon, 14 Apr 2014 19:21:11 GMT

[
  {
    "id": "534c0469f76e1e10c4703c2b",
    "client": "John Doe",
    "orderDate": "2014-04-13T00:00:00Z",
    "items": [
      {
        "name": "bread",
        "quantity": 1.0,
        "price": 1.99
      },
      {
        "name": "milk",
        "quantity": 2.0,
        "price": 2.99
      }
    ]
  },
  {
    "id": "534c0471f76e1e10c4703c2c",
    "client": "Jane Roe",
    "orderDate": "2014-02-22T00:00:00Z",
    "items": [
      {
        "name": "nails",
        "quantity": 100.0,
        "price": 3.5
      },
      {
        "name": "hammer",
        "quantity": 1.0,
        "price": 12.34
      }
    ]
  }
]

Just a final note on this request – when the service is running locally, there’s no authentication by default so our request didn’t need to send any keys. To send the request to the server running on Azure, we need to specify the application key (the default authentication level) in the “x-zumo-application” header.

Wrapping up

The .NET backend for Azure Mobile Services offers a selection of storage providers for the “table” abstraction. Since most examples out there deal with the Entity Framework (SQL Server) based option, hopefully this post will give you some information on how to use our MongoDB provider for storing data. And as usual, we welcome comments and suggestions in this blog, in our MSDN forums or via twitter @AzureMobile.