Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mapping foreign key in HasOptional().WithOptionalDependent() relation in Entity Framework 6

I have the following data-model in Entity Framework 6.1.3:

using System.Data.Entity;

public class Student
{
    public int Id { get; set; }
    public virtual Contact Contact { get; set; }
}

public class Contact
{
    public int Id { get; set; }
    public virtual Student Student { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder builder)
    {
        builder.Entity<Contact>()
            .HasOptional(x => x.Student)
            .WithOptionalDependent(x => x.Contact)
            .WillCascadeOnDelete(true);
    }
}

public static class Program
{
    private static void Main()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());

        using (var context = new MyContext())
            context.Database.Initialize(force: true);
    }
}

When I launch this code, I get exactly the right table structure I am aiming for:

dbo.Contacts
    Id (PK)
    Student_Id (FK, NULL, CASCADE ON DELETE)

dbo.Students
    Id (PK)

However, now I would like to add the Student_Id property to be available in the Contact entity. So I can read the Student_Id without needing to join the other table through .Student.Id navigation.

If I add the property to the Contact entity, I end up either with two columns Student_Id and Student_Id1, or I end up with an error message saying Each property name in a type must be unique..

The column is already in the database, all I need is to have it in the entity as well, why is it so much trouble? Is there a solution?

like image 470
Tom Pažourek Avatar asked Aug 31 '15 14:08

Tom Pažourek


People also ask

How do I add a foreign key to EF core?

The [ForeignKey(name)] attribute can be applied in three ways: [ForeignKey(NavigationPropertyName)] on the foreign key scalar property in the dependent entity. [ForeignKey(ForeignKeyPropertyName)] on the related reference navigation property in the dependent entity.

How do I add a foreign key in fluent API?

You can then configure foreign key properties by using the HasForeignKey method. This method takes a lambda expression that represents the property to be used as the foreign key.

How do you use a foreign key in code first approach?

To create Foreign Key, you need to use ForeignKey attribute with specifying the name of the property as parameter. You also need to specify the name of the table which is going to participate in relationship. I mean to say, define the foreign key table. Thanks for reading this article, hope you enjoyed it.

What is fluent API .NET core?

Entity Framework Fluent API is used to configure domain classes to override conventions. EF Fluent API is based on a Fluent API design pattern (a.k.a Fluent Interface) where the result is formulated by method chaining. In Entity Framework Core, the ModelBuilder class acts as a Fluent API.


2 Answers

I managed to get a response from the Entity Framework Program Manager after asking on GitHub.

Unfortunately this is a limitation of EF6. You can not have a foreign key property in a one-to-one relationship, unless it is also the primary key property. This is essentially because EF6 doesn't support alternate keys/unique indexes, so you can't enforce that a non-primary key property is unique. The fact that you can do it when the foreign key property isn't in the entity is a bit of a quirk... but obviously not something we would remove 😄.

BTW alternate keys (and therefore this scenario) is supported in EF Core.

– Rowan Miller @ https://github.com/aspnet/EntityFramework6/issues/159#issuecomment-274889438

like image 52
Tom Pažourek Avatar answered Sep 22 '22 05:09

Tom Pažourek


If you want to declare the FK property in the dependent entity in an one to one relationship, I'm afraid you must use it as a PK too. EF Code First requires that PK of the dependent entity must be FK of the relationship too:

public class Contact
{
    [Key,ForeignKey("Student")]
    public int StudentId { get; set; }
    public virtual Student Student { get; set; }
}

But I think this is not what you are looking for. So, I think you have three options here:

  • You preserve your current relationship configuration.
  • Create an authentic one to one relationship.
  • Create an one to many relationship

By my experience the last one is the most adjusted to what are you trying to achieve (but that is my opinion). In this case you can work with the Fk property as you want, the only is you need to change the Contact navigation property on Student by a collection (or omit this nav. property and create an unidirectional relationship):

public class Student
{
    public int Id { get; set; }
    public virtual ICollection<Contact> Contacts { get; set; }
}

The configuration would be this way:

 builder.Entity<Contact>()
        .HasOptional(x => x.Student)
        .WithMany(x => x.Contacts)
        .HasForeignKey(x => x.StudentId)
        .WillCascadeOnDelete(true);

Update

A fourth option could be create two unidirectional relationships:

 builder.Entity<Contact>()
        .HasOptional(x => x.Student)
        .WithMany()
        .HasForeignKey(x => x.StudentId)
        .WillCascadeOnDelete(true);

 builder.Entity<Student>()
        .HasOptional(x => x.Contact)
        .WithMany()
        .HasForeignKey(x => x.ContactId)
        .WillCascadeOnDelete(true);

But this option breaks the real relation between the two tables.

like image 31
octavioccl Avatar answered Sep 20 '22 05:09

octavioccl