MVC EF Code First one to one relationship error

Question

I want to have a list of stands (at a trade show) and a list of exhibitors.

The list of stands is separate to the list of exhibitors - however, once registered, I want the exhibitor to be able to book a stand.

When they select/book a stand - I would like to then be able to have a list the stands in my view, and also show the associated exhibitor who has booked it.

Likewise, I would like to list in another view, the exhibitors, and also which stand they have booked.

So I'm trying to setup a one to one relationship (using EF CodeFirst).

However, when trying to add a controller for either the Stand or the Exhibitor, I get the following error:

My models are:

 public class Stand
{
    public int StandID { get; set; }
    public string Description { get; set; }
    public bool Booked { get; set; }
    public int ExhibitorID { get; set; }
    public virtual Exhibitor Exhibitor { get; set; }

}

 public class Exhibitor
{
    public int ExhibitorID { get; set; }
    public string Company { get; set; }
    public int StandID { get; set; }
    public virtual Stand Stand { get; set; }

}

I'm certain it's something to do with the "Virtual" part of the models.

Can anyone please help point out what should be updated, to allow the connection?

Thank you,

Mark

Answer 1

EF doesn't know which entity is the principal (parent) and which is the dependent (child). You need to declare a foreign key on the item that entity that should come first. You can do this with an annotation or a fluent mapping.

Annotation

Add the following namespace:

using System.ComponentModel.DataAnnotations.Schema;

Annotate your Stand class with the following annotation:

public class Stand
{
    [ForeignKey("Exhibitor")]
    public int StandID { get; set; }
    public string Description { get; set; }
    public bool Booked { get; set; }
    public int ExhibitorID { get; set; }
    public virtual Exhibitor Exhibitor { get; set; }

}

Fluent Mapping

Override your OnModelCreating method in your DbContext class to include:

modelBuilder.Entity<Stand>()
    .HasOptional(s => s.Exhibitor)
    .WithRequired(e => e.Stand);

Answer 2

The model you have created is not possible to work with relational databases. The Stand needs an ExibitorId while Exibitor need a StandId. The cyclic relationship does not allow you to insert any rows to either tables.

Assuming an Exibitor may have more than one Stand and converting the relationship to one-to-many is one option.

public class Stand
{
    public int StandID { get; set; }
    public string Description { get; set; }
    public bool Booked { get; set; }
    public int? ExhibitorID { get; set; }
    public virtual Exhibitor Exhibitor { get; set; }    
}

public class Exhibitor
{
    public int ExhibitorID { get; set; }
    public string Company { get; set; }
    public virtual ICollection<Stand> Stands { get; set; }
}

Or you can use shared primary key mapping to make the relationship one-to-one. Where Stand is the principal entity. The Exibitor will use the StandID as its PK.

public class Stand
{
    public int StandID { get; set; }
    public string Description { get; set; }
    public bool Booked { get; set; }
    public virtual Exhibitor Exhibitor { get; set; }
}

public class Exhibitor
{
    public int ExhibitorID { get; set; }
    public string Company { get; set; }
    public virtual Stand Stand { get; set; }
}

Using the Fluent API to configure the relationship.

modelBuilder.Entity<Exibitor>().HasRequired(e => e.Stand)
    .WithOptional(s => s.Exibitor);