CODEWITHSUNDEEP
sql.netsql-serverlinq
M.Y. Babt
M.Y. Babt

Reputation: 2891

SQL: "System.Data.Linq.DuplicateKeyException"

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:

  1. Why was the exception thrown initially? Did it have anything to do with the fact that I set "Id" as the primary key?
  2. 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.

Upvotes: 0

Views: 107

Answers (1)

Barry Keepence
Barry Keepence

Reputation: 136

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

Here are some debug pointers:

  1. Get a raw interface to the SQL so you can type queries yourself.

  2. Make sure the table is empty before you run your code.

  3. Test the system by typing in the raw sql in an sql client to see if it works.

Good luck.

Upvotes: 1

Related Questions