AddOrUpdate() throws error: Modifying a column with the 'Identity' pattern - how should I be handling this?

Question

I've been working through Adrian Hall's book on integrating Xamarin and Azure Mobile Apps. In Chapter 3 he adds a User table to facilitate "Friends" data. In his implementation, the client authenticates the user and then makes a request to a custom endpoint that either adds the user to the database or updates their record. Here's an abridged version of the method in the custom controller:

[HttpGet]
public async Task<IHttpActionResult> Get()
{
    // ...Obtain user info
    User user = new User()
    {
        Id = sid,
        Name = name,
        EmailAddress = email
    };
    dbContext.Users.AddOrUpdate(user);
    dbContext.SaveChanges();
    // ...
}

The trouble is, the 2nd time the same user logs in to the app, this code throws an exception saying

Modifying a column with the 'Identity' pattern is not supported. Column: 'CreatedAt'. Table: 'CodeFirstDatabaseSchema.User'.

This StackOverflow Answer explains that this is because the AddOrUpdate() method nulls out any properties not set on the entity, including CreatedAt, which is an identity column. This leaves me with a couple of questions:

What is the right way to Add or Update an entity if the CreatedAt value cannot be edited? The same SO thread suggests a helper method to look up the existing CreatedAt and apply it to the entity before trying to save it. This seems cumbersome.
Why is this implemented as a custom auth controller that returns a new Auth token when it only needs to add or update a User in a database? Why not use a normal entity controller to add/update the new user and allow the client to continue using the Auth token it already has?

For the CustomAuthController.cs code, see here.

matcheek · Answer 1 · 2018-06-21T10:52:42.673

When you focus on what you are trying to do from SQL perspective it would be like:

update dbo.some_table set some_primary_key = new_primary_key where some_primary_key = ...

which would result in cannot update identity column some_primary_key which makes sense.

But if you do have a reason to update the PK you still can do it if you set the identity insert SET IDENTITY_INSERT dbo.some_table ON; Then after you made an insert you set it off using similar syntax.

But this is rather exceptional scenario. Usually there is no need to manually insert PKs.

Now going back to EF.

The error you are getting is telling you that you cannot modify a column with PK, most likely user_id and/or some other columns if you have composite PK.

So, first time round a new user gets created. Second time round, because you are suing GetOrUpdate a user gets udpated but because you are passing PK it breaks.

Solution?

AddOrUpdate was meant to help with seeding the migrations only. Given its destructive nature I would not recommend using GetOrUpdate anywhere near production.

You can replace GetOrUpdate with two operations Get and Update

Fetch user and then
if not exists then create a new one
or if it does exist then update it

AddOrUpdate() throws error: Modifying a column with the 'Identity' pattern - how should I be handling this?

1 Answers1