ASP.NET Identity - 如何将 dbo.AspNetUsers.Id 更改为非聚集索引？

我正在使用 ASPNET Identity 2.0 并且需要：

• 添加一个名为 ApplicationUserId [int] 聚集索引的新字段
• 将 dbo.AspNetUsers.Id nvarchar 字段从聚集索引更改为非聚集索引

在我的 Models\IdentityModel.cs 中，我添加了：

    public class ApplicationUser: IdentityUser
    {
            [Index(IsClustered = true, IsUnique = true)]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int ApplicationUserId { get; set; }
    }

当然，我知道这会出错，因为我知道 Id 当前是聚集索引。 如何更改 Id 以使默认值不是聚集索引？

基本上我想要这样做的原因是我们的 DBA 不想使用 nvarchar(128) 外键（这是 AspNetUsers 表中的 GUID）。

或者我只是将其创建为：

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ApplicationUserId { get; set; }

然后在创建表后手动修复索引？

或者是我唯一使用的选择整数键而不是字符串 http://typecastexception.com/post/2014/07/13/ASPNET-Identity-20-Extending-Identity-Models-and-Using-Integer-Keys-Instead-of-Strings.aspx?

我正在使用 .NET 4.5 并安装了身份 2.0 示例项目 http://typecastexception.com/post/2014/06/22/ASPNET-Identity-20-Customizing-Users-and-Roles.aspx

 Install-Package Microsoft.AspNet.Identity.Samples -Pre

在此先感谢您的帮助！

我无需将 GUID [nvarchar] 转换为 [int] 即可正常工作

基本上我更新了 Models\IdentityModel.cs 文件

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ApplicationUserId { get; set; }

然后我手动更新了表（AspNetUserLogins、AspNetUserClaims 和 AspNetUserRoles 引用了 AspNetUsers.Id，我仍然想保持原样

begin tran
ALTER TABLE [dbo].[AspNetUserLogins] DROP CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
ALTER TABLE [dbo].[AspNetUserClaims] DROP CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]
ALTER TABLE [dbo].[AspNetUserRoles] DROP CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
ALTER TABLE dbo.[AspNetUsers] DROP CONSTRAINT [PK_dbo.AspNetUsers]
--Uncomment this line in case you need to modify this table again
--ALTER TABLE dbo.[AspNetUsers] DROP CONSTRAINT [dbo.AspNetUsers_ApplicationUserId]

-- Add the constraints back, but now nonclustered
ALTER TABLE dbo.[AspNetUsers] add constraint [PK_dbo.AspNetUsers] primary key     nonclustered 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

-- Add the other constraints back
ALTER TABLE [dbo].[AspNetUserLogins]  WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserClaims]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

-- Add NEW constraint with unique clustered
ALTER TABLE dbo.[AspNetUsers] add constraint [dbo.AspNetUsers_ApplicationUserId] unique clustered 
(
[ApplicationUserId]
)
commit tran

当您查看 dbo.AspNetUsers 的创建表脚本时，它现在看起来像这样：

CREATE TABLE [dbo].[AspNetUsers](
    [Id] [nvarchar](128) NOT NULL,
    [ApplicationUserId] [int] IDENTITY(1,1) NOT NULL,
    [Email] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEndDateUtc] [datetime] NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [dbo.AspNetUsers_ApplicationUserId] UNIQUE CLUSTERED 
(
    [ApplicationUserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]