Sqlite-net extensions relations always null

Question

I am trying to use the extension in MvvmCross 4. What I am trying to do is simple: I have two tables with a one to many relationship and and want to access this.

I have two classes, BusLine and BusLineGroup. Each BusLine has one Group as foreign key. What I do in code is run a simple LINQ-Query to get all Buslines:

var testQuery = 
    from busLine in this._connection.Table<BusLine>()
    select busLine;

The query itself works, but if I check the fields of the returned objects, the Group is always null!. See below for the class and table definitions.

What am I doing wrong? Why is the group always null? Thanks for your help.

The classes in code:

    public class BusLine
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        [ForeignKey(typeof(BusLineGroup))]
        public int BusLineGroup { get; set; }
        [ManyToOne]
        public BusLineGroup LineGroup { get; set; }
    }

    public class BusLineGroup
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Color { get; set; }
        public string MainStations { get; set; }
        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<BusLine> BusLines { get; set; }

    }

The two tables:

CREATE TABLE "BusLineGroup" (
    `Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    `Name`  TEXT NOT NULL,
    `Color` TEXT NOT NULL,
    `MainStations`  TEXT NOT NULL
);
CREATE TABLE "BusLine" (
    `Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    `Name`  TEXT NOT NULL,
    `BusLineGroup`  INTEGER NOT NULL,
    FOREIGN KEY(`BusLineGroup`) REFERENCES `BusLineGroup`(`Id`)
);

Installed Nuget-Packages:

MvvmCross.Plugin.SQLitePCL
SQLiteNetExtensions

Note: The MvvmCross package automatically includes SQLite.Net-PCL. So both of those two use the same PCL.

score 5 · Accepted Answer · answered Apr 05 '16 at 16:24

5

You are not using any SQLite-Net Extensions method there, you are using plain sqlite.net methods that know nothing about your relationships. You have to use the WithChildren methods to read and write relationships from database.

For example, your query can be replaced with this line:

var testQuery = this._connection.GetAllWithChildren<BusLine>();

That will also fetch first-level relationships from database.

I'd recommend you to take a look at the SQLite-Net Extensions documentation and the sample project for more examples.

answered Apr 05 '16 at 16:24

redent84

18,901
4
62
85

Don't know how I could miss that... Thanks, it works now – Felix Apr 05 '16 at 18:33
I get a null reference exception when I try to select records based on the criteria that a extension relationship (ManyToOne) is not null. It throws a NPE, which doesn't make sense since I am only checking if it is null, no dereferencing it. Does it have something to do with the relationship not being ready yet, when selecting? – James Wierzba Jan 24 '17 at 16:25
@JamesWierzba That sounds like a different question. You can't use relationships in your queries because those properties are ignored by sqlite.net. – redent84 Jan 24 '17 at 19:01
I'm using `GetAllWithChildren` I neglected to mention that. That is a sqlite net extension method, no? Anyway yes it should probably be its own question but I found a workaround so no worries thank you – James Wierzba Jan 24 '17 at 19:07

Sqlite-net extensions relations always null

1 Answers1