How to map a 1:1 (optional) mapping in Entity Framework

Question

I have a class like:

public class Employee
{
    [Column("employee_id")]
    public int EmployeId {get;set;}
}


public class Location
{
    [Column("employee_location_id")]
    public int Id {get;set;}

    [Column("employee_id")]
    public int EmployeeId {get;set;}
}

ON the Employee class, I added a virtual property:

public virtual Location Location {get;set;}

I am trying to add an optional property (Lazy loaded), so an employee may have or or 1 Location.

I'm getting an error currently when the mvc application loads now:

System.Data.SqlClient.SqlException: Invalid column name 'Location_Id'.

Did you execute EF migration on your database after the change in model? — Jurica Smircic, May 14 '13 at 19:13
http://stackoverflow.com/questions/6144163/entity-framework-4-1-code-first-navigation-property-not-loading-when-only-the-i — Josh C., May 14 '13 at 19:57

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

Have you tried specifying the FK/Navigation properties explicitly?

public int LocationId { get; set; }
[ForeignKey("LocationId")]
public virtual Location Location { get; set; }

Navigation Property not loading when only the ID of the related object is populated

edited May 23 '17 at 10:32

Community

1
1

answered May 14 '13 at 19:59

Josh C.

4,303
5
30
51

Hi, but the Employee table/model doesn't have the LocationId column. – loyalflow May 15 '13 at 15:08

score 0 · Answer 2 · edited May 23 '17 at 11:55

It is hard to know if you are doing code-first or database/model-first. I will give a working code-first answer (first!). For 1-Many and Many-Many relationships you can do it with annotations, properties etc. But for 1-1 I think you need fluent api as well.

This was also answered in "How do I code an optional one-to-one relationship in EF 4.1 code first with lazy loading and the same primary key on both tables?". The fluent API required is shorter than that answer, I believe.

e.g.

public class ExampleContext : DbContext
{
    public ExampleContext()
        : base("Name=ExampleContext") {
        Configuration.LazyLoadingEnabled = true;
        Configuration.ProxyCreationEnabled = true;
    }

    public DbSet<Employee> Employees { get; set; }
    public DbSet<Location> Locations { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
                .HasOptional(m => m.Location)
                .WithRequired();
    }
}

public class Employee
{
    [Key]
    [Column("employee_id")]
    public int EmployeeId { get; set; }

    public virtual Location Location { get; set; }
}

public class Location
{
    [Key]
    [Column("employee_id")]
    public int EmployeeId { get; set; }
}

EDIT Note the [Key] attributes are not required in this sample to create the migration work, they are just good to convey intent. This is a good reference that talks in more detail about Shared Primary Key Associations

//  Migration class as follows was generated by code-first migrations (add-migration OneToOne) and then updated the database by update-database
public partial class OneToOne : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Employees",
            c => new
                {
                    employee_id = c.Int(nullable: false, identity: true),
                })
            .PrimaryKey(t => t.employee_id);

        CreateTable(
            "dbo.Locations",
            c => new
                {
                    employee_id = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.employee_id)
            .ForeignKey("dbo.Employees", t => t.employee_id)
            .Index(t => t.employee_id);

    }

    public override void Down()
    {
        DropIndex("dbo.Locations", new[] { "employee_id" });
        DropForeignKey("dbo.Locations", "employee_id", "dbo.Employees");
        DropTable("dbo.Locations");
        DropTable("dbo.Employees");
    }
}

Example of use:

using (ExampleContext db = new ExampleContext())
{
    var newEmployee = db.Employees.Add(new Employee() { /* insert properties here */ });
    db.SaveChanges();

    db.Locations.Add(new Location() { EmployeeId = newEmployee.EmployeeId /* insert properties here */ });
    db.SaveChanges();

    var employee1 = db.Employees.First();
    var employee1Location = employee1.Location;
}

hi, thanks, but the Locations table's primary key isn't employeeId, it is LocationId. It has a FK column of employeeId. — loyalflow, May 15 '13 at 15:10
@user1361315. In a 1 to 0-1 relationship you wouldn't usually create a different primary key identity column in the 0-1 side of the relationship. Is there any reason you are doing that? See: [Defining a one-to-one relationship in SQL Server](http://stackoverflow.com/a/1723519/1945631) and then let me know if you want me to update the code for you. — Andy Brown, May 15 '13 at 16:38
@user1361315. And if all you want to do is change the name of the column in `Location`, then just do that and re-run the migration, it will still work just with the different names `[Column("employee_id")] public int EmployeeId { get; set; }` -> `[Column("employee_location_id")] public int LocationId { get; set; }` — Andy Brown, May 15 '13 at 17:29

How to map a 1:1 (optional) mapping in Entity Framework

2 Answers2