How to use InsertOrReplace in sqlite.net PCL?

Question

I am using the PCL version of sqlite.net from here (https://github.com/oysteinkrog/SQLite.Net-PCL).

Here is my simple class.

    public class LogEntry
    {
      [PrimaryKey, AutoIncrement]
      public int Key { get; set;}
      public DateTime Date { get; set; }
    }

When a new instance of LogEntry is created, the Key is automatically set to 0. I set the Date to something and then call InsertOrReplace. The record does get saved in my database. The Key field gets the autoincrement value which happens to be 0 since it is the first record.

I then create a new instance of LogEntry (Key is automatically initialized to 0) and set the date to something else. I then call InsertOrReplace. Since there is an existing record with a Key of 0 that record gets updated.

What is the proper way to deal with this? I considered initializing the Key to -1, but that didn't seem to work either.

Does anyone have an example of this working?

Solution 1

I experienced the same issue as you are describing. Try

var rowsAffected = Connection.Update(object);
if(rowsAffected == 0) {
    // The item does not exists in the database so lets insert it
    rowsAffected = Connection.Insert(object);
}
var success = rowsAffected > 0;
return success;

I just tried above and it works as expected

Solution 2

If you change the Key to a nullable type (int?) it should work. then SQLite sees null coming in and generates new id when needed.

public class LogEntry
    {
      [PrimaryKey, AutoIncrement]
      public int? Key { get; set;}
      public DateTime Date { get; set; }
    }

Solution 3

The way this works is the source of much confusion but whereas Insert treats zeroed primary keys as a special case when AutoIncrement is set, InsertOrReplace does not.

So with:

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

if you InsertOrReplace a series of zero id records into a new table, the first will be stored at id: 0 and each subsequent one will save over it. Whereas if you just Insert each one then because of the AutoIncrement the first will save at id: 1 and the next at id: 2 etc. as you might expect.

If you change the key type to a nullable int, then records with null ids will be treated as inserts by InsertOrReplace, and you don't actually need the AutoIncrement attribute at all in this case, they will still save in sequence starting at 1.

[PrimaryKey]
public int? id { get; set; }

If you can't use that for some reason you can do your own check for zero ids and for those call Insert instead, e.g.

Func<Foo, int> myInsertOrReplace = x => 
{ 
   return x.id == 0 ? _db.Insert(x) : _db.InsertOrReplace(x); 
};

but in this case you must use the AutoIncrement attribute, otherwise first zero insert will be saved at 0 and the second will throw a constraint exception when it attempts insert another such.

Solution 4

To get the result you want, you need to make the id property of your class nullable. see here link

View more solutions

Comments

[Emad]

When you call update in the first line if the Id exists in the table the value gets updated. I mean how can it understand the object isn't already in db?

[Emad]

A better answer can be this one.

[joacar]

By checking if the primary key exists or not. In case of using AutoIncrement attribute with int/long the defaulted value is zero and not valid. (This is a guess but I assume it starts with the identity counting from one). So if the 'object' has Id = 0 then it knows it hasn't been inserted and hence Update returns zero.

[Emad]

But if you look at the original question that logic completely defiles your explanation.

[joacar]

Just re-read the question and now I'm slightly confused. Might be that I initially misread the question and in the example i wrote up didn't took into account the AutoIncrement key. Was some time now I worked with SQLite so cannot recall if I set any option to start seed at one. Need to try this out

[Emad]

The funny thing is your reply has been marked as answer! I used this one link and it's working just fine.