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

Question

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:

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

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?

Answer 1

The Events collection on person1 has even the Event created, which also has Id set

Your person1 object has a list of Events that you created. The collection contained a link to the event1 object that you created. This collection of events was not updated when you inserted or updated the database. You'll find that the Event object has it's Id set when you insert it into the database because the list contains the same object.

SQLite-NET Extensions is not a [auto] lazy-load solution (like Entity Framework). It will get objects only when it is told to. Using the Get method to get an object from the database does not do anything different than SQLite already does. This is because SQLite-NET Extensions does not alter that method. It does however offer new methods like GetWithChildren and FindWithChildren. If you want to get the children of an object you need the methods provided by SQLite-NET Extensions.

The samples provided use these methods as well:

// Get the object and the relationships
var storedValuation = db.GetWithChildren<Valuation>(valuation.Id);
if (euro.Symbol.Equals(storedValuation.Stock.Symbol)) {
    Debug.WriteLine("Object and relationships loaded correctly!");
}

You also wouldn't want the objects to recursively load from Person-Event-Person-Event-etc. as this would never end. If you are starting with a person and want to get all people associated with an event then you would want to get those in another call.

var person = db.GetWithChildren(1);
foreach(var event in person.Events)
{
    // lazy load ourselves
    var allParticipants = db.GetWithChildren(event.Id).Participants;
}

Hope that helps

Answer 2

If anyone wants to see more details on creating many-to-many relationships using SQLite-Net Extensions library, also containing tips and solutions provided here by @Shawn Kendrot, I've collected everything and wrote a post on my blog.

Hope it helps :)