.NET Exceptions – System.Data.Linq.DuplicateKeyException

Moving along through our in-depth .NET Exception Handling series, today we’ll take a closer look at the System.Data.Linq.DuplicateKeyException. The DuplicateKeyException is typically thrown when performing LINQ operations on an underlying database/data context, while attempting to insert or create a record with the same identifying key as another record.

In this article we’ll examine the DuplicateKeyException in more detail, looking at where it sits in the .NET exception hierarchy, along with a bit of sample code illustrating when System.Data.Linq.DuplicateKeyExceptions are (and are not) typically thrown, so let’s get going!

The Technical Rundown

When Should You Use It?

As previously mentioned, a common scenario in which a DuplicateKeyException might occur is when performing LINQ modifications to a database, and attempting to add a record with the same PRIMARY KEY value of another record within that table. Since we’re dealing with databases here, while the included sample code will provide as much detail as possible, some portions of the project are automatically generated by Visual Studio; such components will only be described rather than included as code.

With that out of the way, we’ll start with our LINQ-based sample. If you aren’t aware, .NET has the ability to “map” programmatic relational objects (models) with corresponding database tables within an associated database. This type of mapping is quite common and can be found in many other languages and frameworks, such as Ruby on Rails and PHP. .NET provides a few different methods of achieving this connection, but when working with an existing database schema, by far the easiest option is to use the LINQ to SQL Classes component in Visual Studio. To add this to a project, simply right-click and select Add > LINQ to SQL Classes, just as you’d add any other project component. Note: Depending on your installation, it may be necessary to run the Visual Studio Installer application to explicitly install the LINQ to SQL Classes individual component, if it isn’t already present.

Adding this component to our Visual Studio project creates a .dbml file. We’ll be using it to access the library database, so we’re naming the overall file Library.dbml. We’ve already created the library SQL Server database, so now we need to add a table to interact with. We’ll start with the following query to create the Book table:

Now we open the Library.dbml and drag and drop our newly-created Book table into the designer view. This will cause Visual Studio to automatically generate all the code necessary to map between our Book database table and a relational Book class object. Since this code is created for us, we won’t include most of it here, but this is a small sample of what is generated:

Now that our database to relational object mapping is complete, we can start using LINQ to perform queries and insert new records into our table. This is where our own code is required, so we’ll start with the full example below, after which we’ll explain each section in more detail:

We need to use the data context that was automatically generated via our LINQ to SQL Classes component. We’re able to do this by calling new LibraryDataContext(), since Visual Studio appends DataContext to whatever name we gave to the .dbml component. Our LinqAdapter constructor performs a few basic actions, starting by creating a handful of books and adding them to the database, then retrieving an existing book via LINQ, before finally attempting to add one final book that uses the same Id as an existing record:

The AddBookToDatabase(Book book) method is where most everything takes place. Our Context is automatically mapped to a Table<Book> collection, into which we want to insert the passed book object when Context.SubmitChanges() is called:

As you’ll recall from our SQL creation string, the Book table does not have any sort of constraint placed on its Id PRIMARY KEY column:

As a result, instantiating a new LinqAdapter object as seen above actually ends up creating one new book successfully, immediately followed by the throwing of three System.Data.Linq.DuplicateKeyExceptions:

Let’s dig into this and figure out what’s going on. Since the Id column is an integer value, if no Id value is provided when we add a new Book to the database via LINQ, the Id value defaults to 0 before an insertion attempt is made. Our first book is added without any problem, since nothing in the table exists at the time. However, each subsequent attempt to add another record tries to use the same Id value of 0, which results in a DuplicateKeyException being thrown. Therefore, our library.Book table only contains a single record at this point:

Id Title Author PageCount
0 The Hobbit J.R.R. Tolkien 304

In this case, the solution is to fix the configuration of the Book table, so that there’s some form of constraint on the Id PRIMARY KEY column. We also want the value to auto-increment. This is the updated SQL string we can use to create a proper Book table:

Once the table is updated, we also need to update the Library.dbml file, which will automatically update all the behind-the-scenes mapping code. Once complete, running our example again results in all four books being added successfully:

This may seem strange since we explicitly assigned the Id value of our fourth Book object to be equal to the Id value of an existing record. However, in this sort of scenario our modern development tools try to come to our rescue. Since it would (almost) never be desirable to attempt an insertion using a duplicate PRIMARY KEY value while an identity constraint is in place, such a request is merely ignored on our behalf, and a new (valid) Id is generated and used instead. As a result, our Book table now contains all our records:

Id Title Author PageCount
0 The Hobbit J.R.R. Tolkien 304
1 The Shining Stephen King 823
2 The Name of the Wind Patrick Rothfuss 722
3 Moby Dick Herman Melville 752

Since System.Data.Linq.DuplicateKeyException is housed within the Linq namespace, it stands to reason that it appears only when dealing with LINQ. But, what happens when we try to insert duplicate PRIMARY KEY values using direct SQL connections? To find out, we start with an explicit IBook interface and implementing Book class:

To keep things simple, we’ll be connecting to a local SQLite database, which we’re calling development.sqlite3. We then create a Book table with the following basic query:

Now, in our code, we once again start the process within the constructor of our SqliteAdapter class:

As you can see, we perform similar steps as our LINQ example, except we have to be more explicit here since there’s no behind-the-scenes code to help us. We start by creating the database file, establishing a connection, creating the Book table, then adding some books. We finish by adding one final book with an explicit Id value of 1, which should already exist due to adding some previous books.

The CreateDatabase() and CreateBookTable() methods are quite basic and self-explanatory. The ExecuteSql(string sql, SQLiteExecuteType executeType) method is where most of the work gets done in this class, but overall we’re just asynchronously opening the database connection (if necessary), then executing our passed sql query, based on the execution type enumeration value:

Lastly, to add a book to the database we have a few AddBookToDatabase() method signatures, depending whether we’re adding a book with or without an explicit Id value:

With all that setup we can actually instantiate our SqliteAdapter class and see what happens when we try to add the same four books that we did in the LINQ example:

As we can see, since we aren’t using LINQ, rather then getting a System.Data.Linq.DuplicateKeyException thrown our way, we get an SQLiteException instead. While the message is slightly different, the result is the same: We can’t use an identifier that already exists in the system!

To get the most out of your own applications and to fully manage any and all .NET Exceptions, check out the Airbrake .NET Bug Handler, offering real-time alerts and instantaneous insight into what went wrong with your .NET code, along with built-in support for a variety of popular development integrations including: JIRA, GitHub, Bitbucket, and much more.