CODEWITHSUNDEEP
sqlite-netsqlite-net-extensions
ad0ran
ad0ran

Reputation: 326

Many to Many relationships with look up tables

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?

Upvotes: 0

Views: 820

Answers (1)

redent84
redent84

Reputation: 19239

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.

Upvotes: 1

Related Questions