How can a foreign key be mapped onto an object field in SQLite-Net Extensions?

Question

Context: We are using SQLite-Net Extensions for local data caching with Xamarin. We plan on deploying to iOS, Android and Windows Phone. We have existing data structures used throughout our system (all implementing a common interface) that we would like to store in this manner.

Problem As shown in the code sample, the [ManyToOne] attribute is used to signify a relation field. This does not work. As described on the BitBucket Developer Page the [ForeignKey] attribute can be used to specify the foreign key relation. This seemingly only supports an int. Can we easily adapt our structure to support these relations without duplicating the properties for the Id field. e.g. the following is undesirable.

    [ForeignKey(typeof(Address))]
    public int AddressId { set; get; }

    [ManyToOne]
    public Address Address
    {
        set { address = value; }
        get { return address; }
    }

Code Sample

using SQLite.Net.Attributes;
using SQLiteNetExtensions.Attributes;

namespace Data
{
    [Table("Client")]
    public class Client : IData
    {
        private int id = -1;
        private Address address = null;

        public Client() { }

        public Client(int id)
        {
            this.id = id;
        }

        [PrimaryKey, AutoIncrement, Column("_id")]
        public int Id
        {
            set { id = value; }
            get { return id; }
        }

        [ManyToOne]
        public Address Address
        {
            set { address = value; }
            get { return address; }
        }
    }

    [Table("Address")]
    public class Address : IIdentifiable
    {
        private int id = -1;
        private string someFields = "";

        public Address() { }

        public Address(int id)
        {
            this.id = id;
        }

        [PrimaryKey, AutoIncrement, Column("_id")]
        public int Id
        {
            set { id = value; }
            get { return id; }
        }

        public string SomeFields
        {
            set { someFields = value; }
            get { return someFields; }
        }
    }
}

redent84 · Accepted Answer · 2015-10-21T16:40:43.167

SQLite-Net Extensions is a thin layer over SQLite-Net, and it uses sqlite database for the storage. Relational databases store relations using foreign keys, and sqlite is no different in this aspect. Therefore, SQLite-Net and SQLite-Net Extensions also uses the foreign key mechanism for declaring relationships.

As an alternative, you could use intermediate tables to store relationships, the same way ManyToMany relationships work, but limit one of the ends to one. This way you would mimic a OneToMany, ManyToOne or even OneToOne relationship using ManyToMany relationships and intermediate tables. For example:

[Table("Client")]
public class Client {

    [PrimaryKey, AutoIncrement, Column("_id")]
    public int Id { get; set; }

    [Ignore] // This property shouldn't be persisted
    public Address Address { get; set; }

    // This relationship is in fact a ManyToOne relationship,
    // but we model it as a ManyToMany to avoid adding foreign key to this entity
    [ManyToMany(typeof(AddressesClients))]
    public Address[] Addresses { 
        get { return Address != null ? new []{ Address } : Address; } 
        set { Address = value.FirstOrDefault(); }
    }
}

[Table("Address")]
public class Address
{
    [PrimaryKey, AutoIncrement, Column("_id")]
    public int Id { get; set; }

    public string SomeFields { get; set; }

    [ManyToMany(typeof(AddressesClients), ReadOnly = true)]
    public List<Client> Clients { get; set; }
}

// Intermediate table that defines the relationship between Address and Client
class AddressesClients {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ForeignKey(typeof(Client))]
    public int ClientId { get; set; }

    [ForeignKey(typeof(Address))]
    public int AddressId { get; set; }
}

Of course, this would have some performance penalties.

As for the PrimaryKey, you can use any supported type, and you have to use the exact same type for the opposite ForeignKey, i.e. if you use Guid as primary key, the foreign key that points to that class must be a Guid too . In the demo project we're already using int (the most performant), string and even UUID.

Although this isn't the solution we chose, it is similar. In our case we forked the sqlite-net-extensions repository and modified it. We added a new custom attribute to indicate a foreign key. We then modified the internal read/write operations so that every time the attribute is detected, it writes to a custom Relations table, storing: source table, field, object id and the target object id. — TomTom, Oct 23 '15 at 13:57

How can a foreign key be mapped onto an object field in SQLite-Net Extensions?

1 Answers1