Correct way of mapping a 'one to many' relationship. When having the same relation in multiple entities

Question

Suppose the following structure of classes and relationships:

class Document
{
    public List<Version> DocumentVersions { get; set; }
    // Other properties
}

class Register
{
    public List<Version> RegisterVersions { get; set; }
    // Other properties
}

class Version
{
    public int VersionNumber { get; set; }
    // Other properties
}

When using EF Core, it is going to produce 3 tables, D, R and V respectively where V is going to have 2 FK, one for D and one for R.

My questions are:

• Is EF Core default approach correct? Wouldn't it lead to invalid states where V has no FKs because both FKs can be nullable.
• I've read this and it almost answered my first question but it leads me to another question:
  • How can I tell EF to follow that approach: Should I have to create a derived type of V for each of its owners? or is there any way I can map a single entity to multiple tables and tell EF which relationships belong to which table?

Maybe is worth mention that my example is oversimplified and in reality I have 6 entities using the same V entity.

Answer 1

So, the dilemma is:

A) Should I keep two FKs in Version or

B) build two tables DocumentVersion and RegisterVersion instead of just Version?

Well, the truth is you can do both. You just have to decide which approach suits your system better. Let's have a quick look.

Approach A

To answer your question; yes EF's default approach is correct. Among creating two FKs and building two tables, it will create two FKs. It will create an extra table only in case of intermediate table for a many to many relashionship.

I always, though, recommend that we create all FKs ourselves instead of letting EF do it for us. This way we have more control over the behavior of the relationship and can also access the FKs in our application, since they are an entity's property.

public class Version
{
    [Key]
    public int VersionNumber { get; set; }

    public int? DocumentID { get; set; }
    public virtual Document Document { get; set; }

    public int? RegisterID { get; set; }
    public virtual Register Register { get; set; }

    //Other properties
}

Since Version has a PK, it can create records without any of the FKs having any value. If this is allowed in your business model then leave it as it is. You can later provide a UI to assign "Versions" to either "Documents" or "Registers".

If you want to add some rules in your Version table; for example each record should have at least one FK or only one FK, you can do that by overriding the ValidateEntity method of your DbContext class (or through some sql constraint in the database probably).

    protected override DbEntityValidationResult ValidateEntity(
        DbEntityEntry entityEntry, IDictionary<object, object> items)
    {
        // validate entity from annotations
        var result = base.ValidateEntity(entityEntry, items);

        // custom validation rules
        if (entityEntry.Entity is Version && 
            (entityEntry.State == EntityState.Added || entityEntry.State == EntityState.Modified))
        {
            Version version = ((Version)entityEntry.Entity);
            if (version.DocumentID == null && version.RegisterID == null)
                result.ValidationErrors.Add(new DbValidationError(null, "A document or register must be specified."));
        }

        return result;
    }

Note that you can create your own annotations to validate your entity properties. But these are restricted to a single property. If you want to add validations that combine more than one property, the ValidateEntity method is the only way I know of.

Approach B

There are two ways to implement this approach. The first is to keep the Version table and add two intermediate tables on top.

public class Document
{
    public virtual List<DocumentVersion>  Versions { get; set; }
    // other properties
}

public class Register
{
    public virtual List<RegisterVersion> Versions { get; set; }
    // other properties
}

public class Version
{
    [Key]
    public int VersionNumber { get; set; }

    //Other properties
}

public class DocumentVersion
{
    public int DocumentID { get; set; }
    public virtual Document Document { get; set; }

    public int VersionID { get; set; }
    public virtual Version Version { get; set; }

    // other properties
}

public class RegisterVersion
{
    public int RegisterID { get; set; }
    public virtual Register Register { get; set; }

    public int VersionID { get; set; }
    public virtual Version Version { get; set; }

    // other properties
}

This actualy allows a many-to-many relationship, but you can use it as a one-to-many. The second way is to make Version abstract (not a database table) and build two new tables to inherit from Version:

public class Document
{
    public virtual List<DocumentVersion>  Versions { get; set; }
    // other properties
}

public class Register
{
    public virtual List<RegisterVersion> Versions { get; set; }
    // other properties
}

// don't make this a DbSet
public abstract class Version
{
    [Key]
    public int VersionNumber { get; set; }

    //Other properties
}

public class DocumentVersion : Version
{
    public int DocumentID { get; set; }
    public virtual Document Document { get; set; }

    // other properties
}

public class RegisterVersion : Version
{
    public int RegisterID { get; set; }
    public virtual Register Register { get; set; }}

    // other properties
}

This is a proper and clear one-to-many relationship.

Conclusion

The bottom line is that you can use any of the two approaches and with alterations that suit your needs.

I have used both approaches successfully, but I tend to prefer the second one (and with the abstract class inheritance). The first approach seems more of a way to cut down on database resources or ease of development, but modern databases are not at all stressed by a few tables more and the development could become unnecessarily complex. Further more the second approach allows to extend the functionality of the relationships by adding further properties to each connection table separately. And for the 6 entities you have to deal with, it seems safer to me to go with the second approach. I have used this approach in an application with many file types and relationships and it was always very straight-forward and extendable. Those extra properties in each relation table came very handy too.

Hope I could help,

merry coding!