EF Core - error with duplicated key while adding data with unique index

c# ef-code-first ef-code-first-mapping entity-framework entity-framework-core

Question

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).

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

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

Below I added definitions of classes with their IEntityTypeConfigurations.

Thanks in advance for any hints.

Transaction class:

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 class:

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 class:

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

StockConfiguration class:

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();
        }
    }
1
1
3/20/2019 10:32:09 PM

Accepted Answer

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

Your problem is this line:

var stock1 = new Stock("stock1");

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.

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

1
3/20/2019 9:44:30 PM

Popular Answer

Thanks to @Zer0's and @Steve Py's suggestions I came up with the following solution:

void Insert(IEnumerable<Transaction> data)
        {
            using(var context = dbContextFactory.CreateDbContext(new string[0]))
            {
                List<Stock> stocks = data.Select(s => s.RelatedStock).Distinct(new StockComparer()).ToList();
                context.AddRange(stocks);
                context.SaveChanges();
                stocks = context.Stocks.ToList();

                List<Transaction> newList = new List<Transaction>(data.Count());

                foreach (var t in data)
                {
                        Stock relatedStock = stocks.Where(s => s.Name == t.RelatedStock.Name).First();
                        t.RelatedStock = relatedStock;
                        newList.Add(t);
                }

                context.Transactions.AddRange(newList);
                context.SaveChanges();
            }
        }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow