时间戳

首页 > 解决方案 > EF Core - 添加具有唯一索引的数据时出现重复键错误

c# - EF Core - 添加具有唯一索引的数据时出现重复键错误

问题描述

我正在尝试使用 EF Core 将数据添加到数据库，但我无法克服重复键错误：Cannot insert duplicate key row in object 'dbo.Stocks' with unique index 'IX_Stocks_Name'. The duplicate key value is (stock1).

我有Stock一个与一对多关系的实体Transaction- 一个Stock可以与许多Transactions 相关联。

Stock仅当数据库中没有给定 ID时才会出现问题- 在这种情况下，我可以添加许多Transaction相同的 sStockID并且它可以工作：

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的 s 添加了类的定义。

提前感谢您的任何提示。

Transaction班级：

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

Stock班级：

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

标签： c#entity-frameworkef-code-firstentity-framework-coreef-code-first-mapping

解决方案

表上有一个唯一索引，dbo.Stocks名为IX_Stocks_Name. 你违反了这个索引。

你的问题是这一行：

var stock1 = new Stock("stock1");

您一遍又一遍地创建“stock1”。相反，您应该首先检索（或存储）Stock“stock1”的实体并使用它，如果它已经存在的话。如果它不存在，那么创建一个是安全的。

简而言之，代码正在INSERT与dbo.Stocks现有的Name.

推荐阅读