SQLiteAsyncConnection.InsertAsync(obj) does not return correct key

Question

We are using a SQLite database in our Xamarin.Forms application to persist business objects in the model layer.

• nuget packages used:
  • SQLiteNetExtensions,
  • SQLiteNetExtensions.Async [2.1.0]
  • sqlite-net-pcl [1.5.231])

A Business Object looks like

[SQLite.Table(nameof(MyBo))]
class MyBo
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }

    ... etc
}

Calling

int id1 = conn.InsertAsync(new MyBo(){ ID = 0, Name ="Foo" });
int id2 = conn.InsertAsync(new MyBo(){ ID = 0, Name ="Bar" });

will result in id1 = 1 and id2 = 1

conn.Table<T>().ToListAsync() returns the objects with the correct IDs (1 and 2).

What am I missing, I was expecting to have the InsertAsync call to return the correct value, but it ALWAYS returns 1!?

Answer 1

The InserAsync method, does not return the object ID, it returns the number of rows added (which in your case is correct = 1)

From the repo source:

/// <summary>
        /// Inserts the given object and retrieves its
        /// auto incremented primary key if it has one.
        /// </summary>
        /// <param name="obj">
        /// The object to insert.
        /// </param>
        /// <returns>
        /// The number of rows added to the table.
        /// </returns>
        public Task<int> InsertAsync (object obj)
        {
            return WriteAsync (conn => conn.Insert (obj));
        }

You can insert rows in the database using Insert. If the table contains an auto-incremented primary key, then the value for that key will be available to you after the insert:

var stock = new Stock()
{
    Symbol = "AAPL"
};

await db.InsertAsync(stock);

Console.WriteLine("Auto stock id: {0}", stock.Id);