CODEWITHSUNDEEP
sqlite-net-extensions
Francisco Possetto
Francisco Possetto

Reputation: 443

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

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?

Upvotes: 0

Views: 412

Answers (2)

Hugo
Hugo

Reputation: 134

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.

Upvotes: 0

redent84
redent84

Reputation: 19239

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;
}

Upvotes: 1

Related Questions