在EF4中首先使用CTP4码作为TPH鉴别器的外键

Question

总结一下我的模型：

• 执照和证书是合格的子女。
• 一个资格证书只有一个职业。
• 专业要么是领有执照的种类(第一类)，要么是经认证的类别(第二类)。

Requirement：表示业务实体之间的关系，而不将冗余引入数据库模式。资格类型(执照/证书)必须与职业类型相匹配。

以下是我目前的简化模型--我解释了为什么这个模型在下面不起作用：

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

以下是简化的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")

这不起作用的原因是EF4 不允许 FK属性可以作为TPH鉴别器加倍。这意味着Type不能同时是一个鉴别器和一个外键字段。尝试为每个实体在HasConstraint方法中输入硬代码职业类型也不起作用--这会产生异常。

一种可能的解决方案是在职业中添加一个代理键，去掉资格中的Type，并将其替换为ProfessionId FK。这将消除冗余的担忧，但也会破坏TPH。实际上，歧视者从资格转移到职业。这里的问题是，我还没有找到映射许可证和证书对象的方法。也许我可以把地图换成视图？但我如何在代码中首先做到这一点呢？

所以，现在我面临着许多令人讨厌的选择。有什么建议吗？

Answer 1

通过将其更改为以下模型，我成功地使其工作：

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");
    }
}

但是，如您所知，ProfessionType在资格方面是多余的，并且没有办法解决它，因为正如您所说的，EF不允许您将一个区分器重用为FK，这在这条规则中是有意义的：

专业要么是领有执照的种类(第一类)，要么是经认证的类别(第二类)。

是EF没有意识到的东西，因此它必须阻止它，以保护等级。

就我个人而言，我将按以下方式设计对象模型，我认为它更清晰，也更不多余：

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");
    }
}

它将导致DB中的下列架构：

​

另一种避免干燥的方法是将层次结构改为TPT，而不是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");
    }
}

它将导致DB中的下列架构：

​

​