Concurrency considerations

[aleksandervalle]

I might very well be wrong, but I think only the database approach will guarantee that the invariant is held in a concurrency scenario. Given a scenario where User A and User B both add a product with the same name "PRODUCT" at the same time, then the invariant check in the other approaches might get bypassed, because the duplicate check query for User A's request is run before User B's request has reached the database, and vice versa (there are no products with name "PRODUCT" in the database when the duplicate check queries run).

[ardalis]

You're correct, in the simple version provided. It's a bit tricky to solve, too, because the usual "avoid updates stepping on one another" patterns like adding a timestamp and checking it with the update won't work since the change could be to an entirely different row (and needs to be checked on inserts, too).

You might be able to adjust your insert and update logic to include a where condition like WHERE NOT EXISTS (SELECT 1 FROM TABLE WHERE NAME = @name) or something similar. But now that's not going to be something you can easily use your off-the-shelf ORM tool for.