sql-server - Identity Db：即使未在列上设置约束，也会出现重复的电子邮件错误

Question

从我的 asp.net api 创建用户时，我在 identitydb 中收到重复电子邮件的异常。奇怪的是，在电子邮件列或规范化电子邮件列上没有设置约束，所以不知道为什么它不允许我输入相同的电子邮件地址。身份数据库中是否有一些默认设置用于检查电子邮件重复

错误

Code : "DuplicateEmail"
Description : "Email '[e-mail]' is already taken."

在分析器中跟踪时捕获的执行

exec sp_executesql N'EXEC GlobalExceptionInsert @DateTimeStamp, @Thread, @Class, @Method, @UserName, @Message, @Exception',N'@DateTimeStamp nvarchar(23),@Thread nvarchar(2),@Class nvarchar(13),@Method nvarchar(8),@UserName nvarchar(17),@Message nvarchar(115),@Exception nvarchar(163)',@DateTimeStamp=N'2019/10/23 14:26:13.078',@Thread=N'15',@Class=N'<AddUser>d__7',@Method=N'MoveNext',@UserName=N'Argentex.Core.Api',@Message=N'Error creating new user tmenon. Message: Code: DuplicateEmail. Description: Email ''[e-mail]'' is already taken.',@Exception=N'Argentex.Core.Api.Exceptions.IdentityException: Error creating new user tmenon. Message: Code: DuplicateEmail. Description: Email ''[e-mail]'' is already taken.'

这是表格的截图

桌子

约束

表定义

USE [IdentityDB_CSR]
GO

/****** Object:  Table [dbo].[User]    Script Date: 23/10/2019 13:28:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NULL,
    [NormalizedUserName] [nvarchar](256) NULL,
    [Email] [nvarchar](256) NULL,
    [NormalizedEmail] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [ConcurrencyStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEnd] [datetimeoffset](7) NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [AuthUserId] [int] NOT NULL,
    [Title] [nvarchar](16) NOT NULL,
    [Forename] [nvarchar](256) NOT NULL,
    [Surname] [nvarchar](100) NOT NULL,
    [ClientCompanyId] [int] NOT NULL,
    [ClientCompanyContactId] [int] NOT NULL,
    [UpdatedByAuthUserId] [int] NOT NULL,
    [PhoneNumberMobile] [nvarchar](128) NULL,
    [PhoneNumberOther] [nvarchar](128) NULL,
    [LastUpdate] [datetime2](7) NULL,
    [ASPNumber] [nvarchar](max) NULL,
    [ASPCreationDate] [datetime2](7) NULL,
    [LastTelephoneChange] [datetime2](7) NULL,
    [LastEmailChange] [datetime2](7) NULL,
    [LastPasswordChange] [datetime2](7) NOT NULL,
    [CreateDate] [datetime2](7) NOT NULL,
    [IsApproved] [bit] NOT NULL,
    [Birthday] [datetime2](7) NULL,
    [Notes] [nvarchar](max) NULL,
    [Position] [nvarchar](50) NULL,
    [PrimaryContact] [bit] NULL,
    [IsDeleted] [bit] NOT NULL,
    [IsAdmin] [bit] NOT NULL,
    [ApprovedByAuthUserId] [int] NULL,
    [IsAuthorisedSignatory] [bit] NOT NULL,
    [IsSignatory] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_User_AuthUserId] UNIQUE NONCLUSTERED 
(
    [AuthUserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[User] ADD  DEFAULT (getdate()) FOR [LastPasswordChange]
GO

ALTER TABLE [dbo].[User] ADD  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[User] ADD  DEFAULT ((0)) FOR [IsAuthorisedSignatory]
GO

ALTER TABLE [dbo].[User] ADD  DEFAULT ((0)) FOR [IsSignatory]
GO

UserNameIndex 的定义

USE [IdentityDB_CSR]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [UserNameIndex]    Script Date: 23/10/2019 13:58:00 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[User]
(
    [NormalizedUserName] ASC
)
WHERE ([NormalizedUserName] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

标签： sql-serverasp.net-identityasp.net-core-identity

Answer 1

使用 ASP.NET Core Identity 创建用户时，它将引发默认库，如果of为 true ，则该UserValidator库将确保电子邮件不为空、有效且唯一：options.User.RequireUniqueEmailIdentityOptions

if (manager.Options.User.RequireUniqueEmail)
{
    await ValidateEmail(manager, user, errors);
}

ValidateEmail：

// make sure email is not empty, valid, and unique
private async Task ValidateEmail(UserManager<TUser> manager, TUser user, List<IdentityError> errors)
{
    var email = await manager.GetEmailAsync(user);
    if (string.IsNullOrWhiteSpace(email))
    {
        errors.Add(Describer.InvalidEmail(email));
        return;
    }
    if (!new EmailAddressAttribute().IsValid(email))
    {
        errors.Add(Describer.InvalidEmail(email));
        return;
    }
    var owner = await manager.FindByEmailAsync(email);
    if (owner != null && 
        !string.Equals(await manager.GetUserIdAsync(owner), await manager.GetUserIdAsync(user)))
    {
        errors.Add(Describer.DuplicateEmail(email));
    }
}

源代码

您可以在中设置选项ConfigureServices：

services.Configure<IdentityOptions>(options =>
{
    options.User.RequireUniqueEmail = false;
    ...
});