Im having indirection issues when trying to configure the Fluent API for a property that has a single one-to-one relationship as well as a one-to-many relationship with the same entity. For example:
public class Person
{
public int Id { get; set; }
public int PrimaryNameId { get; set; }
public NameInfo PrimaryName { get; set; } // one-to-one
public IList<NameInfo> Names { get; set; } // one-to-many
}
public class NameInfo
{
public int Id { get; set; }
public int PersonId { get; set; }
public Person Person { get; set; }
public string Name { get; set; }
...
}
I am trying to capture the relationship where a person can have many names but in most cases, I'm only interested in their "PrimaryName". I would prefer to not have a hanging column on the NameInfo table for IsPrimary. That column will end up being NULL/FALSE for >90% of records.
When I tried to manually configure Fluent API for this
**Configuration on Person Entity**
builder.HasOne(c => c.PrimaryName)
.WithOne()
.HasForeignKey<Person>(c => c.PrimaryNameId)
.OnDelete(DeleteBehavior.Restrict)
.IsRequired();
builder.HasMany(c => c.Names)
.WithOne(n => n.Person)
.HasForeignKey(n => n.PersonId);
there seems to exist foreign index key constraints that point to each other such that seeding the database fails. In order to add a NameInfo, I need the PersonId - but in order to add a Person, I need an existing NameInfo Id (for the required PrimaryNameId -> Primary Name).
Relationally you have created a catch-22 which has nothing to do with EF. Table-wise you expect to have:
Person
PersonId [PK Not NULL]
PrimaryNameId [FK Not NULL]
and
Name
NameId [PK Not NULL]
PersonId [FK Not NULL]
Something has to give because you cannot insert one before the other.
The trouble with trying to de-normalize in this manner at an object level, to have "Names" and "PrimaryName" references is that by having a PrimaryNameId in your Person, there is no way to enforce that The Name record pointed by PrimaryName is actually associated to that Person. IsPrimaryName isn't a very reliable option for the same reason, there is no way to enforce that two or more records don't end up with IsPrimaryName = True. You can only make assumptions as much and run data validation checks to guard against invalid combinations after the fact.
When it comes to 1-to-1 relationships, the reason for doing this should be to have a performance benefit. The data pulled out to a 1-to-1 should be infrequently required and/or expensive to retrieve. If a customer must have One set of name values, and can have additional names, then the proper solution would be:
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
// ... additional name-specific fields.
public virtual ICollection<AdditionalName> AdditionalNames { get; set; } // one-to-many
}
Required Name details are not expensive, and they would most likely be used/useful most of the time. There is no benefit in attempting to normalize them out to a NameInfo table simply because it is possible that a Person may have additional names. Rather than attempting to normalize down to a NameInfo table which serves two purposes (a required name, and optional additional names) I would recommend just creating an AdditionalName table for the optional additional names.