最近在开发记录感想功能的时候用到了1对1的数据关系，具体情况是这样的，有这样两个1对1的类型

public class Item
{
    public int Id { get; set; }
    public string Title { get; set; }
    public Note Note { get; set; }
}

public class Note
{
    public int Id { get; set; }
    public string Content { get; set; }
    public int ItemId { get; set; }
    public Item Item { get; set; }
    public bool Deleted { get; set; }
}

它们的1对1关系配置如下：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Note>(e =>
    {
        e.HasOne(x => x.Item).WithOne(x => x.Note).HasForeignKey<Note>(x => x.ItemId);
        e.HasQueryFilter(x => !x.Deleted);
    });
}

Note是软删除的，这里配置了一个QueryFilter

然后我们用dotnet-ef命令构建数据库，生成的脚本如下：

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

CREATE TABLE [Items] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NULL,
    CONSTRAINT [PK_Items] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Notes] (
    [Id] int NOT NULL IDENTITY,
    [Content] nvarchar(max) NULL,
    [ItemId] int NOT NULL,
    [Deleted] bit NOT NULL,
    CONSTRAINT [PK_Notes] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Notes_Items_ItemId] FOREIGN KEY ([ItemId]) REFERENCES [Items] ([Id]) ON DELETE CASCADE
);

GO

CREATE UNIQUE INDEX [IX_Notes_ItemId] ON [Notes] ([ItemId]);

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20190813141425_InitEntities', N'2.2.6-servicing-10079');

GO

再造一条数据，方便测试

USE [demo]
GO

INSERT INTO [dbo].[Items]
           ([Title])
     VALUES
           ('a')
GO

不出意外的话，这个Item的Id会是1

业务代码如下：

[ApiController]
[Route("[controller]")]
public class NoteController : ControllerBase
{
    private readonly DemoContext _db;
    public NoteController(DemoContext db)
    {
        _db = db;
    }

    [HttpGet]
    public IEnumerable<Note> Get()
    {
        return _db.Notes.ToList();
    }

    [HttpPost]
    public void Post()
    {
        var item = _db.Items.Include(x => x.Note).FirstOrDefault(x => x.Id == 1);
        if (item != null)
        {
            item.AddNote(DateTime.Now.ToString("F"));
            _db.SaveChanges();
        }
    }

    [HttpDelete]
    public void Delete()
    {
        var item = _db.Items.Include(x => x.Note).FirstOrDefault(x => x.Id == 1);
        if (item != null)
        {
            item.DeleteNote();
            _db.SaveChanges();
        }
    }
}

就是对Id==1的Item新增/修改/删除Note

有这样一个很简单的场景，用户先新增了Note，然后删除Note，再想新增Note，这时候你就会发现数据库报错了：Note违反了唯一性约束。

由于Note是软删除的，所有当再次新增Note的时候就会出现重复的ItemId。

解决这个问题的思路也很简单，只需要把这个外键的唯一性约束更改为过滤掉Deleted的数据进行约束。

更改关系配置

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Note>(e =>
    {
        e.HasOne(x => x.Item).WithOne(x => x.Note).HasForeignKey<Note>(x =x.ItemId);
        e.HasQueryFilter(x => !x.Deleted);
        e.HasIndex(x => x.ItemId).IsUnique().HasFilter($"[{nameof(Note.Deleted)}]=0");
    });
}

给这个ItemId的唯一性约束加一个条件e.HasIndex(x => x.ItemId).IsUnique().HasFilter($"[{nameof(Note.Deleted)}]=0");

再用dotnet-ef命令生成的数据库更新脚本，如下：

DROP INDEX [IX_Notes_ItemId] ON [Notes];

GO

CREATE UNIQUE INDEX [IX_Notes_ItemId] ON [Notes] ([ItemId]) WHERE [Deleted]=0;

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20190813144240_FilterIndex', N'2.2.6-servicing-10079');

GO

用有条件的INDEX替换了原先的INDEX

现在再次执行先前的业务，新增，删除，再次新增就正常了。

完整代码github