Create Foreign Key using Data Annotations

Question

In the code below, I need to set a foreign key constrant on ParentInfoAddProperties.ParentQuestionAnswersId so that it is dependent on ParentQuestionAnswers.Id (which is a Primary Key). I am attempting to do so using data annotations but Entity Framework 6 wants to create a new Foreign Key column in my ParentQuestionAnswers table which references the ParentInfoAddProperties.Id column not the ParentInfoAddProperties.ParentQuestionAnswersId column. I do not want Entity Framework to create a new foreign key column.

I'd greatly appreciate if someone can explain what data annotations or (if necessary) fluent mappings I should specify to achieve the desired foreign key constrant. Thanks in advance.

namespace Project.Domain.Entities
{  
    public class ParentQuestionAnswers
    {
        public ParentQuestionAnswers()
        {
            ParentInfoAddProperties = new ParentInfoAddProperties();
        }

        [Required]
        public int Id { get; set; }

        [Required]
        public int UserId { get; set; }

        public ParentInfoAddProperties ParentInfoAddProperties { get; set; }
    }

    public class ParentInfoAddProperties
    {
        [Required]
        public int Id { get; set; }

        [Required]
        public int ParentQuestionAnswersId { get; set; }
    }
}

Solution

You could use the following data annotation, and use entity instead of int

[Required]
[ForeignKey("ParentQuestionAnswers")]
public ParentQuestionAnswers ParentQuestionAnswers { get; set; }

to get the Id only you could add the property

public int ParentQuestionAnswersId { get; set; }

but you still need the ParentQuestionAnswers property so EF will understand you .

(these code rows should be under the ParentInfoAddProperties class)

Comments

[gemini6609]

I added the ParentQuestionsAnswers entity property to my ParentInfoAddProperties class but this results in the error "The ForeignKeyAttribute on property 'ParentQuestionsAnswers' on type 'CPN.Domain.Entities.ParentInfoAddProperties' is not valid. The foreign key name 'ParentQuestionsAnswers' was not found on the dependent type 'CPN.Domain.Entities.ParentInfoAddProperties'. The Name value should be a comma separated list of foreign key property names." Any further advise?

[jony89]

try [Required, ForeignKey("ParentQuestionAnswers")] and you don't have ParentQuestionsAnswers, this should be ParentQuestionAnswers

[gemini6609]

I think I need to entirely reset the dbContext, so that I completely rebuild my database from the defined entities?

[gemini6609]

Furthermore, an additional foreign key constraint is unintentionally built which maps the Id of the ParentInfoAddProperties table to the ParentQuestionAnswers table. Any suggestions, anyone? Thanks in advance

[jony89]

as for your database, you could delete it and run your program again or use DropCreateDatabaseAlways(google it) until you get your entities right. as for the additional foreign key there should be only one unless you have added some more DataAnnotations ? please update your code in your question .

[gemini6609]

Your last reply roughly described what I found my issue to be. Adding the entity property ParentInfoAddProperties to the ParentQuestionAnswers was causing EF 6.1 to build undesired foreign keys in my database. Removing that entity property and defining the correct one in the ParentInfoAddProperties class built the correct foreign key. However, I still need entity property ParentInfoAddProperties in the ParentQuestionAnswers class. Is there any way to exclude a property from Entity Framework control? Due to having hundreds of entities I want to use these classes as a view model. Thanks

[gemini6609]

I was able to exclude the property from Entity Framework which was causing the incorrect foreign key to be built by using [NotMapped] data annotation on the property, so this issue is resolved.

[jony89]

it's not recommended to use the Entities as your ViewModel. what will you do with fields such as passwords? just not display them? (then in the post method - once you add an entity, you need to "know" that) but thats a different issue.