Multiple one-to-one relationships to same table

Question

Which is the best way to model multiple one-to-one relationships to a same table (sqlite-net-extensions)? I am looking for a solution like this:

Class WayBill 
{
    [PrimaryKey, AutoIncrement]
    public int Id {get; set;} 

    [ForeignKey(typeof(Organization ))]
    public int ConsignerId {get; set;}

    [OneToOne]
    public Organization Consigner {get; set;}  

    [ForeignKey(typeof(Organization ))]
    public int ConsigneeId {get; set;}

    [OneToOne]
    public Organization Consignee {get; set;} 
}


Class Organization 
{
    [PrimaryKey, AutoIncrement]
    public int Id {get; set;} 

    public string Name {get; set;}
}    

Obviously, the above won't work.

I have considered other options too:

1. create a table (WayBillOrganization) that captures the role of organization: Waybill -->> WayBillOrganization --> Organization

2. put the required inverse OneToMany properties in to the Organization.

3. Handle things manually (ie. store only primary keys in the WayBill class and load the Organizations separately).

Option 2. is something I want to avoid. Organizations are related to so many other classes (not in my example) and even in the case of a waybill, there's a few more relationships I didn't include into the example (carrier, cargo paying party, freight forwarder and so on). Besides, I'd rather use inverse properties only when I need to navigate (for example, I don't use organization to find waybills, so an inverse property is only an extra burden.)

Option 3. isn't that attractive either.

So the option 1. seems to be the way to go. But before going there, I'd like to know if the perfect-world solution in my example is indeed impossible.

So, my question is: Is there a way to model multiple one directional OneToOne relationships without explicitly declared inverse properties?

Answer 1

Your use case is supported in SQLite-Net Extensions. You only have to specify the foreign keys explicitly in the relationship attributes, because automatic discovery may not work as expected:

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

    public int ConsignerId {get; set;}

    [OneToOne("ConsignerId")]
    public Organization Consigner {get; set;}  

    public int ConsigneeId {get; set;}

    [OneToOne("ConsigneeId")]
    public Organization Consignee {get; set;} 
}


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

    public string Name {get; set;}
}

Inverse relationships to the same class are also supported, but must be also declared explicitly at both ends:

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

    public int ConsignerId {get; set;}

    [OneToOne(foreignKey: "ConsignerId", inverseProperty: "ConsignerInverse")]
    public Organization Consigner {get; set;}  

    public int ConsigneeId {get; set;}

    [OneToOne(foreignKey: "ConsigneeId", inverseProperty: "ConsigneeInverse")]
    public Organization Consignee {get; set;} 
}


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

    public string Name {get; set;}

    [OneToOne(foreignKey: "ConsigneeId", inverseProperty: "Consignee")]
    public WayBill ConsigneeInverse { get; set; }

    [OneToOne(foreignKey: "ConsignerId", inverseProperty: "Consigner")]
    public WayBill ConsignerInverse { get; set; }
}