Entity Framework: One-to-one and one-to-many relationship between same tables

user_5137782 0
Viewed 166

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).

.net-5 entity-framework-core entity-framework .net c#
1 Answers

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.

user_4234970
Related