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.