How to Return 409 Conflict for a Duplicate Key with EF Core and MySQL
When a unique index refuses a value, EF Core throws an exception, and your API answers 500. But nothing broke: the client asked for something that already exists, and 409 Conflict is the right answer. This guide reads MySQL's error number from the exception, and catches only that error, with a small helper you can reuse.
Prerequisites
- A .NET C# Web API with EF Core and a unique index. The examples use EF Core 10, Oracle's
MySql.EntityFrameworkCore, MySQL 9.7, and a unique index oncategories.Name, from How to Create a Unique Index with EF Core and MySQL. - Problem details set up, so errors share one shape: How to Return Problem Details Errors in ASP.NET Core.
Step 1: See What Happens with No Handling
Create a category that exists already:
HTTP/1.1 500 Internal Server Error
Content-Type: application/problem+json
{
"type": "https://tools.ietf.org/html/rfc9110#section-15.6.1",
"title": "An error occurred while processing your request.",
"status": 500,
"traceId": "00-5845f1a69cb341950c04084dcf7ace1b-289730e4dd5c381c-00"
}
fail: Microsoft.EntityFrameworkCore.Update[10000]
An exception occurred in the database while saving changes for context type 'Dewiride.Inventory.Data.InventoryDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: Could not save changes. Please configure your entity type accordingly.
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'cables' for key 'categories.IX_categories_Name'
DbUpdateException says saving failed. The inner MySqlException says why, and it carries MySQL's error number, 1062. The outer message is the generic one Oracle's provider uses for every failed save.
Step 2: Write a Small Helper
Put it next to the DbContext, so the API project doesn't need to know about MySQL's types:
using Microsoft.EntityFrameworkCore;
using MySql.Data.MySqlClient;
namespace Dewiride.Inventory.Data;
/// <summary>
/// Says which MySQL error made SaveChanges fail. EF Core wraps the error from MySQL in a
/// DbUpdateException, so the MySqlException with the error number is its inner exception.
/// </summary>
public static class DbUpdateExceptionExtensions
{
/// <summary>MySQL error 1062: a unique index already holds this value.</summary>
public static bool IsDuplicateKey(this DbUpdateException exception) =>
HasMySqlError(exception, MySqlErrorCode.DuplicateKeyEntry);
/// <summary>MySQL error 1451: other rows still point at the row being deleted or changed.</summary>
public static bool IsRowStillReferenced(this DbUpdateException exception) =>
HasMySqlError(exception, MySqlErrorCode.RowIsReferenced2);
/// <summary>MySQL error 1452: a foreign key points at a row that doesn't exist.</summary>
public static bool IsMissingReferencedRow(this DbUpdateException exception) =>
HasMySqlError(exception, MySqlErrorCode.NoReferencedRow2);
private static bool HasMySqlError(DbUpdateException exception, MySqlErrorCode errorCode) =>
exception.InnerException is MySqlException mySqlException
&& mySqlException.Number == (int)errorCode;
}
MySqlErrorCode is an enum in Oracle's connector with names for MySQL's error numbers. Compare the number, not the message, because the message contains values that change with each row.
Step 3: Catch Only That Error
db.Categories.Add(category);
try
{
await db.SaveChangesAsync(cancellationToken);
}
catch (DbUpdateException exception) when (exception.IsDuplicateKey())
{
return TypedResults.Problem(
title: "A category with this name already exists.",
detail: $"Choose a name other than '{category.Name}'. Names that differ only in upper and lower case count as the same name.",
statusCode: StatusCodes.Status409Conflict);
}
catch … when only catches when the condition is true. Any other failure carries on to your general error handling, as it should.
Step 4: See the Result
HTTP/1.1 409 Conflict
Content-Type: application/problem+json
{
"type": "https://tools.ietf.org/html/rfc9110#section-15.5.10",
"title": "A category with this name already exists.",
"status": 409,
"detail": "Choose a name other than 'cables'. Names that differ only in upper and lower case count as the same name.",
"traceId": "00-8763aecc999db0e177ad901e42ec07f9-3396f776bfdb43a3-00"
}
The API's log still shows EF Core's fail: lines for this request, because EF Core logs the failure before your catch runs.
The Same Pattern for Other Errors
| MySQL error | Helper | Answer | Why |
|---|---|---|---|
| 1062, duplicate entry | IsDuplicateKey | 409 | It clashes with what's stored |
| 1451, row still referenced | IsRowStillReferenced | 409 | It clashes with what's stored |
| 1452, referenced row missing | IsMissingReferencedRow | 400 | The request points at nothing |
Several catch blocks can sit in a row:
catch (DbUpdateException exception) when (exception.IsDuplicateKey())
{
return DuplicateSku(product.Sku);
}
catch (DbUpdateException exception) when (exception.IsMissingReferencedRow())
{
return UnknownCategory(product.CategoryId);
}
Why Not Check First?
You could run AnyAsync before inserting. But two requests can both pass that check, and then both insert. The unique index is the only thing that can't be fooled, so you need this catch either way. A check first only adds a query.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
catch (DbUpdateException) with no filter | Every failure looks like a duplicate | Filter with when |
| Matching the message text | It breaks when the wording changes | Compare the number |
| Returning MySQL's message to clients | It reveals index and table names | Write your own message |
Expecting this from ExecuteDeleteAsync | That throws MySqlException directly | See that post |
FAQ
Where do the error numbers come from?
The MySQL server error reference lists them, such as "Error number: 1062; Symbol: ER_DUP_ENTRY".
Can I tell which index refused it?
MySQL's message names it, as categories.IX_categories_Name. If a table has several unique indexes, you can check that text, since there's no separate property for it.
Conclusion
Read MySqlException.Number from the DbUpdateException's inner exception, put the check in a small extension method, and use catch … when to handle only that error. A duplicate becomes a 409 with a clear message, and real failures still reach your general error handling.
Additional Resources
- MySQL 9.7 Server Error Message Reference — every error number.
- DbUpdateException Class — the API reference.
