I am trying to implement a self-referencing relationship in EF Core. I am trying to use a custom foreign key and principal key that are not mapped to a real database columns.
With the below code, I get the error
'No backing field could be found for property 'HeadOfHouseholdForeignKey' of entity type 'Person' and the property does not have a setter
Is there a way to have these foreign/principal keys which aren't mapped to a column in the DB? Is there a way to do it nicely in EF Core?
Example:
PersonContext.cs
using Microsoft.EntityFrameworkCore;
namespace ConsoleApp1
{
public class PersonContext : DbContext
{
DbSet<Person> People;
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<Person>()
.HasOne(p => p.HeadOfHouseHold)
.WithMany()
.HasForeignKey(p => p.HeadOfHouseholdForeignKey)
.HasPrincipalKey(p => p.HeadOfHouseholdPrincipalKey);
}
}
}
Person.cs
namespace ConsoleApp1
{
internal class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
public string FamilyName { get; set; }
public string PersonSequenceNumber { get; set; }
public Person HeadOfHouseHold { get; set; }
public string HeadOfHouseholdForeignKey
{
get
{
// Head of household always has '01' as PersonSequenceNumber in DB, other members increment from 01 to 02, 03, etc
if (PersonSequenceNumber != "01")
{
return FamilyName + "01";
}
else
{
return null;
}
}
}
public string HeadOfHouseholdPrincipalKey
{
get
{
return FamilyName + PersonSequenceNumber;
}
}
}
}
It is not possible to have relation in EF Core, which is not defined using database beneath. You are trying to use foreign key by using read-only property and this is not supported.
If You simply want to .Include() Person
table, You maybe could use composite key:
.HasForeignKey(p => new { p.FamilyName, p.PersonSequenceNumber) });
See examples at: EF Core Relations #Foreign key and EF Core Relations #Principal key
PS. I suppose principal key should be from referenced table, not current:
.HasPrincipalKey(parent => parent.HeadOfHouseholdPrincipalKey);