Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
351 views
in Technique[技术] by (71.8m points)

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

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.
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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();

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...