UpdateWithChildren sqlite-net-extensions

Question

I have confused with UpdateWithChildren and InsertOrReplaceWithChildren.
I can't get it work with UpdateWithChildren, but It can work with InsertOrReplaceWithChildren.
so I have deleted the db, then apply InsertOrReplaceWithChildren,
but the problem is that the Id is AutoIncrement, the Id keeps adding on.
Would you give me some advice?
Thanks.

public class WeekHistory {
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public DayOfWeek DayOfWeek { get; set; }
public int NoOfDays { get; set; }

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

public class DayHistory {
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int Hour { get; set; }
public Action Action { get; set; }
public TimeSpan Duration { get; set; }

[ForeignKey(typeof(WeekHistory))]     
public int WeekId { get; set; }
[ManyToOne]      // Many to one relationship with WeekHistory
public WeekHistory WeekHistory { get; set; }}


List<DayHistory> newMonday = new List<DayHistory>()
{
    new DayHistory() {Hour = 1, Action = Action.Known, Duration = new TimeSpan(0,20,0)},
    new DayHistory() {Hour = 1, Action = Action.Unknown, Duration = new TimeSpan(0,40,0)},
    new DayHistory() {Hour = 2, Action = Action.Known, Duration = new TimeSpan(0,40,0)},
    new DayHistory() {Hour = 2, Action = Action.Unknown, Duration = new TimeSpan(0,20,0)},
    new DayHistory() {Hour = 3, Action = Action.Known, Duration = new TimeSpan(0,50,0)},
    new DayHistory() {Hour = 3, Action = Action.Unknown, Duration = new TimeSpan(0,10,0)}
};

var mondayHistory = dbConn.GetWithChildren<WeekHistory>(1, true);       

//delete the db, instead of the UpdateWithChildren
dbConn.DeleteAll(mondayHistory.DayHistories);       

mondayHistory.DayHistories = newMonday;
mondayHistory.NoOfDays += 1;

//it won't update the child
//dbConn.UpdateWithChildren(mondayHistory);

//apply new db with children
dbConn.InsertOrReplaceWithChildren(mondayHistory);

score 1 · Accepted Answer · answered Dec 19 '15 at 20:42

1

I don't see the issue with your second sample. You are deleting the previous DayHistorys and inserting new ones, so the Ids will be different, but nothing to worry about there.

About your first question, UpdateWithChildren updates registers that already exists in the database. If you call UpdateWithChildren on the parent object, the children (DayHistory in this case) won't be inserted into database. Updating the children won't work because you are not assigning any primary key, so the database can't update them either.

Easier solution here is just insert the elements in the database first and then call UpdateWithChildren to update the foreign keys:

// Delete previous DayHistories to avoid orphaned objects
dbConn.DeleteAll(mondayHistory.DayHistories);

// Assign new elements
mondayHistory.DayHistories = newMonday;
// Insert new elements into database
dbConn.InsertAll(mondayHistory.DayHistories);
// Update parent object to correctly assign foreign keys for the relationships
dbConn.UpdateWithChildren(mondayHistory);

answered Dec 19 '15 at 20:42

redent84

18,901
4
62
85

Thanks, but I don't want to delete old children and create new children table (newMonday), whenever it have replaced with new children, AutoIncrement Id will be increased, How could I keep Id and replace old children table with new children table (newMonday)? – tom tom Dec 20 '15 at 21:47
because It won't happen, but it could be out of maximum AutoIncrement to update children table. – tom tom Dec 20 '15 at 21:57
Are you really going to insert more than 2147483647 elements in the database? If you want to update elements, you have to assign the identifiers, you can't update elements with no identifier. Once you have assigned the identifiers, you can call `Update` with each modified element, or use the `recursive` flag on `UpdateWithChildren` if you have set the `CascadeOperations` property on the relationship attribute. – redent84 Dec 21 '15 at 12:29
Thanks again, redent84, but I found the reply at the forums.xamarin.com that there's no recursive version of UpdateWithChildren method, and there isn't InsertOrUpdateWithChildren (it guess it is InsertOrReplaceWithChildren?). Would you give some code I'm not good at, and it is confusing to me. – tom tom Dec 21 '15 at 14:05
https://forums.xamarin.com/discussion/20304/announcement-cascade-operations-in-sqlite-net-extensions – tom tom Dec 21 '15 at 14:13
I think I have a big misunderstood of the UpdateWithChildren. var mondayHistory = dbConn.GetWithChildren(1, true); for (int i = 0; i < newMonday.Count; i++) { mondayHistory.DayHistories[i].Duration = newMonday[i].Duration; } dbConn.UpdateAll(mondayHistory.DayHistories); it doesn't create identifiers, update the content as I wanted from the dbConn.UpdateWithChildren(mondayHistory), am i right? – tom tom Dec 21 '15 at 20:29
Yes, if you want to update the already existing elements, that's the right way. Glad it helped. – redent84 Dec 22 '15 at 09:19

UpdateWithChildren sqlite-net-extensions

1 Answers1