I lose the foreign key when I update children in object with other foreign key

Question

I'am using SQLite.NET-PCL and SQLiteNetExtensions

OBJECTS:

public class Object1
{
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }

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

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

public class Object2
{
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }

    [ForeignKey(typeof(Object1))]
    public int object1_id { get; set; }
    [ManyToOne]
    public Object1 Object1 { get; set; }

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

public class Object3
{
    [PrimaryKey, AutoIncrement]
    public int id { get; set }
    public string name {get; set;}

    [ForeignKey(typeof(Object2))]
    public int object2_id { get; set; }
    [ManyToOne]
    public Object2 Object2 { get; set; }


    [ForeignKey(typeof(Object1))]
    public int object1_id { get; set; }
    [ManyToOne]
    public Object1 Object1 { get; set; }
}

"Insert Object1 - this works"

connection.Insert(Object1);

"Insert Object2s and UpdateWithChildren Object1 - this works"

        List<Object2> list_object2 = await API_query;
    List<Object2> Object2List = new List<Object2>();
    foreach (Object2 item in list_object2)
    {
         connection.Insert(item);
         Object2List.Add(item);
    }
    Object1.ListObject2 = Object2List;
    connection.UpdateWithChildren(Object1);

"Insert Object3s and UpdateWithChildren Object2 - this UpdateWithChildren works but too update Object2.object1_id to 0"

    List<Object3> list_object3 = await API_query
List<Object3> Object3List = new List<Object3>();
foreach (Object3 item in list_object3) 
{
    connection.Insert(item);
    Object3List.Add(item);
}
Object2.ListObject3 = Object3List;
connection.UpdateWithChildren(Object2);

When I update object2 with children, Object2.object1_id is 0, I lose the Object1_foreign_key in Object2.

Any idea? Whats is my problem? What's the error?

What's the value of `Object2.Object1` and the value of `Object2.Object1.id` before the second update? Because it seems that you're not assigning it anywhere. — redent84, Jan 19 '17 at 09:08
Before second update, the Object2.Object1 return Object1 and Object2.Object1.id return Object1 id. The Object2 and Object3 list were inserted previously. All objects into lists have primary key. After second update, Object2.object1_id is 0 — Francisco Possetto, Jan 19 '17 at 13:24
I'm not able to reproduce your error. Can you post your inserts and updates required to reproduce it? — redent84, Jan 19 '17 at 15:15
You're not assigning `Object2.Object1` anywhere. Are you sure that `Object2.Object1` is not `null` before the last update? Can you print it before and after the update? — redent84, Jan 20 '17 at 13:15
you are okey. After first UpdateWithChildren `Object2.object1_id = object1_id` but `Object2.Object1 is null`. — Francisco Possetto, Jan 20 '17 at 16:41

score 1 · Answer 1 · answered Jan 20 '17 at 16:22

I think that your problem is that these lines:

Object1.ListObject2 = Object2List;
connection.UpdateWithChildren(Object1);

Are setting the foreign keys correctly, but then, you are calling this with an element of Object2List:

connection.UpdateWithChildren(Object2);

At this point, Object2 is null, because the inverse relationship hasn't been set, and therefore the foreign key is set to 0.

To solve it, if you don't plan to update the relationship from Object2, you can set the Object1 -> Object2 relationship to ReadOnly:

[ManyToOne(ReadOnly = true)]
public Object1 Object1 { get; set; }

Alternatively, you can set the inverse relationship manually:

foreach (Object2 item in list_object2)
{
     connection.Insert(item);
     Object2List.Add(item);
     item.Object1 = Object1;
}

Thakns, I am going to try this way then I going to say you if is correct. — Francisco Possetto, Jan 20 '17 at 16:45

score 0 · Accepted Answer · answered Jan 20 '17 at 21:18

0

Before second UpdateWithChildren you must set Object1 to Object2.

Object2.Object1 = Object1;

and then you can do the sencond UpdateWithChildren.

Object2.ListObject3 = Object3List;
connection.UpdateWithChildren(Object2);

If you didn't set the relationship before update, you lose it.

answered Jan 20 '17 at 21:18

Hugo

134
1
10

Creating a second account and answering yourself with the response I just gave you is weird. – redent84 Jan 21 '17 at 17:23
Francisco is my partner, I just put the code as it really works. Best regards! – Hugo Jan 22 '17 at 01:37
No mate, this is my account. Hagnr is another person. – Francisco Possetto Apr 14 '17 at 23:21

I lose the foreign key when I update children in object with other foreign key

2 Answers2