CODEWITHSUNDEEP
sqliteazurexamarinazure-mobile-services
aritchie
aritchie

Reputation: 601

Azure IMobileServiceSyncTable Parser Stack Overflow

I have an app that uses Azure Mobile offline sync. I'm trying to query a iMobileServiceSyncTable using the following:

var listOfIds = dataItems.Select(i => i.Id).ToList();

IEnumerable<DataItemValue> items = await dataItemValueTable
                .Where(i => listOfIds.Contains(i.DataItemId))
                .ToEnumerableAsync();

If the number of items in listOfIds is small then everything works fine. If it's larger, e.g. 180, then I get an error:

Exception thrown: 'Microsoft.WindowsAzure.MobileServices.SQLiteStore.SQLiteException' in System.Private.CoreLib.dll
Error executing SQLite command: 'parser stack overflow'.:GetDataItemValues::Sync error: {0}

It should be the equivalent of "SELECT * FROM tablename WHERE Id IN ('a', 'b', 'c'...)", but 180 values in the "IN" statement seems low to be causing an issue? If I use DB Browser for SQLite and manually do they query, it works with no issues.

Any idea why the Azure MobileServices client is throwing the error?

Upvotes: 1

Views: 479

Answers (1)

Bruce Chen
Bruce Chen

Reputation: 18465

It should be the equivalent of "SELECT * FROM tablename WHERE Id IN ('a', 'b', 'c'...)", but 180 values in the "IN" statement seems low to be causing an issue?

Following Debugging the Offline Cache and override the method ExecuteQueryInternal, then you could find that the generated SQL statement looks like this:

SELECT * FROM [City] WHERE ((((([id] = @p1) OR ([id] = @p2)) OR ([id] = @p3)) OR ([id] = @p4)) OR ([id] = @p5))

I don't know the format or length of your Id for querying. You need to test it on your side and obtain a proper count based on your Id, then divide your listOfIds and execute your query in batch to retrieve your records. The code snippet would look as follows:

var listOfIds = dataItems.Select(i => i.Id).ToList();
List<DataItemValue> items= new List<DataItemValue>();
int batchSize = 50;
for (int i = 0; i < Math.Ceiling((double)listOfIds.Count / batchSize); i++)
{
    var ids = listOfIds.Skip(i * batchSize).Take(batchSize);
    var results = await cityTable.Where(c => ids.Contains(c.Id)).ToEnumerableAsync();
    if (results != null)
        items.AddRange(results);
}

Any idea why the Azure MobileServices client is throwing the error?

Exception thrown: 'Microsoft.WindowsAzure.MobileServices.SQLiteStore.SQLiteException' in System.Private.CoreLib.dll Error executing SQLite command: 'parser stack overflow'.:GetDataItemValues::Sync error: {0}

The SDK would internally invoke ExecuteQueryInternal under MobileServiceSQLiteStore.cs, I assumed that the generated SQL statement may hits Limits In SQLite:

Maximum Depth Of An Expression Tree

SQLite parses expressions into a tree for processing. During code generation, SQLite walks this tree recursively. The depth of expression trees is therefore limited in order to avoid using too much stack space.

The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression tree depth. If the value is 0, then no limit is enforced. The current implementation has a default value of 1000.

Upvotes: 1

Related Questions