Fixed: "Duplicate entry for key 'order_lines.IX_order_lines_OrderId_ProductId'" When a List Repeats a Product in a Minimal API
A request that listed the same product twice in one order got a 500, and the log had MySQL's Duplicate entry '2-9' for key 'order_lines.IX_order_lines_OrderId_ProductId'. The database was right to refuse. But a mistake in the request should be a 400, and it shouldn't take stock and open a transaction to find out.
What I Was Doing
In my .NET C# Web API on ASP.NET Core 10, an order has a list of lines, and the order_lines table has a unique index on (OrderId, ProductId). A client sent this:
POST http://localhost:5190/api/v1/orders
Content-Type: application/json
{"customerName":"Ana Costa","customerEmail":"ana@example.com","lines":[{"productId":9,"quantity":1},{"productId":9,"quantity":1}]}
What I Expected
A 400, saying what's wrong.
What I Got
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-9dfbbfb8d0fe674c2c1e15fa682e2054-1322c60811126a73-00"
}
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry '2-9' for key 'order_lines.IX_order_lines_OrderId_ProductId'
What I Checked
What the value means. '2-9' is the index's two columns: order 2, product 9.
What was left behind. Nothing: the failure rolled back the transaction, along with the stock it had taken.
What validation looked at. My attributes check each field and each line, and none of them compares lines with each other.
Why It Happens
The rule "each product once per order" only existed in the database, so the only way to break it was to get all the way to the INSERT. By then, it's an exception, and nothing treats it as the client's mistake.
The Fix
Check the whole list in validation, with IValidatableObject:
public sealed record PlaceOrderRequest(
[Required, StringLength(200)] string CustomerName,
[Required, EmailAddress, StringLength(254)] string CustomerEmail,
[Required, MinLength(1), MaxLength(50)] IReadOnlyList<PlaceOrderLine> Lines) : IValidatableObject
{
public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
{
// The order_lines table allows a product once per order.
if (Lines is not null && Lines.Select(l => l.ProductId).Distinct().Count() != Lines.Count)
{
yield return new ValidationResult(
"Each product can appear only once in an order.",
[nameof(Lines)]);
}
}
}
Keep the unique index too, because it's what protects the data from any other code.
After the Fix
HTTP/1.1 400 Bad Request
Content-Type: application/problem+json
{
"type": "https://tools.ietf.org/html/rfc9110#section-15.5.1",
"title": "One or more validation errors occurred.",
"status": 400,
"errors": {
"Lines": [
"Each product can appear only once in an order."
]
},
"traceId": "00-4ffe8158b5cbfdbcf2a6e2e55e673525-8c534c6fc29a011a-00"
}
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Removing the unique index | Other code can store duplicates | Keep both |
| Merging repeated lines without saying so | The client doesn't know what it ordered | Refuse, or document it |
Catching 1062 and answering 400 | Work happens before you find out | Validate first |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
Validate never runs | AddValidation() is missing, or an attribute failed first | The how-to |
Lines is null inside Validate | A client sent none | Keep the null check |
| The same error on a real race | Two requests with one key | Catch it, and return the first |
Conclusion
A database error from a bad request means a rule isn't being checked early enough. Validate the whole list before any database work, and keep the unique index as the guarantee.
Additional Resources
- Validation in minimal APIs —
AddValidation. - IValidatableObject — the interface.
