Replacement for sqlite-extensions: InsertWithChildren (too many Variables)

Question

i have a project here, were a big amount of data is read from different sources. In a special logic, a data/object-modell is build with these data. So as a result i retrieve a complete SQLite capable object model.

The data were previously written to the SQLite database using a simple:

  _connection.InsertWithChildren(model, true);

But, since the source of the data became bigger, this is not possible anymore, cause the Insert method will throw an "too many variables" exception. ;(

Now, i am looking for an replacement for this method. The difficulty here is that within my model, i nearly always have Foreign-Keys in both directions. Parent has Childs, Childs knows Parent.

Performance is not an issue. I don't care if the function needs 10Seconds or 5Minutes. But does anyone have an idea how to handle the Insert, while all Foreign Keys are filled correctly?

If i use a simple

foreach(var entity in _entityList)
  _connection.Insert(entity);

the foreign Keys (IDs) are all Guid.Empty;

best regards and cheers,

Chris

Answer 1

Until issue #64 is fixed you can use ReadOnly properties on lists.

For example:

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

    [OneToMany(ReadOnly = true)]
    public List<Bar> Bars { get; set; }
}

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

    [ForeignKey(typeof(Foo))]
    public Guid ParentId { get; set; }

    [ManyToOne]
    public Foo ParentFoo { get; set; }
}

Will no longer hit the variable limit issue regardless of the operation executed.

---

You can now insert the elements safely:

// Insert parent 'foo' element
// This won't insert the children or update their foreign keys
conn.InsertWithChildren(foo); 

// Insert all children
// This will also update ParentId foreign key if ParentFoo property is set
conn.InsertAllWithChildren(bars)

Or use plain SQLite.Net methods assigning the foreign keys yourself:

conn.Insert(foo);
foreach (var bar in bars) {
    bar.ParentId = foo.Id;
    conn.Insert(bar);
}