Mapping 1 to 0..1 in Entity Framework Code First

Question

I am trying to get 1 to 0..1 mapping to work in Entity Framework Code First, but keep getting the error: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'.

I have a MainLocation with a required Location, but as the Locations can have multiple child Locations, a MainLocation is not required in the Location object.

The MainLocation has a similar relation to MainLocationAddress, but this is a 1 to 1 relation which in turn make up the same db table.

The ER model should look like this:

My entities look like this:

[Table("MainLocation")]
public class MainLocation : IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required]
    public virtual MainLocationAddress Address { get; set; }
    [Required]
    public virtual Location Location { get; set; }
}

[Table("MainLocation")]
public class MainLocationAddress : BaseAddress
{
    [Key, ForeignKey("MainLocation")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public virtual MainLocation MainLocation { get; set; }
}

public class Location : IEntity
{
    public int Id { get; set; }
    public virtual Location ParentLocation { get; set; }
    public virtual ICollection<Location> ChildLocations { get; set; }
    protected virtual MainLocation MainLocation { get; set; }
    internal MainLocation GetMainLocation() { return this.MainLocation; }
    internal void SetMainLocation(MainLocation mainLocation) { MainLocation = mainLocation; }
}

I have configured the associations in OnModelCreating in my DbContext class:

modelBuilder.Entity<MainLocation>()
            .HasRequired(x => x.Location)
            .WithOptional();

modelBuilder.Entity<MainLocation>()
            .HasRequired(x => x.Address)
            .WithRequiredPrincipal();

PS! The MainLocation property on Location is protected because it should not be accessed directly. Instead I have a service-layer which gets the value from the Location or the inherited value from a ParentLocation. I have tried to change it to public, to see if it made any changes regarding the error I'm getting.

Although I was able to extend .WithOptional() to .WithOptional(x => x.MainLocation), there were still no changes in the stated error.

Answer 1

I have achieved a 1:0-1 association between two entities, Person and User. The requirement, a User must have one and only one Person; while a Person may or may not be associated with a User.

public class Person
{
    public int PersonId { get; set; }
    public virtual User User { get; set; }
}

public class User
{
    public int UserId { get; set; }
    public int PersonId { get; set; }
    public virtual Person Person { get; set; }
}

Define EntityTypeConfiguration classes as follows and include them in the DbContext OnModelCreating method.

public class PersonConfiguration : EntityTypeConfiguration<Person>
{
    public PersonConfiguration()
    {
        ToTable("People");
        HasKey(p => p.PersonId);
        Property(p => p.PersonId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        HasOptional(p => p.User).WithRequired(u => u.Person); // User is option here but
                                                              // Person is required in User
    }

}

public class UserConfiguration : EntityTypeConfiguration<User>
{
    public UserConfiguration()
    {

        ToTable("Users");
        HasKey(u => u.UserId);
        Property(u => u.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        // Create a unique index in the Users table on PersonId
        Property(u => u.PersonId).IsRequired().HasColumnAnnotation("Index",
            new IndexAnnotation(new IndexAttribute("IX_PersonId") { IsUnique = true }));

     }

}

Put the following lines in the DbContext.OnModelCreating method.

        modelBuilder.Configurations.Add(new PersonConfiguration());
        modelBuilder.Configurations.Add(new UserConfiguration());

Run an Add-Migration command and you will get something like the following in the DbMigration,Up method. Make changes as follows.

        CreateTable(
            "dbo.Users",
            c => new
                {
                    Id = c.String(nullable: false, maxLength: 128),
                    PersonId = c.Int(nullable: false),
                    Person_PersonId = c.Int(nullable: false), // Delete this
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.People", t => t.Person_PersonId) // change to .ForeignKey("dbo.People", t => t.PersonId)
            .Index(t => t.PersonId, unique: true)  // append a ';'
            .Index(t => t._Person_PersonId); // Delete this

        CreateTable(
            "dbo.People",
            c => new
                {
                    PersonId = c.Int(nullable: false, identity: true),
               })
            .PrimaryKey(t => t.PersonId)

Modify the Down() method as follows.

Change

 DropForeignKey("dbo.Users", "Person_PersonId", "dbo.People");

to

 DropForeignKey("dbo.AppUsers", "PersonId", "dbo.People");

Change

 DropIndex("dbo.AppUsers", new[] { "Person_PersonId" });

to

 DropIndex("dbo.AppUsers", new[] { "PersonId" });

Run the Update-Database command targeting this migration. The resulting Users and People tables will have a one-to-one association on the PersonId foreign key.

Answer 2

This scenario works fine on the Update-Database in that the desired database structure showing a one-to-one relationship between the User and People tables. However, for some reason attempts to query the Users table.

I changed the User class by removing the PersonId property.

public class User
{
    public int UserId { get; set; }
    public virtual Person Person { get; set; }
}

The UserConfiguration class becomes:

public class UserConfiguration : EntityTypeConfiguration<User>
{
    public UserConfiguration()
    {

        ToTable("Users");
        HasKey(u => u.UserId);
        Property(u => u.UserId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

     }

}

The Add-Migration produces: CreateTable( "dbo.Users", c => new { Id = c.String(nullable: false, maxLength: 128), Person_PersonId = c.Int(nullable: false), // Keep this }) .PrimaryKey(t => t.UserId) .ForeignKey("dbo.People", t => t.Person_PersonId) .Index(t => t._Person_PersonId); // Change to .Index(t => t._Person_PersonId, unique: true);

The Update-Database produces User and People table with one-to-one relationship and the framework uses its own generated Person_PersonId column.

Answer 3

I have come to realize that this is not possible to accomplish in EF as it is at the moment (the 1 to 0..1 association).

I have solved it in our solution by letting all the child locations have a reference to the main location. I can still get the top organization by looking for a location with no parent location. So, although it's not quite what we wanted, it doesn't break our business-model.