ASP.NET Core 3.1系列（20）——EFCore中的删除实体操作

于 2022-12-20 13:37:19 发布
阅读量1.4k 收藏 4
点赞数 1
分类专栏： ASP.NET Core C# 文章标签： ASP.NET Core
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HerryDong/article/details/128374279
版权
C# 同时被 2 个专栏收录
72 篇文章 17 订阅
订阅专栏
ASP.NET Core
32 篇文章 42 订阅
订阅专栏

1、前言

前一篇博客介绍了EFCore中添加实体的相关内容，本文开始介绍与之对应的删除实体操作。

2、构建测试数据库

还是与之前一样，在SQL Server中创建一个数据库Dao，然后创建一张Author数据表，代码如下：

USE [Dao]
GO

/****** Object:  Table [dbo].[Author]    Script Date: 2022/12/16 8:55:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Author](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](20) NULL,
	[Gender] [nvarchar](10) NULL,
	[Age] [int] NULL,
	[Email] [nvarchar](30) NULL,
 CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Author表数据如下所示：

IdNameGenderAgeEmail
1张三男3511111111@qq.com
2李四女4022222222@qq.com
3王五男3733333333@qq.com

最终生成的实体类和数据库上下文代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Models
{
    public partial class Author
    {
        [Key]
        public int Id { get; set; }
        [StringLength(20)]
        public string Name { get; set; }
        [StringLength(10)]
        public string Gender { get; set; }
        public int? Age { get; set; }
        [StringLength(30)]
        public string Email { get; set; }
    }
}

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using App.Models;

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.
// If you have enabled NRTs for your project, then un-comment the following line:
// #nullable disable

namespace App.Context
{
    public partial class DaoDbContext : DbContext
    {
        public DaoDbContext()
        {
        }

        public DaoDbContext(DbContextOptions<DaoDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Author> Author { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=rt-dongshenfeng;Initial Catalog=Dao;User ID=sa;Password=gis1a6b7c!Z;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

3、删除单个实体

3.1、先查询、后删除

在EFCore中，我们可以遵循先查询、后删除的方式对实体进行删除。EFCore提供了Remove方法供我们调用，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            Author author = _dbContext.Set<Author>().Where(p => p.Name == "张三").FirstOrDefault();
            if (author != null)
            {
                _dbContext.Set<Author>().Remove(author);
                return _dbContext.SaveChanges();
            }
            return 0;
        }
    }
}

除了Remove方法，EFCore也提供了Entry方法删除实体，只需要将实体的状态改为Deleted即可，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            Author author = _dbContext.Set<Author>().Where(p => p.Name == "张三").FirstOrDefault();
            if (author != null)
            {
                _dbContext.Entry(author).State = EntityState.Deleted;
                return _dbContext.SaveChanges();
            }
            return 0;
        }
    }
}

上面的方法都是通过先根据条件查询出实体、然后再删除的方式删除实体，因此它们在后台会生成2条SQL：

SELECT TOP(1) [a].[Id], [a].[Age], [a].[Email], [a].[Gender], [a].[Name]
FROM [Author] AS [a]
WHERE [a].[Name] = N'张三'

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=1

3.2、创建实体直接删除

上面的方法是先查询、后删除，因此后台会生成2条SQL，其中一条执行select，另一条执行delete。那么能不能只生成1条语句就实现删除功能呢？答案当然是可以的，EFCore支持手动创建实体并删除的模式，但注意：手动创建的实体必须包含主键，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            Author author = new Author
            {
                Id = 2
            };
            _dbContext.Set<Author>().Remove(author);
            return _dbContext.SaveChanges();
        }
    }
}

也可以调用Entry方法，代码如下所示：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            Author author = new Author
            {
                Id = 2
            };
            _dbContext.Entry(author).State = EntityState.Deleted;
            return _dbContext.SaveChanges();
        }
    }
}

使用这种方式删除实体，后台只会生成1条SQL：

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=2

4、批量删除实体

4.1、先查询、后删除

使用Remove与foreach结合可以批量删除实体，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = _dbContext.Set<Author>().Where(p => p.Name == "张三" || p.Name == "李四").ToList();
            foreach (Author author in authors)
            {
                _dbContext.Remove(author);
            }
            return _dbContext.SaveChanges();
        }
    }
}

也可以将Entry与foreach结合实现批量删除，嗲吗如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = _dbContext.Set<Author>().Where(p => p.Name == "张三" || p.Name == "李四").ToList();
            foreach (Author author in authors)
            {
                _dbContext.Entry(author).State = EntityState.Deleted;
            }
            return _dbContext.SaveChanges();
        }
    }
}

EFCore也提供了RemoveRange方法实现批量删除，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = _dbContext.Set<Author>().Where(p => p.Name == "张三" || p.Name == "李四").ToList();
            _dbContext.Set<Author>().RemoveRange(authors);
            return _dbContext.SaveChanges();
        }
    }
}

上面三种方法在后台都会生成3条SQL，其中一条为select，另外两条为delete：

SELECT [a].[Id], [a].[Age], [a].[Email], [a].[Gender], [a].[Name]
FROM [Author] AS [a]
WHERE ([a].[Name] = N'张三') OR ([a].[Name] = N'李四')

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=1

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=2

4.2、创建实体直接删除

EFCore也支持创建实体直接删除的方式实现批量删除实体，但实体必须包含主键，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = new List<Author>()
            {
                new Author
                {
                    Id = 1
                },
                new Author
                {
                    Id = 2
                }
            };
            foreach (Author author in authors)
            {
                _dbContext.Set<Author>().Remove(author);
            }
            return _dbContext.SaveChanges();
        }
    }
}

也可以使用Entry结合foreach的方式，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = new List<Author>()
            {
                new Author
                {
                    Id = 1
                },
                new Author
                {
                    Id = 2
                }
            };
            foreach (Author author in authors)
            {
                _dbContext.Entry(author).State = EntityState.Deleted;
            }
            return _dbContext.SaveChanges();
        }
    }
}

RemoveRange也支持创建实体直接删除，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = new List<Author>()
            {
                new Author
                {
                    Id = 1
                },
                new Author
                {
                    Id = 2
                }
            };
            _dbContext.Set<Author>().RemoveRange(authors);
            return _dbContext.SaveChanges();
        }
    }
}

由于省去了查询的步骤，因此后台只生成2条SQL：

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=1

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=2

5、使用事务删除实体

开启事务也可以删除实体，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            using (var transacton = _dbContext.Database.BeginTransaction())
            {
                try
                {
                    _dbContext.Set<Author>().Remove(new Author
                    {
                        Id = 1
                    });
                    _dbContext.Set<Author>().Remove(new Author
                    {
                        Id = 2
                    });
                    _dbContext.SaveChanges();
                    _dbContext.Database.CommitTransaction();
                    return 1;
                }
                catch
                {
                    _dbContext.Database.RollbackTransaction();
                    return 0;
                }
            }
        }
    }
}

后台共生成2条SQL：

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=1

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Author]
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;

',N'@p0 int',@p0=2

6、使用原生SQL删除实体

使用原生SQL也可以实现删除实体，代码如下：

using App.Context;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            var connection = _dbContext.Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            using (var command = connection.CreateCommand())
            {
                SqlParameter[] parameters =
                {
                    new SqlParameter(@"Name1", "张三"),
                    new SqlParameter(@"Name2", "李四")
                };

                command.CommandText = "delete from Author where Name=@Name1 or Name=@Name2";
                command.CommandType = CommandType.Text;
                command.Parameters.AddRange(parameters);

                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    command.Parameters.Clear();
                }
            }
        }
    }
}

7、Z.EntityFramework.Extensions.EFCore删除实体

Z.EntityFramework.Extensions.EFCore提供了比较友好的删除实体的扩展方法，如BuikDelete，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            List<Author> authors = new List<Author>
            {
                new Author
                {
                    Id= 1
                },
                new Author
                {
                    Id = 2
                }
            };
            try
            {
                _dbContext.BulkDelete(authors);
                return 1;
            }
            catch
            {
                return 0;
            }
        }
    }
}

如果不希望手动创建实体，也可以使用DeleteFromQuery方法，代码如下：

using App.Context;
using App.Models;
using Microsoft.AspNetCore.Mvc;
using System.Linq;

namespace App.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        protected readonly DaoDbContext _dbContext;

        public AuthorController(DaoDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public ActionResult<int> Delete()
        {
            try
            {
                _dbContext.Set<Author>().Where(p => p.Name == "张三" || p.Name == "李四").DeleteFromQuery();
                return 1;
            }
            catch
            {
                return 0;
            }
        }
    }
}

8、结语

本文主要介绍了EFCore中删除实体的相关内容。在数据量较大的情况下，开发者可以考虑使用使用原生SQL或借助于第三方库实现大批量数据的删除操作。

HerryDong
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值