Foreign key as TPH discriminator in EF4 using CTP4 Code First

Question

To summarize my model:

• License and Certificate are children of Qualification
• A Qualification has one and only one Profession
• A Profession is either a licensed kind (type 1) or a certified kind (type 2)

Requirement: Represent the relationships between business entities without introducing redundancy into database schema. Type of Qualification (license/certificate) must match type of Profession.

Here is my simplified model as it currently stands -- I explain why this doesn't work below:

Public Class Profession
    <Key()>
    <DataMember(Order:=0)>
    Public Property Type As Integer
    <Key()>
    <DataMember(Order:=1)>
    Public Property Code As String

    Public Property Title As String
End Class

Public Class Qualification
    Public Property Id As Integer
    Public Property PersonId As Integer
    Public Property Type As Integer
    Public Property ProfessionCode As String
    Public Overridable Property Person As Person
    Public Overridable Property Profession As Profession
End Class

Public Class License
    Inherits Qualification

    Public Property Number As String        
End Class

Public Class Certificate
    Inherits Qualification

    Public Property IssuerName As String    
End Class

Here's the simplified ModelBuilder:

modelBuilder.Entity(Of Qualification) _
    .Property(Function(q) q.ProfessionCode).IsRequired()

modelBuilder.Entity(Of Qualification) _
    .HasRequired(Of Profession)(Function(q) q.Profession) _
    .HasConstraint(Function(q, p) p.Type = q.Type AndAlso p.Code = q.ProfessionCode)

modelBuilder.Entity(Of Qualification) _
    .MapHierarchy() _
    .Case(Of Qualification)(Function(q) New With {
        q.Id,
        q.PersonId,
        q.ProfessionCode,
        .Type = 0) _
    .Case(Of License)(Function(q) New With {
        q.Number,
        .Type = 1}) _
    .Case(Of Certificate)(Function(q) New With {
        q.IssuerName,
        .Type = 2}) _
    .ToTable("dbo.Qualifications")

The reason this doesn't work is that EF4 does not allow FK properties to double as a TPH discriminator. This means Type cannot be both a discriminator and a foreign key field. Trying to hard-code Profession Type in the HasConstraint method for each entity also doesn't work -- this generates an exception.

A possible solution would be to add a surrogate key to Profession, get rid of Type property in Qualification and replace it with ProfessionId FK. This would remove the redundancy concern, but also destroy TPH. In effect, the discriminator moves from Qualification to Profession. The problem here is that I haven't figured out a way to map License and Certificate objects. Maybe I can map to views instead? But how do I do that in Code First?

So, now I am faced with a number of unsavory choices. Any suggestions?

Answer 1

I managed to make it works by changing it to this model:

public class Profession {    
    [Key][DataMember(Order = 0)]    
    public int Type { get; set; }
    [Key][DataMember(Order = 1)]
    public string Code { get; set; }
    public string Title { get; set; }
}

public class Qualification {
    public int Id { get; set; }               
    [Required]
    public int ProfessionType { get; set; }
    [Required]
    public string ProfessionCode { get; set; }                
    [Required]
    public virtual Profession Profession { get; set; }
}

public class License : Qualification {
    public string Number { get; set; }  
}

public class Certificate : Qualification {
    public string IssuerName { get; set; }
}

class Context : DbContext {
    public DbSet<Qualification> Qualifications { get; set; }
    public DbSet<Profession> Professions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<Qualification>()
            .HasRequired<Profession>(q => q.Profession)
            .HasConstraint((q, p) => q.ProfessionCode == p.Code 
                                     && q.ProfessionType == p.Type);

        modelBuilder.Entity<Qualification>().MapHierarchy()
            .Case<Qualification>(q => new {
                q.ProfessionCode,
                q.ProfessionType,
                q.Id,                    
                Type = 0
            }).Case<License>(q => new {
                q.Number,
                Type = 1
            }).Case<Certificate>(q => new {
                q.IssuerName,
                Type = 2
            }).ToTable("Qualifications");
    }
}

However, as you can tell, ProfessionType is redundant on Qualification, and there is no way to workaround it since like you said, EF would not let you to reuse a discriminator as a FK which make sense since this rule:

A Profession is either a licensed kind (type 1) or a certified kind (type 2)

is something that EF is not aware of therefore it has to prevent it in order to protect the hierachy.

Personally, I would design the object model as follows which I think is more clear and less redundant:

public class Profession {
    public int ProfessionId { get; set; }        
    public int Type { get; set; }
    public string Code { get; set; }
    public string Title { get; set; }
}

public class Qualification {
    public int Id { get; set; }
    public int ProfessionId { get; set; }                
    [Required]
    public virtual Profession Profession { get; set; }
}

public class License : Qualification {
    public string Number { get; set; }  
}

public class Certificate : Qualification {
    public string IssuerName { get; set; }
}

class Context : DbContext {
    public DbSet<Qualification> Qualifications { get; set; }
    public DbSet<Profession> Professions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<Qualification>()
            .HasRequired<Profession>(q => q.Profession)
            .HasConstraint((q, p) => q.ProfessionId == p.ProfessionId);

        modelBuilder.Entity<Qualification>().MapHierarchy()
            .Case<Qualification>(q => new {
                q.ProfessionId,                   
                q.Id,                    
                Type = 0
            })
            .Case<License>(q => new {
                q.Number,
                Type = 1
            })
            .Case<Certificate>(q => new {
                q.IssuerName,
                Type = 2
            })
            .ToTable("Qualifications");
    }
}

Which results to the following schema in DB:

Yet another way to avoid DRY would be to turn the hierarchy to be TPT instead of TPH:

public class Profession {
    [Key]
    [DataMember(Order = 0)]
    public int Type { get; set; }
    [Key]
    [DataMember(Order = 1)]
    public string Code { get; set; }
    public string Title { get; set; }
}

public class Qualification {
    public int Id { get; set; }
    [Required]
    public int ProfessionType { get; set; }
    [Required]
    public string ProfessionCode { get; set; }
    [Required]
    public virtual Profession Profession { get; set; }
}

public class License : Qualification {
    public string Number { get; set; }
}

public class Certificate : Qualification {
    public string IssuerName { get; set; }
}

class Context : DbContext 
{
    public DbSet<Qualification> Qualifications { get; set; }
    public DbSet<Profession> Professions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<Qualification>()
            .HasRequired<Profession>(q => q.Profession)
            .HasConstraint((q, p) => q.ProfessionCode == p.Code
                                     && q.ProfessionType == p.Type);

        modelBuilder.Entity<Qualification>().MapHierarchy(q => new 
        {
            q.Id,
            q.ProfessionCode,
            q.ProfessionType,
        })
        .ToTable("Qualifications");

        modelBuilder.Entity<License>().MapHierarchy(l => new 
        {
            l.Id,
            l.Number
        })
        .ToTable("Licenses");

        modelBuilder.Entity<Certificate>().MapHierarchy(c => new 
        {
            c.Id,
            c.IssuerName
        })
        .ToTable("Certificates");
    }
}

Which results to the following schema in DB: