Can’t insert duplicate key row in object dbo.ApiScopes

December 28, 2018 0 By Toby Worth

I’ve had this error twice now, but since I’ve had a large Rusty Nail for Boxing-boxing day (27th) I can’t remember what causes it and Gittr is impossible to search, so I’m going to keep a log in case I’m tipsy next time I see this.

I managed to find what I did last time on Gittr (luckily it wasn’t that busy this month). It turns out all I did was:

1. Not use the convenience functions to instantiate the ApiResource. Explicitly declaring the props, instead.
2. Use parentheses in the scope declaration (instead of just new Scope{…})

It comes down to the ApiResource initialiser, since this was creating the object before I assigned the values (i.e. using the parentheses before the curly-brace notation ‘added’ other property values).

The ApiResource class has a default value of an empty hashset for the ‘Scopes’ property. With the exception of the default constructor, all the constructors add a scope to the scope list.

So, the default constructor does some scope creation under the hood. By calling the defaults first, the secondary scope creation was presumably creating duplicate SQL. This wouldn’t be a problem with in-memory configuration, but clearly is for SQL-based config storage.

The ApiResource constructor has a few overloads, but ultimately calls the one that relies on a property that is automatically-assigned a new HashSet().

Inside that constructor, a default scope is created and assigned the name/description pair matching the name assigned via the constructor parameter of the ApiResource. The source code examples provided from the IS4 repo actually do cause duplicates when imported vie EF Core. The appear to only differ in casing, but there may be more to it.

The solution to work with both In-Memory and EF Core configuration storage is to use at least one constructor parameter and also ensure the custom scopes provided differ from the defaults (in more than just case).