Xamarin.Android: SQLite-Net Extensions many-to-many - no such table, NULL collection

Viewed 1227

I've installed SQLite-Net Extensions package into my Xamarin.Android project in Visual Studio 2015 and I'm trying to implement many-to-many relationship between Person and Event entities. I've followed the official documentation of the library, but I can't make it working.

Classes I created:

Person.cs:

[Table("People")]
public class Person
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }

    public string LastName { get; set; }

    public string PhoneNumber { get; set; }

    public string Email { get; set; }

    [ManyToMany(typeof(PersonEvent), CascadeOperations = CascadeOperation.All)]
    public List<Event> Events { get; set; }
}

Event.cs:

[Table("Events")]
public class Event
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }
    public DateTime Date { get; set; }
    public string Place { get; set; }

    [ManyToMany(typeof(PersonEvent), CascadeOperations = CascadeOperation.All)]
    public List<Person> Participants { get; set; }
}

PersonEvent.cs:

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

    [ForeignKey(typeof(Event))]
    public int EventId { get; set; }
}

To test it, I'm removing the database file to be sure a clean one is created on the device and then trying to create a Person, an Event, insert both into the DB first and then assign the Event to the Person and save again using UpdateWithChildren method:

var dbFile = new File(Constants.DbFilePath);
dbFile.Delete();

var db = new SQLiteConnection(new SQLitePlatformAndroid(), Constants.DbFilePath);
db.CreateTable<Person>();
db.CreateTable<Event>();
db.CreateTable<PersonEvent>();

var event1 = new Event
{
    Name = "Volleyball",
    Date = new DateTime(2017, 06, 18),
    Place = "Sports hall"
};

var person1 = new Person
{
    Name = "A",
    LastName = "B",
    PhoneNumber = "123456789"
};


db.Insert(person1);
db.Insert(event1);
person1.Events = new List<Event> { event1 };
db.UpdateWithChildren(person1);

First of all, I needed to add CreateTable<PersonEvent>() line because if it wasn't present I was getting the exception: SQLite.Net.SQLiteException: no such table: PersonEvent, however I thought I don't have to create this intermediate table manually. Shouldn't SQLite-Net Extensions create it automatically somehow?

With this line added, the first two inserts of event1 and person1 are working correctly (after inserting both entities have their Ids assigned). The Events collection on person1 has even the Event created, which also has Id set: enter image description here

However, the "opposite entity", in this case event1 has its Participants collection (List of Person) NULL:

enter image description here

I tried everything and nothing is working. As I understood reading SQLite-Net Extensions docs, it should be resolved automatically. I even added this CascadeOperations = CascadeOperation.All on the collections, but it doesn't help as you can see. I'm I still doing something wrong?

2 Answers
Related