3

Asp.net identity creates the primary key as nvarchar(128) with a clustered index. Based on http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/ I want to either

  1. Update my application to have a column with clustered index being on an int
  2. Change from guid to int for primary key storage.

What would you suggest? If the first solution is the way to go, does entity framework provide a way to implement this?
Is ASP.NET Identity - How to change the dbo.AspNetUsers.Id into a nonclustered index? the only way

Community
  • 1
  • 1
ksop
  • 33
  • 2
  • 2
    Possible duplicate [How to change type of id in Microsoft.AspNet.Identity.EntityFramework.IdentityUser](http://stackoverflow.com/questions/19553424/how-to-change-type-of-id-in-microsoft-aspnet-identity-entityframework-identityus) – Izzy Jan 14 '15 at 12:29
  • You could use the approach that i've posted here: http://stackoverflow.com/questions/6532418/how-to-combine-using-membership-api-with-own-application-related-data So a new table(`aspnet_UserID`) which maps the `guid`s and the `int`s. Then you don't need to change the membership-provider and everything still works but you can link all your tables with the `int` instead of the `guid`. – Tim Schmelter Jan 14 '15 at 12:30
  • 1
    if you are not going to have x thousands of users, I would not sweat on it. – hazimdikenli Jan 14 '15 at 12:55
  • @TimSchmelter the question is about Asp.Net Identity, not Membership Provider and these are very different. – trailmax Jan 14 '15 at 13:59
  • 1
    @trailmax: you're right, i'm not familiar with Asp.Net Identity so i've lumped both together. – Tim Schmelter Jan 14 '15 at 14:10
  • @TimSchmelter I think that was a great suggestion and the one that i would have definitely implemented if identity v2 didn't give us the flexibility to specify our own primary key type. I think my best strategy is of restructuring. – ksop Jan 14 '15 at 15:29
  • @hazimdikenli The problem is that i'll have thousands of users and, if you 've seen the comment in the blog, joining with a large table with millions of rows is suboptimal. – ksop Jan 14 '15 at 15:32

1 Answers1

3

For #2, If you don't need (or want) to use GUIDs as the primary key you can change them to ints like so:

public class ApplicationUser : IdentityUser<int, ApplicationUserLogin, 
    ApplicationUserRole, ApplicationUserClaim>
{
}

public class ApplicationUserLogin : IdentityUserLogin<int>
{
}

public class ApplicationUserClaim : IdentityUserClaim<int>
{
}

public class ApplicationRole : IdentityRole<int, ApplicationUserRole>
{
}

public class ApplicationUserRole : IdentityUserRole<int>
{
}

Then declare your DbContext like so:

public class MyContext: IdentityDbContext<ApplicationUser, ApplicationRole, int,
  ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim> 
{
}
JamesT
  • 2,988
  • 23
  • 29
  • Is there any benefits by using int instead of string? (and vice versa - string instead of int)? – Sam Jan 27 '19 at 01:00
  • nvarchar(128) will support more users than an int will. NewID() in SQL, GetNewID() & NewGuid() in C# can generate unique IDs. Int sync may be quicker on smaller tables. but can't be used for merge replication sync. – Joseph Poirier Apr 03 '19 at 04:01
  • Also, users won't feel that gnawing OCD urge to keep tables ordered by the ID fields, & will more likely maintain tables sorted by name or some other more relevant field to the data the table holds. – Joseph Poirier Apr 03 '19 at 04:06