Entity Framework 1:1 relationship Code First

Question

I'm struggling here. I've tried through data annotations and via the Fluent API and still not working correctly. Desperate for help now. Basically, I have two tables. A Company table and an Address Table. A company must have a head office address (which should live in the Address Table) and an Address must have a Company which is belongs too. I'm really struggling to set this up correctly.

I'll put the Code First Entities then show what I have already got.

[Table("Address")]
public class Address
{
    [Key]
    public long AddressId { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Address3 { get; set; }
    public string Address4 { get; set; }
    public string Address5 { get; set; }
    public string Town { get; set; }
    public string County { get; set; }
    public string Country { get; set; }
    public string PostCode { get; set; }
    public virtual Company Company { get; set; }
    public DateTime? RemovedDate { get; set; }
    public long? RemovedBy { get; set; }
}

[Table("Company")]
public class Company
{
    [Key ]
    public long CompanyId { get; set; }
    public string Name { get; set; }
    public string WebsiteUrl { get; set; }
    public virtual Address Address { get; set; }
    public User LeadUser { get; set; }
    public DateTime ActiveSince { get; set; }
    public DateTime? ActiveTill { get; set; }
    public string VatRegistration { get; set; }
    public string LicenseKey { get; set; }
    public LicenseStatus LicenseStatus { get; set; }
    public bool CanAgreementBeExtended { get; set; }
    public string BillingEmail { get; set; }
    public string PhoneNumber { get; set; }
    public string MobileNumber { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime DateUpdated { get; set; }
    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<LicenseHistory> LicenseHistories { get; set; }
}

//Seeded data inserted as follows
 var testCompany = new Company
                          {
                              ActiveSince = DateTime.UtcNow,
                              Name = "Test Company",
                              LeadUser = adminUser,
                              DateCreated = DateTime.UtcNow,
                              DateUpdated = DateTime.UtcNow,
                              BillingEmail = "admin@test.co.uk",
                              CanAgreementBeExtended = true,
                              LicenseStatus = LicenseStatus.PendingAgreement,
                              MobileNumber = "1234567890",
                              PhoneNumber = "1234567890",
                              VatRegistration = "1234567890"
                          };

        context.Companies.AddOrUpdate(u => u.Name, testCompany);

var testAddress = new Address
        {
            Address1 = "Test Ltd",
            Address2 = "1 Test Gardens",
            Address3 = "Test Heath",
            Address4 = string.Empty,
            Address5 = string.Empty,
            County = "Test",
            Town = "Test",
            Country = "United Kingdom",
            PostCode = "TE5 T11",
            Company = testCompany
        };

        context.Addresses.AddOrUpdate(u => new { u.AddressId }, testAddress);

        testCompany.Address = testAddress;

        context.Companies.AddOrUpdate(u => u.Name, testCompany);

//Fluent API set up as follows in the OnModelCreating
            modelBuilder.Entity<Address>()
                    .HasRequired(ad => ad.Company)
                    .WithOptional(s => s.Address);

Can anyone spot what I'm doing wrong? I've been playing round with different combinations for the past few days and it just doesn't work. I just keep getting errors, the latest error based on the code above is...

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'AddressId'.

Any ideas please?

Answer 1

You can't have a true one to one in SQL Server (see How do I create a real one-to-one relationship in SQL Server), but there is a workaround in EF where you make the primary key of the second entity also a foreign key:

// [Table("Company")] -- not needed unless different
public class Company
{
    // [Key ] -- will be key by convention
    public long CompanyId { get; set; }
    ...
    public virtual Address Address { get; set; }
}

public class Address
{
    [Key, ForeignKey("Company")]
    public long AddressId { get; set; }
    public string Address1 { get; set; }
    ...
    public virtual Company Company { get; set; }
}

You can also do it with fluent code like:

modelBuilder.Entity<Company>() 
    .HasRequired(t => t.Address) 
    .WithRequiredPrincipal(t => t.Company);