As a mobile developer one of the things I’m critical of is ensuring that network requests are kept to a minimum. Having built several sync solutions in the past I was a little surprised with the current Azure Mobile Service implementation, and here’s why. By now, if you’ve read my previous posts you’ll recall seeing that each table is synchronised by invoking PullAsync to retrieve the latest updates from the Mobile Service, and invoke PushAsync to push local changes up to the Mobile Services. This seems reasonable – however, what I saw when I ran Fiddler is that each time I synchronised a table I would always seem the most recent entry being returned. Not believing what I saw I repeatedly ran the PullAsync method, and sure enough, each time I invoked it, the most recent entity would be returned. This in itself wouldn’t be so bad but the way that the Mobile Service offline support works out if it has retrieved all the entities for a query is to add the $skip query string to each query until no items are returned. The upshot is that not only does the most recent entity get returned, a second unnecessary call is made which returns no items – so there’s a network bandwidth and latency cost!
The reason for this is that there is a difference in time resolution between Entity Framework and SQL Server. Let’s use Visual Studio, or SQL Server Management Studio, or the online Azure SQL management tool, to query the contents of the Inspections table. Here we can see that the most recent update to this table was at 2015-01-09 11:45:40.0194886 +00:00.
Now, let’s take a look at the queries that PullAsync generates – the query filter is updatedAt >= 2015-01-09 11:45:40.0190000 +00:00. Firstly, this uses ge which is greater than or equal. Secondly, look at the lack of precision in the timestamp – this means you’re always going to get the most recent entry returned (at least). You’ll see that the second query uses the same filter, except it adds $skip=1 to jump over the entry that was returned by the first query.
https://realestateinspector.azure-mobile.net/tables/Inspection?$filter=(__updatedAt%20ge%20datetimeoffset’2015-01-09T11%3A45%3A40.0190000%2B00%3A00′)&$orderby=__updatedAt&$skip=0&$top=50&__includeDeleted=true&__systemproperties=__createdAt%2C__updatedAt%2C__version%2C__deleted
https://realestateinspector.azure-mobile.net/tables/Inspection?$filter=(__updatedAt%20ge%20datetimeoffset’2015-01-09T11%3A45%3A40.0190000%2B00%3A00′)&$orderby=__updatedAt&$skip=1&$top=50&__includeDeleted=true&__systemproperties=__createdAt%2C__updatedAt%2C__version%2C__deleted
Now, let’s see if we can improve the query – we’re going to change this:
var inspectionQ = MobileService.GetSyncTable<Inspection>().CreateQuery();
await MobileService.GetSyncTable<Inspection>().PullAsync(typeof(Inspection).Name, inspectionQ);
To this:
await MobileService.PullLatestAsync<Inspection>();
Where PullLatestAsync is an extension method defined as follows:
public static class MobileServiceClientExtensions
{
public async static Task PullLatestAsync<TTable>(this IMobileServiceClient client) where TTable : BaseEntityData
{
// Get the most recent
var mostRecent = await client.GetLatestAsync<TTable>();
// Convert the most recent into a query (assuming there is one)
if (mostRecent != null)
{
var maxTimestamp = mostRecent.UpdatedAt.AddMilliseconds(1);
var q = client.GetSyncTable<TTable>()
.CreateQuery()
.Where(x => (x.Id != mostRecent.Id || x.UpdatedAt > maxTimestamp));
// Do a (filtered) pull from the remote tabl
await client.GetSyncTable<TTable>().PullAsync(typeof(TTable).Name, q);
}
else
{
await client.GetSyncTable<TTable>().PullAsync(typeof(TTable).Name, client.GetSyncTable<TTable>().CreateQuery());
}
}
public async static Task<TTable> GetLatestAsync<TTable>(this IMobileServiceClient client) where TTable : BaseEntityData
{
return (await client.GetSyncTable<TTable>()
.OrderByDescending(x => x.UpdatedAt)
.Take(1)
.ToListAsync()).SingleOrDefault();
}
}
This extension method requires us to extend our BaseEntityData to include the UpdatedAt property so that we can use it in our query:
public class BaseEntityData
#if SERVICE
: EntityData{}
#else
{
public string Id { get; set; }
[CreatedAt]
public DateTimeOffset CreatedAt { get; set; }
[UpdatedAt]
public DateTimeOffset UpdatedAt { get; set; }
[Version]
public string Version { get; set; }
}
#endif
Running this now, we see that our filter expression has been extended to exclude the most recent entity, unless it has changed.
https://realestateinspector.azure-mobile.net/tables/Inspection?$filter=(((id%20ne%20’6d9f9a19-da8f-4bd6-a4d3-dd888a67e00e’)%20or%20(__updatedAt%20gt%20datetimeoffset’2015-01-09T22%3A45%3A40.0200000%2B11%3A00′))%20and%20(__updatedAt%20ge%20datetimeoffset’2015-01-09T11%3A45%3A40.0190000%2B00%3A00′))&$orderby=__updatedAt&$skip=0&$top=50&__includeDeleted=true&__systemproperties=__createdAt%2C__updatedAt%2C__version%2C__deleted