Why AspNetUsers doesn't use email as Primary Key?

[joelGarcia93]

hello everyone, I have a question about entity framework authentication, it suppose the queries would be faster with email as primary key instead email as index (UNIQUE). someone can explain me why? or what strategy are you using to handle the performance of the query when a user is signing in.

After of all Thank you so much, Im new in EntityFramework and it's really awesome. Thank you :D

[Tarig0]

Stack overflow is a better platform, for this question.

EF doesn't handle the execution of the queries just the generation of the sql and mapping of the results. Also you should mention what database engine you are using, each one is different.

But PK vs UX is minimal hit to performance. Like you can't even blink in the amount of time you lose

[joelGarcia93]

Im using SQL server enginne innodb, one teacher of the university told me is a bad practice to do that for the performance but I wanna know the comment of a Microsoft engineer or someone who creates the framework to can tell him why you manage in that way the authentication.

Thank you for the answer

[benaadams]

email would be a poor primary key. The user primary key, would also be the foreign key for any table that referred to the user; so replicated across most tables.

• email is an arbitrarily long field so can be expensive to match and join tables with
• email is a changing field (as the user changes emails); so updating it if it was the primary key would require a cascading update to every table which would be very expensive, or cause data to become detached from the user in other tables (if it wasn't updated in other tables)
• if you allow multiple identities per user (multiple emails, google login, facebook login, etc); then you'd now have a many key relationship to other tables.
• Also often the email for the different logins changes (e.g. google login is not same as microsoft login) so which is the valid email to use? Some 3rd party auth providers don't provide email (e.g. twitter, steam); so do you just disallow those users (as you can't use a null field as the foreign key)
• Email address is also personal data under GDPR; so you would want to isolate it to one place rather than have it replicated across every table

[joelGarcia93]

@benaadams does it affect the performance? I think is same velocity, but the teacher told me is more slow unique index than primary key Im really so confused.

Thank you for the answer

[benaadams]

That's a more complex question

• Primary key is normally what the rows in the table are sorted on and it is part of the row; so you can do a binary type search to look up the row using primary key and get its data.
• An index is normally more of a dictionary type structure, so a very fast lookup up; however what you are looking up with the index is the primary key, which you then use to lookup the row. So it would be slower as you additionally do a lookup on primary key.
• However, you can also have an index with additional columns. e.g. email (index) + fullname (column) + primary key This then becomes faster if you only use the columns in the index as it doesn't need to additionally lookup the row the table; and the index rows are generally shorter (as they have less data than the full table row) so can be traversed faster than the table row.

So the primary key is generally faster, but an index can be faster in some circumstances depending on the data and the index; if that helps?

Still shouldn't use email for a primary key; regardless of the performance for the reasons above (its a bad type of data to use, as its often large and it changes)

[joelGarcia93]

Amazing Sr, Im gonna share this link to my teacher. Thank you so much and have a nice day.

[roji]

It's worth mentioning that depending on your database, primary keys don't necessarily define the table physical sort order, or what is sometimes referred to as "clustering". AFAIK in PostgreSQL the primary key isn't more efficient than any other index, but on the other hand you can cluster a table based on any index (although clustering doesn't get maintained and must be periodically executed again to affect newly-inserted rows).

[joelGarcia93]

hello developer's in the Table AspNetUsers in the Primary key Id you are using nvarchar(450) again I have other question, it suppose the primary key has to be short instead a long field, why did you declare 450 characters? it supposed its a bad practice right?

Im very curious, please I don't ask these questions to get angry someone, I just wanna know why the expert's do that in the frameworks, Im a beginner and I wanna know why the expert's handle in that way the development of DB. my teacher told me if I have thousands or millions of users or resources in a table, don't have a good architecture in the DB could be a trouble in the performance.

If you have a website with thousand of millions of data in the tables, please let me know if you use AspNetCore MVC with entity framework using the conventions of the framework, for example the ID 450 characters, to use email as index unique, to generate ID auto-generated for almost all tables?

Again thank you so much for take time in this amazing discussion.

THANK YOU DEVELOPERS!!!

[benaadams]

in the Table AspNetUsers in the Primary key Id you are using nvarchar(450)

I've also wondered that...

[ajcvickers]

@joelGarcia93 Is the question why is the primary key a string, or why is it constrained to 450 characters? For the former, I don't know. For the latter, because there is a limit in SQL Server (and some other database systems) on the size for string key columns, and 450 was at some point determined to be a reasonable default, based primarily on SQL Server's limit, taking into account that it may be part of a composite key. @divega May be able to give more details.

[joelGarcia93]

@ajcvickers as @benaadams wrote above, the primary key has to be constrained with the minimum characters, 450 is a very long number for a primary key, I believe with 20 characters would be ok, don't you believe that? actually my teacher told me in the SQL server in about 2005 year, it wasn't possible to do that, because it isn't a good practice in the architecture of the DB.

[divega]

@joelGarcia93 I can't remember if we made an explicit decision in ASP.NET Core Identity not to configure the size of the key. Or if we did any performance analysis with different lengths. @haok, do you remember?

I just found an article at https://sqlperformance.com/2017/06/sql-plan/performance-myths-oversizing-strings that goes into good detail about this, and not surprisingly, the conclusion is that for certain queries, keys that are declared with shorter max length can be more efficient (i.e. allocate less memory on the database server), independently of the length of the data stored into them.

When an application has knowledge of the max length to be stored in a string property, it is advisable for it to configure it explicitly to that size. EF Core supports several ways of doing that. If you are using ASP.NET Core Identity for an application that is going to support millions of users, you can configure the lenght explicitly by overrding OnModelCreating in the ApplicationDbContext.

As @ajcvickers mentioned, 450 is a just default picked by EF Core conventions. I would only like to add that in absence of explicit configuration we err on the side of large keys, because strings on .NET don't really have a specific max length. For this same reason, string properties that are not part of a key are mapped to nvarchar(max) by default.