How does LINQ handle duplicate records on a key field on a InsertOnSubmit
If I am writing a record to a table using LINQ and the InsertOnSubmit command and the database has a key field defined to eliminiate duplication of records......what happens when LINQ tries to write a record that would be a duplicate record in that table? Does it throw an error or will it just skip to the next record and continue?
ASKER
Fernando,
Thanks for an awesome explanation. One question though, that is exactly what I want to happen. This way, I do not have to worry about inserting duplicate records. My questions is, is it possbile to process the exception and tell the system to continue adding records to the database? Basically I want to insert any records that are not a duplicate based on the key fields in the table.
Thanks
Kenny
Thanks for an awesome explanation. One question though, that is exactly what I want to happen. This way, I do not have to worry about inserting duplicate records. My questions is, is it possbile to process the exception and tell the system to continue adding records to the database? Basically I want to insert any records that are not a duplicate based on the key fields in the table.
Thanks
Kenny
Just put the each insert into a try statement (you have to do one by one), then in the catch, make sure to log any exceptions in a text file or on screen.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fernando,
I will try this tonight. Thanks for the actual code to perform this task.
I will try this tonight. Thanks for the actual code to perform this task.
Not a problem, glad I was able to help. ;=)
In short any changes made to the DB are rolled back and a exception is thrown. You can catch the exception and see which item threw the exception and fix and resubmit.
From Microsoft Documentation:
When you do call SubmitChanges(), the DataContext will attempt to translate all your changes into equivalent SQL commands, inserting, updating, or deleting rows in corresponding tables. These actions can be overridden by your own custom logic if you desire, however the order of submission is orchestrated by a service of the DataContext known as the change processor.
The first thing that happens when you call SubmitChanges() is that the set of known objects are examined to determine if new instances have been attached to them. These new instances are added to the set of tracked objects. Next, all objects with pending changes are ordered into a sequence of objects based on dependencies between them. Those objects whose changes depend on other objects are sequenced after their dependencies. Foreign key constraints and uniqueness constraints in the database play a big part in determining the correct ordering of changes. Then, just before any actual changes are transmitted, a transaction is started to encapsulate the series of individual commands unless one is already in scope. Finally, one by one the changes to the objects are translated into SQL commands and sent to the server.
At this point, any errors detected by the database will cause the submission process to abort and an exception will be raised. All changes to the database will be rolled back as if none of the submissions ever took place. The DataContext will still have a full recording of all changes so it is possible to attempt to rectify the problem and resubmit them by calling SubmitChanges() again.
The above was found in http://msdn.microsoft.com/en-us/library/bb425822.aspx
Fernando