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

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?

Sign In or Register to comment.