SQLite.Net Extensions issue with InsertOrReplaceWithChildren

Question

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

I have two classes A and B defined as follows:

public class A
{

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

    [Unique]
    public string Name
    {
        get;
        set;
    }

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

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

}

public class B
{

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

    [Unique]
    public string Name
    {
        get;
        set;
    }

    public string LastModified
    {
        get;
        set;
    }

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

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

    public B()
    {
    }

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

}

I am trying to use insert or replace in the following way:

        var sons1 = new List<B>
        {
            new B("uno"),
        };

        var a1 = new A("padre", sons1);

        var sons2 = new List<B>
        {
            new B("uno2"),
        };

        var a2 = new A("padre", sons2);

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

        using (var conn = DatabaseStore.GetConnection())
        {
            conn.InsertOrReplaceWithChildren(a1, true);
            conn.InsertOrReplaceWithChildren(a2, true);
        }

The problem is that the second InsertOrReplaceWithChildren is throwing a Constraint exception, that is not thrown if we remove the unique constraint on A.Name. Isn't InsertOrReplaceWithChildren supposed to replace the object if a unique constraint is violated?

Sounds like a bug. Probably here: https://bitbucket.org/twincoders/sqlite-net-extensions/src/fe7c6539bf56f652aae06d0af26b54cff2ffcfd3/SQLiteNetExtensions/Extensions/WriteOperations.cs?at=master#cl-272 — redent84, Mar 18 '15 at 16:11

redent84 · Accepted Answer · 2015-03-18T16:33:34.323

I checked the commits for that line, now I remember: it seems that the AutoIncrement primary key only works for Insert statements and not for InsertOrReplace. As consequence if you call InsertOrReplace with an AutoIncrement primary key set to 0, it will always overwrite the same value in the database with ID 0 instead of correctly adding new values.

This issue is easy to check by executing this test:

var a = new List<A> {
    new A("a1", null),
    new A("a2", null),
    new A("a3", null),
    new A("a4", null),
    new A("a5", null),
    new A("a6", null)
};

foreach (var element in a) {
    conn.InsertOrReplace(element);
}

// Will fail expecting 6 elements but returning only 1
Assert.AreEqual(a.Count, conn.Table<A>().ToList().Count);

To solve it you have to give up either the AutoIncrement primary key or the Unique constraint check for the InsertOrReplace statement to work.

These are drop-in replacement classes that should work as expected giving up the AutoIncrement primary keys:

public class A
{

    [PrimaryKey]
    public Guid Id { get; set; }

    [Unique]
    public string Name { get; set; }

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

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

public class B
{

    [PrimaryKey]
    public Guid Id { get; set; }

    [Unique]
    public string Name { get; set; }

    public string LastModified { get; set; }

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

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

    public B() {}

    public B(string name)
    {
        Id = Guid.NewGuid();
        Name = name;
    }
}

Hope it helps.

Thanks for your help. Actually I'm working on a data model that is more complicated than this one, so every time I am inserting I'm checking if the object is already there. To be honest, I had another more strange issue, that unfortunately I didn't manage to reproduce with a smaller example, but I've found a workaround also for that. — papafe, Mar 18 '15 at 17:34
Besides, I think that a source of confusion is also given by the fact that your example will work with `InsertOrReplaceWithChildren` and it will correctly assign the IDs — papafe, Mar 18 '15 at 17:49

SQLite.Net Extensions issue with InsertOrReplaceWithChildren

1 Answers1