Xamarin - Joining 2 or more tables (Azure)

Garett Burdey 1 Reputation point
2021-10-15T01:09:15.227+00:00

I am in a situation where I would like to join 2 or more tables. The application is defined with a Model, DataService, ViewModel and View.
In our current calls we are dealing with just single tables via: App.MobileService.GetTable<XYZ>().Where....; however this does not lend itself to joining to multiple tables. What are our options / best approach.
Considering, SQLLite? Maybe utilize Azure views (Joining within) and model that Azure object in a similar manner that we are for single tables.

Looking for some guidance in this area that will scale well, and be intuitive without alot of overhead.
Thanks

Xamarin
Xamarin
A Microsoft open-source app platform for building Android and iOS apps with .NET and C#.
5,295 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,145 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ryan Hill 25,981 Reputation points Microsoft Employee
    2021-10-19T16:57:44.4+00:00

    If you're using Azure Mobile Apps for a backend, the Mobile Service is built upon the OData spec. That means calling your backend with something like http://host/service/Products?$filter=style has Sales.Pattern'Yellow' should theoretically work, see https://learn.microsoft.com/en-us/odata/concepts/queryoptions-overview.

    However, in searching the mobile apps repo, I didn't see any methods that would support this type of querying. Options I can suggest trying are:

    1. From the client side, make the call using an HTTP client process the results as JSON. This assumes that the backend DbContext has been relationships configured in for the DbSet.
    2. Pull down both table sources and perform the join through LINQ against the result set on result set on the client side.
    3. Create a feature request for this functionality and check the docs for updates.
    0 comments
  2. Adrian Hall (MSFT) 156 Reputation points Microsoft Employee
    2021-10-19T17:55:29.807+00:00

    It depends on whether you are dealing with online data (i.e. communicating with the backend) or offline data (communicating with a SQLite backed sync table). If the former, I'd suggest providing a materialized view of the combined data that meets your requirements. As long as the select against the view supports the fields necessary for the data transfer, you will be ok.

    If the latter, you can do a specific JOIN by using the SQLite database directly. See: https://github.com/Azure/azure-mobile-apps-net-client/blob/master/src/Microsoft.Azure.Mobile.Client.SQLiteStore/MobileServiceSQLiteStore.cs#L581

    Something like: 

    var list = await store.ExecuteQueryAsync("", "SELECT a.Id,a.Name,b.Details from table1 a, table2 b WHERE a.Id = b.Id", null);

    should work.