Microsoft.Datasync.Client.IOfflineTable - 'Contains' is not supported in a 'Where' clause

[Brian-Con]

public async Task<IEnumerable<HZP_Storage_Unit_Pending>> GetPendingUnitsBySalesOrderNos(List<string> salesOrderNumbers)
        {
            List<HZP_Storage_Unit_Pending> storageUnitsPending = new List<HZP_Storage_Unit_Pending>();

            if (salesOrderNumbers != null || salesOrderNumbers.Count() > 0)
            {
                storageUnitsPending = await Table.Where(c => salesOrderNumbers.Contains(c.HZ_SalesOrderNo)).ToListAsync();
            }

            return storageUnitsPending;
        }

In the code above, the line storageUnitsPending = await Table.Where(c => salesOrderNumbers.Contains(c.HZ_SalesOrderNo)).ToListAsync(); works against Microsoft.WindowsAzure.MobileServices.Sync.IMobileServiceSyncTable.

However, against Microsoft.Datasync.Client.IOfflineTable, it throws the following error:
"'value(System.Collections.Generic.List`1[System.String]).Contains(c.HZ_SalesOrderNo)' is not supported in a 'Where' clause"

To get it to work, I've rewritten the code as follows:

public async Task<IEnumerable<HZP_Storage_Unit_Pending>> GetPendingUnitsBySalesOrderNos(List<string> salesOrderNumbers)
        {
            List<HZP_Storage_Unit_Pending> storageUnitsPending = new List<HZP_Storage_Unit_Pending>();

            if (salesOrderNumbers != null || salesOrderNumbers.Count() > 0)
            {
                storageUnitsPending = (await Table.ToListAsync()).Where(c => salesOrderNumbers.Contains(c.HZ_SalesOrderNo)).ToList();
            }

            return storageUnitsPending;
        }

The problem with this rewrite is that it results in very poor performance.

Is there a better way of doing this?

Thanks!

[adrianhall]

Nope - we don't support lists in the SQL statement. The LINQ variant is a restricted set of methods that the OData backend understands.

[StargazerNC]

Hello,

@adrianhall Would you (or anybody, actually) suggest an alternative?

In the .Net Framework version, because sometimes the number of items would create an "impossible string of OR conditions, we dropped this and used it to send the list as a header on the request and filter on the backend. However, never liked the "feel" of this...

Hence I'm asking for alternatives for OfflineTable and PullAsync methods...

Thanks!

[adrianhall]

The offline table has a mechanism for writing SQL specifically. See https://github.com/Azure/azure-mobile-apps/blob/main/sdk/dotnet/src/Microsoft.Datasync.Client.SQLiteStore/OfflineSQLiteStore.cs#L305

[OuTseries]

Hi,

@adrianhall Thank you so much for the reply.
So we can query the offline table using sql. But what about the sync mechanism?

Using the IOfflineTable.PullItemsAsync is there any way to fetch data as if a "Contains" is used?
We are currently building a predicate with multiple "or" using PredicateBuilder but as stated that might create an "impossible string of OR conditions".

Thanks!

[adrianhall]

I can't suggest something that will work in all cases. If you can move the list to the remote service somehow (e.g. the user selects the items from the list and you sync that), then you can produce a new repository that does the requisite join based on the table that stores the things that the user selects and the actual data you want to sync. However, it presumes a domain model that may not be yours.

[tomysaw]

Hey @adrianhall! Is there still no good way to select, e.g., by multiple ids from the offline table?

[adrianhall]

Correct - I’m switching over to Entity Framework Core for the offline table in CommunityToolkit/Datasync, so you’ll be able to do it there. Current state of that, though, is best described as “slow going”. Datasync is a complex beast.

[tomysaw]

Got it, thank you! Providing I have a "readonly" table - do you see any issues if I open a traditional SQliteAsyncConnection in parallel to Datasync? So I would have better capabilities for querying but at the same time I'd keep the incremental sync and offline access provided by Datasync

[adrianhall]

I've never tried it and others have reported various conflicts in the underlying sqlite3_green library that all library writers depend on. It can't hurt, but be aware the connections may conflict.