InsertWithChildrenAsync() and duplicate primary keys

Question

I get a list of objects from a webservice. The data should be stored in a SQLite database. Thereby I can store the first item in the database with InsertWithChildrenAsync(), on the second I get an exception and the app crashes.

Class definition:

public class Color
{
    [PrimaryKey]
    public string Code { get; set; }
    public string Name { get; set; }
}
public class Person
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    [ForeignKey(typeof(Color))]
    public string FavoriteColorId { get; set; }
    [OneToOne(CascadeOperations = CascadeOperation.All)]
    public Color FavoriteColor { get; set; }
}

Initialization:

var dbPath = DependencyService.Get<IStorageService>().GetFilePathForDB();
DependencyService.Get<IStorageService>().DeleteFile(dbPath);
var db = new SQLiteAsyncConnection(dbPath);
await db.CreateTableAsync<Color>();
await db.CreateTableAsync<Person>();

Code demonstrating the issue:

var pers1 = new Person()
{
    Id = 1,
    Name = "John",
    FavoriteColor = new Color() { Code = "FF0000", Name = "Red" }
};

var pers2 = new Person()
{
    Id = 2,
    Name = "Doe",
    FavoriteColor = new Color() { Code = "FF0000", Name = "Red" }
};

await db.InsertWithChildrenAsync(pers1, true);
await db.InsertWithChildrenAsync(pers2, true);

Error message

SQLite.SQLiteException: Constraint

This doesn't happen if I use

var pers2 = new Person()
{
    Id = 2,
    Name = "Doe",
    FavoriteColor = new Color() { Code = "00FF00", Name = "Green" }
};

The problem is that the same primary key is inserted twice. One solution would be to use auto increment, but then the same data is stored multiple times. How can I use the same data for different objects? The data from the webservice is parsed and later stored in the database. I don't hold the objects in memory all the time. Otherwise I could use something like

Color red = new Color { Code = "00FF00", Name = "Green" };
pers1.FavoriteColor = red;
pers2.FavoriteColor = red;

Do I need a many-to-many table? What about deletion? Currently, I'm planning to use DeleteAsync(), but the entry can't be deleted entirely, because another instance is using it. Does the method take this into account?

What are my options?

Answer 1

If you have a complex data structure like me, you can do the following: Primary keys, which don't use the autoincrement flag [PrimaryKey, AutoIncrement], have to be dealed with seperately.

For insertions, don't use CascadeOperations = CascadeOperation.CascadeInsert. Instead you have to insert them manually. E.g.

await StoreColorAsync(db, red);
await db.InsertWithChildrenAsync(pers1, false);

public async Task StoreColorAsync(SQLiteAsyncConnection db, Color color)
{
    if (color == null)
        return;

    var foundItem = await GetColorAsync(db, color.Code);
    if (foundItem != null)
    {
        await db.UpdateAsync(color);
    }
    else
    {
        await db.InsertAsync(color);
    }
}

public async Task<Color> GetColorAsync(SQLiteAsyncConnection db, string colorCode)
{
    var queryResult = await db.Table<Color>().Where(c => c.Code == colorCode).CountAsync();
    if (queryResult > 0)
    {
        return await db.GetAsync<Color>(colorCode);
    }
    else
    {
        return null;
    }
}

For deletion, only use CascadeOperations = CascadeOperation.CascadeDelete for entries with the autoincrement flag. Here I leave the other entries in the database, which will be perhaps reused later. On some special events (e.g. logout) I clear all tables.