4

Is it possible to have multiple OneToOne relationships within an entity using SQLite.Net.Async Extensions PCL 1.3.0?

Example:

[Table("body")]
public class Body
{
    [OneToOne(CascadeOperations = CascadeOperation.All)]
    [Column ("left")]
    public Hand Left { get; set; }

    [OneToOne(CascadeOperations = CascadeOperation.All)]
    [Column ("right")]
    public Hand Right { get; set; }
}

[Table("hand")]
public class Hand
{
    // In this I do not need a reference back to Body.
}

I have been trying using the following answer:

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

And with these site as inspiration also:

https://bitbucket.org/twincoders/sqlite-net-extensions/overview

https://bitbucket.org/twincoders/sqlite-net-extensions/src/65a1f8519347c40c948855cfc1a1d4d8bbcc8748/Tests/ReflectionExtensionsTests.cs?at=master&fileviewer=file-view-default

Unfortunately with no luck so far. Is it even possible?

Community
  • 1
  • 1
dynamokaj
  • 471
  • 7
  • 19

1 Answers1

5

Yes, that's totally possible, unfortunately you can't rely on automatic foreign key and inverse relationship discovery, so you'll need to specify it manually.

For example, for a int primary key and foreign keys declared like in the same class:

public class Body
{
    [OneToOne(foreignKey: "LeftId", CascadeOperations = CascadeOperation.All)]
    public Hand Left { get; set; }

    [OneToOne(foreignKey: "RightId", CascadeOperations = CascadeOperation.All)]
    public Hand Right { get; set; }

    // Foreign key for Left.Id
    public int LeftId { get; set; }
    // Foreign key for Right.Id
    public int RightId { get; set; }
}

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

If your foreign keys are declared in Hand object the attribute properties are equivalent:

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

    [OneToOne(foreignKey: "LeftId", CascadeOperations = CascadeOperation.All)]
    public Hand Left { get; set; }

    [OneToOne(foreignKey: "RightId", CascadeOperations = CascadeOperation.All)]
    public Hand Right { get; set; }
}

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

    // Foreign key for Body.Id where this object is Left
    public int LeftId { get; set; }
    // Foreign key for Body.Id where this object is Right
    public int RightId { get; set; }
}

And inverse properties, if needed, must be specified in the inverseProperty key of the OneToOne attribute of both ends:

public class Body
{
    // Skipping foreign keys and primary key

    [OneToOne(foreignKey: "LeftId", inverseProperty: "LeftBody", CascadeOperations = CascadeOperation.All)]
    public Hand Left { get; set; }

    [OneToOne(foreignKey: "RightId", inverseProperty: "RightBody", CascadeOperations = CascadeOperation.All)]
    public Hand Right { get; set; }
}

public class Hand
{
    // Skipping foreign keys and primary key

    [OneToOne(foreignKey: "LeftId", inverseProperty: "Left", CascadeOperations = CascadeOperation.All)]
    public Body LeftBody { get; set; }

    [OneToOne(foreignKey: "RightId", inverseProperty: "Right", CascadeOperations = CascadeOperation.All)]
    public Body RightBody { get; set; }
}
redent84
  • 18,901
  • 4
  • 62
  • 85
  • Thanks @redent84, I finally made it work using your examples. Is there any different of the two approaches with regards to performance, i.e. is it prefered to keep the foreign keys in Body or Hand in my case? I actually only need the Body to know the two hands and I am planning to query them recursive, so one should think that it is prefered to hold the foreign keys inside the Body? -A bonus question is when I should query the above structure then I set the recursive-flag in GetAllWithChildrenAsync. Is it possible to specify which Objects to get or maybe the the depth, i.e. how deep to fetch? – dynamokaj Sep 24 '15 at 16:20
  • Performance-wise is alway better to access objects by primary key, so if you're going to recursively access from `Body` to `Hands` is better to keep the foreign keys in the `Body`. It also makes more sense in this particular scenario. As for the recursive, change the `CascadeOperations` to something more conservative if you don't need it. There's currently no way to specify the depth on runtime, it's only based on `CascadeOperations` property of the relationship attribute. – redent84 Sep 25 '15 at 09:22
  • "Unfortunately you can't rely on automatic foreign key and inverse relationship discovery" - Personally, I'd prefer this, not only for performance, but to reduce guesswork and relying on a framework to try assume thing based on strings! – Tor Oct 05 '17 at 20:58