Update an object remove relationsships / RefreshForeignKeys

[Matthias Habegger]

Hi there,

I have a model class "Book" with a relationship to the model class "Author".

Now I do the following:

Book book= db.Get<Book>(b => b.ISBN == 555);
book.Title = "My new book title";
db.UpdateWithChildren(book);

After UpdateWithChildren the book has the new title assigned but it removed the author from the book!

The same happens when I do not change any property at all:

Book book = db.Get<Book>(b => b.ISBN == 333);
db.UpdateWithChildren(book);

As a workaround I added "db.GetChildren(book);" before I execute "UpdateWithChildren". The workaround works fine until I replace the author with another already persistent author:

Book book = db.Get<Book>(b => b.ISBN == 333);
db.Author = newAuthor;
db.GetChildren(book);
db.UpdateWithChildren(book);

GetChildren now replaces the set "newAuthor" in the object with the old author.

Another workaround would be to get children before I set any other property. But that doesn't make sense because to work so means to get all children of any book object directly after I load them from the database as a precaution that no relationships got lost. So I loose a lot of performance.

In my opinion this isn't what it should be like, I would expect that "UpdateWithChildren" automatically would handle this.

I debugged it found that the "RefreshForeignKeys" method does the problem. It assume that when a relationship property (Author) is NULL, that I removed that relationship on purpose. But it could also be that I just didn't load that child before i will update it.

(Another way to solve this would be that GetChildren do not override already newly set properties.)

Here are my model classes:

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

        public string Title { get; set; }

        [ForeignKey(typeof(Author))]
        public int AuthorId { get; set; }

        [ManyToOne(CascadeOperations =CascadeOperation.CascadeRead)]
        public Author Author { get; set; }
    }

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

        public string Firstname { get; set; }
        public string Lastname { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.CascadeRead)]
        public List<Book> Books { get; set; }
    }

Thanks in advance.

[Guillermo Gutiérrez]

UpdateWithChildren updates all the foreign keys of the current object and then updates it into database. If your object has no relationships at all (null or empty), all relationships will be deleted. This is expected behavior. If you don't want to update foreign keys, you can use Update method instead of UpdateWithChildren.

To avoid issues with empty relationships, you can use also WithChildren objects when fetching elements from database:

Book book = db.GetWithChildren<Book>(b => b.ISBN == 333);
db.Author = newAuthor;
db.UpdateWithChildren(book);

Or using plain SQLite.Net operations:

Book book = db.Get<Book>(b => b.ISBN == 333);
db.AuthorId = newAuthor.Id;
db.Update(book);

Kind regards.

[Matthias Habegger]

I tried your first advice but I does not work. There is no operator overloading which accept a Linq Expression.

--

And what about the following scenario:

I have an additional class "Category" which is related to Book.

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

        public string Name { get; set; }

        [OneToMany(CascadeOperations =CascadeOperation.CascadeRead)]
        public List<Book> Books { get; set; }
    }

    class Book
    {
        ...

        [ForeignKey(typeof(Category))]
        public int CategoryId { get; set; }

        [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
        public Category Category { get; set; }
    }

I get now an object which already have an author and a category, replace the author and wonna save it.

Book book = db.Get<Book>(b => b.ISBN == 333);
db.Author = newAuthor;
db.UpdateWithChildren(book);

UpdateWithChildren will add the new author but will remove the category-relationship. So I always need to

1. load all children
2. replace the child
3. save the object with "UpdateWithChildren"

when I change a relationship?

Kind regards.

[Guillermo Gutiérrez]

Yes, that's correct. UpdateWithChildren will persist the current object state into database. If the book doesn't have a category, it will set the CategoryId foreign key to 0 before saving to database.

Book book = db.Get<Book>(b => b.ISBN == 333);
db.getChildren(book); // Or use any *WithChildren method to fetch the object
book.Author = newAuthor;
db.UpdateWithChildren(book);

Fetching by primary key is very fast, but if you don't want to load the relationships to memory, you can fall back to plain SQLite-Net operations and manage foreign keys by yourself where micro-performance is required. For example:

Book book = db.Get<Book>(b => b.ISBN == 333);
book.AuthorId = newAuthor.Id;
db.Update(book);

Kind regards.

[Matthias Habegger]

Well, that's not convenient but as it is implemented like that I have to live with it.

Thank you very much for your help.

Kind regards.

[Matthias Habegger]

It isn't a bug but a missunderstanding how it works. A programmer must do more himself than I thought.