告诉实体框架为这个外键使用正确的名称

Question

编辑:我可能应该包括Email, Address和Phone实体:

Public Class AddressEntity
    Inherits AddressComplexEntity
    <IgnoreForCompare()>
    Public Property AddressId() As Guid
    Public Sub New()
        AddressId = Guid.NewGuid()
    End Sub
End Class
Public Class AddressComplexEntity
    Inherits BaseEntity(Of AddressComplexEntity)
    Public Property Street() As String
    Public Property Suite() As String
    Public Property City() As String
    Public Property State() As String
    <MaxStringLength(10)> _
    Public Property ZipCode() As String
    Public Property Country() As String
End Class

---

Public Class PhoneEntity
    Inherits BaseEntity(Of PhoneEntity)
    Public Sub New()
        _PhoneId = Guid.NewGuid
    End Sub
    Public Property PhoneTypeId() As Integer
    Public Property PhoneType() As PhoneType
    Public Property AreaCode() As Integer
    Public Property Prefix() As Integer
    Public Property LineNumber() As Integer
    Public Property PhoneId() As Guid
End Class

---

Public Class EmailEntity
    Inherits BaseEntity(Of EmailEntity)
    Public Sub New()
        EmailId = Guid.NewGuid
    End Sub
    Public Sub New(ByVal Email As String)
        Me.New()
        Me.Email = Email
    End Sub
    Public Property EmailId As Guid
    Public Property PersonId As Guid
    Public Property Person As PersonEntity
    Public Property Email As String
End Class

---

我有以下表格:

CREATE TABLE [dbo].[Person](
    [PersonId] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](255) NULL,
    [MiddleName] [nvarchar](255) NULL,
    [LastName] [nvarchar](255) NULL,
    [DateOfBirth] [datetime] NULL,
    [DateOfBirthIsGuess] [bit] NULL,
    [NickName] [nvarchar](255) NULL,
    [Suffix] [nchar](10) NULL,
    [Gender] [char](1) NULL,
        [SSN] [numeric](9, 0) NULL,
 CONSTRAINT [PK__Person__04E4BC85] PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)
    )

---

CREATE TABLE [dbo].[Patient](
    [PatientId] [uniqueidentifier] NOT NULL,
    [CenterNumber] [nvarchar](10) NOT NULL,
    [GuarantorNumber] [nvarchar](10) NOT NULL,
    [DependantNumber] [nvarchar](10) NOT NULL,
    [GuarantorId] [uniqueidentifier] NULL,
    [PatientInsurance] [varchar](50) NULL,
    [PrimaryCarePhysician] [varchar](255) NULL,
    [PatientFirstIsPrimaryCarePhysician] [bit] NOT NULL,
 CONSTRAINT [PK__Patient__47DBAE45] PRIMARY KEY NONCLUSTERED 
(
    [PatientId] ASC
))

---

CREATE TABLE [dbo].[Guarantor](
    [GuarantorId] [uniqueidentifier] NOT NULL,
    [CenterNumber] [nvarchar](10) NOT NULL,
    [GuarantorNumber] [nvarchar](10) NOT NULL,
    [IndustrialClientNumber] [int] NULL,
    [Employer] [varchar](4000) NULL,
 CONSTRAINT [PK__Guarantor__44FF419A] PRIMARY KEY CLUSTERED 
(
    [GuarantorId] ASC
))

---

Public Class PersonEntity
    Public Sub New()
        Me.PersonId = Guid.NewGuid
    End Sub
    Public Property PersonId() As Guid
    Public Property Address() As ICollection(Of AddressEntity)
    Public Property Email() As ICollection(Of EmailEntity)
    Public Property Phone() As ICollection(Of PhoneEntity)
    Public Property FirstName() As String
    Public Property MiddleName() As String
    Public Property LastName() As String
    Public Property Suffix() As String
    Public Property DateOfBirth() As Nullable(Of Date)
    Public Property Gender() As Char
    Public Property Ssn() As Nullable(Of Integer)
End Class

---

Public Class PatientEntity
    Inherits Person.PersonEntity

    Public Property Visits() As IList(Of VisitEntity)
    Public Property GuarantorId() As Guid
    Public Property Guarantor() As GuarantorEntity
    Public Property Center() As CenterEntity
    Public Property DependantNumber() As String
    Public Property PatientInsurance() As String
    Public Property PrimaryCarePhysician() As String
    Public Property PatientFirstIsPrimaryCarePhysician() As Boolean
    Public Property GuarantorNumber() As String
    Public Sub New()
        MyBase.New()
        _Visits = New List(Of VisitEntity)
    End Sub
End Class

---

Public Class GuarantorEntity
    Inherits Person.PersonEntity
    Public Sub New()
    End Sub
    Public Property CenterNumber() As String
    Public Property GuarantorNumber() As String
    Public Property CenterGuarantorNumber() As String
    Public Property IndustrialClientNumber() As Integer
    Public Property Employer() As String
End Class

---

下面是我的dbcontext的设置:公共类MyRepository: DbContext{public MyRepository(): this("PatientFirst") {}

    public MyRepository(string connectionNameOrString)
        : base(connectionNameOrString)
    {
        //don't want to create or update the database
        Database.SetInitializer<MyRepository>(null);
    }
    public DbSet<GuarantorEntity> Guarantors { get; set; }
    public DbSet<PersonEntity> People { get; set; }
    public DbSet<PatientEntity> Patients { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new VisitEntityConfiguration());
        modelBuilder.Configurations.Add(new PersonEntityConfiguration());
        modelBuilder.Configurations.Add(new GuarantorEntityConfiguration());
        modelBuilder.Configurations.Add(new PatientEntityConfiguration());
        modelBuilder.Configurations.Add(new AddressEntityConfiguration());
        modelBuilder.Configurations.Add(new EmailEntityConfiguration());
        modelBuilder.Configurations.Add(new PhoneEntityConfiguration());
    }
}
public class VisitEntityConfiguration : EntityTypeConfiguration<VisitEntity>
{
    public VisitEntityConfiguration()
    {
        ToTable("vVisit");
        HasKey(v => v.VisitId);
    }
}
public class PersonEntityConfiguration : EntityTypeConfiguration<PersonEntity>
{
    public PersonEntityConfiguration()
    {
        ToTable("vPerson");
        HasKey(p => p.PersonId);
        HasMany(p => p.Address)
            .WithMany()
            .Map(m =>
                     {
                         m.MapLeftKey("PersonId");
                         m.MapRightKey("AddressId");
                         m.ToTable("vPersonAddress");
                     });
        HasMany(p => p.Phone)
            .WithMany()
            .Map(m =>
                     {
                         m.MapLeftKey("PersonId");
                         m.MapRightKey("PhoneId");
                         m.ToTable("vPersonPhone");
                     });
        HasMany(p => p.Email)
            .WithRequired()
            .HasForeignKey(e => e.PersonId);
    }
}
public class PatientEntityConfiguration : EntityTypeConfiguration<PatientEntity>
{
    public PatientEntityConfiguration()
    {
        ToTable("vPatient");
        HasKey(p => p.PersonId);
        Ignore(p => p.Guarantor);
        Ignore(p => p.Center);
        //HasOptional(p => p.Guarantor);
    }
}
public class GuarantorEntityConfiguration : EntityTypeConfiguration<GuarantorEntity>
{
    public GuarantorEntityConfiguration()
    {
        ToTable("vGuarantor");
        HasKey(g => g.PersonId);
        Ignore(g=>g.CenterGuarantorNumber);
    }
}
public class AddressEntityConfiguration : EntityTypeConfiguration<AddressEntity>
{
    public AddressEntityConfiguration()
    {
        ToTable("vAddress");
        HasKey(a => a.AddressId);
    }
}
public class EmailEntityConfiguration : EntityTypeConfiguration<EmailEntity>
{
    public EmailEntityConfiguration()
    {
        ToTable("vEmail");
        HasKey(e => e.EmailId);
    }
}
public class PhoneEntityConfiguration : EntityTypeConfiguration<PhoneEntity>
{
    public PhoneEntityConfiguration()
    {
        ToTable("vPhone");
        HasKey(p => p.PhoneId);
        Ignore(p=>p.PhoneType);
    }
}

---

在我的控制器担保，如果我只是对dbset做正常查询，不包括Email属性，我得到xml返回，我希望，但当我在查询中包含Email属性，它给了我一个sql错误:

public GuarantorEntity GetGuarantorEntity(Guid id)
{
    GuarantorEntity guarantorEntity = db.Guarantors
        .Include(p => p.Address)
        .Include(p => p.Phone)
        .Include(p => p.Email) //If you comment this line out everything works, but of course you don't get email object populated
        .FirstOrDefault(r => r.PersonId == id);
    if (guarantorEntity == null)
    {
        throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
    }
    return guarantorEntity;
}

这是我得到的错误(减去堆栈跟踪，只是告诉我SQL抛出错误):

<Error><Message>An error has occurred.</Message><ExceptionMessage>Invalid column name 'Person_PersonId'.</ExceptionMessage><ExceptionType>System.Data.SqlClient.SqlException</ExceptionType><StackTrace></StackTrace></Error>

包含Email的

由于某种原因试图调用FK列"Person_PersonId"，为什么它要使用这个而不是在Email对象上调用"PersonId"呢?我如何让它使用"PersonId"为FK而不是"Person_PersonId"?

Answer 1

这个映射…

HasMany(p => p.Email)
    .WithRequired()
    .HasForeignKey(e => e.PersonId);

…是不正确的。你必须使用:

HasMany(p => p.Email)
    .WithRequired(e => e.Person)
    .HasForeignKey(e => e.PersonId);

如果你没有在WithRequired实体框架中提供逆属性，则假定Email.Person导航属性是Person和Email之间的另一个第二个关系的一部分。这就是EF在查询中使用第二个外键Person_PersonId的原因。