EF Core-在添加具有唯一索引的数据时出现重复键错误 [英] EF Core - error with duplicated key while adding data with unique index

问题描述

我正在尝试使用EF Core将数据添加到数据库中，但是我无法克服重复键错误:无法在具有唯一索引'IX_Stocks_Name'的对象'dbo.Stocks'中插入重复键行.重复的键值为(stock1).

I'm trying to add data to the database using EF Core, but I cannot overcome duplicate key error: Cannot insert duplicate key row in object 'dbo.Stocks' with unique index 'IX_Stocks_Name'. The duplicate key value is (stock1).

我有一个实体 Stock 与 Transaction 具有一对多关系-一个 Stock 可以与许多 Transaction相关 s.

I have entity Stock that has relation one-to-many with Transaction - one Stock can be related with many Transactions.

仅当数据库中没有具有给定ID的 Stock 时，才不会出现问题-在这种情况下，我可以使用相同的添加许多 Transaction > StockID ，并且有效:

The problem doesn't occur only if there's no Stock with a given ID in the database - in that case I can add many Transactions with the same StockID and it works:

using(var context = dbContextFactory.CreateDbContext(new string[0]))
     {
        List<Transaction> list = new List<Transaction>();
        //If there's no Stock with ID "stock1" in the database the code works.
        //Executing the code for the second time results in error, however, if I changed stock1 to stock2 it would work 
        var stock1 = new Stock("stock1");
        list.AddRange(new Transaction[]
        {
        //I can add two exactly the same `Transaction`s and it's ok when executed on a fresh database
        new Transaction(stock1, DateTime.Now, 1M, 5),
        new Transaction(stock1, DateTime.Now, 1M, 5),
        });
        context.Transactions.AddRange(list);
        context.SaveChanges();
     }

下面，我添加了带有类 IEntityTypeConfiguration 的类的定义.

Below I added definitions of classes with their IEntityTypeConfigurations.

预先感谢您的提示.

交易类:

public class Transaction
    {
        public Stock RelatedStock { get; set; }
        public DateTime TransactionTime { get; set; }
        public Decimal Price { get; set; }
        public int Volume { get; set; }
        public Transaction() { }
    }

TransactionConfiguration 类:

public class TransactionConfiguration : IEntityTypeConfiguration<Transaction>
    {
        public void Configure(EntityTypeBuilder<Transaction> builder)
        {
            builder
                .ToTable("Transactions");

            builder
                .Property<int>("TransactionID")
                .HasColumnType("int")
                .ValueGeneratedOnAdd()
                .HasAnnotation("Key", 0);

            builder
                .Property(transaction => transaction.Price)
                .HasColumnName("Price")
                .IsRequired();

            builder
                .Property(transaction => transaction.TransactionTime)
                .HasColumnName("Time")
                .IsRequired();

            builder
                .Property(transaction => transaction.Volume)
                .HasColumnName("Volume")
                .IsRequired();

            builder
                .HasIndex("RelatedStockStockID", nameof(Transaction.TransactionTime))
                .IsUnique();
        }
    }

股票类:

public class Stock
  {
     public string Name { get; set; }
     public ICollection<Transaction> Transactions { get; set; }
     public Stock() { }
  }

StockConfiguration 类:

public class StockConfiguration : IEntityTypeConfiguration<Stock>
    {
        public void Configure(EntityTypeBuilder<Stock> builder)
        {
            builder
                .ToTable("Stocks");

            builder
                .Property<int>("StockID")
                .HasColumnType("int")
                .ValueGeneratedOnAdd()
                .HasAnnotation("Key", 0);

            builder
                .Property(stock => stock.Name)
                .HasColumnName("Name")
                .HasMaxLength(25)
                .IsRequired();

            builder
                .HasMany(stock => stock.Transactions)
                .WithOne(transaction => transaction.RelatedStock)
                .IsRequired();

            builder
                .HasIndex(stock => stock.Name)
                .IsUnique();
        }
    }

推荐答案

dbo.Stocks 表上有一个唯一索引，名为 IX_Stocks_Name .您违反了此索引.

There's a unique index on the dbo.Stocks table named IX_Stocks_Name. You're violating this index.

您的问题是这一行:

var stock1 = new Stock("stock1");

您正在一遍又一遍地创建"stock1".相反，您应该首先检索(或存储)"stock1"的 Stock 实体，并使用该实体(如果已经存在).如果不存在，则可以安全地创建一个.

You're creating "stock1" over and over. Instead, you should first be retrieving (or storing) the Stock entity for "stock1" and using that, if it already exists. If it doesn't exist then it's safe to create one.

简而言之，代码正在对具有现有 Name 的 dbo.Stocks 进行 INSERT .

In short, the code is doing an INSERT into dbo.Stocks with an existing Name.

这篇关于EF Core-在添加具有唯一索引的数据时出现重复键错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！