0
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?

ad0ran
  • 326
  • 4
  • 15

1 Answers1

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.

redent84
  • 18,901
  • 4
  • 62
  • 85
  • Changing CascadeOperations to just read worked. Would performing the two-step operation be more standard practice when inserting new records into a table that has a relationship with a lookup table? – ad0ran Apr 28 '15 at 14:39
  • Two-step operation is what the library does internally. The second solution just doesn't require you to change the model definition if you require recursive write operations elsewhere. If not needed, stick to the first solution to avoid writing `Color` objects by error. – redent84 Apr 28 '15 at 14:55