SQLite and SQLite-Extensions can not perform OneToOne relationsip

Question

I'm using SQLite-Net-Extensions. I'm attempting to define a OneToOne relationship so that when my Accounts model is loaded it will also include the Campaign so that I can access the campaign name.

The problem is Accounts.Campaign is always null. I have data in both tables.

Here's my tables in SQLite:

CREATE TABLE `campaigns` (
    `Id`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `Name`  TEXT UNIQUE
);

and

CREATE TABLE `accounts` (
    `Id`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `CampaignId`    INTEGER,
    `MobileNumber`  TEXT UNIQUE,
    `Password`  TEXT
);

Below are my models:

namespace SMA.Models
{
    [SQLite.Table("accounts")]
    class Accounts
    {
        [PrimaryKey, AutoIncrement]
        public Int32 Id { get; set; }
        [ForeignKey(typeof(Campaigns))]
        public Int32 CampaignId { get; set; }
        [MaxLength(11)]
        public string MobileNumber { get; set; }
        [MaxLength(50)]
        public string Password { get; set; }

       [OneToOne("Id")]
       public Campaigns Campaign { get; set; }
    }
}

and

namespace SMA.Models
{
    [Table("campaigns")]
    class Campaigns
    {
        [PrimaryKey, AutoIncrement]
        public Int32 Id { get; set; }
        [MaxLength(50)]
        public string Name { get; set; }
    }
}

I run the following code to fetch all of the accounts:

var accounts = this.db.Table<SMA.Models.Accounts>().ToList();

Also tried ...

var accounts = this.db.Query<Account>("SELECT * FROM accounts");

And ...

var accounts = this.db.Query<Account>("SELECT * FROM accounts JOIN campaigns ON accounts.CampaignID = campaigns.ID");

When I inspect accounts the account data is there, but Accounts.Campaign is null. I can't seem to see what I'm doing wrong.

redent84 · Accepted Answer · 2016-10-10T08:35:38.047

0

Try using SQLite-Net Extension read methods instead of plain sqlite.net Query or Table. For example:

var accounts = this.db.GetAllWithChildren<Account>();

Also, make sure that you're either setting foreign keys manually or using SQLite-Net Extensions to write relationships to database.

To make SQLite-Net Extensions methods available make sure that you're importing SQLiteNetExtensions.Extensions namespace:

import SQLiteNetExtensions.Extensions;

If they're still not available, make sure that there are no duplicated sqlite.net libraries in your packages.

edited Oct 10 '16 at 08:35

answered Oct 09 '16 at 17:46

redent84

18,901
4
62
85

`GetAllWithChildren` isn't available. I am setting `this.db` like so ... `this.db = new SQLiteConnection(new SQLiteConnectionString(this.dbFileName, true).ConnectionString);`. Do I have to use a SQLite-Net-Extensions connection methods? – BugHunterUK Oct 09 '16 at 18:36
@BugHunterUK SQLite-Net Extensions extends standard sqlite.net connection class, so that connection should work fine. Are you importing `SQLiteNetExtensions.Extensions` namespace to bring up the extension methods? – redent84 Oct 10 '16 at 08:36
I was using `SQLite` instead of `SQLite.Net` to create the connection. Works fine now thank you. – BugHunterUK Oct 10 '16 at 08:38

SQLite and SQLite-Extensions can not perform OneToOne relationsip

1 Answers1