Children won't updated Xamarin SQLite Extension - ManyToMany relationship

Issue

I have four Tables With Properties (member which has Players and each on participate in many Sports) the last table The intermidiate table for many to many relationship

[Table("Members")]
public class MemberModel : BaseDatabaseItem
{
    public string MemberShipCode { get; set; }
    public string MemberShipYear { get; set; }

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

[Table("Players")]
public class PlayerModel : BaseDatabaseItem
{
    public string PlayerName { get; set; }

    public double PlayerPayment { get; set; }

    [ForeignKey(typeof(MemberModel))]
    public int MemberModelId { get; set; }

    [ManyToMany(typeof(PlayerSport))]
    public List<Sport> Sports { get; set; }
}

[Table("Sports")]
public class Sport : BaseDatabaseItem
{
    private SportCaegory sportCaegory;

    public string SportName { get; set; }

    [Ignore]
    public SportCaegory SportCaegory 
    {
        get 
        {
            return sportCaegory = new SportCaegory
            {
                SportPrice = SportPrice,
                SportType = SportType
            };
        }
        set
        {
            sportCaegory = value;
            if (sportCaegory == null)
                return;
            SportType = sportCaegory.SportType;
            SportPrice = sportCaegory.SportPrice;
        }
    }

    public string SportType { get; set; }
    public double SportPrice { get; set; }

    [ManyToMany(typeof(PlayerSport))]
    public List<PlayerModel> Players { get; set; }

}

public class PlayerSport : BaseDatabaseItem
{
    [ForeignKey(typeof(PlayerModel))]
    public int PlayerModelId { get; set; }

    [ForeignKey(typeof(Sport))]
    public int SportId { get; set; }
}

BaseDataItem is only a class has ID (Primary - AutoIn)

When I save The Member In DataBase I save Sports Then Players And finally The member

            foreach (var sport in Players.SelectMany(p => p.Sports))
            {
                await _sportsRepository.SaveItemAsync(new Sport
                {
                    Id = sport.Id == 0 ? 0 : sport.Id,
                    SportName = sport.SportName,
                    SportCaegory = sport.SportCaegory
                });
            };

            foreach (var player in Players)
            {
                await _playersRepository.SaveItemAsync(new PlayerModel
                {
                    Id = player.Id == 0 ? 0 : player.Id,
                    PlayerName = player.PlayerName,
                    PlayerPayment = player.PlayerPayment,
                    Sports = player.Sports
                });
                await _playersRepository.SaveWithChildrenAsync(player);

            };

            var member = new MemberModel
            {
                Id = string.IsNullOrEmpty(Id) ? 0 : int.Parse(Id),
                MemberShipCode = MemberShipCode,
                MemberShipYear = MemberShipYear,
                MembershipNPlayers = Players.ToList()
            };

            await _membersRepository.SaveWithChildrenAsync(member);

SaveWithChildrenAsync (only check for Ids) =>

    public async Task SaveWithChildrenAsync(T entity)
    {
        if (entity.Id != 0)
            await UpdateWithChildren(entity);
        else
            await InsertWithChildren(entity);
    }

When I retrive The data, Sports List count always equal zero for each player

        var member = await _membersRepository.GetWithChildren(MemberId) ;


        var MemberPlayers = member.MembershipNPlayers;

        foreach (var player in MemberPlayers)
        {
            var p = await _playersRepository.GetWithChildren(player.Id);
            Players.Add(p);
        }
        MemberShipCode = member.MemberShipCode;
        MemberShipYear = member.MemberShipYear;

How to solve It?

Solution

Due to some parts of the code you do not provided, i make a simple code to update the db and receive the data. You could check the code below.

Model classes:

 [Table("Members")]
public class MemberModel : BaseDatabaseItem
{
    public string MemberShipCode { get; set; }
    public string MemberShipYear { get; set; }

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

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

[Table("Players")]
public class PlayerModel : BaseDatabaseItem
{
    public string PlayerName { get; set; }

    public double PlayerPayment { get; set; }

    [ForeignKey(typeof(MemberModel))]
    public int MemberModelId { get; set; }

    [ManyToMany(typeof(PlayerSport))]
    public List<Sport> Sports { get; set; }
}

[Table("Sports")]
public class Sport : BaseDatabaseItem
{
    private SportCaegory sportCaegory;

    public string SportName { get; set; }

    [Ignore]
    public SportCaegory SportCaegory
    {
        get
        {
            return sportCaegory = new SportCaegory
            {
                SportPrice = SportPrice,
                SportType = SportType
            };
        }
        set
        {
            sportCaegory = value;
            if (sportCaegory == null)
                return;
            SportType = sportCaegory.SportType;
            SportPrice = sportCaegory.SportPrice;
        }
    }

    public string SportType { get; set; }
    public double SportPrice { get; set; }

    [ManyToMany(typeof(PlayerSport))]
    public List<PlayerModel> Players { get; set; }

}
public class SportCaegory
{
    public string SportType { get; set; }
    public double SportPrice { get; set; }


}
public class PlayerSport : BaseDatabaseItem
{
    [ForeignKey(typeof(PlayerModel))]
    public int PlayerModelId { get; set; }

    [ForeignKey(typeof(Sport))]
    public int SportId { get; set; }
}

Inserting and reading data:

   var dbName = "CustomersDb.db3";
        var path = Path.Combine(System.Environment.
          GetFolderPath(System.Environment.
          SpecialFolder.Personal), dbName);


        var db = new SQLiteConnection(path);
        db.CreateTable<MemberModel>();
        db.CreateTable<PlayerModel>();
        db.CreateTable<PlayerSport>();
        db.CreateTable<Sport>();

        var Member1 = new MemberModel()
        {

            MemberShipCode = "A",
            MemberShipYear = "111"
        };
        var Player1 = new PlayerModel()
        {
            MemberModelId = 12,
            PlayerName = "B",
            PlayerPayment = 1.2,

        };
        var SportPlayer1 = new PlayerSport()
        {
            PlayerModelId = 1,
            SportId = 2
        };
        var Sport1 = new Sport()
        {

            SportCaegory = new SportCaegory()
            {
                SportPrice = 12,
                SportType = "tY"
            },
            SportName = "Name1",
            SportPrice = 13,
            SportType = "ww"
        };


        db.Insert(Member1);
        db.Insert(Player1);
        db.Insert(Sport1);
        db.Insert(SportPlayer1);

        Member1.MembershipNPlayers = new List<PlayerModel> { Player1 };
        Player1.Sports = new List<Sport> { Sport1 };
        Sport1.Players = new List<PlayerModel>() { Player1 };

        db.UpdateWithChildren(Member1);
        db.UpdateWithChildren(Sport1);
        db.UpdateWithChildren(Player1);


        var M = db.GetWithChildren<MemberModel>(Member1.Id);
        var P = db.GetWithChildren<PlayerModel>(Player1.Id);

Screenshot:

Answered By - Wendy Zang - MSFT