Tell Entity Framework to use the correct name for this Foreign Key

Question

Edit: I should probably include the Email, Address, and Phone entities:

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

---

I have the following tables:

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

---

Here is the setup for my dbcontext: public class 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);
    }
}

---

In my controller for guarantors, if I just do normal query against dbset, without including the Email property I get xml returned that I would expect, but when I include the email property in the query it gives me a sql error:

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

Here is the error I am getting (minus the stacktrace that just tells me that SQL is throwing the error):

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

with the Email included it is for some reason trying to call the FK column "Person_PersonId", why would it use this instead of just "PersonId" as it is called on the Email object. How do I get it to use "PersonId" for the FK instead of "Person_PersonId"?

Answer 1

This mapping ...

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

...is incorrect. You must use:

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

If you don't supply the inverse property in WithRequired Entity Framework assumes that the Email.Person navigation property is part of another second relationship between Person and Email. This is the reason for the second foreign key Person_PersonId EF is using in the query.