c# - Entity Framework - One-to-One - ReferentialConstraint is mapped to a store-generated column

Question

I have what should be a simple one-to-one relationship to create within EF. But I'm receiving the following error when I try to insert:

ReferentialConstraint is mapped to a store-generated column. Column: 'ACCOUNT_ID'.

Console App example:

namespace EF_ConsoleApp_Test
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var account = new Account
            {
                AccountNumber = "00123456",
                CustomerValue = new Customer { FirstName = "Joe" }
            };

            using (var db = new MainContext())
            {
                db.Accounts.Add(account);
                db.SaveChanges();
            }
        }
    }

    [Serializable]
    [Table("CUSTOMERS")]
    public class Customer
    {
        [Key]
        [Column("CUSTOMER_ID")]
        public int? Id { get; set; }

        [Required]
        [Column("FIRST_NAME")]
        [StringLength(45)]
        public string FirstName { get; set; }

        public virtual Account Account { get; set; }

        public Customer() { }
    }

    [Serializable]
    [Table("ACCOUNTS")]
    public class Account
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        [Column("ACCOUNT_ID")]
        public int? Id { get; set; }

        [Required]
        [Column("ACCOUNT_NUMBER")]
        [Display(Name = "Account Number")]
        [StringLength(16)]
        public string AccountNumber { get; set; }

        [Column("CUSTOMER_ID")]
        public int? CustomerId { get; set; }

        public virtual Customer CustomerValue { get; set; }

        /// <summary>
        /// Default Constructor
        /// </summary>
        public Account() { }
    }

    internal class MainContext : DbContext
    {
        internal MainContext() : base("name=DB.Context")
        {
            Database.SetInitializer<MainContext>(null);
        }

        public virtual DbSet<Account> Accounts { get; set; }

        public virtual DbSet<Customer> Customers { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Configure FK
            modelBuilder.Entity<Customer>()
                .HasRequired(c => c.Account)
                .WithRequiredPrincipal(a => a.CustomerValue);

            base.OnModelCreating(modelBuilder);
        }
    }
}

Database Table Create statements:

CREATE TABLE [dbo].[CUSTOMERS](
    [CUSTOMER_ID] [INT] IDENTITY(1,1) NOT NULL,
    [FIRST_NAME] [varchar](45) NOT NULL,
 CONSTRAINT [PK_CUSTOMERS] PRIMARY KEY CLUSTERED 
(
    [CUSTOMER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACCOUNTS](
    [ACCOUNT_ID] [INT] IDENTITY(1,1) NOT NULL,
    [CUSTOMER_ID] [int] NOT NULL,
    [ACCOUNT_NUMBER] [varchar](16) NOT NULL,
 CONSTRAINT [PK_ACCOUNTS] PRIMARY KEY CLUSTERED 
(
    [ACCOUNT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[ACCOUNTS]  WITH CHECK ADD  CONSTRAINT [FK_ACCOUNTS_CUSTOMERS] FOREIGN KEY([CUSTOMER_ID])
REFERENCES [dbo].[CUSTOMERS] ([CUSTOMER_ID])
GO

App.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.1" />
  </startup>

  <connectionStrings>
    <add name="DB.Context"
         connectionString="data source=localdb;initial catalog=EF_TEST;Integrated Security=SSPI;MultipleActiveResultSets=True;App=EntityFramework;Connection Timeout=30;encrypt=true;trustServerCertificate=true;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

</configuration>

What do I need to change to make this work?

Notes:

• I'm using EF 6.2 and .NET 4.7.1.
• I inherited this database schema and the one-to-one relationship cannot be changed.
• I'm trying to avoiding explicitly creating a transaction by only calling SaveChanges() once opposed to wrapping a separate call to create Customer first, then Account.

Answer 1

By convention EF6 represents the one-to-one relationships using the so called Shared Primary Key Association, where the PK of the dependent entity also serves as FK to the principal entity.

In your case, it considers Account.Id to be the FK to Customer, and since it's auto-generated, you get the exception in question.

The additional problem is that EF6 does not support one-to-one relationship with explicit FK property (there is no HasForeignKey fluent API similar to one-to-many relationships).

So you need to remove the AccountId property from the model and leave only the navigation property. Also, although not strongly necessary, it would be good to follow the naming conventions and just call it Account rather than AccountValue.

In other words, replace

[Column("CUSTOMER_ID")]
public int? CustomerId { get; set; }

public virtual Customer CustomerValue { get; set; }

with

public virtual Customer Customer { get; set; }

The FK column name can be specified using the MapKey fluent API:

modelBuilder.Entity<Customer>()
    .HasRequired(c => c.Account)
    .WithRequiredPrincipal(a => a.Customer)
    .Map(m => m.MapKey("CUSTOMER_ID")); // <--

And you are done.

Now the following correctly inserts first a new Customer and then a new Account referencing it:

var account = new Account
{
    AccountNumber = "00123456",
    Customer = new Customer { FirstName = "Joe" }
};
db.Accounts.Add(account);
db.SaveChanges();