InsertOrReplace ignores PrimaryKey AutoIncrement

[Johan-dutoit]

If I use InsertOrReplace to insert a new record, which has a primary key of 0, it inserts the record with 0 instead of using the next available id.

[AndersSoborg]

Hi There

Please have a look at https://github.com/praeclarum/sqlite-net/issues/312

[Johan-dutoit]

Hi,

I don't believe they are related. #312 says it always returns 1 where I am not worried about what it is returning, but more on what is happening.

Just had a look at the source code, and yes it is using the Insert method, but the issues are still unrelated.

[aMarCruz]

Hack the SQLite.cs code. Comment line 1930:

    //   decl += "autoincrement ";

in the class for your tables, declare your primary key as...

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

...after INSERT, because the AutoIncrement attribute, sqlite-net copy the id value to the field Id of the instance, where you can read it.

SQLite 3.8.x automatically generates a new key for fields of type INTEGER PRIMARY KEY (ascending) if the value is not given in the INSERT, no need "AUTOINCREMENT".

With this hack, sqlite-net sends only INTEGER PRIMARY KEY with CREATE TABLE command for fields [PrimaryKey, AutoIncrement]. You need set the "AutoIncrement" attribute, sqlite-net will not update the value of Id w/o this.

(sorry by my "english").

[ColonelDuddits]

If I understand your question correctly, the behavior you describe is a feature of SQLite. SQLite allows you to specify values for the primary key column. Because id is specified as zero the first time, your row gets inserted with zero. Each time you use InsertOrReplace with id = 0 after that, you are actually replacing an existing row. To get the result you want, you need to make the id property of your class nullable. Don't set a value for it; InsertOrReplace will manage that for you.

[MartinWeb]

Sorry to dig that post but it appears that the problem is still there. AutoIncrement is apparently not working with Insert.

I didn't find any answer to that on Google or here. So i did a workaround before i execute the Insert, i check for the last Id of the table and increment it (if one row is present).

Stock lastStock = database.Table<Stock> ().OrderByDescending (u => u.Id).FirstOrDefault ();
if (lastStock != null) {
    newStock.Id = lastStock.Id + 1;
} else {
    lastStock.Id = 1;
}

[tibitoth]

Yes, the InsertOrReplace is still not working :( My workaround to solve that problem: Update and Insert method is working good.

using (var dbConn = new SQLiteConnection(_dbPath))
{
    var item = dbConn.Table<DbType>().FirstOrDefault(r => r.Id == id);
    if (item != null)
    {
        item.Property = newValue;
        dbConn.Update(item, typeof(DbType));
    }
    else
    {
        item = new DbType() { Property = newValue, };
        dbConn.Insert(item, typeof(DbType));
    }
}

[ghost]

Thanks @ColonelDuddits making PK nullable made InsertOrReplace work correctly.

[UweKeim]

Making the ID nullable did work for me too.

I've changed something like this:

[Table(@"Categories")]
public sealed class ShopCategoryModel
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    // ...
}

to something like this:

[Table(@"Categories")]
public sealed class ShopCategoryModel
{
    [PrimaryKey, AutoIncrement]
    public int? Id { get; set; }

    // ...
}

But I think it is really "ugly" to use nullable IDs in my code (always using GetValueOrDefault() decreases readability, IMO).

[shujjju]

Thanks @ColonelDuddits, @Kwoth making PK nullable worked for me.

[andrewpros]

Well it is not working as is should, the null id get always incremented by 1, whats the point, it should work like insert or update, if it is the some record, don't update the id.

[praeclarum]

This is looking like a bug to me. I'll take a look...

[TheCodeTraveler]

I confirmed this bug is still present in v1.6.258-beta.

Following @UweKeim's work-around and using a nullable int, e.g. int?, for the [PrimaryKey] worked for me:

[PrimaryKey, AutoIncrement]
public int? ID { get; set; }

[manicsquirrel]

It's 2021 and this is still a problem and a barrier to new users. I would still be pulling my hair out had I not found this issue. Requiring the use of a nullable integer for an autoincremented primary key violates the concept of a primary key.

[olumide-oyetoke]

I've resorted to always using nullable int for primary key.

[IndianaGary]

I found it more convenient to implement my own "SaveItem( Type obj)" function; My version tests the Id field for 0 and performs an Add; if not zero it performs an Update...

Regards, Gary

From: Olumide Oyetoke @.> Sent: Sunday, June 20, 2021 2:06 AM To: praeclarum/sqlite-net @.> Cc: Subscribed @.***> Subject: Re: [praeclarum/sqlite-net] InsertOrReplace ignores PrimaryKey AutoIncrement (#327)

I've resorted to always using nullable int for primary key.

You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpraeclarum%2Fsqlite-net%2Fissues%2F327%23issuecomment-864522730&data=04%7C01%7C%7Cd10de1fc00f84a5f5fb408d933ca8ffb%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637597767357815234%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=KVRxd7O1yxkjZkqbYH4pJcF0GHV3z%2F%2BYyO7Qa%2FfoU2A%3D&reserved=0, or unsubscribehttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FADFAH7BHXXJR54CBHI3SNQLTTWVN3ANCNFSM4AXIEK4A&data=04%7C01%7C%7Cd10de1fc00f84a5f5fb408d933ca8ffb%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637597767357815234%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=LbjRq5maRRCTlTSZholJ5BJl8q7vF71spGvaEyrD9r8%3D&reserved=0.

[dhippo78]

No need for InsertOrReplace.

Wait for InsertAsync....

var T = DatabaseAsyncConnection.InsertAsync(object); T.Wait(); return T;

Guaranteed to work.

[justdmitry]

+1 from me that this bug is very annoying.

InsertOrReplace behave differently with Id=0 compared to Insert: Insert replaces value with autogenerated one, while InsertOrUpdate does not. This is not consistent behavior.

[it11111111]

+1 from me. Spent a whole day breaking my head to find out this was actually a very annoying bug.

[memsom]

If I understand your question correctly, the behavior you describe is a feature of SQLite. SQLite allows you to specify values for the primary key column. Because id is specified as zero the first time, your row gets inserted with zero. Each time you use InsertOrReplace with id = 0 after that, you are actually replacing an existing row. To get the result you want, you need to make the id property of your class nullable. Don't set a value for it; InsertOrReplace will manage that for you.

This fixes the issue for me