EF6多对多不能删除

Question

我使用下面的代码使用EF6内核创建了表。

问题是，UserA，UserB创建并分配了不同的索赔。一点问题都没有。当分配现有的索赔时(例如，UserB被分配给UserB的索赔

public class User
{
    [Key]
    public Guid UserId { get; set; }
    
    [MaxLength(200)]
    public string Username { get; set; }

    [Required]
    public Guid Subject { get; set; }

    public ICollection<UserClaim> Claims { get; set; } = new List<UserClaim>();

    public List<UserClaimRelation> UserClaimRelations { get; set; }
}


public class UserClaim 
{

    [Key]
    public Guid UserClaimId { get; set; } 

    [MaxLength(250)]
    [Required]
    public string Type{ get; set; } 

    [Required]
    public Guid Subject { get; set; }

    [MaxLength(250)]
    [Required]
    public string Value { get; set; }

    public ICollection<User> Users { get; set; }

    public List<UserClaimRelation> UserClaimRelations { get; set; }
}

public class UserClaimRelation
{
        public Guid UserClaimId{ get; set; }
        public UserClaim Claim { get; set; }

        public Guid UserSubject{ get; set; }
        public User User { get; set; }
}


public class MyDbContext: DbContext
{
        public DbSet<User> Users { get; set; }

        public DbSet<UserClaim> UserClaims { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        
            modelBuilder.Entity<User>()
            .HasMany(p => p.Claims)
            .WithMany(p => p.Users)
            .UsingEntity<UserClaimRelation>(
                j => j
                    .HasOne(pt => pt.Claim)
                    .WithMany(t => t.UserClaimRelations)
                    .HasPrincipalKey(f => f.UserClaimId),
                j => j.HasOne(p => p.User)
                    .WithMany(t => t.UserClaimRelations)
                    .HasPrincipalKey( t => t.Subject),
                j => j.HasKey( t => new {t.UserClaimId, t.UserSubject})
            );
}

我正在尝试删除SubjectId的声明

public async Task<bool> DeleteUserClaimsBySubjectAsync(Guid subject)
{
            if (subject == Guid.Empty)
            {
                throw new ArgumentNullException(nameof(subject));
            }
            
        var claims = await _context.UserClaims
                .Where(x => x.Subject == subject).ToListAsync();
            

            foreach (var claim in claims)
            {
               _context.UserClaims.Remove(claim);    
            }

            //_context.UserClaims.RemoveRange(claims);

            return (await _context.SaveChangesAsync() > 0);
}

在删除DeleteUserClaimsBySubjectAsync中的声明时，我得到以下错误

SqlException:违反主键约束'PK_UserClaimRelation‘。无法在对象'dbo.UserClaimRelation‘中插入重复键。重复键值为(13229d33-99e0-41b3-b18d-4f72127e3971，8acbbd 40-1608-41f2-de59-08d9b58b83d3)。

SQL事件探查器查询

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [UserClaimRelation] ([UserClaimId], [UserSubject])
VALUES (@p0, @p1),
(@p2, @p3),
(@p4, @p5),
(@p6, @p7),
(@p8, @p9);
DELETE FROM [UserClaims]
WHERE [UserClaimId] = @p10 AND [ConcurrencyStamp] = @p11;
SELECT @@ROWCOUNT;

DELETE FROM [UserClaims]
WHERE [UserClaimId] = @p12 AND [ConcurrencyStamp] = @p13;
SELECT @@ROWCOUNT;

DELETE FROM [UserClaims]
WHERE [UserClaimId] = @p14 AND [ConcurrencyStamp] = @p15;
SELECT @@ROWCOUNT;

DELETE FROM [UserClaims]
WHERE [UserClaimId] = @p16 AND [ConcurrencyStamp] = @p17;
SELECT @@ROWCOUNT;

DELETE FROM [UserClaims]
WHERE [UserClaimId] = @p18 AND [ConcurrencyStamp] = @p19;
SELECT @@ROWCOUNT;

',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 nvarchar(4000),@p12 uniqueidentifier,@p13 nvarchar(4000),@p14 uniqueidentifier,@p15 nvarchar(4000),@p16 uniqueidentifier,@p17 nvarchar(4000),@p18 uniqueidentifier,@p19 nvarchar(4000)',

@p0='6203BBC3-BD52-4E32-3DDD-08D9B86AB745',@p1='D860EFCA-22D9-47FD-8249-791BA61B07C7',
@p2='80B958C5-FF44-400F-3DDE-08D9B86AB745',@p3='D860EFCA-22D9-47FD-8249-791BA61B07C7',
@p4='A844CB59-5A00-488A-3DDF-08D9B86AB745',@p5='D860EFCA-22D9-47FD-8249-791BA61B07C7',@p6='794AB806-3215-45AF-3DE0-08D9B86AB745',@p7='D860EFCA-22D9-47FD-8249-791BA61B07C7',@p8='80EFF0DF-AE56-419A-3DE1-08D9B86AB745',@p9='D860EFCA-22D9-47FD-8249-791BA61B07C7',@p10='1A48CF6E-E1CD-4042-3DE4-08D9B86AB745',@p11=N'742674d8-3c59-4be4-ac09-38c6804acb66',@p12='258ABD95-6B2A-45CC-3DE6-08D9B86AB745',@p13=N'd206316a-71af-46b7-92e4-bddca669ad87',@p14='4FB1D2E3-18BE-4AFC-3DE3-08D9B86AB745',@p15=N'3eb17fb7-998b-47e1-a97f-3640cbd82b7a',@p16='6C2B9A18-5C8F-4068-3DE5-08D9B86AB745',@p17=N'415b2cc1-b50a-4ec8-872d-13b6342dcd33',@p18='951CEEA6-D057-4588-3DE2-08D9B86AB745',@p19=N'3dfd76dd-d515-4daa-893c-f1ae97aa063a'

Answer 1

好的，所以您希望这里有多到多的关系--多个用户可以有多个不同的声明，多个声明可以分配给多个用户。

所以首先- Users：

public class User
{
    [Key]
    public Guid UserId { get; set; }
    
    [MaxLength(200)]
    public string Username { get; set; }

    [Required]
    public Guid Subject { get; set; }

    public ICollection<UserClaim> Claims { get; set; } = new List<UserClaim>();
}

然后，UserClaims

public class UserClaim 
{

    [Key]
    public Guid UserClaimId { get; set; } 

    [MaxLength(250)]
    [Required]
    public string Type{ get; set; } 

    [Required]
    public Guid Subject { get; set; }

    [MaxLength(250)]
    [Required]
    public string Value { get; set; }

    public ICollection<User> Users { get; set; }
}

请注意，在上面的任何一个中，都没有导致UserClaimRelation的导航属性。事实上，我们将这个UserClaimRelation作为一个单独的实体模型完全删除--这个示例不需要它。如果你真的想要的话，你可以映射它，但是让我们集中精力来完成这个工作。

现在，建立关系：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasMany(x => x.Claims)
        .WithMany(x => x.Users)
        .UsingEntity<Dictionary<string, object>>(
            "UserClaimRelation",
            x => x.HasOne<User>().WithMany(),
            x => x.HasOne<UserClaim>().WithMany(),
            x => x.ToTable("UserClaimRelation"));
}

现在，在更新/重新创建/重新应用迁移到数据库(取决于您的操作方式)之后，您应该能够按主题删除声明，而无需修改DeleteUserClaimsBySubjectAsync方法。

这应该会将其全部设置起来，并创建中间表。

验证这段代码，我在内存中输入

Answer 2

我应用了密码。创建的表如下图所示。但是当我试图用userB的主题更新一个userB的主题时，我得到了以下错误

​

​

更新

​

SqlException:违反主键约束'PK_UserClaimRelation‘。无法在对象'dbo.UserClaimRelation‘中插入重复键。重复键值为(bd7eb87f-2a5f-4921-758e-08d9b97d3121,13229d33-99e0-41b3-b18d-4f72127e3971)。bcos试图在UserClaimRelation中插入，与我最初提供的分析器查询相同。