Offline sync with Azure Mobile Apps not working, database looks empty

Question

I am trying to achieve offline database sync using Azure Mobile Apps. When I use the remote version (using MobileServiceClient.GetTable instead of MobileService.GetSyncTable) everything works successfully. However, I can't make it work with local SQLite database.

DatabaseContext class is registered as a singleton in the Autofac container.

public class DatabaseContext
{
    private readonly MobileServiceClient _client;
    private readonly MobileServiceSQLiteStore _store;

    public DatabaseContext()
    {
        if (File.Exists(Constants.LocalDatabase))
        {
            File.Delete(Constants.LocalDatabase);
        }

        _client = new MobileServiceClient(Constants.ApiEndpoint);
        _store = new MobileServiceSQLiteStore(Constants.LocalDatabaseName);
    }

    public async Task Initialize()
    {
        _store.DefineTable<Activities>();

        await _client.SyncContext.InitializeAsync(_store);
        await _client.SyncContext.PushAsync();

        var activitiesTable = _client.GetSyncTable<Activities>();
        await activitiesTable.PullAsync(null, activitiesTable.CreateQuery());


        // DATA IS EMPTY
        var data = await activitiesTable.CreateQuery().ToListAsync();
    }
}

My model class:

public class Activities
{
    public Guid Id { get; set; }
    public string Name{ get; set; }
    public DateTimeOffset? UpdatedAt { get; set; }
    public DateTimeOffset? CreatedAt { get; set; }
    public byte[] Version { get; set; }
}

When I look at my streaming logs inside the azure dashboard, I can see this request:

GET /tables/Activities $skip=0&$top=50&__includeDeleted=true&$skip=0&$top=50&__includeDeleted=true&X-ARR-LOG-ID=19cd669b-df9a-41ee-b86e-9db766fc8e01 443 - 70.52.250.32 ZUMO/4.0+(lang=Managed;+os=Android;+os_version=6.0.1;+arch=Unix;+version=4.0.0.0) - 200 0 0 3228 1241 2160

Calling it with Postman returns data.

Any idea?

Answer 1

Though your Activities model may work, I would define my model as follows:

public class Activities
{
    public string Id { get; set; }
    public string Name { get; set; }
    [UpdatedAt]
    public DateTimeOffset? UpdatedAt { get; set; }
    [CreatedAt]
    public DateTimeOffset? CreatedAt { get; set; }
    [Version]
    public byte[] Version { get; set; }
}

Note: The createdAt and updatedAt and version fields are updated by the database, if you push record(s) to the server, they will be populated.

I tested on my side and found I could pull the records from server and there has records in my sqlite db.

await activitiesTable.PullAsync(null, activitiesTable.CreateQuery());

The above code would automatically send multiple requests (default pagesize 50 for a single request) to pull all records, then it would execute the following sql statement to update your local data store:

BEGIN TRANSACTION

INSERT OR IGNORE INTO [TodoItem] ([id]) VALUES (@p0),(@p1),(@p2),(@p3),(@p4),(@p5),(@p6),(@p7),(@p8),(@p9),(@p10),(@p11),(@p12),(@p13),(@p14),(@p15),(@p16),(@p17),(@p18),(@p19),(@p20),(@p21),(@p22),(@p23),(@p24),(@p25),(@p26),(@p27),(@p28),(@p29),(@p30),(@p31),(@p32),(@p33),(@p34),(@p35),(@p36),(@p37),(@p38),(@p39),(@p40),(@p41),(@p42),(@p43),(@p44),(@p45),(@p46),(@p47),(@p48),(@p49)

UPDATE [TodoItem] SET [Text] = @p0,[UserId] = @p1 WHERE [id] = @p2

UPDATE [TodoItem] SET [Text] = @p0,[UserId] = @p1 WHERE [id] = @p2

.

.

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT OR IGNORE INTO [__config] ([id]) VALUES (@p0)

UPDATE [__config] SET [value] = @p0 WHERE [id] = @p1

COMMIT TRANSACTION

For more details, you could refer to Debugging the Offline Cache.

Based on your description, you could retrieve the data from your server, but it could not update your local sqlite db. I would recommend that you could leverage fiddler to capture the network traces when calling PullAsync and open your sqlite db to check your table.

Additionally, as Using soft delete in Mobile Services mentioned as follows:

When using the Offline data Sync for Mobile Services feature, the client SDK automatically queries for deleted records and removes them from the local database. Without soft delete enabled, you need to write additional code on the backend so that the client SDK knows which records to remove from the local store. Otherwise, the client local store and backend will be inconsistent with regard to these deleted records and the client method PurgeAsync() must be called to clear the local store.

Note: You need to check the deleted property from the records when your calling PullAsync.

Moreover, you could leverage the "DEPLOYMENT > Quickstart" under your mobile app to download and run the Xamarin.Forms solution to narrow this issue. For more details, you could refer to here. Also, you could refer to adrian hall's book about Chapter 3 - Data Access and Offline Sync.

Answer 2

The problem was coming from the API-side. I was able to retrieve records using Postman, however, the id field was not lowercase.

[Key]
[TableColumn(TableColumnType.Id)]
public string Id { get; set; }

Should be:

[Key]
[TableColumn(TableColumnType.Id)]
public string id { get; set; }

Very frustrating bug.

Given this tutorial: https://adrianhall.github.io/develop-mobile-apps-with-csharp-and-azure/chapter3/server/, this class cannot work properly because of "Id" field being camel case.

namespace Chapter3.DataObjects
{
    public class Example : EntityData
    {
        public string StringField { get; set; }
        public int IntField { get; set; }
        public double DoubleField { get; set; }
        public DateTimeOffset DateTimeField { get; set; }
    }
}

EntityData class will work using remote tables (MobileClientService.GetTable<T>) but not with sync tables (MobileClientService.GetSyncTable<T>) .