SQL: "System.Data.Linq.DuplicateKeyException"

Question

I am new to programming. To familiarize myself with F# and SQL Server, I decided to try implementing the code found here. I have pasted it below for the convenience of those who do not want to click on that link:

let newRecord = new dbSchema.ServiceTypes.Table1(Id = 100,
                                                 TestData1 = 35, 
                                                 TestData2 = 2.0,
                                                 Name = "Testing123")
let newValues =
    [ for i in [1 .. 10] ->
          new dbSchema.ServiceTypes.Table3(Id = 700 + i,
                                           Name = "Testing" + i.ToString(),
                                           Data = i) ]
// Insert the new data into the database.
db.Table1.InsertOnSubmit(newRecord)
db.Table3.InsertAllOnSubmit(newValues)
try
    db.DataContext.SubmitChanges()
    printfn "Successfully inserted new rows."
with
   | exn -> printfn "Exception:\n%s" exn.Message

I created two tables, named Table1 and Table 3, in SQL Server. Initially, I set the primary key in each table to be the ID. Running the code above immediately returned this output:

Exception: Cannot add an entity with a key that is already in use.

I started randomly trying various ways of resolving the issue (bad practice, I know!), based on the information I found by Googling the exception. One approach I took was to change the primary key in each table to be "Name" instead. This time, no exception was thrown.

My problem was solved, but I do not understand how or why. So my questions are:

Why was the exception thrown initially? Did it have anything to do with the fact that I set "Id" as the primary key?
Did the fact that I change the primary key to "Name" eradicate the error? If so, how and why did that make a difference?

Thanks in advance for your help. Please let me know if my question is insufficiently detailed.

Why you have hard-coded ids? 100 for Table1 entity and 701..710 for Table3 entities — Sergey Berezovskiy
– Sergey Berezovskiy, Commented May 8, 2015 at 8:46
In general you probably wouldn't set the ID at all - leave it to the database, that's what Identity is for. — Bridge
– Bridge, Commented May 8, 2015 at 10:48

Barry Keepence · Accepted Answer · 2015-05-08 08:52:04Z

1

This looks like it should work - the Id should be unique.

Here are some debug pointers:

Get a raw interface to the SQL so you can type queries yourself.
Make sure the table is empty before you run your code.
Test the system by typing in the raw sql in an sql client to see if it works.

Good luck.

answered May 8, 2015 at 8:52

Barry Keepence

1365 bronze badges

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

Collectives™ on Stack Overflow

SQL: "System.Data.Linq.DuplicateKeyException"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related