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?

Answer 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

Answer 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; }
    }

Answer 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.

Answer 4

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

Answer 5

My solution for this is kind of similar to Joacar's, but instead of doing an update, I select the item, if it's null, I create a new item, otherwise update that items values, and then call InserOrReplace.

var existingKey = await this.GetItem(key);
Item item;

if (existingKey.Value != null)
{
    profile = new Item
    { 
        Id = existingKey.Id,
        Key = existingKey.Key,
        Value = newValue,
    };

    this.InsertOrReplaceAsync(item);
}
else
{
    item = new Item
    {
        Key = key,
        Value = value, 
    };

    this.InsertAsync(item);
}

It might not be optimal, but it worked for me.