How to implement a recursive relationship with SQLite-Net Extentions

Question

I have a table Employee that must have a recursive relationship eg.

Row 1: EmployeeId = 1, EmployeeName = Albert, SupervisorId = NULL;

Row 2: EmployeeId = 2, EmployeeName = Leonardo, SupervisorId = 1;

i.e. EmployeeId(2) is a child of EmployeeId(1)

I have the following code in C# in which I use SQLite-Net Extentions to implement a recursive relationship:

public class Employee
{
    public Employee()
    {
        Subordinates = new List<Employee>();
    }

    [PrimaryKey]
    [MaxLength(36)]
    public string EmployeeGuid { get; set; }

    public string EmployeeName { get; set; }

    [OneToMany("SupervisorGuid")]
    public List<Employee> Subordinates { get; set; }

    [ManyToOne("EmployeeGuid")]
    public Employee Supervisor { get; set; }

    [ForeignKey(typeof(Employee))]
    [MaxLength(36)]
    public string SupervisorGuid { get; set; }
}

Next I test the code- I create two Employees and add them to the table Employee:

1st Employee

Employee employee1 = new Employee
{
    EmployeeGuid = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa",
    EmployeeName = "Albert"
};

Insert(employee1);

2nd Employee

Employee employee2 = new Employee
{
    EmployeeGuid = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb",
    EmployeeName = "Leonardo",
    SupervisorGuid = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
};

Insert(employee2);

BUT when I do a call

GetByGuid(string guid) 

with guid being of the 1st employee, I get the following error:

Additional information: Object of type 'Project.Models.Employee' cannot be converted to type 'System.Collections.Generic.List`1

Does SQLite-Net support recursive relationships? Any suggestions please?

UPDATE

Code for GetByGuid():

public T GetByGuid(string guid)
{
    return Database.GetWithChildren<T>(guid);
}

Also when I add the 2nd Employee without specifying the Foreign Key and do the call it works...

Answer 1

In recursive relationships you have to manually specify inverse relationships, like this:

public class Employee
{
    [PrimaryKey]
    [MaxLength(36)]
    public string EmployeeGuid { get; set; }

    public string EmployeeName { get; set; }

    [OneToMany(inverseProperty: "Supervisor")]
    public List<Employee> Subordinates { get; set; }

    [ManyToOne(inverseProperty: "Subordinates")]
    public Employee Supervisor { get; set; }

    [ForeignKey(typeof(Employee))]
    [MaxLength(36)]
    public string SupervisorGuid { get; set; }
}

I've tested it and this works as expected. However, I've created a new issue in bitbucket because I think that this behavior can be improved so this case will work in a near future:

public class Employee
{
    [PrimaryKey]
    [MaxLength(36)]
    public string EmployeeGuid { get; set; }

    public string EmployeeName { get; set; }

    [OneToMany]
    public List<Employee> Subordinates { get; set; }

    [ManyToOne]
    public Employee Supervisor { get; set; }

    [ForeignKey(typeof(Employee))]
    [MaxLength(36)]
    public string SupervisorGuid { get; set; }
}

Hope it helps.

Answer 2

It seems GetWithChildren does not return T, but List<T>, so you need to do e.g.:

public IEnumerable<T> GetByGuid(string guid)
{
    return Database.GetWithChildren<T>(guid);
}

or if you only want the item with the supplied Guid:

public T GetByGuid(string guid)
{
    return Database.GetWithChildren<T>(guid)
               .FirstOrDefault(i => i.EmployeeGuid == guid);
}

But then GetWithChildren is possibly the wrong method.