SQLite.net Extensions loads the same entity multiple times rather than returning the same reference

Question

I'm using the PCL version of SQLite.Net Extensions in a Windows 10 universal app. This is my first time using it. It generally seems to be working, but it appears to be loading an entity multiple times rather than reusing a reference to the same object.

According to the SQLite.Net extensions documentation:

SQLite-Net Extensions will ensure that any object is loaded only once from the database and will resolve circular dependencies and inverse relationships while maintaining integral reference. This means that any returned object of the same class with the same identifier will be a reference to exactly the same object.

That does not appear to be happening for me. Here is my code:

public class Group {
    [PrimaryKey, AutoIncrement]
    public Guid Id { get; set; }

    public string GroupName { get; set; }

    public override string ToString() {
        return string.Format("Group [ID: {0}, HashCode: {1}] GroupName={2}", Id.ToString().Last(4), GetHashCode(), GroupName);
    }

    [ManyToMany(typeof(GroupMember), CascadeOperations = CascadeOperation.CascadeRead)]
    public List<Member> Members { get; set; }

    public void DebugIt() {
        Debug.WriteLine(this);
        foreach (var member in Members) Debug.WriteLine("    " + member);
    }
}

public class Member {
    [PrimaryKey, AutoIncrement]
    public Guid Id { get; set; }

    public string Name { get; set; }

    public override string ToString() {
        return string.Format("Member [ID: {0}, HashCode: {1}] Name={2}", Id.ToString().Last(4), GetHashCode(), Name);
    }

    [ManyToMany(typeof (GroupMember), CascadeOperations = CascadeOperation.CascadeRead)]
    public List<Group> Groups { get; set; }

    public void DebugIt() {
        Debug.WriteLine(this);
        foreach (var group in Groups) Debug.WriteLine("    " + group);
    }
}

public class GroupMember {
    [PrimaryKey, AutoIncrement]
    public Guid Id { get; set; }
    [ForeignKey(typeof(Group))]
    public Guid GroupID { get; set; }
    [ForeignKey(typeof(Member))]
    public Guid MemberId { get; set; }
}

public class DatabaseGroups {
    private const string FileName = "db.sqlite";
    private SQLiteConnection _db;

    public async Task<bool> LoadAsync() {
        var exists = await FileHelper.DoesFileExistAsync(FileName);
        _db = new SQLiteConnection(new SQLitePlatformWinRT(), DatabaseFullPath,
            exists ? SQLiteOpenFlags.ReadWrite : SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite);
        if (!exists) InitializeWithDefaults();
        return await FileHelper.DoesFileExistAsync(FileName);
    }

    private void InitializeWithDefaults() {
        _db.CreateTable<Group>();
        _db.CreateTable<Member>();
        _db.CreateTable<GroupMember>();

        var group1 = new Group {GroupName = "Group 1"};
        var group2 = new Group {GroupName = "Group 2"};
        var member1 = new Member {Name = "Bob"};
        var member2 = new Member {Name = "Jane"};

        _db.Insert(group1);
        _db.Insert(group2);
        _db.Insert(member1);
        _db.Insert(member2);

        group1.Members = new List<Member> {member1, member2};
        _db.UpdateWithChildren(group1);

        group2.Members = new List<Member> {member1, member2};
        _db.UpdateWithChildren(group2);
    }

    private static StorageFolder DatabaseFolder {
        get { return ApplicationData.Current.LocalFolder; }
    }

    private static string DatabaseFullPath {
        get { return Path.Combine(DatabaseFolder.Path, FileName); }
    }

    public void DebugIt() {
        foreach (var groupId in _db.Table<Group>().Select(g => g.Id)) {
            var group = _db.GetWithChildren<Group>(groupId);
            group.DebugIt();
        }
        foreach (var memberId in _db.Table<Member>().Select(m => m.Id)) {
            var member = _db.GetWithChildren<Member>(memberId);
            member.DebugIt();
        }
    }
}

protected override async void OnLaunched(LaunchActivatedEventArgs e) {
    _db = new DatabaseGroups();
    await _db.LoadAsync();
    _db.DebugIt();

When it runs, I create some initial data. I then load those objects using GetWithChildren and debug it. Here are the results:

Group[ID: 4858, HashCode: 51192825] GroupName = Group 1
    Member[ID: dbfa, HashCode: 64971671] Name = Jane
    Member[ID: b047, HashCode: 30776584] Name = Bob
Group[ID: 30f0, HashCode: 53439890] GroupName = Group 2
    Member[ID: dbfa, HashCode: 36062904] Name = Jane
    Member[ID: b047, HashCode: 9089598] Name = Bob
Member[ID: b047, HashCode: 20305449] Name = Bob
    Group[ID: 30f0, HashCode: 9648315] GroupName = Group 2
    Group[ID: 4858, HashCode: 29803642] GroupName = Group 1
Member[ID: dbfa, HashCode: 36899882] Name = Jane
    Group[ID: 30f0, HashCode: 23318221] GroupName = Group 2
    Group[ID: 4858, HashCode: 60865449] GroupName = Group 1

As you can see, the objects appear to be loading correctly, but the object references for Group 1 (for example) are different (see the hash code).

Am I perhaps misinterpreting the way SQLite.Net Extensions handles object references? Perhaps it handles reusing object references within a single call to GetWithChildren, but not across multiple calls on the same SQLiteConnection?

If that's the case, how are you supposed to load a more complex object graph with these sorts of relationships?

Answer 1

You are correct, SQLite-Net Extensions caches the objects for recursive calls to avoid reference loops and handle inverse relationships, but it doesn't cache the objects between calls.

SQLite-Net Extensions is just a thin layer over SQLite.Net, if integral reference is important for you, you can go back to manual queries for more complexes operations.

If you have any suggestion or pull request, they are always welcome ;)