How to catch and parse a SQL Server Duplicate Key Exception

When developing an application that connects to a SQL Server (or Azure SQL) database, you will often want to leverage the features that the database engine provides to help minimise checks that would otherwise need to be performed at the application level. By delegating these checks to the database, you can simplify your application logic, reduce the possibility of bugs such as race conditions, and improve the performance of your application.

One such example of this is when you need to eliminate the possibility of a duplicate value being inserted into a specific column for more than one row in a table. Sure, you could query the database first to check that the value doesn’t already exist, but this would involve a database lookup, reducing performance and risking a race condition unless appropriate locking is used.

With SQL Server and Azure SQL, it is possible to define a Unique Index on a specific column to prevent a duplicate value from being inserted. An error will be raised with a specific SQL Server Error Number if the unique constraint is violated.

In this article, I will show how you can catch and parse a Duplicate Key error in your .NET application when using Entity Framework Core as the ORM (Object Relational Mapper).

Setup

Let’s start by setting up a quick test environment so that we can try things out.

If you want to follow along, I recommend that you start by creating a simple C# Console App using Visual Studio or Visual Studio Code, targeting the latest .NET version which is 8.0 at the time of writing.

Entities

First, let’s assume we are working on a ‘Todo’ application that uses the following types for the entities.

public enum TodoStatus
{
    New = 0,
    InProgress = 1,
    Completed = 2
}
 
public class Todo
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Title { get; set; } = "";
    public TodoStatus Status { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
}

Go ahead and add these types to your project if you are following along.

For the purposes of this article, we want to ensure that the Email property of the User entity is unique.

DbContext

Next, we’ll create a DbContext class that contains a DbSet for the entities, as follows.

using Microsoft.EntityFrameworkCore;
 
public class TodoDbContext : DbContext
{
    public DbSet<Todo> Todos { get; set; }
    public DbSet<User> Users { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=SqlExceptionHandling;Trusted_Connection=True;");
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Todo>().Property(t => t.Title).HasMaxLength(100);
        modelBuilder.Entity<User>().HasIndex(u => u.Email).IsUnique();
    }
}

Note that in a real-world application, the connection string should come from a configuration source.

In the OnModelCreating method a Unique Index is added to the Email property of the User by calling the HasIndex method followed by the IsUnique method. The equivalent SQL to create the Unique Index is as follows.

CREATE UNIQUE NONCLUSTERED INDEX IX_Users_Email ON dbo.Users
(
    Email ASC
)

An alternative way to define a Unique Index with Entity Framework Core is to add a data annotation attribute to the entity class as shown below.

using Microsoft.EntityFrameworkCore;
 
[Index(nameof(Email), IsUnique = true)]
public class User
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
}

I prefer to keep database-specific code out of my entity classes. However, you are free to choose either approach.

Exception Handling

Now that we have the basic setup of the project in place, let’s look at how we can handle the Duplicate Key error.

Adding a User

In the ‘Program.cs’ file of your Console App, add the following code.

try
{
    using var context = new TodoDbContext();
 
    context.Database.EnsureCreated();
 
    context.Users.Add(new User
    {
        Email = "test@example.com"
    });
 
    await context.SaveChangesAsync();
}
catch (Exception ex)
{
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(ex);
}

Note that calling the EnsureCreated method offers a simple way to ensure the database is created with the appropriate schema according to our entity definition for demo purposes. However, you would not typically use this approach in production.

The code creates a new context, adds a new user to the context and then attempts to save the changes back to the database, with the logic wrapped in a try/catch block to capture and output any errors that may occur to the Console.

When you run the Console App for the first time with the above code in place, you should find that the program completes successfully without any exceptions occurring.

However, if you run the program a second time, an exception should be raised and the details output to the Console.

The exception message clearly indicates that a Duplicate Key error has been detected and contains helpful information that includes the relevant Object Name, Index Name, and Duplicate Value.

Unfortunately, the SqlException object doesn’t include these details as easy-to-access properties. However, all is not lost, as the error message is in a standard format that hasn’t changed for many years. This means we can parse the details from the message, which we’ll look at shortly.

Catching the Exception

As it stands, we are catching any type of Exception that may occur when saving the changes to the database.

But what if we want to catch a SqlException specifically, and only when a Duplicate Key error has occurred?

To achieve this we can use an Exception Filter, as shown below.

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
 
try
{
    // Code omitted for brevity.
}
catch (DbUpdateException ex)
when (ex.InnerException is SqlException e && e.Number == 2601) // 2601 = Duplicate Key.
{
    Console.WriteLine("Duplicate value detected.");
 
    // Handle duplicate value error.
}

When using Entity Framework Core, the SqlException will be wrapped by a DbUpdateException.

In the above code, we are catching a DbUpdateException, but only when the InnerException is a non-null SqlException instance and when the Number property is equal to the documented Duplicate Key SQL Error Number.

Parsing the Exception

Now for the fun bit! To parse data from the Duplicate Key error message, we’ll create a record that can be used to hold the key details we are interested in, as follows.

public record DuplicateKeySqlError(string ObjectName = "", string IndexName = "", string DuplicateValue = "")
{
    public const int Number = 2601;
 
    public string Schema => ObjectName?.Split('.')?[0..^1].FirstOrDefault() ?? "";
    public string Table => ObjectName?.Split('.')?.LastOrDefault() ?? "";
}

The above record accepts the individual details via its constructor and includes a couple of calculated properties to parse the Schema and Table details from the Object Name.

A constant for the Error Number is also included and we can reference this in our Exception handling code.

Next, we can write an extension method, as follows.

public static class SqlExceptionExtensions
{
    public static DuplicateKeySqlError ParseDuplicateKeyError(this SqlException ex)
    {
        if (ex.Number is not DuplicateKeySqlError.Number)
        {
            return new();
        }
 
        var regex = new Regex(@"object '(?<ObjectName>.+?)' with unique index '(?<IndexName>.+?)'. The duplicate key value is \((?<DuplicateValue>.+?)\)\.");
        var match = regex.Match(ex.Message);
 
        if (!match.Success)
        {
            return new();
        }
 
        return new DuplicateKeySqlError(
            match.Groups["ObjectName"].Value,
            match.Groups["IndexName"].Value,
            match.Groups["DuplicateValue"].Value);
    }
}

The ParseDuplicateKeyError extension method shown above operates on a SqlException instance and will only attempt to parse the Exception Message if the Error Number indicates a Duplicate Key error.

Since the error message adheres to a standard format, a regular expression can be used to parse the details that we are interested in as groups e.g. <ObjectName>.

If the regular expression match is successful, a new DuplicateKeySqlError instance is returned with the value of the regular expression groups passed into the record constructor.

Outputting the Error Details

Now we can use the parsing logic to provide a better error message to the user.

Below is the updated code from the Program.cs file.

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
 
try
{
    using var context = new TodoDbContext();
 
    context.Database.EnsureCreated();
 
    context.Users.Add(new User
    {
        Email = "test@example.com"
    });
 
    await context.SaveChangesAsync();
}
catch (DbUpdateException ex)
when (ex.InnerException is SqlException e && e.Number == DuplicateKeySqlError.Number)
{
    var error = e.ParseDuplicateKeyError();
    Console.WriteLine("User with email '{0}' already exists.", error.DuplicateValue);
}
catch (Exception ex)
{
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(ex);
}

Within the first catch block, the Duplicate Key error message is parsed using the extension method we just created and the duplicate value is reported back to the user. The message should be similar to the following.

Note that you will need to be careful about what information you return to the user. The duplicate value should be safe to return for the majority of scenarios, but it’s usually not a good idea to leak database table names and other schema details back to the client. You may wish to log these details somewhere instead if required.

Summary

In this article, we have looked at how to catch a SQL Server Duplicate Key Exception and parse data from it.

We started by setting up a very basic ‘Todo’ app, looking at the types/entities involved and the DbContext.

We then reproduced an issue where adding a new User to the database with a duplicate Email fails with a Duplicate Key error due to a Unique Index constraint being violated.

Following this, we looked at how to catch the Duplicate Key error and parse some key details from it for logging purposes and/or display to the user where appropriate.