SQLite-Net Extensions how to correctly update object recursively

Question

I am using SQLite-Net PCL together with SQLite-Net extensions for the development of an application using Xamarin.

I have a one to many relationship between two classes A and B defined as follows:

   public class A
{

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

    public string Name
    {
        get;
        set;
    }

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

    public A()
    {
    }

    public A(string name, List<B> sons)
    {
        Name = name;
        Sons = sons;
    }

}

public class B
{

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

    public string Name
    {
        get;
        set;
    }

    [ForeignKey(typeof(A))]
    public int FatherId
    {
        get;
        set;
    }

    [ManyToOne]
    public A Father
    {
        get;
        set;
    }

    public B()
    {
    }

    public B(string name)
    {
        Name = name;
    }

}

What I would like to do, is to retrieve an object of type A from the database, remove one of the Sons objects of type B and update the database accordingly. This is what I have tried:

        var sons = new List<B>
        {
            new B("uno"),
            new B("due"),
            new B("tre"),
        };

        one = new A("padre", sons);

        using (var conn = DatabaseStore.GetConnection())
        {
            conn.DeleteAll<A>();
            conn.DeleteAll<B>();

            conn.InsertWithChildren(one, true);

            A retrieved = conn.GetWithChildren<A>(one.Id);
            retrieved.Sons.RemoveAt(1);
        }

        using (var conn = DatabaseStore.GetConnection())
        {
            var retrieved = conn.GetWithChildren<A>(one.Id);
            retrieved.Sons.RemoveAt(1); //"due"

            //conn.UpdateWithChildren(retrieved);
            conn.InsertOrReplaceWithChildren(retrieved, true);
        }

The problem is that both with UpdateWithChildren and with InsertOrReplaceWithChildren the the object is not really removed from the database, but only it's foreign key nulled. Is it possible to make it delete the son object?

Answer 1

You're not really trying to delete any object at all. You're just removing the relationship between the two objects, but nothing stops you from having more objects related to any of them, so deleting any is not correct as you may break other relationships.

It should be more like this:

using (var conn = DatabaseStore.GetConnection())
{
    var retrieved = conn.GetWithChildren<A>(one.Id);
    var due = retrieved.Sons[1];

    // This is not required if the foreign key is in the other end,
    // but it would be the usual way for any other scenario
    // retrieved.Sons.Remove(due);
    // conn.UpdateWithChildren(retrieved);

    // Then delete the object if it's no longer required to exist in the database
    conn.delete(due);
}