How To Read and Write Records In SQLite Using SQLite Net Extensions?

Question

Here is the documentation I've looked at and may be helpful: Sample SQLite OneToMany Unit Test and General Read and Write Documentation in Readme

My use-case is that I've already inserted an Item and I am now editing an Item. So I will need to basically update the Item record and insert n ItemPhoto records. Basically, I'mtalking about the case of SaveItem(..) where Item.Id != 0.

It seems that when I step through the code to write to the database I am seeing all of the keys being assigned to the objects in memory appropriately. However, later when I go to read an Item by calling GetWithChildren(..) in every case except one the ItemPhotos property has a Count of 0. The only time that ItemPhotos actually gets populated is the case when the ItemPhotoId is 0. My best guess is that somehow the ItemPhotoId is not being set before GetWithChildren(..) is run and then it only works when the default in-memory value of 0 actually matches the database's ItemPhotoId for the given Item.

Here is my code showing the models and the read and write code:

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

    public string Text { get; set; }

    public string Description { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<ItemPhoto> ItemPhotos { get; set; }
}

public class ItemPhoto
{
    [PrimaryKey, AutoIncrement]
    public int ItemPhotoId { get; set; }

    [ForeignKey(typeof(Item))]
    public int ItemId { get; set; }

    public string FileLocation { get; set; }

    [ManyToOne]      // Many to one relationship with Item
    public Item Item { get; set; }
}

class SqlLiteDataStore
{
    static SQLiteConnection Database;

    ...

    public Item GetItem(int id)
    {
        return Database.GetWithChildren<Item>(id, true);
    }

    public Item SaveItem(Item item)
    {
        // Simpler Attempt #1
        // Database.InsertOrReplaceWithChildren(item);
        // return item;

        // Manual Attempt #2
        if (item.Id != 0)
        {
            foreach (var photo in item.ItemPhotos)
            {
                if (photo.ItemPhotoId == 0)
                    Database.Insert(photo);
            }
            Database.UpdateWithChildren(item);

            return item;
        }
        else
        {
            Database.InsertWithChildren(item, true);
            return item;
        }
    }

    ...

}

Answer 1

Well I fixed it by dropping the existing tables and recreating them and beforehand just renaming the ItemPhoto's ItemPhotoId property to Id. Maybe the library assumes the primary key name of Id or maybe I changed something else along the way in terms of the models that just needed the tables to be recreated instead of migrated? Also, the simpler attempt at saving with just the Database.InsertOrReplaceWithChildren(item); seems to work just fine so I'm going with that for what it's worth.