xamarin - SQLite-Net Extensions - GetAllWithChildrenAsync not pulling everything

Question

I am trying to use SQLite-Net Extensions to create a Relational Database. I'm running into an issue when trying to pull the Term object from the database. It successfully pulls over its associated courses, but not the courses associated assessments and notes. I'm not sure if the problem lies in how I insert the objects into the database, how I pull the objects from the database, or how I have the objects attributes listed.

I feel like the SQLite-Net Extensions documentation is extremely limited, so I'm not even sure what's going on. I've tried it many different ways, including adding CascadeOperations, but non of those seemed to help.

Here is the (simplified) code for my objects:

[Table("Terms")]
public class Term
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    [OneToMany]
    public List<Course> Courses { get; set; }

    public Term() { }
    public Term(string name, List<Course> courses)
    {
        Name = name;
        Courses = courses;
    }

Courses

[Table("Courses")]
public class Course
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    [ForeignKey(typeof(Term))]
    public int TermID { get; set; }
    public string Name { get; set; }
    [OneToMany]
    public List<Assessment> Assessments { get; set; }
    [OneToMany]
    public List<Note> Notes { get; set; }

    public Course() { }

    public Course(string name, List<Assessment> assessments, List<Note> notes)
    {
        Name = name;
        Assessments = assessments;
        Notes = notes;
    }
}

Assessments

[Table("Assessments")]
public class Assessment
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    [ForeignKey(typeof(Course))]
    public int CourseID { get; set; }
    public string Name { get; set; }

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

Notes

[Table("Notes")]
public class Note
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    [ForeignKey(typeof(Course))]
    public int CourseID { get; set; }
    public string Name { get; set; }
    public string Text { get; set; }

    public Note() { }
    public Note(string name, string note)
    {
        Name = name;
        Text = note;
    }
}

And here is the code for inserting and getting objects: Inserting

public bool SaveTermAsync(Term term)
    {
        if (term.ID != 0)
        {
            _database.UpdateWithChildrenAsync(term);
            return true;
        }
        else
        {
            foreach (var course in term.Courses)
            {
                foreach (var assessment in course.Assessments)
                {
                    _database.InsertAsync(assessment);
                }
                foreach (var note in course.Notes)
                {
                    _database.InsertAsync(note);
                }
                _database.InsertAsync(course);
            }
            _database.InsertAsync(term);
            _database.UpdateWithChildrenAsync(term);
            return false;
        }
    }

Getting

public Task<List<Term>> GetTermsAsync()
    {
        return _database.GetAllWithChildrenAsync<Term>();
    }

I know it's a bit of a code dump, but I have no idea where or what could be going wrong. If anyone could give any information about what is potentially going wrong, that would be awesome. Perhaps I'm simply expecting something to happen that isn't actually how it works. I don't know.

Also, if anyone has any links to some better documentation than https://bitbucket.org/twincoders/sqlite-net-extensions/src/master/ that would be awesome

EDIT

I tried using Cascading Options as well, CascadeRead, CascadeInsert, and CascadeAll. Using CascadeInsert or CascadeAll with _database.InsertWithChildrenAsync(term, true) resulted in a crash. The crash does not provide any error messages, and even wrapping the InsertWithChildren with a try catch block didn't work. Removing the recursive bool caused the program not to crash, and actually get the closest to what I'm looking for. Assessments and Notes are no longer null, but are still empty. Here's my updated code:

Saving and Getting:

public async Task<List<Term>> GetTermsAsync()
    {
        return await _database.GetAllWithChildrenAsync<Term>(recursive: true);
    }

public async void SaveTermAsync(Term term)
    {
        if (term.ID != 0)
        {
            await _database.UpdateWithChildrenAsync(term);
        }
        else
        {
            //Trying this with recursion results in crash
            await _database.InsertWithChildrenAsync(term);
        }
    }

One-To-Many Relationships:

//In Term
[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<Course> Courses { get; set; }

//In Courses
[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<Assessment> Assessments { get; set; }
[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<Note> Notes { get; set; }

Also, I forgot to include last time how I'm populating the tables in the first place.

public bool CreateTables()
    {
        _database.CreateTableAsync<Term>().Wait();
        _database.CreateTableAsync<Course>().Wait();
        _database.CreateTableAsync<Assessment>().Wait();
        _database.CreateTableAsync<Note>().Wait();
        return true;
    }

    public Task<int> ClearTablesTest()
    {
        _database.DropTableAsync<Term>();
        _database.DropTableAsync<Course>();
        _database.DropTableAsync<Assessment>();
        return _database.DropTableAsync<Note>();
    }

async public Task<int> PopulateTestData()
    {
        await ClearTablesTest();
        CreateTables();

        Term term = new Term("Test Term", true, DateTime.Now, DateTime.Now.AddDays(10),
            new List<Course>
            {
                new Course("Test Course", CourseStatus.Completed, "Guys Name", "(999)-999-9999", "email@gmail.com", 6, DateTime.Now, DateTime.Now.AddDays(10),
                new List<Assessment>
                {
                    new Assessment("Test Assessment", AssessmentType.Objective, false, DateTime.Now, DateTime.Now.AddDays(10))
                },
                new List<Note>
                {
                    new Note("Test Note", "This is a test note.")
                })
            });
        App.Database.SaveTermAsync(term);
        return 0;
    }

question from:https://stackoverflow.com/questions/65839623/nested-one-to-many-tables-sqlite-net-extensions

Answer 1

I finally figured out what was causing the crash as well as causing general confusion within SQLite-Net Extensions.

In my Assessment class, the property

public string BackgroundColor
    {
        get { return IsComplete ? "#558f45" : "Gray"; }
        set { BackgroundColor = value; }
    }

was causing the crash when recursion was used. I've been scouring the web for over two weeks looking for solutions to this issue, but haven't found anything similar to this. I submitted a bug report on the SQLite-Net Extensions bitbucket.

If anyone knows why this specific line would cause issues, I'd love to hear your input. Until then I'm going to mark this question as answered and continue work on my app.

Thanks @redent84 for your help thus far on this issue.