Many to Many relationships with look up tables

Question

public class Person {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [ManyToMany(typeof(PersonColor), CascadeOperations = CascadeOperation.All)]
    public List<Color> FavoriteColors { get; set; } 
}

public class Color {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }

    [ManyToMany(typeof(PersonColor))]
    public List<Person> People { get; set; }
}

public class PersonColor {
    [ForeignKey(typeof(Person))]
    public int PersonId { get; set; }

    [ForeignKey(typeof(Color))]
    public int ColorId { get; set; }
}

...

var person = new Person() {
    FirstName = "Adrian",
    LastName = "Simbulan",
    FavoriteColors = new List<Color>() {
        new Color() {Name = "Red"},
        new Color() {Name = "Green"}
    }
};

await _db.InsertWithChildrenAsync(person);

Ok so i'm trying to establish a many to many relationship between Person and Color. The color table will be pre-populated with static data.

Now the problem is, whenever I execute the "InsertWithChildrenAsync" command, it always inserts new data into the Color lookup table. Is there a way to insert a Person record with selected colors with out affecting the Color table?

Answer 1

Try removing the write cascade operation from FavoriteColors attribute:

[ManyToMany(typeof(PersonColor), CascadeOperations = CascadeOperation.CascadeRead)]
public List<Color> FavoriteColors { get; set; }

This way the library won't perform recursive write operations on that table.

---

Another way without modifying the relationship is performing a two-step operation. First inserting the object and then updating the relationship:

await _db.InsertAsync(person);
await _db.UpdateWithChildrenAsync(person);

---

In both cases the the objects in the FavoriteColors list should already exist in the database and should have a valid primary key assigned. According to this, your sample code will never work because the identifier is 0 in all Color objects.