DawidSibinski DawidSibinski -4 years ago 145
C# Question

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

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?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download