• EF Core 中处理 1对1 关系


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

    不出意外的话,这个ItemId会是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==1Item新增/修改/删除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

  • 相关阅读:
    pip 或 pip3 升级操作
    virtualenv和virtualenvwrapper 的安装和使用
    打包 Framework 时使用 CocoaPods 引入第三方库的方法
    数据库-代码建表
    Oracle-数据库
    JAVA-网络编程
    JAVA-枚举
    JAVA-多线程
    0927-练习
    JAVA-流
  • 原文地址:https://www.cnblogs.com/kexxxfeng/p/11349118.html
Copyright © 2020-2023  润新知