Datetime issue and joins in azure mobile apps RRS feed

  • Question

  • Hello,

    I have 2 questions regarding Azure mobile services.

    I'm using Xamarin.Forms and could not quite understand how the DateTime parsing works. I have followed the ToDo sample app and made a generic class for all the tables in my app.

    Structure is like this:          

    public class TableSyncManager
    public void InitClient()
    public void DefineTable<T>() where T : BaseDto
    public IMobileServiceSyncTable<T> GetTable<T>() where T : BaseDto
    public async Task<Tuple<bool, string>> InsertAsync<T>(T item) where T : BaseDto
    public async Task<Tuple<bool, string>> UpdateAsync<T>(T item) where T : BaseDto
    public async Task<Tuple<bool, string>> DeleteAllAsync<T>() where T : BaseDto
    public async Task<Tuple<bool, string>> DeleteAsync<T>(T item) where T : BaseDto
    public async Task PushTablesAsync()
    public async Task PullTableAsync<T>() where T : BaseDto

    The problem is I don't know how do I run custom raw queries which might have joins and cases on tables(Which is my second question). So to solve this I have created a separate SQLiteConnection just for getting data set but for CRUD operations, I use TableSyncManager so the azure knows the which rows qualifies for sync. 

    I know this approach is not good, its just get the work done kinda situation.

    DateTime parsing is where I am stuck.I have two columns 'CreatedAt' & 'UpdatedAt' both are of datetime type. When i retrieve the record using TableSyncManager, i get the datetime just fine but when i query from sqlite i get the default datetime (01/01/0001) in those columns.

    Can anyone please guide me here I'm badly stuck.

    How can I solve this DateTime problem and how can I implement joins in Azure mobile services?

    Sunday, December 1, 2019 10:01 AM

All replies

  • Thanks for asking question! We apologize for the inconvenience that this issue caused. After performing the initial investigations on the issue with the information you have shared We feel that this requires a much deeper investigation, so request you to send an email to AzCommunity[at]Microsoft[dot]com  with your subscription ID referencing this thread and we will assist you with engaging Azure technical support.

    Wednesday, December 4, 2019 3:03 AM
  • We were expecting your email but did not receive it. Just to highlight a couple of things here, you have stated that “When I retrieve the record using TableSyncManager, I get the datetime just fine but when I query from SQLite I get the default datetime (01/01/0001) in those columns.", so it sounds like things are working fine when using Azure Mobile Apps, however when querying the SQLite DB directly you do not get proper date times. This doesn’t appear to be a Mobile App issue. To isolate the issue, you may check to see how it queries locally and re-validate the SQL lite connection and query. Generally, you can convert all timestamps to UTC, so that you can use them for sorting and searching or based on your requirement you can have searching, sorting and other computations not in SQL but in your App.  Take a look at the approach outlined in the blog and see if it helps.

    Kindly let us know how it goes for further investigation. You can reach us on the email as Sneha mentioned above for a much closer analysis we would be more than happy to assist you.

    Note: This post contains a third-party site for your reference & convenience only, please exercise caution while accessing 3rd party sites.

    Tuesday, January 7, 2020 6:42 PM