Navigate to SQLite object related to an other object, fetched from the database

Question

I'm trying to navigate to an object, related to an other object.

In this situation, there are 2 classes; StockTakeSession and Location. These 2 have a one-to-one relation. When there are 2 objects created of each class and I set the relation as in the code below. I can navigate through it as I like without any issues. But when I fetch a StockTakeSession from the database, only LocationId has a value, and Location itself is null. To solve this, I created the extended get method for StockTakeSession.Location.

I'm quite new with C#/SQLite/ORM, so I was wondering if this is just how it is suppose to work or do I need to use a different approach?

Thanks for any suggestion.

Robert

---

These are the 2 Classes:

// These are the Stock Take Sessions
public class StockTakeSession : DomainModels
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public DateTime DateTime { get; set; }

    // Navigation properties
    // One to many relationship with StockItems
    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<StockItem> StockItems { get; set; }


    // Specify the foreign key to Location
    [ForeignKey(typeof(Location))]
    public int LocationId { get; set; }

    // One to one relationship with Location

    private Location location;

    [OneToOne]
    public Location Location
    {


        get
        {
            if (location == null)
            {
                DataBase db = new DataBase();

                Location locationTemp = db.SelectLocation(LocationId);

                return locationTemp;
            }
            else
                return location;

        }

        set
        {
            location = value;
        }
    }
}

// These are the Locations where the Stock Take Sessions are done
public class Location : DomainModels, IComparable<Location>
{
    [JsonProperty("id"), PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Street { get; set; }
    public int Number { get; set; }
    public string Postcode { get; set; }
    public string City { get; set; }
    public bool Completed { get; set; }

    [Ignore]
    public string Label
    {
        get
        {
            return Name + " - (" + Postcode + ")";
        }
    }

    public int CompareTo(Location other)
    {
        return Name.CompareTo(other.Name);
    }

    // Navigation property
    // One to many relationship with StockItems
    [OneToMany(CascadeOperations = CascadeOperation.All), Ignore]
    public List<StockItem> StockItems { get; set; }

    // Specify the foreign key to StockTakeSession
    [ForeignKey(typeof(StockTakeSession))]
    public int StockTakeSessionId { get; set; }

    // One to one relationship with StockTakeSession
    [OneToOne]
    public StockTakeSession StockTakeSession { get; set; }

}

How I store the relation between to objects:

StockTakeSession newSession = new StockTakeSession
{
    LocationId = selectedLocation.Id,
    Location = selectedLocation,
    DateTime = DateTime.Now

};

db.Insert(newSession, true);

Answer 1

In sqlite-net-extensions, relationships are not loaded on demand. You have to fetch them from Database when you need them.

Change your implementation for Location to a simple getter/setter.

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

Then, load the session relationships when you need it:

db.GetChildren(session);

For example:

db.GetWithChildren<StockTakeSession>(stockId);

Will fetch the StockTakeSession object with that Id and also load relationships and set the inverse relationships.