最近在开发记录感想功能的时候用到了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