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

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

score 2 · Accepted Answer · answered Mar 20 '19 at 21:44

2

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.

answered Mar 20 '19 at 21:44

Zer0

7,191
1
20
34

As for the line you mentioned - I was creating `Stock` manually for the sake of depicting a problem. Actually, I parse a text file and pass `List` into the function and then call `context.Transactions.AddRange(list)` Of course, every stock is refered by lots of transactions. Could you elaborate a little bit more on a possible approach to solving the problem? – Łukasz Sypniewski Mar 20 '19 at 22:07

score 0 · Answer 2 · answered Mar 20 '19 at 23:05

0

You need to check if there is a Stock already known for the desired ID. for the sake of your example:

var stock1 = context.Stocks.SingleOrDefault(x => x.StockId == "stock1") ?? new Stock("stock1");

This will associate an existing stock if one is already in the database, or associate a new one if there is none.

answered Mar 20 '19 at 23:05

Steve Py

26,149
3
25
43

Thank you for the hint. To be honest, I would expect Entity Framework to take care of associating entities in such way automatically. – Łukasz Sypniewski Mar 20 '19 at 23:17
It wouldn't make sense to do that automatically. Use auto-generated numeric or guid keys to avoid this issue. – Dave Shinkle Oct 01 '19 at 21:26

score 0 · Answer 3 · answered Mar 20 '19 at 23:13

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

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

3 Answers3

Linked