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
- All .NET exceptions are derived classes of the
System.Exceptionbase class, or derived from another inherited class therein. System.SystemExceptionis inherited from theSystem.Exceptionclass.System.InvalidOperationExceptioninherits directly fromSystem.SystemException.- Finally,
System.Data.Linq.DuplicateKeyExceptioninherits directly fromSystem.InvalidOperationException.
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:
CREATE TABLE [dbo].[Book] (
[Id] INT PRIMARY KEY NOT NULL,
[Title] NCHAR (100) NOT NULL,
[Author] NCHAR (100) NOT NULL,
[PageCount] INT NULL
);
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:
[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Book")]
public partial class Book : INotifyPropertyChanging, INotifyPropertyChanged
{private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
private int _Id;
private string _Title;
private string _Author;
private System.Nullable<int> _PageCount;
// ...
}
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:
using System.Linq;
using System.Data.SqlClient;
using Utility;namespace Airbrake.Data.Linq.DuplicateKeyException
{
class Program
{
static void Main(string[] args)
{
var adapter = new LinqAdapter();
}
}internal class LinqAdapter
{
private LibraryDataContext Context { get; }public LinqAdapter()
{
// Set data context.
Context = new LibraryDataContext();
// Create basic books and add to database.
AddBooksToDatabase();
// Retrieve existing book from database.
var existingBook = (from book in Context.Books
select book).FirstOrDefault();
// Add book with explicit Id equal to existing Id.
if (existingBook != null)
{
AddBookToDatabase(new Book
{
Id = existingBook.Id,
Title = "Moby Dick",
Author = "Herman Melville",
PageCount = 752
});
}
}/// <summary>
/// Add passed Book to database.
/// </summary>
/// <param name="book">Book record to be added.</param>
private void AddBookToDatabase(Book book)
{
try
{
// Specify that insertion should be performed when submission occurs.
Context.Books.InsertOnSubmit(book);
// Submit the insertion changes.
Context.SubmitChanges();
// Output successful addition.
Logging.Log($"Book added successfully: '{book.Title}' by {book.Author} at {book.PageCount} pages.");
}
catch (System.Data.Linq.DuplicateKeyException exception)
{
// Output expected DuplicateKeyException.
Logging.Log(exception);
}
catch (SqlException exception)
{
// Output unexpected SqlExceptions.
Logging.Log(exception, false);
}
}/// <summary>
/// Add a set of default Books to the database.
/// </summary>
private void AddBooksToDatabase()
{
AddBookToDatabase(new Book
{
Title = "The Hobbit",
Author = "J.R.R. Tolkien",
PageCount = 304
});
AddBookToDatabase(new Book
{
Title = "The Shining",
Author = "Stephen King",
PageCount = 823
});
AddBookToDatabase(new Book
{
Title = "The Name of the Wind",
Author = "Patrick Rothfuss",
PageCount = 722
});
}
}
}using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
using System.Text;namespace Utility
{
/// <summary>
/// Houses all logging methods for various debug outputs.
/// </summary>
public static class Logging
{
/// <summary>
/// Outputs to <see cref="System.Diagnostics.Debug.WriteLine"/> if DEBUG mode is enabled,
/// otherwise uses standard <see cref="Console.WriteLine"/>.
/// </summary>
/// <param name="value">Value to be output to log.</param>
public static void Log(string value)
{
#if DEBUG
Debug.WriteLine(value);
#else
Console.WriteLine(value);
#endif
}
/// <summary>
/// When <see cref="Exception"/> parameter is passed, modifies the output to indicate
/// if <see cref="Exception"/> was expected, based on passed in `expected` parameter.
/// <para>Outputs the full <see cref="Exception"/> type and message.</para>
/// </summary>
/// <param name="exception">The <see cref="Exception"/> to output.</param>
/// <param name="expected">Boolean indicating if <see cref="Exception"/> was expected.</param>
public static void Log(Exception exception, bool expected = true)
{
string value = $"[{(expected ? "EXPECTED" : "UNEXPECTED")}] {exception.ToString()}: {exception.Message}";
#if DEBUG
Debug.WriteLine(value);
#else
Console.WriteLine(value);
#endif
}
}
}
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:
internal class LinqAdapter
{
private LibraryDataContext Context { get; }public LinqAdapter()
{
// Set data context.
Context = new LibraryDataContext();
// Create basic books and add to database.
AddBooksToDatabase();
// Retrieve existing book from database.
var existingBook = (from book in Context.Books
select book).FirstOrDefault();
// Add book with explicit Id equal to existing Id.
if (existingBook != null)
{
AddBookToDatabase(new Book
{
Id = existingBook.Id,
Title = "Moby Dick",
Author = "Herman Melville",
PageCount = 752
});
}
}
// ...
}
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:
/// <summary>
/// Add passed Book to database.
/// </summary>
/// <param name="book">Book record to be added.</param>
private void AddBookToDatabase(Book book)
{
try
{
// Specify that insertion should be performed when submission occurs.
Context.Books.InsertOnSubmit(book);
// Submit the insertion changes.
Context.SubmitChanges();
// Output successful addition.
Logging.Log($"Book added successfully: '{book.Title}' by {book.Author} at {book.PageCount} pages.");
}
catch (System.Data.Linq.DuplicateKeyException exception)
{
// Output expected DuplicateKeyException.
Logging.Log(exception);
}
catch (SqlException exception)
{
// Output unexpected SqlExceptions.
Logging.Log(exception, false);
}
}
/// <summary>
/// Add a set of default Books to the database.
/// </summary>
private void AddBooksToDatabase()
{
AddBookToDatabase(new Book
{
Title = "The Hobbit",
Author = "J.R.R. Tolkien",
PageCount = 304
});
AddBookToDatabase(new Book
{
Title = "The Shining",
Author = "Stephen King",
PageCount = 823
});
AddBookToDatabase(new Book
{
Title = "The Name of the Wind",
Author = "Patrick Rothfuss",
PageCount = 722
});
}
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:
CREATE TABLE [dbo].[Book] (
[Id] INT PRIMARY KEY NOT NULL,
[Title] NCHAR (100) NOT NULL,
[Author] NCHAR (100) NOT NULL,
[PageCount] INT NULL
);
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:
Book added successfully: 'The Hobbit' by J.R.R. Tolkien at 304 pages.
[EXPECTED] System.Data.Linq.DuplicateKeyException: Cannot add an entity with a key that is already in use.
[EXPECTED] System.Data.Linq.DuplicateKeyException: Cannot add an entity with a key that is already in use.
[EXPECTED] System.Data.Linq.DuplicateKeyException: Cannot add an entity with a key that is already in use.
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:
CREATE TABLE [dbo].[Book] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Title] NCHAR (100) NOT NULL,
[Author] NCHAR (100) NOT NULL,
[PageCount] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
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:
Book added successfully: 'The Hobbit' by J.R.R. Tolkien at 304 pages.
Book added successfully: 'The Shining' by Stephen King at 823 pages.
Book added successfully: 'The Name of the Wind' by Patrick Rothfuss at 722 pages.
Book added successfully: 'Moby Dick' by Herman Melville at 752 pages.
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:
public interface IBook
{
string Author { get; set; }
int PageCount { get; set; }
string Title { get; set; }
}public class Book : IBook
{
public string Author { get; set; }
public int PageCount { get; set; }
public string Title { get; set; }public Book() { }
public Book(string title, string author, int pageCount)
{
Author = author;
PageCount = pageCount;
Title = title;
}
public override string ToString()
{
return $"'{Title}' by {Author} at {PageCount} pages";
}
}
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:
CREATE TABLE [Book] (
[Id] INTEGER PRIMARY KEY NOT NULL,
[Title] text NOT NULL,
[Author] text NOT NULL,
[PageCount] bigint NULL
);
Now, in our code, we once again start the process within the constructor of our SqliteAdapter class:
internal class SqliteAdapter
{
private SQLiteConnection Connection { get; }
private const string DatabaseFilePath = "development.sqlite3";public SqliteAdapter()
{
// Create database.
CreateDatabase();
// Connect to database.
Connection = new SQLiteConnection($"Data Source={DatabaseFilePath};Version=3");
// Create book table.
CreateBookTable();
// Add default books to database.
AddBooksToDatabase();
// Add book with existing Id to database.
AddBookToDatabase(new Book
{
Title = "Moby Dick",
Author = "Herman Melville",
PageCount = 752
}, 1);
}
// ...
}
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:
/// <summary>
/// Creates Book table in database, if necessary.
/// </summary>
private void CreateBookTable()
{
const string sql = @"CREATE TABLE IF NOT EXISTS Book
(
Id INTEGER PRIMARY KEY,
Title TEXT NOT NULL,
Author TEXT NOT NULL,
PageCount INTEGER
);";
ExecuteSql(sql);
}/// <summary>
/// Creates database at DatabaseFilePath, if necessary.
/// </summary>
private static void CreateDatabase()
{
if (!File.Exists(DatabaseFilePath))
SQLiteConnection.CreateFile(DatabaseFilePath);
}
/// <summary>
/// Executes passed SQL string using appropriate SQLiteExecuteType.
/// </summary>
/// <param name="sql">SQL string to execute.</param>
/// <param name="executeType">Type of execution to use.</param>
private async void ExecuteSql(string sql, SQLiteExecuteType executeType = SQLiteExecuteType.Default)
{
try
{
// Open connection, if necessary.
if (Connection.State != System.Data.ConnectionState.Open)
{
await Connection.OpenAsync();
}
// Create command from passed SQL string.
var command = new SQLiteCommand(sql, Connection);
// Check executeType parameter.
switch (executeType)
{
case SQLiteExecuteType.Default:
var rowsAffected = await command.ExecuteNonQueryAsync();
// Output number of affected rows.
Logging.Log($"Query complete: {rowsAffected} row(s) affected.");
break;
case SQLiteExecuteType.Reader:
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
// Retrieve values for each read row.
var values = new object[reader.FieldCount - 1];
reader.GetValues(values);
// Output values.
Logging.Log(values);
}
break;
case SQLiteExecuteType.Scalar:
await command.ExecuteScalarAsync();
break;
case SQLiteExecuteType.None:
break;
default:
// Throw exception if executeType value is something unexpected.
throw new ArgumentOutOfRangeException(nameof(executeType), executeType, null);
}
}
catch (System.Data.Linq.DuplicateKeyException exception)
{
// Output expected DuplicateKeyException.
Logging.Log(exception);
}
catch (SQLiteException exception)
{
// Output unexpected SQLiteException.
Logging.Log(exception, false);
}
}
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:
/// <summary>
/// Add some basic books to the database.
/// </summary>
private void AddBooksToDatabase()
{
AddBookToDatabase(new Book
{
Title = "The Hobbit",
Author = "J.R.R. Tolkien",
PageCount = 304
});
AddBookToDatabase(new Book
{
Title = "The Shining",
Author = "Stephen King",
PageCount = 823
});
AddBookToDatabase(new Book
{
Title = "The Name of the Wind",
Author = "Patrick Rothfuss",
PageCount = 722
});
}/// <summary>
/// Add passed IBook to database.
/// </summary>
/// <param name="book">Book to be added.</param>
private void AddBookToDatabase(IBook book)
{
var sql = $"INSERT INTO [Book] (Title, Author, PageCount) VALUES ('{book.Title}', '{book.Author}', {book.PageCount})";
ExecuteSql(sql);
Logging.Log($"Book successfully added to database: {book}");
}
/// <summary>
/// Add passed IBook to database, using passed identity.
/// </summary>
/// <param name="book">Book to be added.</param>
/// <param name="id">Id to be used.</param>
private void AddBookToDatabase(IBook book, int id)
{
var sql = $"INSERT INTO [Book] (Id, Title, Author, PageCount) VALUES ({id}, '{book.Title}', '{book.Author}', {book.PageCount})";
ExecuteSql(sql);
Logging.Log($"Book successfully added to database: {book}");
}
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:
Query complete: -1 row(s) affected.
Query complete: 1 row(s) affected.
Book successfully added to database: 'The Hobbit' by J.R.R. Tolkien at 304 pages
Query complete: 1 row(s) affected.
Book successfully added to database: 'The Shining' by Stephen King at 823 pages
Query complete: 1 row(s) affected.
Book successfully added to database: 'The Name of the Wind' by Patrick Rothfuss at 722 pages
[UNEXPECTED] System.Data.SQLite.SQLiteException (0x80004005): constraint failed
UNIQUE constraint failed: Book.Id
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.