30 Days of Zumo.v2 (Azure Mobile Apps): Day 19 – ASP.NET Table Controllers

I’ve got a pretty good table controller going from my last article now. However, there are some features of ASP.NET Table Controllers that I am not using yet. I’d like to cover those as a miscellany of features that don’t really have any association with each other – you need to consider them individually and decide if your situation warrants their use.

Soft Delete

I’ve mentioned Soft Delete before with respect to offline sync. Soft Delete allows you to notify your clients that a record has been deleted. When you delete a record, it’s just marked as deleted. That allows other clients to download the new state and update their offline sync cache. At some later date, you can remove the deleted records through another process. I’ll be covering that process in a future posting.

Soft Delete is enabled on a per-table basis in the Initialize() method:

        protected override void Initialize(HttpControllerContext controllerContext)
        {
            base.Initialize(controllerContext);
            MyDbContext context = new MyDbContext();
            DomainManager = new EntityDomainManager<TodoItem>(context, Request, enableSoftDelete: true);
        }

The addition to the initialization of the EntityDomainManager is all you need to enable soft delete. The entity domain manager that is used to manage all the requests on this table handles the actual implementation of soft delete.

Seeding of Data

Let’s say you want to initialize a table with some content? The database initializer has a method called Seed() that is called when the database is created for the first time. It does not fill in information on application restart, nor does it fill in information during an EF Migration, so it’s not a good solution for that. You can find the database initializer in the App_Start/AzureMobile.cs file:

    public class AzureMobileInitializer : CreateDatabaseIfNotExists<MyDbContext>
    {
        protected override void Seed(MyDbContext context)
        {
            // You can seed your database here
#if SEED_DATA
            List<TodoItem> todoItems = new List<TodoItem>
            {
                new TodoItem { Id = Guid.NewGuid().ToString(), Text = "First item", Complete = false },
                new TodoItem { Id = Guid.NewGuid().ToString(), Text = "Second item", Complete = false },
            };

            foreach (TodoItem todoItem in todoItems)
            {
                context.Set<TodoItem>().Add(todoItem);
            }

#endif

            base.Seed(context);
        }
    }

Personally, I dislike the seeding of data and try to avoid it, but it is useful in certain circumstances.

Debugging

The Node.js server SDK has a nice logging mechanism that logs the actual SQL statements that are being made. ASP.NET doesn’t do this out of the box. Fortunately, ASP.NET with SQL Azure is based on Entity Framework, so I can set up logging using the Entity Framework technique. Put this in your DbContext constructor (which, for me, is in Models/MyDbContext.cs):

        private const string connectionStringName = "Name=MS_TableConnectionString";

        public MyDbContext() : base(connectionStringName)
        {
            this.Database.Log = (message) => Debug.Write(message);
        }

You will get output in the Log Stream (which is under the Tools menu for your App Service in the Azure Portal). Make sure you publish a “Debug” version of your app.

Did You Know? You can turn on diagnostic logging and view the log stream directly from Visual Studio. Open the Server Explorer, expand the Azure -> App Service node, find your mobile app and right-click on it. Click on View Settings to update the web server logging, and View Streaming Logs to view the logs. The logs appear in the Output window.

There are also various techniques to connecting a remote debugger from Visual Studio to your site. If you want to do this, then check out the Azure Article on the subject.

One note is that model differences – where the DTO on the server and the Model on the client app differ – matters. If you do have model differences, the SQL commands on the backend won’t even execute. In these cases, you will want to capture the Response output on the server or check out the response on the client. I use the following in my Post handler, for example:

        // POST tables/TodoItem
        public async Task<IHttpActionResult> PostTodoItem(TodoItem item)
        {
            Debug.WriteLine($"POST tables/TodoItem");
            var emailAddr = await GetEmailAddress();
            Debug.WriteLine($"Email Address = {emailAddr}");
            item.UserId = emailAddr;
            Debug.WriteLine($"Item = {item}");
            try
            {
                TodoItem current = await InsertAsync(item);
                Debug.WriteLine($"Updated Item = {current}");
                return CreatedAtRoute("Tables", new { id = current.Id }, current);
            }
            catch (HttpResponseException ex)
            {
                Debug.WriteLine($"Exception: {ex}");
                Debug.WriteLine($"Response: {ex.Response}");
                string content = await ex.Response.Content.ReadAsStringAsync();
                Debug.WriteLine($"Response Content: {content}");
                throw ex;
            }
        }

Using Existing Tables

This is a big and – to my mind – fairly complex topic, so be aware that I consider this advanced. Let’s say you have an existing database, with an existing table, and for some reason you don’t have the ability to adjust the table. So you can’t add the system columns to the database. How do you deal with this? Well, there is a way. Qualitatively, you create another table that holds the system columns and then you create an updatable view that acts like a table but isn’t.

Step 1: Don’t let the Mobile App update the Database Schema

When you go this route, you are responsible for updating the database. The Mobile App will no longer do changes for you. In your App_Start/AzureMobile.cs Startup class, do the following change:

            // Initialize the database with EF Code First
            // Database.SetInitializer(new AzureMobileInitializer());
            Database.SetInitializer<MyDbContext>(null);

The original line is above the new line and commented out.

Step 2: Create the System Columns Table

Let’s say you have a table called [myapp].[TodoItem] which is defined like this:

CREATE TABLE [myapp].[TodoItem] (
  [id]       BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  [UserId]   NVARCHAR(255) NOT NULL,
  [Title]     NVARCHAR(255) NOT NULL,
  [Complete] BIT
)
GO

I can create a system properties table like this:

CREATE TABLE [mobile].[TodoItem_SystemProps] (
  [id]        NVARCHAR(255) CONSTRAINT [DF_todoitem_id] DEFAULT (CONVERT([NVARCHAR](255),NEWID(),(0))) NOT NULL,
  [createdAt] DATETIMEOFFSET(7) CONSTRAINT [DF_todoitem_createdAt] DEFAULT (CONVERT([DATETIMEOFFSET](7),SYSUTCDATETIME(),(0))) NOT NULL,
  [updatedAt] DATETIMEOFFSET(7) NULL,
  [version]   ROWVERSION NOT NULL,
  [deleted]   BIT DEFAULT ((0)) NOT NULL,
  [item_id]   BIGINT NOT NULL
  PRIMARY KEY NONCLUSTERED ([id] ASC)
)
GO

Step 3: Create an Mobile SQL View

Here is my view:

CREATE VIEW [mobile].[TodoItem] AS
SELECT
    [mobile].[TodoItem_SystemProps].[id],
    [mobile].[TodoItem_SystemProps].[createdAt],
    [mobile].[TodoItem_SystemProps].[updatedAt],
    [mobile].[TodoItem_SystemProps].[version],
    [mobile].[TodoItem_SystemProps].[deleted],
    [mobile].[TodoItem_SystemProps].[item_id],
    [myapp].[TodoItem].[UserId],
    [myapp].[TodoItem].[Title],
    [myapp].[TodoItem].[Complete]
FROM
    [myapp].[TodoItem],
    [mobile].[TodoItem_SystemProps]
WHERE
    [myapp].[TodoItem].[id] = [mobile].[TodoItem_SystemProps].[item_id]
GO

This produces a composite read-only view of the original table with the system properties. However, it doesn’t quite work yet.

Step 4: Handle Updates to the Original Table

I need to wire up two specific areas – firstly, when the original data is updated, I also update the system properties table. This is a series of three triggers.

CREATE TRIGGER
    [myapp].[TRG_TodoItem_Insert]
ON
    [myapp].[TodoItem]
AFTER
    INSERT
AS BEGIN
    DECLARE @itemid AS BIGINT
    SELECT @itemid = inserted.id FROM inserted
    INSERT INTO [mobile].[TodoItem_SystemProps] ([item_id], [updatedAt]) VALUES (@itemid, CONVERT(DATETIMEOFFSET(7), SYSUTCDATETIME()));
END
GO

CREATE TRIGGER
    [myapp].[TRG_TodoItem_Update]
ON
    [myapp].[TodoItem]
AFTER
    UPDATE
AS BEGIN
    UPDATE
        [mobile].[TodoItem_SystemProps]
    SET
        [updatedAt] = CONVERT(DATETIMEOFFSET(7), SYSUTCDATETIME())
    FROM
        INSERTED
    WHERE
        INSERTED.id = [mobile].[TodoItem_SystemProps].[item_id]
END
GO

CREATE TRIGGER
    [myapp].[TRG_TodoItem_Delete]
ON
    [myapp].[TodoItem]
AFTER
    DELETE
AS BEGIN
    DECLARE @itemid AS BIGINT
    SELECT @itemid = deleted.id from deleted
    DELETE FROM [mobile].[TodoItem_SystemProps] WHERE [item_id] = @itemid
END
GO

Each trigger handles one case of insert, update, or delete. The primary thing to do in update is to update the updatedAt time to match the current time. Insertion creates a new record and deletion deletes the record. Important note here is that deletion does not handle soft delete – something else will have to be done for when the record is deleted on the original table.

Step 5: Handle Updates from Azure Mobile Apps

The next step is to ensure that when an update (or an insert or a delete) comes in from Azure Mobile Apps, the right thing happens on the tables. For insertion (or deletion), this means creating the original item – not the system props (these are handled by the triggers above). For updates, I need to update just the original table – not the system props (except for soft delete).

CREATE TRIGGER
    [mobile].[TRG_Mobile_TodoItem_Insert]
ON
    [mobile].[TodoItem]
INSTEAD OF
    INSERT
AS BEGIN
    DECLARE @userid AS NVARCHAR(255)
    SELECT @userid = inserted.UserId FROM inserted
    DECLARE @title AS NVARCHAR(255)
    SELECT @title = inserted.Title FROM inserted
    DECLARE @complete AS BIT
    SELECT @complete = inserted.Complete FROM inserted

    INSERT INTO
        [myapp].[TodoItem] ([UserId], [Title], [Complete])
    VALUES
        (@userid, @title, @complete)

    IF UPDATE(Id) BEGIN
        DECLARE @itemid AS BIGINT
        SELECT @itemid = @@identity
        DECLARE @id AS NVARCHAR(255)
        SELECT @id = inserted.Id FROM inserted
        UPDATE [mobile].[TodoItem_SystemProps] SET [Id] = @id WHERE [item_id] = @itemid
    END
END;
GO

CREATE TRIGGER
    [mobile].[TRG_Mobile_TodoItem_Update]
ON
    [mobile].[TodoItem]
INSTEAD OF
    UPDATE
AS BEGIN
    DECLARE @id AS NVARCHAR(255)
    SELECT @id = inserted.id FROM inserted
    DECLARE @itemid AS BIGINT
    SELECT @itemid = [item_id] FROM [mobile].[TodoItem_SystemProps] WHERE [id] = @id

    IF UPDATE(UserId) BEGIN
	    DECLARE @userid AS NVARCHAR(255)
		SELECT @userid = inserted.UserId FROM inserted
        UPDATE [myapp].[TodoItem] SET [UserId] = @userid WHERE [id] = @itemid
    END
    IF UPDATE(Title) BEGIN
		DECLARE @title AS NVARCHAR(255)
		SELECT @title = inserted.Title FROM inserted
        UPDATE [myapp].[TodoItem] SET [Title] = @title WHERE [id] = @itemid
    END
    IF UPDATE(Complete) BEGIN
		DECLARE @complete AS BIT
		SELECT @complete = inserted.Complete FROM inserted
        UPDATE [myapp].[TodoItem] SET [Complete] = @complete WHERE [id] = @itemid
    END
    IF UPDATE(deleted) BEGIN
	    DECLARE @deleted AS BIT
		SELECT @deleted = inserted.deleted FROM inserted
        UPDATE [mobile].[TodoItem_SystemProps] SET [deleted] = @deleted WHERE [item_id] = @itemid
    END
END
GO

CREATE TRIGGER
    [mobile].[TRG_Mobile_TodoItem_Delete]
ON
    [mobile].[TodoItem]
INSTEAD OF
    DELETE
AS BEGIN
    DECLARE @id AS NVARCHAR(255)
    SELECT @id = deleted.id FROM deleted
    DECLARE @itemid AS BIGINT
    SELECT @itemid = [item_id] FROM [mobile].[TodoItem_SystemProps] WHERE [id] = @id

    DELETE FROM [myapp].[TodoItem] WHERE [id] = @itemid
	DELETE FROM [mobile].[TodoItem_SystemProps] WHERE [id] = @id
END
GO

A standard SQL view is read-only. By using triggers, you can make it adjust the underlying tables that are being used to construct the view and hence make it read-write. You can read more about creating triggers on MSDN.

There are also similar things you can do for multi-tables. Let’s say you have a mobile view that is comprised of a customer and an order. When you view the order, you want to combine the data with the order. When the order is deleted or removed from the mobile device, the customer data for that order is also removed. To implement this, use the same technique. Create a view that has all the customer data for the order plus the order. Call it “CustomerOrder”. Then, when a mobile device updates the record, you can use triggers to actually do the updates to the underlying tables.

Step 6: Test your SQL Setup

The reality is that this is a lot of SQL for someone who doesn’t do a lot of SQL work. Mistakes happen. It’s a good idea to test the SQL by doing manual updates. There are six tests you need to do:

  1. INSERT into the main table, then SELECT from the VIEW – ensure the createdAt and id fields are set.
  2. UPDATE the record you just inserted, then SELECT from the VIEW – ensure the updatedAt and version fields are set.
  3. DELETE the record you just inserted, then SELECT from the System Properties table – ensure the record is deleted.
  4. INSERT into the view, then SELECT from the main table – ensure the record is created.
  5. UPDATE the view, then SELECT from the main table – ensure the record is updated. Also SELECT from the System Properties table – ensure the updatedAt and version fields are updated.
  6. DELETE the record you created via the via, then SELECT from the main table and system properties table – ensure both records are deleted.

This is a relatively easy test to do and only needs to be done when you update the table. If you update the main table, don’t forget to update the triggers as well.

Changing the Mobile Schema

The final thing to do here is to change the SQL schema for my operations to the [mobile] schema. By default, Azure Mobile Apps uses the [dbo] schema. This table is stored in the [mobile] schema. This is a pure Entity Framework problem. Probably the easiest method is to use HasDefaultSchema in the DbContext:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("mobile");
            modelBuilder.Conventions.Add(
                new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
                    "ServiceTableColumn",
                    (property, attributes) => attributes.Single().ColumnType.ToString()
                )
            );
        }

This will move all the referenced tables to the [mobile] schema. If you want to do just one table, it’s a little more complex. Something like:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //modelBuilder.HasDefaultSchema("mobile");
            modelBuilder.Entity<TodoItem>().ToTable("TodoItem", "mobile");
            modelBuilder.Conventions.Add(
                new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
                    "ServiceTableColumn",
                    (property, attributes) => attributes.Single().ColumnType.ToString()
                )
            );
        }

Finally, you can also do this as a data annotation on the DTO:

using Microsoft.Azure.Mobile.Server;
using Newtonsoft.Json;
using System.ComponentModel.DataAnnotations.Schema;

namespace backend.dotnet.DataObjects
{
    [Table("TodoItem", Schema="mobile")]
    public class TodoItem : EntityData
    {
        public string UserId { get; set; }

        public string Title { get; set; }

        public bool Complete { get; set; }

        // When asked for the string representation, return the JSON
        public override string ToString()
        {
            return JsonConvert.SerializeObject(this);
        }
    }
}

Any of these techniques ensure that Entity Framework is using the appropriate place in your SQL database for queries and updates.

Next Steps

I’ll be honest here. I almost broke my arm patting myself on the back for this one. SQL is not my forte and this works as advertised, including with Soft Delete, Opportunistic Concurrency, and Incremental Offline Sync.

In my next article, I’m going to take a look at the options you have available when the table controller doesn’t quite fit what you want to do. I’ll delve into the world of Custom APIs for both Node.js and ASP.NET. Until then, my changes for today are on my GitHub Repository.

30 Days of Zumo.v2 (Azure Mobile Apps): Day 18 – ASP.NET Authentication

I introduced the ASP.NET backend in my last article, but it was rather a basic backend. It just did the basic TodoItem single table controller with no authentication. Today, I’m going to integrate the Azure Authentication / Authorization and adjust the table controller to produce a personal table – similar to the Node.js environment I posted about much earlier in the series.

If you followed along the journey so far, your backend is already configured for Authentication / Authorization. If you are using a new site for the ASP.NET backend, you may want to go back to Day 3 and read about setting up Authentication again.

Setting up the Project

The team has split the NuGet packages for Azure Mobile Apps up significantly so you only have to take what you need. You need to add the following NuGet packages to your project:

  • Microsoft.Azure.Mobile.Server.Authentication

You will also need to edit your App_Start/AzureMobile.cs file to take account of authentication:

using Owin;
using System.Configuration;
using System.Data.Entity;
using System.Web.Http;
using Microsoft.Azure.Mobile.Server;
using Microsoft.Azure.Mobile.Server.Authentication;
using Microsoft.Azure.Mobile.Server.Config;
using Microsoft.Azure.Mobile.Server.Tables.Config;
using backend.dotnet.Models;

namespace backend.dotnet
{
    public partial class Startup
    {
        public static void ConfigureMobileApp(IAppBuilder app)
        {
            HttpConfiguration config = new HttpConfiguration();

            // Configure the Azure Mobile Apps section
            new MobileAppConfiguration()
                .AddTables(
                    new MobileAppTableConfiguration()
                        .MapTableControllers()
                        .AddEntityFramework())
                .MapApiControllers()
                .ApplyTo(config);

            // Initialize the database with EF Code First
            Database.SetInitializer(new AzureMobileInitializer());

            MobileAppSettingsDictionary settings = config.GetMobileAppSettingsProvider().GetMobileAppSettings();
            if (string.IsNullOrEmpty(settings.HostName))
            {
                app.UseAppServiceAuthentication(new AppServiceAuthenticationOptions
                {
                    SigningKey = ConfigurationManager.AppSettings["SigningKey"],
                    ValidAudiences = new[] { ConfigurationManager.AppSettings["ValidAudience"] },
                    ValidIssuers = new[] { ConfigurationManager.AppSettings["ValidIssuer"] },
                    TokenHandler = config.GetAppServiceTokenHandler()
                });
            }

            // Link the Web API into the configuration
            app.UseWebApi(config);
        }
    }
}

I’ve got some extra packages to deal with. Then I need to set up authentication. The Authentication / Authorization provider requires me to configure it with JWT keys. Note that this is also how I could deal with custom authentication from a provider like Auth0 – just set up the signing key, audience and issuer and let Azure Mobile Apps deal with it.

Want to do local debugging with user authentication? Check out this blog post.

In order for the app settings to work, I need to add the app settings I am using to the web.config file:

  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
    <add key="SigningKey" value="READ FROM AZURE"/>
    <add key="ValidAudience" value="https://{yoursite}.azurewebsites.net"/>
    <add key="ValidIssuer" value="https://{yoursite}.azurewebsites.net"/>
  </appSettings>

It actually doesn’t matter what value is there – the values will be overwritten by the Azure App Service when it runs. You can put your development values in there if you like.

Configuring a Table Controller

Now that I have configured the project, I can configure a table controller. This amounts to putting the standard [Authorize] attribute to the methods and/or controllers I want to authorize.

Note: One of the common problems is developers who say that “things are always authenticated, even if I don’t want them to be”. It’s likely you set the Authentication / Authorization setting to always authenticate – let anonymous connections through and you can then control which routes get authentications.

My personal table requires the entire table to be authenticated, so I just add the [Authorize] attribute to the entire class, like this:

namespace backend.dotnet.Controllers
{
    [Authorize]
    public class TodoItemController : TableController<TodoItem>
    {
        protected override void Initialize(HttpControllerContext controllerContext)
        {
            base.Initialize(controllerContext);
            MyDbContext context = new MyDbContext();
            DomainManager = new EntityDomainManager<TodoItem>(context, Request);
        }

The Personal Table DTO

My original DTO needs to be updated in preparation for the personal table:

using Microsoft.Azure.Mobile.Server;

namespace backend.dotnet.DataObjects
{
    public class TodoItem : EntityData
    {
        public string UserId { get; set; }

        public string Text { get; set; }

        public bool Complete { get; set; }
    }
}

Since this is Entity Framework, I would normally need to do an Entity Framework Code First Migration to get that field onto my database. You can find several walk-throughs of the process online. This isn’t an Entity Framework blog, so I’ll leave that process to better minds than mine. Just know that you have to deal with this aspect when using the ASP.NET backend. (Node deals with this via dynamic schema adjustments).

Dealing with Claims

When using the Azure Mobile Apps SDK, the User (technically, HttpContext.User) is available within your table controller. It’s specified as a ClaimsPrincipal and you can read it like this:

        private string GetAzureSID()
        {
            var principal = this.User as ClaimsPrincipal;
            var sid = principal.FindFirst(ClaimTypes.NameIdentifier).Value;
            return sid;
        }

I don’t want the Security ID. I want the email address of the user. To do that, I need to delve deeper:

        private async Task<string> GetEmailAddress()
        {
            var credentials = await User.GetAppServiceIdentityAsync<AzureActiveDirectoryCredentials>(Request);
            return credentials.UserClaims
                .Where(claim => claim.Type.EndsWith("/emailaddress"))
                .First<Claim>()
                .Value;
        }

The User.GetAppServiceIdentityAsync() method returns all the information contained in the /.auth/me endpoint, but placed into a class so you can deal with it. The claims are in the UserClaims property which returns an IEnumerable – a Claim is something with a Type and a Value. The email address is actually something like http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress – but it may be something else on Facebook, for example. To be reasonable, I just need the claim to end with emailaddress. The first one listed is the one I want.

Adjusting the controller response

I’m going to need to do some adjustments to the various endpoints in the table controller to use this.

GetAll

The GetAllTodoItems() method uses the Query() method to construct a query based on the inbound OData query. I need to adjust that using something akin to LINQ to add a clause for the UserId:

        // GET tables/TodoItem
        public async Task<IQueryable<TodoItem>> GetAllTodoItem()
        {
            Debug.WriteLine("GET tables/TodoItem");
            var emailAddr = await GetEmailAddress();
            return Query().Where(item => item.UserId == emailAddr);
        }

There are lots of things you can do with a Query() object, so this is a great area for experimentation.

GetItem

I can also use a similar query for the GetItem method:

        // GET tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public async Task<SingleResult<TodoItem>> GetTodoItem(string id)
        {
            Debug.WriteLine($"GET tables/TodoItem/{id}");
            var emailAddr = await GetEmailAddress();
            var result = Lookup(id).Queryable.Where(item => item.UserId == emailAddr);
            return new SingleResult<TodoItem>(result);
        }

The Lookup() method returns a Queryable with 0 or 1 entries. I then use LINQ to further filter based on the email address, before re-constituting the result into a SingleResult object. I find it’s easier to read (and test) when returning objects rather than IHttpActionResults. However, you can use whatever you are most comfortable with.

PatchItem and DeleteItem

The Patch and Delete are so close to one another that I combined them. I’ll take a look at the PATCH version – check the code for the DELETE version:

        // PATCH tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959
        public async Task<TodoItem> PatchTodoItem(string id, Delta<TodoItem> patch)
        {
            Debug.WriteLine($"PATCH tables/TodoItem/{id}");
            var item = Lookup(id).Queryable.FirstOrDefault<TodoItem>();
            if (item == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
            var emailAddr = await GetEmailAddress();
            if (item.UserId != emailAddr)
            {
                throw new HttpResponseException(HttpStatusCode.Forbidden);
            }
            return await UpdateAsync(id, patch);
        }

In this version, I am doing the following logic:

  • Lookup the item – if it isn’t there, produce a 404 Not Found response
  • Does it belong to me – if not, produce a 403 Forbidden response
  • Update the record and return it

PostItem

Finally, the PostItem is relatively easy:

        // POST tables/TodoItem
        public async Task<IHttpActionResult> PostTodoItem(TodoItem item)
        {
            Debug.WriteLine($"POST tables/TodoItem");
            var emailAddr = await GetEmailAddress();
            item.UserId = emailAddr;
            TodoItem current = await InsertAsync(item);
            return CreatedAtRoute("Tables", new { id = current.Id }, current);
        }

This version overwrites whatever the user supplied with the authenticated information.

Publishing

When publishing, don’t forget to use a Code First Migration to get the extra field in the table. I must admit that I cheated here and just wiped out my database table. You can browse your database directly from Visual Studio. Open the Server Explorer, expand the Azure node (you will need to enter your Azure credentials), then expand the SQL Databases node. Finally right-click on the database and select Open in SQL Server Object Explorer.

day-18-server-explorer

You will have to enter the credentials for your SQL server. You will also have to permit your Client IP to access the database. Once you have done that, you can use Visual Studio to browse your tables and manage your data.

Next Steps

This is actually a huge step forward – I’ve now got equivalent functionality within both the Node.js and ASP.NET backends. I’ll continue to cover both Node.js and ASP.NET equally in the future. Next, however, I’m going to take a look at some final thoughts on ASP.NET controllers – things like soft delete, logging, and using existing tables. Until next time, my code is on my GitHub Repository.

30 Days of Zumo.v2 (Azure Mobile Apps): Day 17 – ASP.NET Backend Introduction

I have concentrated on the Node.js backend for Azure Mobile Apps thus far. However, Azure Mobile Apps also supports an ASP.NET 4.6 (not ASP.NET core) backend. I’m going to start exploring the ASP.NET backend in this article, looking at a lot of the same functionality as I did for the Node.js backend.

Why use the ASP.NET backend?

Given the functionality of the Node.js backend, you may be wondering why you would even consider the ASP.NET backend. Well, there are a few reasons:

  1. You are more familiar with C# and ASP.NET in general.
  2. You want to utilize the large package library available via NuGet.
  3. You are doing cross-platform development in Xamarin and want to share code between backend and client apps.
  4. You want to use more complex types than a string, number, date or boolean.

Similarly, there are good reasons to use the Node.js backend:

  1. You are more familiar with Javascript and/or node programming.
  2. You want to utilize the large package library available via npm.
  3. You are primarily a frontend developer, don’t care about the schema and want to use the dynamic schema feature.

I find Node.js to be simpler to write – it requires less code to write functionally identical backends. I find ASP.NET to be easier to debug a lot of the time, with many errors being found at compile time (something that doesn’t exist in Node) rather than run time.

Starting with an ASP.NET MVC application

When kicking off a new project, I’d normally tell you to start with an example or template that is close to the pattern you want to deploy. Azure Mobile Apps has templates for the ASP.NET backend in the Azure SDK. Why not start with one of those?

Great question and you can certainly start with a specific Azure Mobile Apps project template. However, I want to show off how you can add Azure Mobile Apps to any ASP.NET application. Azure Mobile Apps is a good platform for exposing SQL data to a web or mobile application. There is nothing magical about the Azure Mobile Apps templates – they are really just ASP.NET templates with some in-built code. So I’ve started my code with the standard ASP.NET 4.6 MVC template. I’ve done some changes to it – most notably removing the built-in identity manager (which creates a database table for handling sign-ins) and removing Application Insights.

You might be wondering how I get two backends in the same solution and choose which one to deploy. The .deployment file has a project property that tells Azure App Service which project to deploy.

You can find my initial code at my GitHub Repository, tagged as pre-day-17.

Introducing the Azure Mobile Apps .NET Server SDK

Did you know the Azure Mobile Apps team develops all their SDKs as open source on GitHub? Here is a complete list of the SDKs, together with their links:

In addition, the Azure Mobile Apps team publishes the SDKs in “the normal places” – that depends on the client language – npm for JavaScript, for example, or NuGet for the .NET Server SDKs. That means you can generally just work with the SDKs as you normally would.

There are several parts to installing the Server SDK into an ASP.NET application:

  1. Configure Entity Framework for your database
  2. Configure Azure Mobile Apps Server SDK
  3. Create your model
  4. Create a table controller

I’m going to do the basic one today – starting where I did with the Node.js backend – at the beginning, configuring the server to handle the TodoItem model and table.

Configuring Entity Framework

My first stop is to the web.config file. In the standard template, the connection string is called DefaultConnection – I need to change it to match the connection string that is used by Azure Mobile Apps:

  <connectionStrings>
    <add name="MS_TableConnectionString" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-backend.dotnet-20160417041539.mdf;Initial Catalog=aspnet-backend.dotnet-20160417041539;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Just the name needs to change here. I don’t need to do any other changes because the Azure Mobile Apps Server SDK takes care of it for me.

Configuring Azure Mobile Apps

First stop – I need to add some NuGet packages. Here is the list:

  • Microsoft.Azure.Mobile.Server
  • Microsoft.Azure.Mobile.Server.Entity
  • Microsoft.WindowsAzure.ConfigurationManager
  • Microsoft.AspNet.WebApi.Owin

To install the NuGet packages, right-click on the References node and select Manage NuGet Packages… Click on Browse and then search for the packages. Once you’ve found one, click on the Install button:

day-17-nuget

This list is a minimal list – I haven’t included items that are in the dependencies.
Pretty much any plugin of this magnitude has a startup process. I am placing mine in App_Start/AzureMobile.cs:

using Owin;
using System.Data.Entity;
using System.Web.Http;
using Microsoft.Azure.Mobile.Server.Config;
using Microsoft.Azure.Mobile.Server.Tables.Config;
using backend.dotnet.Models;

namespace backend.dotnet
{
    public partial class Startup
    {
        public static void ConfigureMobileApp(IAppBuilder app)
        {
            HttpConfiguration config = new HttpConfiguration();

            // Configure the Azure Mobile Apps section
            new MobileAppConfiguration()
                .AddTables(
                    new MobileAppTableConfiguration()
                        .MapTableControllers()
                        .AddEntityFramework())
                .MapApiControllers()
                .ApplyTo(config);

            // Initialize the database with EF Code First
            Database.SetInitializer(new AzureMobileInitializer());

            // Link the Web API into the configuration
            app.UseWebApi(config);
        }
    }

    public class AzureMobileInitializer : CreateDatabaseIfNotExists<MyDbContext>
    {
        protected override void Seed(MyDbContext context)
        {
            // You can seed your database here
            base.Seed(context);
        }
    }
}

The major work here is done after the MobileAppConfiguration(). I add any table controllers that are defined and hook them up to the data source defined via the DbContext. Once that is done, I call the database initializer, which will create the database and tables that I need. For that, I need a database context, which is defined in Models/MyDbContext.cs:

using backend.dotnet.DataObjects;
using Microsoft.Azure.Mobile.Server.Tables;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;

namespace backend.dotnet.Models
{
    public class MyDbContext : DbContext
    {
        private const string connectionStringName = "Name=MS_TableConnectionString";

        public MyDbContext() : base(connectionStringName)
        {

        }

        public DbSet<TodoItem> TodoItems { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Add(
                new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
                    "ServiceTableColumn",
                    (property, attributes) => attributes.Single().ColumnType.ToString()
                )
            );
        }
    }
}

This is fairly standard stuff. I set the connection string name to the MS_TableConnectionString – which I am using because that’s the connection string that Azure App Service creates when adding a data connection. The OnModelCreating() method is boilerplate for Azure Mobile Apps – just go with it.

Finally, don’t forget to link the configuration into the Startup.cs file:

using Microsoft.Owin;
using Owin;

[assembly: OwinStartupAttribute(typeof(backend.dotnet.Startup))]

namespace backend.dotnet
{
    public partial class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            ConfigureMobileApp(app);
        }
    }
}

If you find yourself not able to query anything at all and are getting 404 responses to everything, then it’s likely you missed this step.

Create a Model (or DTO)

Normally, I would be talking about models here. Azure Mobile Apps uses things called “Data Transfer Objects” or DTOs for short. They inherit from EntityData – this adds the five system columns to my table. Here is my DataObjects/TodoItem.cs file:

using Microsoft.Azure.Mobile.Server;

namespace backend.dotnet.DataObjects
{
    public class TodoItem : EntityData
    {
        public string Text { get; set; }

        public bool Complete { get; set; }
    }
}

Want a different field name on the remote end? Just use a JsonProperty transform – like this:

using Microsoft.Azure.Mobile.Server;
using Newtonsoft.Json;

namespace backend.dotnet.DataObjects
{
    public class TodoItem : EntityData
    {
        public string Text { get; set; }

        [JsonProperty(PropertyName = "complete")]
        public bool IsComplete { get; set; }
    }
}

I’m not doing this since I’m reusing the database I used for the Node.js backend. At this point, everything should compile, so you can do some sanity checks on your code – are you missing the NuGet packages, for example?

Create a Table Controller

A Table Controller looks just like a regular controller. In fact, there is some scaffolding help you get from the Azure SDK. Just right-click on the Controllers folder in the Solution Explorer and select Add -> Controller… – the Azure Mobile Apps Table Controller will be listed:

day-17-add-new-controller

On the next screen, you will be asked for the model class and the DbContext – I’ve conveniently just completed those:

day-17-add-new-controller-2

Click on OK and that’s it – you don’t need to do anything else.

Running locally

You can just press F5 to run this now (or right-click on the project, use Set As Startup Project… and then run it). Test it with Postman:

day-1-postman

Next Steps

I’m not ready to deploy this to Azure yet. I’ve got just a basic ASP.NET backend running. My Node.js backend handled authentication and provided a unique view of the data based on the users email address. I want to implement that capability before I deploy to Azure. That will be the topic next time.

Until then, you can get my code from my GitHub Repository.

Adding Offline Sync to an Azure Mobile Apps Universal Windows App

I added a data sync capability to my Universal Windows App last time around. This is good, but there are a few problems with it. I mentioned a few of them when I concluded the last article. I’m going to concentrate on a main problem in this article and suggest a mechanism by which you can solve it. TL;DR – you want to use Offline Sync to get around performance as well as network connectivity issues.

When you log on with the application provided in uwp-0.6, there is a delay and it’s relatively significant. It will get worse as you get more and more records. We alleviate the problem somewhat by having a local cache of the current set of data. The cache is renewed when you log in. What about a mobile device though? Firstly, you can’t always count on a connection to the Internet, so logging in and retrieving the current state may not be possible. I regularly go “off the net” when I am travelling abroad, for example. Secondly, there may be a ton of data there – do you really want to transfer all that data each and every time you log in? Mobile providers are tying profits to bandwidth usage, so you want to minimize your bandwidth usage to be a good app. For all these reasons, implementing a local sync cache that is persistent is just a good idea.

Fortunately, the Azure Mobile Apps .NET Client SDK provides for an offline sync capability built on top of SQLite. I’m going to adjust the TaskStore.cs file to handle this today. To start, ensure you install the SQLite for Universal App Platform extension – you can install it from the Extensions and Updates dialog in the Tools menu:

11132015-1

You should also add the NuGet Package WindowsAzure.MobileServices.SQLiteStore – ensure you get the 2.0.0-beta release (or later). Finally, ensure you add a reference to the SQLite for Universal App Platform to your project. Right-click on the References and select Add Reference… Select the Extensions list and place a check-mark next to SQLite for Universal App Platform:

11132015-2

All the other changes are in the TaskStore.cs. First, some new references:

// Offline Sync Requirements
using Microsoft.WindowsAzure.MobileServices.SQLiteStore;
using Microsoft.WindowsAzure.MobileServices.Sync;

We also need a mechanism to create the local SQLite store and sync the database. I do this in the same method:

        private async Task SynchronizeStoreAsync()
        {
            if (!App.MobileService.SyncContext.IsInitialized)
            {
                System.Diagnostics.Debug.WriteLine("Creating local cache context");
                var store = new MobileServiceSQLiteStore("taskstore.db");
                store.DefineTable<TaskItem>();
                await App.MobileService.SyncContext.InitializeAsync(store);
            }
            System.Diagnostics.Debug.WriteLine("Pushing changes to remote server");
            await App.MobileService.SyncContext.PushAsync();
            System.Diagnostics.Debug.WriteLine("Pulling changes from remote server");
            await tableController.PullAsync("taskItems", tableController.CreateQuery());
        }

Finally, each of the methods (Create, Update, Delete, Refresh) needs to call this function at an appropriate time – after the write or before the read. For example, here is the Create method:

        public async Task Create(TaskItem item)
        {
            item.Id = Guid.NewGuid().ToString();
            Add(item);
            if (User != null)
            {
                System.Diagnostics.Debug.WriteLine("Inserting item into remote table");
                await tableController.InsertAsync(item);
                await SynchronizeStoreAsync();
                System.Diagnostics.Debug.WriteLine("Inserted item into remote table");
            }
        }

When the first synchronization event happens, the local SQLite database file will be created in ~/AppData/Local/Packages/_something_/LocalState. You can find it using PowerShell:

11132015-3

I use the SQL Server Compact/SQLite Toolbox extension for Visual Studio to access the contents of the database. Once installed, you can access it through the Tools menu. To add your SQLite database, right-click on the Data Connections and select Add SQLite Connection… Click on Browse and find your taskstore.db file. Click on Close and your file will be added to your Data Connections. You can expand it and see the tables.

Note that there are three tables that are not yours – the main one you should be concerned with is __operations. This contains a list of changes that have not been pushed to the remote table yet. In normal operation this should be empty. However, it will contain the “to be sent” records when you are off-line.

You can get the code for this update from my GitHub Repository.

Conclusion

I did this project the wrong way round. I thought I was being clever by putting together a local cache then added data sync and then offline sync. In reality, I should have written my store based on the offline sync cache and then added the online code later on. In that way, my store would have been always available and then sync to the cloud would be an optional extra. I’m definitely doing that for the future.

I also need to get a generic filtering and sorting capability. I’m thinking of a hierarchy where the store is the lowest level and it emits an CollectionChanged event (i.e. it implements the INotifyCollectionChanged interface). The FilteredCollection<> class will wrap that collection and do the filtering on the object via predicates, emitting a CollectionChanged event only if something changed. Finally, the SortedCollection<> class will wrap that collection and do the sorting for me. By doing it as three separate classes, I get the advantage of handling those elements completely separately and I can still work within the Universal App Platform restrictions. That, however, is for another time.

Visual Studio 2015: My Favorite Extensions (Mobile Edition)

So, you have a hankering for mobile development? Well, it depends on which way you go – Apache Cordova, Xamarin Forms, Westminster, or straight up iOS and Android development? Visual Studio can help you with all of it, but you have to get the right tools.

I’ll be honest here – I’m just starting out in Mobile dev, and some of the tools I am using are marginally useful. However, I’m trying out a bunch and seeing what’s working.

Emulators and Simulators

You definitely need to install the Visual Studio Emulator for Android. Running on a simulator is a must for any serious developer and the Visual Studio Emulator is better than the Google emulator – by a lot. Visual Studio will install it for you if you select anything mobile in the installer. If not, the link will allow you to download it.

Cross-Platform Tools

You definitely want to try out Xamarin (just check the box when you install Visual Studio), but there are some extras. The Xamarin.Android Templates Pack is a good selection of new templates – there is a new blank app and a navigation app with Material Design plus a bunch of item templates. There is also the Xamarin Forms Player. If you have a real device and you install Xamarin Forms Player on that device, you can hook it up to Visual Studio and show what the XAML would look like on the device. I tend to use simulators more than real devices, so this is marginally useful for me.

I definitely recommend Xamarin, especially if you already code in C#/XAML. The tools they provide (including the iOS build host) are great.

On the Apache Cordova side, you can just use all the web technologies I posted about before. But, in addition, I like the Cordova Multiplatform Template. It’s not blank (and I like starting from blank), but it’s such a good starting template that it mostly works for more complex applications. Just ensure you understand it before you use it. I’ve also been playing with Onsen UI recently – it’s a set of web components for your mobile apps. I’ve found it really easy to work with and there is a Visual Studio Extension for it – the Onsen UI Templates. There are other starter packs as well – including Angular/TypeScript and Ionic. I’m just rather partial to starting blank, and I prefer the React/Flux architecture.

I did find the Visual Studio tools for Apache Cordova had a bug in it. If you are using JSX (ie. developing a React appliation), the Apache Cordova project will not deploy if any of the JSX files are open. You will see the following error:

Capture

In which case, you close all the JSX files and re-deploy your application.

XAML and Universal Windows Apps

There are two things in XAML that I want – the first is regions. Why is it not there already? Fortunately, XAML Regions is there to help. It does the folding markers within comments. The second item I want is a style formatter. I like Code Maid because it does this across many file formats and languages in a consistent manner. It also does a bunch of other editor things that I do on a regular basis, which makes it a good addition to the toolbox.

I’ve added the UWP Templates to my template list. It includes a blank app that doesn’t have App Insights, which makes it “more blank than blank” – a good starting point.

What’s not included…

ReSharper is probably the biggest thing I DON’T use. I’m sure it is a fine product and many people advise using it. It’s a trial (the full version costs $600 right now, but you get a free 30-day trial via download). I’m an individual programmer – ReSharper is definitely not on my list of things to spend money on.

Do you have a favorite?

When you are developing mobile or C#/Windows apps, what are your favorite extensions? Let me know in the comments.

Adventures in XAML: Layouts for HTML Developers

I’ve recently started exploring mobile apps as part of my new job, so quick hints are liable to abound. Todays is about developing a starting UI for a Windows Universal App. I like the way that the UI for mobile apps are going – whether you pick iOS 8, Material Design from Google or Windows Universal. Today, I want to explore how to get that nice layout from the News App. I’m learning XAML with an eye towards Xamarin eventually. Here is the Sports app, for example:

MSN-Sport-new-Windows-10-Mobile-screens

Note that there is a title bar with a menu icon on the left and a search icon on the right. In most apps there is also a footer bar that is light gray and has a series of icons for common operations – like edit, share and so on. In the middle is a content area that can scroll. That’s what I wanted to emulate. Something like this:

xaml-grid-design

This was done with PowerPoint Storyboarding – a feature I found awesome. There are other tools for doing storyboarding and they all assist with visualizing your UI before you start coding the layout. Given I’m working in XAML, this is very useful.

Now to the XAML. Microsoft publishes design guidelines (just like Google and Apple do). I know from reading the design guidelines that there is precious little information about actual sizing – this is a shame since that sort of introduction should be up front and center for consistency.

There are actually a few different layouts available by default in XAML:

  • The Canvas layout can be considered a HTML page where everything is using the fixed positioning option – not very useful in general.
  • The StackPanel layout can be considered a HTML page with a bunch of DIVs. Unless you do something special, they just stack up.
  • The Grid layout can be considered old-school table layout – I shudder when I think this way.
  • The VariableSizedWrapGrid is like the Grid but it can wrap elements when there isn’t enough room.

No, there isn’t an equivalent of the CSS3 flexbox control, which depresses me. I like thinking in terms of flexbox. I did a bunch of work early in my career on layouts with tables, so I’m comfortable there even if it doesn’t feel right any more. I’ve got three rows (the title bar, the main content area and the app bar) and three columns (the menu bar, the title area and the search icon). These nicely line up with what I need.

I’m not going to go into creating a Universal App – there are other tutorials for that. Let’s dive into the XAML. My main page is defined inside of MainPage.xaml:

    <Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
        <Grid.RowDefinitions>
            <RowDefinition Height="60"/>
            <RowDefinition Height="*"/>
            <RowDefinition Height="26"/>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="60"/>
            <ColumnDefinition Width="*"/>
            <ColumnDefinition Width="60"/>
        </Grid.ColumnDefinitions>
    </Grid>

The first step to laying out an application with a grid is to define the grid. Fortunately, Visual Studio gives us some help – here is my visual display:

xaml-grid-definition

I can now lay out the various pieces. My first step is to put in the title bar – I want a menu icon on a red background in the top left corner, the title in the middle and the search icon on the top right corner. Inside the closing Grid element, I can place the following:

        <!-- Title Bar Area -->
        <Canvas Grid.Row="0" Grid.Column="0" Background="Red"/>
        <Grid Grid.Row="0" Grid.Column="1" Background="LightGray">
            <TextBlock Grid.Row="0" Grid.Column="0" FontSize="26.667" VerticalAlignment="Center" Margin="8,0,0,0">Title</TextBlock>
        </Grid>
        <Canvas Grid.Row="0" Grid.Column="2" Background="LightGray"/>

This is why the Grid is so reminiscent of the old HTML table layouts – grids within grids. Each grid is just a simple cell, but I want to put color behind it, so I need something to wrap the element. It could be a Grid, StackedLayout or Canvas, really. The Grid allows me to center (vertically and horizontally) the contents. One of the nice things I’ve found is that I can lay down a canvas to set up a background color, then lay down an icon, text block or whatever I need to do the icon. The icon is laid over the top of the canvas. For example, I want a search icon in the top right corner with a gray background – I can do the following:

<Canvas Grid.Row="0" Grid.Column="2" Background="LightGray"/>
<TextBlock Grid.Row="0" Grid.Column="2" FontFamily="Segoe UI Symbol" HorizontalAlignment="Center" VerticalAlignment="Center" FontSize="29.333"></TextBlock>

The element within the TextBlock is U+E2FB within the Segoe UI Symbol font. I chose it by opening the Character Map and finding it (it’s practically at the end). Segoe UI Symbol has a lot of great symbols to use within it and it’s on every single Windows machine, so it’s a great font to use for this. Another great font is “Segoe MDL2 Assets”, which has the hamburger menu icon in it. I can add a Hamburger Menu icon to the red area like this:

<Canvas Grid.Row="0" Grid.Column="0" Background="Red"/>
<TextBlock Grid.Row="0" Grid.Column="0" Foreground="White" FontFamily="Segoe MDL2 Assets" HorizontalAlignment="Center" VerticalAlignment="Center" FontSize="26.667"></TextBlock>

Again, the text block is chosen by cut-and-paste from the character map.

The AppBar

For the lower section, there is actually a control for this called the AppBar. I want my app bar to cover the entire width, so I use a ColumnSpan to do that:

<!-- The App Bar -->
<AppBar Grid.Row="2" Grid.Column="0" Grid.ColumnSpan="3" Height="60" Background="LightGray">
    <AppBarButton Icon="Edit">Edit</AppBarButton>
</AppBar>

the app bar actually has some logic in it where clicking on the More icon (in the bottom right corner) will raise it up. The default size is 26 – set by the grid definition. When the more icon is clicked, the height becomes 60 and the app bar appears. This is all done with just three lines of code.

The Main Content Area

I want a scroll bar on the main content area. I can do this easily with XAML:

        <!-- The Content Area -->
        <ScrollViewer Grid.Row="1" Grid.Column="0" Grid.ColumnSpan="3" VerticalScrollMode="Auto">
            <TextBlock TextWrapping="WrapWholeWords">
                Lorem ipsum dolor sit ... insert lots of text from a lorem ipsum generator (2000 words or so)
            </TextBlock>
        </ScrollViewer>

The scroll viewer can be wrapped around anything and puts the appropriate scroll bars in when necessary. Once this is done, this is what the designer shows:

xaml-grid-layout

Running the Project

One of the nice things about Visual Studio 2015 is the number of options you have for running your project. I tend to run mine in the simulator when developing for Windows and the Windows Phone Emulator when developing for Windows Phone. This is a universal app. That means the same code can be run on either. Ideally, you would set up a responsive design so that one layout was used for the phone and one for the desktop. I haven’t quite gotten that far yet.

xaml-grid-simulator

You can, of course, run on your own desktop. However, that clutters your start menu and so is not a good choice. You can also run on another device (remote) – for example, running on a Surface. That also requires further setup.

More XAML discoveries are coming, so stay tuned.