Azure Mobile Apps: Writing a Fast Custom DomainManager, Part 2

By October 25, 2015 Azure, C#, SQL Server 3 Comments

Picking Up Where We Left Off

Last time, I wrote about how to develop a custom DomainManager to plug into an Azure Mobile Apps TableController. The goal is to implement a DomainManager that works seamlessly with the Azure Mobile App client SDKs, enables CRUD against database tables with non-string keys, and generates good SQL that can be supported with ordinary general-purpose indexes. I covered which class to inherit from, the methods you’ll need to implement, and some of the helpful utilities that the parent class affords you. In this post, I will finish discussing the remaining utilities, show how to implement the necessary methods in the custom DomainManager, and then show the generated SQL captured by SQL Profiler when querying the custom TableController and explain why this SQL is better than that which is generated by the SimpleMappedEntityDomainManager from the MSDN blogs.

Entity Manipulation Utilities

The last category of utilities provided by MappedEntityDomainManager that I covered were the key-parsing utilities. As you’ll recall, between GetKey and GetCompositeKey, you have built-in support to convert the string ID parameter passed into the three methods you need to implement. What about actually performing the lookup, update, and delete? There’s nothing stopping you from writing them yourself, but MappedEntityDomainManager once again provides assistance, making it even easier.

UpdateEntityAsync and DeleteItemAsync

These two methods have very similar signatures:

Combined with GetKey (or GetCompositeKey) to parse the key values, suddenly implementations for UpdateAsync and DeleteAsync become trivial one-liners:


This utility method simplifies the implementation of the Lookup method. Unlike the previous methods that just took the key values as arguments, this method requires a predicate expression.

I assume there’s a technical reason for why the signature of this one differs from the other two, even though those methods also need to look up a single entity in order to update or delete the entity identified by the key. I sure can’t think of one, though. It seems weird to me, like this method was developed by a different person than the other two.

It should be possible to write code that uses reflection to build an expression that compares the key property of TModel to the parsed key, but it takes a lot more code than the more straightforward approach of putting the responsibility of constructing the predicate on the consuming TableController. Here’s how I did it:

_lookupPredicateFactory is a function that returns a predicate expression, and in my implementation, it is initialized when the DomainManager is constructed.

This is how it looks to construct an instance in a TableController:

In natural language, “Given a scalar key value, return a function expression that compares the given key value to the model’s ID property.” There’s no need to specify any types because the compiler infers them from the generic type parameters. And with that, guess what? The custom domain manager is fully implemented and wired to a TableController and ready for testing!

Profiling The Results

ID Lookups

It’s time to see if the custom DomainManager works and evaluate whether the SQL is improved over the SimpleMappedEntityDomainManager. Run the mobile app project and you’ll see a page that says that the app is running. Now, to test the insert, update, and delete endpoints, you’ll need to be able to specify the HTTP verb, but to test the get and query methods, you can start with the browser’s address bar. I already had some test data in my DB because I went DB first and hand-coded the entity class afterwards. With SQL Server Profiler and pointed it at (localdb)\v11.0, I hit the following URL to get an ID I already knew existed:

And I was rewarded with this statement in the profiler:

Why do I say “rewarded?” If you are unfamiliar with what distinguishes good SQL from bad, it might not seem so obvious. Let me compare the crucial lines emitted from SimpleMappedEntityDomainManager versus this new DomainManager:

The thing to understand about these two WHERE clauses is how the ID column is referenced. In order for SQL Server to be able to use an index on a column, it must be “naked”; it should not be wrapped inside typecasts and function calls. It is possible to create indexes that will work on column references that are wrapped like that, but the index is of limited usefulness and each index on a table imposes a little more overhead to keep it up-to-date, so in general you want a few widely-applicable indexes rather than a lot of narrowly-applicable ones. Applying this knowledge to the SQL above, the WHERE clause generated by SimpleMappedEntityDomainManager would not be supported by an index on the ID column; it would require a specialized index on CAST( [Extent1].[ID] AS nvarchar(max)). In comparison, the WHERE clause generated by this new implementation can be supported by an index on the ID column.

OData Queries

My custom entity has a single non-key column called ‘Data’. All TableControllers expose an endpoint that can be queried using the OData standard. To query for an entity by its Data property, enter a URL like this:

Here’s the WHERE clause that gets caught by the profiler:

Just as with the ID lookup, this query accesses Data column directly with no wrapping casts or functions, which is what we want to see. The SimpleMappedEntityDomainManager works equivalently, if you were to compare them.

One Gotcha

One thing to be wary of is that doing an OData query for an ID value will suffer the same bad WHERE clause that you’re trying to escape from. This URL:

Will result in this WHERE clause:

…so don’t do that. If you’re thinking that you could get around that by making your filter be “id eq 1”, let me stop you right there. That query will result in an exception thrown by attempting to apply an equality operator to two incompatible types (String and Int32). This happens because the default implementation of MappedEntityDomainManager.Query casts the model type to the DTO type prior to applying the query. So to efficiently access entities by ID, use the lookup URL above. If you absolutely must perform an OData $filter on the ID column, also include filter criteria on other columns that are covered by indexes as well.

Coming Up Next

This post has started to run long, so I’m going to pause here. Next time, I’ll discuss one way to test the insert/update/delete capabilities of the TableController and show the generated SQL for those operations as well.

The following two tabs change content below.

Adam Anderson

Master Consultant at Falafel Software
Adam Anderson is a Microsoft Certified Solution Developer with over 19 years of experience. He started as a consultant using Delphi to rapidly deliver custom, high-quality business solutions in a wide variety of industries. His later experiences afforded him the opportunity to become familiar with a number of different products, technologies, and disciplines, including SQL Server and T-SQL, Oracle and PL/SQL, Crystal Reports, SQL Reporting Services, Internet Information Services, .NET Framework, LINQ, Entity Framework, CSS, HTML, Javascript, Kendo UI, ASP.NET MVC & Web API, ServiceStack, Command batch files, PowerShell, normalized relational database design, dynamic databases, and good object-oriented design based on design patterns and testability.