Retrieve object with all child elements (and sub child elements)

Question

I want to retrieve an object, which itself has some properties which are of type object. These child objects again contain some object as properties.

If I try to retrieve the data from the database I get the result with the children on the first level, but the data from successive levels is null. In the below example Window is null. From the other properties I get the data back as expected. If I look into the database, the relationship seems to be correct. Only the reading from the data is faulty.

Relationship

Car

public class Car
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Model { get; set; }
    public string Color { get; set; }

    [ForeignKey(typeof(Door))]
    public int DoorId { get; set; }

    [OneToOne]
    public Door Door { get; set; }
}

Door

public class Door
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Side { get; set; }

    [ForeignKey(typeof(Window))]
    public int WindowId { get; set; }

    [OneToOne]
    public Window Window { get; set; }
}

Window

public class Window
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public bool Automatic { get; set; }
}

Example

public MainPage()
{
    InitializeComponent();

    Window window = new Window();
    window.Automatic = true;

    Door door = new Door();
    door.Side = "left";
    door.Window = window;

    Car car = new Car();
    car.Color = "red";
    car.Model = "MX5";
    car.Door = door;

    this.car = car;

}

protected override async void OnAppearing()
{
    base.OnAppearing();

    await App.Database.StoreCarAsync(this.car);
    Car existingCar = await App.Database.GetCarAsync(this.car.Id);
    System.Diagnostics.Debug.WriteLine("finished");
}

Database

public class Database
{
    private readonly SQLiteAsyncConnection database;

    public Database(string databasePath)
    {
        this.database = new SQLiteAsyncConnection(databasePath);
        this.database.CreateTableAsync<Car>().Wait();
        this.database.CreateTableAsync<Door>().Wait();
        this.database.CreateTableAsync<Window>().Wait();
    }

    public async Task<Car> GetCarAsync(int carId)
    {
        var queryResult = await this.database.Table<Car>().Where(c => c.Id == carId).CountAsync();
        if (queryResult > 0)
        {
            return await this.database.GetWithChildrenAsync<Car>(carId);
        }
        else
        {
            return null;
        }
    }

    public async Task<Door> GetDoorAsync(int doorId)
    {
        var queryResult = await this.database.Table<Door>().Where(d => d.Id == doorId).CountAsync();
        if (queryResult > 0)
        {
            return await this.database.GetWithChildrenAsync<Door>(doorId);
        }
        else
        {
            return null;
        }
    }

    public async Task<Window> GetWindowAsync(int windowId)
    {
        var queryResult = await this.database.Table<Window>().Where(w => w.Id == windowId).CountAsync();
        if (queryResult > 0)
        {
            return await this.database.GetWithChildrenAsync<Window>(windowId);
        }
        else
        {
            return null;
        }
    }

    public async Task StoreCarAsync(Car car)
    {
        await this.StoreDoorAsync(car.Door);

        var foundItem = await this.GetCarAsync(car.Id);
        if (foundItem != null)
        {
            await this.database.UpdateWithChildrenAsync(car);
        }
        else
        {
            await this.database.InsertWithChildrenAsync(car);
        }
    }

    public async Task StoreDoorAsync(Door door)
    {
        await this.StoreWindowAsync(door.Window);

        var foundItem = await this.GetDoorAsync(door.Id);
        if (foundItem != null)
        {
            await this.database.UpdateWithChildrenAsync(door);
        }
        else
        {
            await this.database.InsertWithChildrenAsync(door);
        }
    }

    public async Task<int> StoreWindowAsync(Window window)
    {
        var foundItem = await this.GetWindowAsync(window.Id);
        if (foundItem != null)
        {
            return await this.database.UpdateAsync(window);
        }
        else
        {
            return await this.database.InsertAsync(window);
        }
    }
}

Isn't this possible or what I'm doing wrong here?

PS: I'm using the latest SQLiteNetExtensions.Async v2.0.0-alpha2 NuGet package.

score 4 · Accepted Answer · answered Jun 23 '17 at 11:49

4

You should be using cascaded read to read a complete relationship hierarchy. First mark your entity relationship to allow cascaded reads, for example:

public class Car
{
    //snip

    [OneToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public Door Door { get; set; }
}

Then when you read from the database, set the recursive parameter to true. For example:

return await this.database.GetWithChildrenAsync<Door>(doorId, recursive: true);

answered Jun 23 '17 at 11:49

DavidG

113,891
12
217
223

I added the `CascadeOperation` attribute to `Car` and `Door`. `recursive` is also true for `GetCarAsync()` and `GetDoorAsync()`. The result for `Window` is still `null`. Do I have to store it differently or should I use `CascadeInsert`? – testing Jun 23 '17 at 12:07
OK, my fault. I deleted the database entirely from the file system and now it works. There was still the old database ... One more question: Can `CascadeRead` be combined with `CascadeInsert`? Or can only exist the one or other? Which to use when? – testing Jun 23 '17 at 12:15
2

You can use both: `[OneToMany(CascadeOperations = CascadeOperation.CascadeRead | CascadeOperation.CascadeInsert)]` – DavidG Jun 23 '17 at 12:23

Retrieve object with all child elements (and sub child elements)

1 Answers1