SQLite.NET Extensions Many To Many, no join table records created

Question

I am new to using SQLite.NET and the Extensions.

To my best ability I have followed the guides I've found but no records are being created in the many to many join tables and I've no idea why.

I have a solution NuGet dependency on the SQLiteNetExtensions project.

I have the following tables:

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

    [MaxLength(50)]
    public string FirstName { get; set; }

    [MaxLength(50)]
    public string Surname { get; set; }

    [ManyToMany(typeof(Participant))]
    public List<Journey> Journeys { get; set; }
}

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

    [ManyToMany(typeof(Participant))]
    public List<Contact> Contacts { get; set; }

    [ManyToMany(typeof(Waypoint))]
    public List<Location> Locations { get; set; }
}

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

    [MaxLength(50)]
    public string Name { get; set; }

    public double Latitude { get; set; }

    public double Longitude { get; set; }

    [ManyToMany(typeof(Waypoint))]
    public List<Journey> Journeys{ get; set; }
}

public class Participant
{
    [ForeignKey(typeof(Contact))]
    public int ContactId { get; set; }

    [ForeignKey(typeof(Journey))]
    public int JourneyId { get; set; }
}

public class Waypoint
{
    [ForeignKey(typeof(Location))]
    public int LocationId { get; set; }

    [ForeignKey(typeof(Journey))]
    public int JourneyId { get; set; }
}

When I build the database I use the following test code:

string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "xandroid.db3");

var db = new SQLiteConnection(platform, dbPath);

db.DropTable<Location>();
db.DropTable<Waypoint>();
db.DropTable<Contact>();
db.DropTable<Participant>();
db.DropTable<Journey>();

db.CreateTable<Location>();
db.CreateTable<Waypoint>();
db.CreateTable<Contact>();
db.CreateTable<Participant>();
db.CreateTable<Journey>();

Location home = new Location { Name = "Home", Latitude=22.22, Longitude=22.22 };
Location perth = new Location { Name = "Perth", Latitude = 4444.4444, Longitude = 4444.4444 };
db.InsertAll(new List<Location> { home, perth });

Contact beans = new Contact { FirstName = "Beans", Surname = "Connor" };
Contact winston = new Contact { FirstName = "Winston", Surname = "Connor" };
db.InsertAll(new List<Contact> { beans, winston });

Journey travelToPerth = new Journey { Locations = new List<Location> { perth }, Contacts = new List<Contact> { beans, winston }};
Journey returnHome = new Journey { Locations = new List<Location> { home }, Contacts = new List<Contact> { beans, winston}};
db.InsertAll(new List<Journey> { travelToPerth, returnHome } );

When I access the data I use the following code:

var waypoints = db.Table<Waypoint>();
Console.Out.WriteLine(waypoints.Count() + " recorded waypoints");

var participants = db.Table<Participant>();
Console.Out.WriteLine(participants.Count() + " recorded participants");

var journeys = db.Table<Journey>();
Console.Out.WriteLine(journeys.Count() + " recorded journeys");

The output of which is:

0 recorded waypoints
0 recorded participants
2 recorded journeys

Answer 1

You are inserting the objects using plain sqlite.net methods, that know nothing about your relationships. To save and load relationships you have to use SQLite-Net Extension methods. Most sqlite-net methods also have a WithChildren alternative to save relationships:

import SQLiteNetExtensions.Extensions;

db.InsertAllWithChildren(new List<Journey> { travelToPerth, returnHome } );

Will insert both elements and insert the required records to Participant and Waypoint tables to save the relationships.

Note: This still requires Location and Contact elements to be already inserted in database. To insert objects recursively, take a look at the Cascade operations section of the SQLite-Net Extensions documentation.