Identity 2.0 String or Int primary key?

Question

I'm going to create a new Authorization DLL for our exiting and new applications. We want to add the functionability of Identity 2.0 primary for POCO classes, OAuth and claims. (Claims are really important)

As such, the first thing that comes into mind is the Primary Key being a string. I know this can be changed but also I first researched why is it a string and I found out this SO answer : Why do the ASP.NET Identity interfaces use strings for primary and foreign keys? where they cleary specify that string is 'better' to avoid casting, since claims does use strings.

All our current applications uses Int as Primary Key (Auto increment).

Instead of going blind and change the Identity implementations to use int I researched a bit more and one solution suggested in the same SO answer is to have a string representation of the ID.

Something like :

[Index(IsClustered = false)]
string Id
[Index(IsClustered = true)]
int LogicalId

I'm not sure what's a clustered index but reading it's somehow how the row are arranged in physical disk, so it's about performance so I will go with it as is ok.

My questions are:

MAIN : What's best practice / suggestion for this scenario ?

a) Having a LogicalId int,
b) overriding the default implementation of Identity with int
c) convert all your int to string (as guid)

in case of LogicalId (a) :

What would be the purpose of doing that if we can't ever use the PK as string anyway ? How am I supposed to increment the LogicalId when SaveChanges a new entity on the DB ? EF will do that for me automagically ?

in case of override identity implementation with TKey as int: (b)

User Id on Claims are (i guess) always strings. Would that matter if we use several external providers for our users ? Is it possible to create a concrete implementation of EntityFramework for Int ? Anyone already did ?

Uninstall-Package Indentity.EntityFramework
Install-Package Identity.EntityFrameworkInt

that would be nice :D

convert all data to string (c)

Isn't a pain to work with GUIDs ? Those who work with GUID Does you also have another 'simpler' identifier for public use ? Can't stop imaging customer service :

I cannot find your user on our records, can you please provide your user id?

ouch

Site is not working when I click on one of the 50 {similar product}

- What's the product id ?

lol

We have two products equal, please delete one.

- Sure, give me the product id

cuac

score 1 · Accepted Answer · edited May 23 '17 at 12:24

I don't read the linked question as implying that they are better. string isn't better it's just different. The reason as I read it is that strings can hold ints, guids, and really anything. If they had chosen to use int then they are stuck with int.

As you have observed, TKey was added in Identity 2.0 to allow for different types of keys and choosing a different kind of key isn't going "against best practice" (though you should choose something traditional).

Whenever you intend to save a new entity you would NOT set it as an auto-incrementing identity and just use code to generate a new guid. Not really any pain there.

You shouldn't run into performance issues unless you're developing something serving a lot of users at once all hitting your DB.

Personally, I choose ints because I have a central DB handling everything and I like the simplicity of ints over guids. Though I would choose Guids if I was developing some sort of system with concurrent DBs.

One thing that strings still provide over any other key type in identity is that the extension User.Identity.GetUserId() still returns a string, so you have to cast that even after overriding TKey. Hopefully that will be fixed sometime.

Identity 2.0 String or Int primary key?

1 Answers1

Linked