SQLite-Net Extension both one-to-one and one-to-many relationships between two entities

Question

I am using SQLite-Net PCL together with SQLite-Net extensions for the development of an application using Xamarin.

In my model I have two entities, let us call them A and B, that are connected by both a one-to-one and a one-to-many relationships. For instance, A has a one-to-one relationship with B, and A has also a one-to-many relationship with B.

Is it possible to express such behaviour with SQLite-Net extensions?

score 15 · Accepted Answer · edited Nov 03 '20 at 13:08

Yes, but you have to explicitly declare the foreign keys and inverse properties in the relationship attribute, because otherwise the library may get the wrong foreign key for the relationship.

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

        [OneToMany("O2MClassAKey", "BObjectsInverse")]
        public List<ClassB> BObjects { get; set; }

        [OneToOne("O2OClassAKey", "BObjectInverse")]
        public ClassB BObject { get; set; }

        // Other properties
        public string Bar { get; set; }
    }
        
    public class ClassB
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        [ForeignKey(typeof (ClassA))]
        public int O2MClassAKey { get; set; }

        [ForeignKey(typeof (ClassA))]
        public int O2OClassAKey { get; set; }

        // Inverse relationships, these are optional
        [ManyToOne("O2MClassAKey", "BObjects")]
        public ClassA BObjectsInverse { get; set; }
        [OneToOne("O2OClassAKey", "BObject")]
        public ClassA BObjectInverse { get; set; }

        // Other properties
        public string Foo { get; set; }
    }

Please note that the foreign key O2OClassAKey for the OneToOne relationship can be declared in any of the classes.

If you don't need inverse properties, you can skip them in the relationship attribute:

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

        [OneToMany("O2MClassAKey")]
        public List<ClassB> BObjects { get; set; }

        [OneToOne("O2OClassAKey")]
        public ClassB BObject { get; set; }

        // Other properties
        public string Bar { get; set; }
    }
        
    public class ClassB
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        [ForeignKey(typeof (ClassA))]
        public int O2MClassAKey { get; set; }

        [ForeignKey(typeof (ClassA))]
        public int O2OClassAKey { get; set; }

        // Other properties
        public string Foo { get; set; }
    }

Thanks for your answer @redent84 , this is exactly what I was looking for. I am just not much sure about what do you mean by "the foreign key for the oneToMany relationship can be declared in any of the classes". — papafe, Mar 12 '15 at 09:00
Sorry, it was a typo. I meant to say the `OneToOne` property. The foreign key of a one-to-one relationship can be in any of the two ends. — redent84, Mar 12 '15 at 09:08
A couple of things to keep in mind with this: 1. When you insert a row with a relationship, you MUST call `.UpdateWithChildren` for the relationships to be updated in the database. I fought this for hours trying to figure out why all of my relationships are being stored as _null_. 2. Don't forget your `CascadeOperations` and `ReadOnly` tags if needed. — JL West, May 09 '18 at 13:45
@JLWest yes, you have to `InsertWithChildren` or `UpdateWithChildren` after insertion to update relationships. — redent84, May 09 '18 at 14:00
@redent84 In my experience `InsertWithChildren` or `InsertOrReplaceWithChildren` will not update the relationships correctly in the database. All of my relationships were null until I called `UpdateWithChildren` — JL West, May 09 '18 at 14:23

SQLite-Net Extension both one-to-one and one-to-many relationships between two entities

1 Answers1

Linked