Xamarin.Android: many-to-many SQLite-Net extensions - no such table, collection is NULL
I have installed the SQLite-Net Extensions package in my Xamarin.Android project in Visual Studio 2015 and I am trying to implement a many-to-many relationship between objects Person
and Event
. I have followed the official documentation of the library , but I cannot get it to work.
The 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 delete the database file to make sure it creates a clean, and then trying to create Person
, Event
first push both the database and then assign Event
to Person
and then keep using the method UpdateWithChildren
:
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 a row CreateTable<PersonEvent>()
, because if it was missing, I would get an exception: SQLite.Net.SQLiteException: no such table: PersonEvent
however, I thought I didn't need to manually create this intermediate table. Doesn't SQLite-Net Extensions create it automatically?
When adding this line, the first two inserts from event1
and person1
work correctly (after inserting both objects, their IDs are assigned). Collection Events
on person1
has even created Event
, which also has an identifier:
However, the "opposite object" in this case event1
has its own collection Participants
(List Person
) NULL
:
I tried everything and nothing works. As I understood from reading the SQLite-Net Extensions docs, this should be resolved automatically. I even added this one CascadeOperations = CascadeOperation.All
to the collections, but it doesn't help as you can see. Am I still doing something wrong?
source to share
The Events collection on person1 even has an event created that also has an ID
Your object person1
has a list of the events you created. The collection contained a link to the created object event1
. This set of events was not updated when the database was added or updated. You will find that the Event object has an Id when you insert it into the database, because the list contains the same object.
SQLite-NET extensions are not an [automatic] lazy solution (like Entity Framework). He will only receive objects when told to. Using the Get method to get an object from the database does nothing but SQLite. This is because SQLite-NET Extensions does not change this method. However, it offers new methods like GetWithChildren and FindWithChildren.If you want to get the child objects of an object, you need the methods provided by SQLite-NET Extensions.
The samples presented also use these methods:
// 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 objects to be recursively loaded from Person-Event-Person-Event-etc. since it never ends. If you are starting with a person and you want all people to be associated with an event, you would like to get them in another challenge.
var person = db.GetWithChildren(1);
foreach(var event in person.Events)
{
// lazy load ourselves
var allParticipants = db.GetWithChildren(event.Id).Participants;
}
Hope it helps
source to share
If anyone would like to see more details on how to create many-to-many relationships using the SQLite-Net Extensions library, also containing the tips and solutions provided here by @Shawn Kendrot, I put it all together and wrote a post on my blog .
Hope this helps :)
source to share