Azure App Services (Mobile App) Violation of PRIMARY KEY constraint

Question

I am using Azure App Services (Mobile Apps) to develop an application using the offline sync feature with SQLite. My data object model is:

public class Customer : EntityData
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    //…
    public DateTime DateCreated { get; set; }
    public DateTime DateModified { get; set; }
    public virtual IList<Card> Cards { get; set; }
}
public class Card : EntityData
{
    public bool IsDisabled { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime LastUsedDate { get; set; }
}

And my controller code is:

    // GET tables/Customer
    public IQueryable<Customer> GetAllCustomers()
    {
        return Query();
    }

    // GET tables/Customer/48D68C86-6EA6-4C25-AA33-223FC9A27959
    public SingleResult<Customer> GetCustomer(string id)
    {
        return Lookup(id);
    }

    // PATCH tables/Customers/48D68C86-6EA6-4C25-AA33-223FC9A27959
    public Task<Customer> PatchCustomer(string id, Delta<Customer> patch)
    {
        return UpdateAsync(id, patch);
    }

    // POST tables/Customer
    public async Task<IHttpActionResult> PostCustomer(Customer item)
    {
        Customer current = await InsertAsync(item);
        return CreatedAtRoute("Tables", new { id = current.Id }, current);
    }

    // DELETE tables/Customer/48D68C86-6EA6-4C25-AA33-223FC9A27959
    public Task DeleteCustomer(string id)
    {
        return DeleteAsync(id);
    }

I am not quite sure how this is mapped behind the scene, but from my client I am calling:

    await App.MobileService.SyncContext.PushAsync();
    await customerTable.PullAsync("customers", customerTable.CreateQuery());

All works fine when I perform the initial sync of data to insert customers, however, when I try to update any of them I am receiving an error “The operation failed due to a conflict: 'Violation of PRIMARY KEY constraint 'PK_dbo.Cards'. Cannot insert duplicate key in object 'dbo.Cards'. The duplicate key value is (000000003414). Note I have not changed the card details, only the customer. I have seen this post: https://blogs.msdn.microsoft.com/azuremobile/2014/06/18/insertupdate-data-with-1n-relationship-using-net-backend-azure-mobile-services/ However it seems overly complex and I am not sure if it is what I am after… does anyone know what is happening?

I suspect that you are looking at an Entity Framework issue because the Id is a string - not an int. Set up the appropriate CardId as a string in your Customer object, then link via the ForeignKey annotation, perhaps? — Adrian Hall
Mobile services require the Id to be a string, not sure why... My controller implementation is just a rename of the functions supplied with the sample solution when you provision a Mobile Services backend... — Robin
have you put a breakpoint on PatchCustomer? or is it hitting insert again? — SWilko
I have put a breakpoint on PatchCustomer and it is hitting that correctly. it is then calling UpdateAsync and returns the error as described above... — Robin

lindydonna lindydonna · Accepted Answer · 2016-05-25T20:46:56

There are a lot of possible things that could be going wrong: maybe your client is actually doing another insert instead of an update, which is why you'd get a 409 conflict exception. Or, less likely, the insert succeeded but the response was lost, so the client will retry the insert, and will get the exception.

Or, it could be that the problem is the relationship between the tables. To debug this, first you should log your outgoing requests so you can see what's going on. If your client is sending inserts instead of updates, you'll see that in your log. See Log outgoing requests in managed client (Xamarin, Windows).

You can then attach a debugger to either your remote service or the one running locally to see why the Entity Framework validation error is occurring.

Btw, if you're doing offline sync, you should also add conflict handling code. Here's a sample that handles 409 (for the case when Insert suceeds and the response is lost) and 412 precondition failed (when two clients try to update the same data): https://github.com/lindydonna/xamarin-forms-offline-sync/blob/master/XamarinFormsOffline/SyncHandler.cs#L23.

Azure App Services (Mobile App) Violation of PRIMARY KEY constraint

2 Answers