Need some help: self referencing many to many relationship between users

Topics: Customizing Orchard, General
Aug 1, 2013 at 2:05 PM
Hi,
I tried to translate the relationship, described in the title, from a project which uses EF, to Orchard.
Originally it has a user model and a UserUser model which defines the relations as follow:
[Table("UsersUsers")]
    public class UserUser
    {
        [Key, Column(Order = 0), ForeignKey("SourceUser")]
        public virtual int SourceUserId { get; set; }

        [Key, Column(Order = 1), ForeignKey("DestinationUser")]
        public virtual int DestinationUserId { get; set; }

        public virtual User SourceUser { get; set; }

        public virtual User DestinationUser { get; set; }
What I try in Orchard, is to create a equivalent table to store relations between UserPartRecords.
Right now I have this:
public class UserUserRecord : ContentPartRecord 
    {
        public virtual UserPartRecord SourceUser { get; set; }

        public virtual UserPartRecord DestinationUser { get; set; }

        public virtual RequestState State { get; set; }
(with a corresponding UserUserPart of course.)
This is my current migration:
SchemaBuilder.CreateTable("UserUserRecord", table => table
                .Column<int>("SourceUser_id")
                .Column<int>("DestinationUser_id")
                .Column<int>("State")
                .Column<string>("RequestText")
                .Column<string>("ResponseText")
            );
I also tried the following for creating the ForeignKeys:
SchemaBuilder.CreateForeignKey(
                "FK_SourceUserId",
                "DotNetFabrik.Social.Core",
                typeof(UserUserRecord).Name,
                new[] { "SourceUser_id" },
                "Orchard.Users",
                typeof(UserPartRecord).Name,
                new[] { "Id" }
            );
The code snippets above are one of a few solutions I tried, but I don't wanna spam them all here. However I had no success in creating a table with two ForeignKeys linking two UserPartRecords together. Can anyone tell me please what I do wrong and where i miss the obvious?

TL;DR
I need a table with two ForeignKeys: SourceUserId and DestinationUserId which are both UserPartRecords. Any help would be much appreciated.
Developer
Aug 5, 2013 at 8:34 AM
When you say "no success", what exactly do you mean? Do you get an error, or are the tables not being created?
Aug 5, 2013 at 12:32 PM
Edited Aug 6, 2013 at 10:37 AM
Ah yeah sorry that was a bit unclear.
The tables are in fact created, the foreignKeys are not.

Edit:
If someone would hand me a small code snippet I would be damn grateful.
Unfortunately I don't have the time right now to look into this issue for hours. :/
Aug 7, 2013 at 8:43 AM
Push: also interested in creating a user-user relation.