CODEWITHSUNDEEP
c#wpfentity-frameworkef-code-first
mcalex
mcalex

Reputation: 6798

Foreign Key issue with Code first from Existing Database

tl;dr Do I need to have a foreign key id field as a property in the related class with EF code-first?

Following ScottGu's advice, i have created a model to reflect an existing database. Two of the tables in the db are: Project and ProjectType with a foreign key relationship. Each Project has a ProjectType.

I have added the necessary virtual fields to the model classes to reflect the relationship:

public class Project {
  public int ProjectID { get; set; }
  public string ProjectName { get; set; }
  public DateTime CreationDate { get; set; }
  ...
  public virtual ProjectType ProjectType {get; set; }
  ...
}

public class ProjectType {
  public int ProjectTypeID { get; set; }
  ...
  public virtual ICollection<Project> Projects { get; set;}
  ...
}

According to Scott (as shown in the image below), there is no need for the actual (foreign key) ID field to be exposed in the dependent class, ie, I don't need a public int ProjectTypeID { get; set; } property in the Project class.

Model definition

However, when I try a call to retrieve the data, I hit an EntityCommandExecutionException with an InnerException of: Invalid column name 'ProjectType_ProjectTypeID'

Initial googling suggested adding a [ForeignKey] annotation to the ProjectType property. I tried both [ForeignKey("ProjectType")] and [ForeignKey("ProjectTypeID")] but neither worked.

Further googling suggested using FluentAPI with a call to:

modelBuilder.Entity<Project>().HasRequired<ProjectType>(p => p.ProjectType)
                              .WithMany(pt => pt.Projects)

in an OnModelCreating method, but this falls over with the same Invalid column name error.

So, do I need to have the ProjectTypeID field as a property in the Project class? If not, how do I tell EF to use the ProjectTypeID as the foreign key?

Upvotes: 2

Views: 1078

Answers (1)

Alaa Masoud
Alaa Masoud

Reputation: 7135

What is the foreign key column name in the existing database? You don't need to add a foreign key field but you do need to configure your modelBuilder so that foreign key names match.

modelBuilder.Entity<Project>()
            .HasRequired<ProjectType>(p => p.ProjectType)
            .WithMany(pt => pt.Projects)
            .Map(p => p.MapKey("FK_NAME_IN_EXISTING_DB"));

You can also choose the option to have EF generate code first from database.

Upvotes: 2

Related Questions