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

Zer0 · Accepted Answer · 2019-03-20 21:44:30Z

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, 2019 at 21:44

Zer0

7,3841 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Łukasz Sypniewski Over a year ago

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<Transaction> 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?

Steve Py · Accepted Answer · 2019-03-20 23:05:19Z

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, 2019 at 23:05

Steve Py

36.4k4 gold badges34 silver badges58 bronze badges

2 Comments

Łukasz Sypniewski Over a year ago

Thank you for the hint. To be honest, I would expect Entity Framework to take care of associating entities in such way automatically.

Dave Shinkle Over a year ago

It wouldn't make sense to do that automatically. Use auto-generated numeric or guid keys to avoid this issue.

Łukasz Sypniewski · Accepted Answer · 2019-03-20 23:13:29Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related