Sqlite.net extentions, Cant get data with GetWithChildren

Question

I'm creating a database with serveral classes as children of other classes. Writing with foreign keys works fine however when i try to get the data back it throws the error:

An unhandled exception of type 'SQLiteNetExtensions.Exceptions.IncorrectRelationshipException' occurred in SQLiteNetExtensions.dll

Additional information: MatchDetail.timeline: At least one entity in a OneToOne relationship must have Foreign Key

This is my creation code:

SQLiteConnection db = new SQLiteConnection(new SQLite.Net.Platform.Win32.SQLitePlatformWin32(), "Matches.db3");
db.CreateTable<ParticipantIdentity>();
db.CreateTable<MatchDetail>();
db.CreateTable<Timeline>();
db.InsertWithChildren(md, true);
var m = db.GetWithChildren<MatchDetail>(matchId, true);

And my class:

[Table("Matches")]
public class MatchDetail
{
    [PrimaryKey]
    public long matchId { get; set; }
    public int mapId { get; set; }
    [JsonConverter(typeof(DateTimeConverterFromLong))]
    public DateTime MatchCreation { get; set; }
    public long matchDuration { get; set; }
    public MatchMode matchMode { get; set; }
    public MatchType matchType { get; set; }
    public string matchVersion { get; set; }

    public string platformId { get; set; }
    public Queuetype queueType { get; set; }
    public Region region { get; set; }
    public Season season { get; set; }

    [ForeignKey(typeof(Timeline), Name = "TimelineId"), Indexed]
    public int timelineId { get; set; }
    [OneToOne("TimelineId", CascadeOperations = CascadeOperation.All)]
    public Timeline timeline { get; set; }

    [ForeignKey(typeof(ParticipantIdentity), Name = "ParticipantId"), Indexed]
    public int participantIdentitiesId { get; set; }
    [ManyToOne("ParticipantId", CascadeOperations = CascadeOperation.All)]
    public List<ParticipantIdentity> participantIdentities { get; set; }
}

The other classes are just an id and some other basic types, I've been trying to work this out but it just doesnt want to work.

Edit:

[ForeignKey(typeof(ParticipantIdentity))]
public int participantIdentitiesId { get; set; }
[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<ParticipantIdentity> participantIdentities { get; set; }

With error:

An unhandled exception of type 'SQLiteNetExtensions.Exceptions.IncorrectRelationshipException' occurred in SQLiteNetExtensions.dll

Additional information: MatchDetail.participantIdentities: Unable to find foreign key for OneToMany relationship

Answer 1

You are manually specifying that the foreign key is named 'TimelineId' but in reality the foreign key is named 'timelineId' (note capitalization of the first letter).

Change this:

[ForeignKey(typeof(Timeline), Name = "TimelineId"), Indexed]
public int timelineId { get; set; }
[OneToOne("TimelineId", CascadeOperations = CascadeOperation.All)]
public Timeline timeline { get; set; }

To this:

[ForeignKey(typeof(Timeline)]
public int timelineId { get; set; }
[OneToOne(CascadeOperations = CascadeOperation.All)]
public Timeline timeline { get; set; }

Explicitly declaring the foreign key name can lead to refactoring issues, so this is the recommended way unless otherwise is strictly required.

---

The other relationship is declared as a ManyToOne but it's in fact a OneToMany. To make it work you have to change the attribute type and move the foreign key to the other end.

Change the relationship from:

[ForeignKey(typeof(ParticipantIdentity), Name = "ParticipantId"), Indexed]
public int participantIdentitiesId { get; set; }
[ManyToOne("ParticipantId", CascadeOperations = CascadeOperation.All)]
public List<ParticipantIdentity> participantIdentities { get; set; }

With this:

[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<ParticipantIdentity> participantIdentities { get; set; }

And add the foreign key to MatchDetail to the ParticipantIdentity class:

[ForeignKey(typeof(MatchDetail)]
public int matchDetailId { get; set; }

Take a look at the SQLite-Net Extensions documentation and sample project for more examples.