Old records change (undesirably) after adding new Many-to-Many object

Question

When I add new Many-to-Many object, some relationship fields value in old records are set to null or 0 (Reference: Image 1).

Also some previous intermediate tables record are deleted (Reference: Image 2).

Here is the code:

Models:

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


[Table(nameof(Quoter))]
public class Quoter : ModelBase
{
    [Unique, MaxLength(30)]
        public string Name {set; get;}

        [ManyToMany(typeof(QuoterProfession), CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
        public List<Profession> Professions {set; get;}

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


[Table(nameof(Quote))]
public class Quote : ModelBase
{
        public string Statement {set; get;}

        [ForeignKey(typeof(Quoter))]
        public int QuoterId { get; set; }
        [ManyToOne(CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
        public Quoter Quoter {set; get;}

        [ForeignKey(typeof(Profession))]
        public int ProfessionId { get; set; }
        [ManyToOne(CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
        public Profession Profession {set; get;}
}


[Table(nameof(Profession))]
public class Profession : ModelBase
{
        [Unique, MaxLength(20)]
        public string Name {set; get;}

        [ManyToMany(typeof(QuoterProfession), CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
        public List<Quoter> Quoters {set; get;}

        [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
        public List<Quote> Quotes {set; get;}
}


[Table(nameof(QuoterProfession))]
class QuoterProfession : ModelBase
{
        [ForeignKey(typeof(Quoter))]
        public int QuoterId { get; set; }
        [ForeignKey(typeof(Profession))]
        public int ProfessionId { get; set; }
 }

ViewModel:

public class AddQuoterPageVm   
{
    public Quoter Quoter { get; set; }

        public List<Profession> Professions { get; set; }

        public ObservableCollection<Profession> QuoterProfessions { get; set; }
        public Profession QuoterProfession { get; set; }

        public Profession QuoteProfession { get; set; }

        public Quote Quote { get; set; }

        public ObservableCollection<Quote> Quotes { get; set; }

    public void SaveQuoter()   // Add a new Quoter with all his properties & Save
    {
        Quoter = new Quoter {Name = "QuoterN"};
        asyncConnection.InsertWithChildrenAsync(Quoter).Wait();   // Adds a new Quoter to database


        AddProfessions();   // Add profession to database
        Professions = asyncConnection.GetAllWithChildrenAsync<Profession>().Result;

        // Add QuoterN's profession
    QuoterProfessions = new ObservableCollection<Profession>{ Professions[0], Professions[1] };
        Quoter.Professions = new List<Profession>(QuoterProfessions);

        Quotes = new ObservableCollection<Quote> 
        {
            new Quote {Statement = "q1 q1", Profession = Professions[0]},
            new Quote {Statement = "q1 q2", Profession = Professions[1]}
        };
    Quoter.Quotes = new List<Quote>(Quotes);   // Add QuoterN's quotes
            Quotes = null;  

    // Update QuoterN in database after adding additional properties
        asyncConnection.InsertOrReplaceWithChildrenAsync(Quoter, true);   
    }

    private void AddProfessions()   // Adds professions to database
    {
        asyncConnection.GetAllWithChildrenAsync()
                    .ContinueWith(professionListTask =>
                {
                    if (professionListTask.Result.Any()) return;

                    var professions = new List<Profession>
                    {
                        new Profession {Name = "Profession1"},
                        new Profession {Name = "Profession2"},
                        new Profession {Name = "Profession3"},
                        new Profession {Name = "Profession4"},
                        new Profession {Name = "Profession5"},
                        new Profession {Name = "Profession6"},
                        new Profession {Name = "Profession7"}
                    };

                    asyncConnection.InsertAllWithChildrenAsync(professions);
                });
    }
}

How can I add Quoter without affecting (undesirably) previous records?

score 0 · Answer 1 · answered Oct 17 '16 at 09:36

Probably having all relationships recursive at both ends is causing issues because you're only setting one of them and the other end may be inconsistent. As you're not using recursive insert operations I'd recommend you to remove CascadeOperation.CascadeInsert from all relationships, or mark the inverse relationships as ReadOnly.

I'm not sure if waiting asynchronous operations like that may be causing issues, so I'd try to fix it too. Try awaiting asynchronous operations and returning Task instead of void to return the resulting task:

public async Task SaveQuoter() {

    Quoter = new Quoter {Name = "QuoterN"};
    await asyncConnection.InsertWithChildrenAsync(Quoter);
    await AddProfessions();
    Professions = await asyncConnection.GetAllWithChildrenAsync<Profession>();
    ...
}

You can check at the sample code in the IntegrationTests project contained in the sources.

score 0 · Accepted Answer · edited May 23 '17 at 12:33

0

I updated Quoter using asyncConnection.UpdateWithChildren(Quoter). After using this old records are not deleted.

Previously I was using asyncConnection.InsertOrReplaceWithChildrenAsync(Quoter, true).

Reference

edited May 23 '17 at 12:33

Community

1
1

answered Oct 23 '16 at 16:37

Anil

193
3
14

Old records change (undesirably) after adding new Many-to-Many object

2 Answers2