使用EF Core增删改查

九极天
147 阅读13分钟

执行原生SQL

生成复杂报表，原生SQL语句可能更方便

原生SQL的缺点

  1. 原生SQL语句需要把表名、列名等硬编码到SQL语句，不符合模型驱动、分层各类的思想，程序员直接面对数据库表，无法利用EF Core强类型的特性，如果模型发生改变，必须手动变更SQL语句
  2. 无法利用EF Core强大的SQL翻译机制来屏蔽不同数据库的差异。可能无法跨数据库，某些和数据库相关的代码EF Core可能无法成功翻译为SQL语句
  3. 没有自动提示

落地建议

把只能用原生SQL写的逻辑用FromSqlInterpolated去执行，然后把分页、分组、二次过滤、排序、Include等其他逻辑尽可能使用EF Core的标准操作去实现

ExecuteSqlInterpolated使用

非查询语句

不需要saveChange

ctx.Database.ExecuteSqlInterpolated
    ($@"insert into T_Articles(Title,Message,Price) 
    select Title,{name},Price from T_Articles where Price>={age}");
//异步
await ctx.Database.ExecuteSqlInterpolatedAsync
    ($@"insert into T_Articles(Title,Message,Price)
    select Title,{name},Price from T_Articles where Price>={age}");

实体相关查询语句

如果要执行的原生SQL是一个查询语句，并且查询的结果也能对应一个实体，就可以调用对应实体的DbSet的FromSqlInterpolated方法来执行一个查询sql语句，同样使用字符串内插值来传递参数

string titlePattern = "%中%";
var queryable= ctx.Articles.FromSqlInterpolated($@"select * from T_Articles where Title like{titlePattern} order by newid()");
foreach (var item in queryable) {..遍历才会执行... }

string titlePattern = "%中%";
var queryable= ctx.Articles.FromSqlInterpolated($@"select * from T_Articles where Title like{titlePattern}");
foreach (var item in queryable.OrderBy(a=>Guid.NewGuid()).Skip(1).Take(2)) {..遍历才会执行... }

注意

  1. 实体上FromSqlInterpolated只有同步方法，因为返回IQueryable类型
  2. 查询处理的实体也可以修改

缺点

  1. SQL查询必须返回实体类型对应数据库表的所有列
  2. 结果集中的列名必须与属性映射到的列名称匹配
  3. 只能单表查询，不能使用join语句进行关联查询。但是可以在查询后使用Include()来进行关联数据的获取
  4. 不适合执行结果复杂的报表语句，因为结果很难与唯一实体对应

ExecuteSqlInterpolated、ExecuteSqlRaw比较

ExecuteSqlInterpolated：ExecuteSqlInterpolated参数是FormattableString类型，而不是string。内部转换成参数化查询，所以不会造成SQL注入攻击

ExecuteSqlRaw：需要自己处理查询参数。可能会有SQL注入攻击，不推荐使用

使用Dapper进行任意SQL查询

FromSqlInterpolated只能单表查询，但是在实现报表查询等的时候，SQL语句通常也是非常复杂的，不仅要多表Join，而且返回的查询结果一般也都不会和一个实体类完整对应。因此需要一种执行任意SQL查询语句的机制。

EF Core允许把实体或存储过程映射为实体，因此可以把复杂的查询语句写成视图或存储过程，然后再声明对应的实体类，并且在DbContext中配置对应的DbSet。但是不推荐写存储过程：项目复杂查询很多，导致实体太多。非实体的DbSet。DbSet膨胀，不利于项目的维护

这种情况推荐执行原生SQL语句，但是直接用ADO.NET比较复杂。推荐用Dapper

//查询结果不是和一个实体相关
select Author,Count(*) from T_Books group by Author

代码查看EF Core生成的SQL

为什么不用SQL Server Profiler？

  1. SQL Server Profiler是查看服务器的所有活动，虽然可以设定一些过滤器，但是配置起来很麻烦
  2. SQL Server Profiler只能用在SQL Server上，不能用于别的数据库。所以需要更通用的方法

方法一：标准日志

特点：适合于标准的项目

步骤：

1）按照之前的方法，引入日志框架

2）在DbContext的派生类中

//正常通过注入的方式引入，不要通过下面这种方式
//AddConsole代表输出到控制台
private static ILoggerFactory loggerFactory=LoggerFactory.Create(b=>b.AddConsole());
//OnConfiguring中...
optionsBuilder.UseLoggerFactory(loggerFactory);

方法二：简单日志

特点：不需要引入日志框架，使用简单，适用于demo、演示等

1、在DbContext的派生类中

//OnConfiguring中...
optionsBuilder.LogTo(msg => {
    Console.WriteLine(msg);
});

上面输出的是EF Core的整个工作过程，日志很多，需要自己过滤下

optionsBuilder.LogTo(msg => {
    if (!msg.Contains("CommandExecuting")) return;
    Console.WriteLine(msg);
});

方法三：ToQueryString

上面两种方式无法直接得到一个操作的SQL语句，而且在操作很多的情况下，容易混乱。ToQueryString不需要真的执行查询就能获得SQL语句，但是只能获取查询操作的SQL语句。

EF Core的Where方法返回的是IQueryable类型，DbSet也实现了IQueryable接口。IQueryable有扩展方法ToQuerystring可以获得SQL。

var persons=ctx.Persons.Where(x => x.Id==1);
string sql=persons.ToQueryString();

查询

普通查询

Where

1、使用Where指定查询条件

//返回IQueryable
IQueryable<Book> books = ctx.Books.Where(b => b.Price > 80);
foreach(var book in books) {
    Console.WriteLine(book.Title);
}

Select

2、使用Select只获取部分字段，提高性能

var book = ctx.Books.Select(a=>new {a.Id,a.Title}).First();

Single、SingleAsync

3、使用Single、SingleAsync指定查询条件并查询唯一数据

var book = ctx.Books.Single(b => b.Title == "SQL金典");

OrderBy

4、使用OrderBy进行排序

var books = ctx.Books.OrderBy(b => b.Price).Where(b=>b.Price>10);

OrderBy的使用问题

//参数keySelector如下，TSource是实体类类型，TKey是排序字段的类型

Expression<Func<TSource, TKey>> keySelector

那么如何通过前端传递的字符串，动态指定排序列来排序？即如何动态构建keySelector

GroupBy

5、使用GroupBy进行分组，使用Select对分组后的结果进行重构

var items = ctx.Books.GroupBy(b => b.AuthorName)
    .Select(g => new { Name = g.Key, BooksCount = g.Count(), MaxPrice = g.Max(b => b.Price) });
foreach (var e in items) {
    Console.WriteLine($"{e.Name}，{e.BooksCount}，{e.MaxPrice}");
}
//选择分组中最新日期的数据
GroupBy(...).Select(g=>g.OrderBy(...).LastOrDefault(...))

FirstOrDefault

var l=ctx.Leaves.FirstOrDefault();

FndAsync

使用DbContext的FindAsync根据主键查询

await dbCtx.FindAsync<Album>(albumId);

关联查询

查询一的端，带多的端全部数据

使用Include后才会Join查询

Article a = ctx.Articles.Include(a=>a.Comments).Single(a => a.Id == 2);

注意，不能直接查询，否则执行后“一”的端的List为空，因为没有关联查询

//错误写法
Article a = ctx.Articles.Single(a => a.Id == 2);

查询一的端，由多的端的条件

查询评论中含有"微软“的所有文章

方法一：由“一”的端查询

//SQL采用Exists相关子查询实现
ctx.Article.Where(a=>a.Comments.Any(c=>c.Message.Contains("微软")));

方法二：由“多”的端查询

//SQL采用join实现
ctx.Comments.Where(c=>c.Messgge.Contains("微软")).Select(c=>c.Article).DIstinct();

查询多的端，带一的端的全部数据

同样需要使用include进行关联查询

注意，这样会存在循环引用问题

Comment cmt = ctx.Comments.Include(a => a.TheArticle).Single(ctx => ctx.Id == 3);
坑：循环引用问题

比如一对多的查询，查询多的端的时候带1的端的数据，1的端又有存储多的端的list，list中每个元素又是多的端的数据，这样无限嵌套...

报错信息

System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32.

原因是，由于进行了对象嵌套或EF Core进行了关联查询造成“对象循环”，层级太深解析不了

解决方案

1、安装Microsoft.AspNetCore.Mvc.NewtonsoftJson包

Install-Package Microsoft.AspNetCore.Mvc.NewtonsoftJson

2、在program.cs中或公共配置类中

注意：一定要在这里加上时间格式的设置，否则在别的地方另外配置时间格式无效

//...
using Newtonsoft.Json;

public static class WebApplicationBuilderExtensions {
    public static void ConfigureExtraServices(this WebApplicationBuilder builder, InitializerOptions initOptions) {
      IServiceCollection services = builder.Services;
      services.AddControllers().AddNewtonsoftJson(option => {
        //忽略循环引用
        option.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
        //设置时间格式。而非“2008-08-08T08:08:08”这样的格式
        option.SerializerSettings.DateFormatString = "yyyy-MM-dd HH:mm:ss";
    });
  }
}

这样，查询出来的数据是下面的形式。只有一层冗余，不会循环嵌套下去。神奇的是，冗余的数据不包括上面那条维护履历自身

//一条维护履历
{
  //导航属性，一条设备库信息
  equip:{
    //设备库的其他属性...略
    //冗余的属性，一的端的list
    experience:[
      //维护履历，不包括上面那条维护履历自身
      {
        //...此处停止嵌套，不再有equip属性
      }
    ]
  }
}

缺陷：在另外的地方，对时间格式的转换不再生效。下面是转换时间格式的无效代码

public static class WebApplicationBuilderExtensions {
    public static void ConfigureExtraServices(this WebApplicationBuilder builder, InitializerOptions initOptions) {
      IServiceCollection services = builder.Services;
      //下面的设置将不再生效
      services.Configure<JsonOptions>(options =>{
        //设置时间格式。而非“2008-08-08T08:08:08”这样的格式
        options.JsonSerializerOptions.Converters.Add(new DateTimeJsonConverter("yyyy-MM-dd HH:mm:ss"));
    });
  }
}

查询多的端，带一的端的部分数据

这种写法虽然没有Include，但还会有Join操作

var cmt=ctx.Comments.Select(c=>new {Id=c.Id,AId=c.TheArticle.Id}).Single(c=>c.Id==3)

注意：无法使用Include选择部分数据

//错误写法
ctx.Experiences.Include(e => e.Equip.OwningDepartment).Where(e => e.Equip.OwningDepartment == organization)

查询多的端，由一的端的条件

方法一：从多的端（推荐）

return await ctx.Experiences.Where(e => e.Equip.OwningDepartment == organization).ToArrayAsync();

不需要使用Include，用了反而报错，原因未知

await ctx.Experiences.Include(e => e.Equip).Where(e => e.Equip.OwningDepartment == organization).ToArrayAsync();

报错信息

System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 32. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles.

方法二：从一的端（更麻烦）

场景：一的端的条件可能查询出多个一

1）由一的端查询，得到一个数组，数组每个元素是“多”的端的列表

var experienceLists = await ctx.Equips.Where(e => e.OwningDepartment == organization)
    .Select(e => e.Experience).ToArrayAsync();

2）将子元素整合为一个新的数组

List<Experience> experiences = new List<Experience>();
foreach (var list in experienceLists) {
    experiences.AddRange(list);
}
return experiences.ToArray();

多对多查询，带另一端全部数据

查询所有的老师，同时通过Include附带学生信息

var teachers = ctx.Teachers.Include(t => t.Students);
foreach (var t in teachers) {
    Console.WriteLine(t.Name);
    foreach (var student in t.Students) {
            Console.WriteLine("\t" + student.Name);
        }
}

一对一查询，由一端条件查询另一端

查询所有由蜗牛快递负责的订单信息

ctx.Orders.Where(o=>o.Delivery.CompanyName=="蜗牛快递");

Join关联表

//参数1代表要关联的表，参数2、3代表匹配的的字段，参数4代表返回的对象
var relationResult = pageResult.Join(ctx.Projects, eq => eq.ProjectId, pr => pr.Id, (eq, pr) => new {
    pr.ProjIdentityInfo.ProjectCode,
    eq.Id,
    eq.EquipDetailInfo,
    //...
}).AsNoTracking();

注意

  1. 这里必须取消跟踪.AsNoTracking，否则会报错。猜测是生成了匿名对象，EF Core无法跟踪值对象，只能在标准实体中跟踪值对象

报错信息

A tracking query is attempting to project an owned entity without a corresponding owner in its result,but owned entities cannot be tracked without their owner. Either include the owner entity in the result or make the query non-tracking using 'AsNoTracking'.

  1. 返回的是匿名对象，这样不需要额外再写一个实体类
  2. GroupBy().Select()无法和Join()一起使用（无法翻译），Where()可以和Join一起使用
//下面的语句无法被翻译
processes.GroupBy(p => p.Equip.Id).Select(p => p.OrderByDescending(p => p.RecordCreationInfo.DateTime).First())
    .Join(...)

解决方案：客户端评估。将GroupBy().Select()筛选后的数据拉到内存，将别的表筛选后的数据也拉到内存，在内存中Join。

使用全局查询筛选器

全局查询筛选器：EF Core会自动将这个查询筛选器应用于涉及这个实体类型的所有LINQ查询

缺点

  1. 可能带来性能问题，和索引配合使用时，处理不好会导致仍然全盘扫描。需要给筛选列也加上索引

场景

当表有软删除IsDeleted属性时，后续我们查询都需要加上IsDeleted=false，此时就可用全局查询筛选器

使用方法

在配置类中添加HasQueryFilter筛选条件即可，会自动添加到查询条件中去

//HasQueryFilter
builder.HasQueryFilter(a => a.IsDeleted == false);

忽略此筛选器

//IgnoreQueryFilters
var a = ctx.Articles.IgnoreQueryFilters().Where(a => a.Id == 9).Single();

插入

单表插入

1、新建一个实体对象

    Dog d=new Dog();
    d.Name = "Trump";

2、把实体添加到DbContext的逻辑表中，并saveChanges保存

也可以使用AddRange插入多个，但是这样并不会更高效，仍是在一个存储过程中执行一条条插入SQL

ctx.Dogs.Add(d); 
//ctx.Dogs.AddRange(b1,b2,b3);

一对多的插入

以文章、评论表为例子

1、先创建“一”的端的实例，再创建“多”的端的实例

    Article a1 = new Article();
    a1.Title = "ywl被评为。。";
    a1.Message = "据报道。。。";
    
    Comment c1 = new Comment { Messag = "太牛了吧" };
    Comment c2 = new Comment { Message = "吹吧" };

2、将“多”的端的实例添加到“一”的端的实例的List导航属性中

    a1.Commment.Add(c1);
    a1.Commment.Add(c2);

3、把新建的“一”的端实例添加到DbContext中，并SaveChanges保存即可

    ctx.Articles.Add(a1);
    await ctx.SaveChangesAsync();

因为EFCore会顺竿爬，所以不需要显式地为“多”的端的导航属性TheArticle 赋值，不需要显式地把“多”的端的实例添加到DbContext中

多对多的插入

以老师、学生的多对多关系为例

1、新建几个老师对象、学生对象

    Student s1 = new Student { Name = "张三" };
    Student s2 = new Student { Name = "李四" };
    Student s3 = new Student { Name = "王一博" };
    
    Teacher t1 = new Teacher { Name = "Tom" };
    Teacher t2 = new Teacher { Name = "Jerry" };
    Teacher t3 = new Teacher { Name = "Zack" };

2、在学生对象的List导航属性中添加老师

s1.Teachers.Add(t1);
s1.Teachers.Add(t2);

s2.Teachers.Add(t2);
s2.Teachers.Add(t3);

s3.Teachers.Add(t1);
s3.Teachers.Add(t2);
s3.Teachers.Add(t3);

3、把学生对象、老师对象都添加到DbContext中，并SaveChanges保存

ctx.Teachers.Add(t1);
ctx.Teachers.Add(t2);
ctx.Teachers.Add(t3);

ctx.Students.Add(s1);
ctx.Students.Add(s2);
ctx.Students.Add(s3);
await ctx.SaveChangesAsync();

一对一的插入

以订单对应快递单为例

1、创建一个订单对象，创建一个快递单对象，在快递单的导航属性中指明订单

Order o1=new Order();
o1.Name = "订单名称";

Delivery d1=new Delivery();
d1.CompanyName = "顺丰";

d1.Order = o1;//导航属性

2、为了保险，将两个对象都添加到DbContext，并SaveChanges

    ctx.Orders.Add(o1);
    ctx.Deliveries.Add(d1);
    await ctx.SaveChangesAsync();

理论上，由于只设置了Delivery指向Order，若存一个，则要存Delivery。但是最好两个都存，最保险

将具有导航属性的实体保存

单向导航属性的插入

以员工信息表和离职单表为例，离职单表需要有申请人、审批人，对应于员工信息表。无明确对应关系（不是一对一、一对多这种）

1、创建一个用户，创建一个离职单，并将离职单的用户属性赋值

User u2=new User{Name="haha"};
Leave l1=new Leave{Remarks="回家种地",Requester=u2};

2、将离职单添加到DbContext，并SaveChange即可

Leave l1=new Leave{Remarks="回家种地",Requester=u2};
ctx.Leave.Add(l1);

修改

必须先查询出来然后修改，改完实体属性，直接saveChanges即可

var b = ctx.Books.Single(b => b.Title == "SQL金典");
b.AuthorName = "JunWu";
await ctx.SaveChangesAsync();

修改对应关系

在具有导航属性的一段修改，将方法写到实体类中（充血模型）

//配置在“多”的端的实体类中
public Experience ChangeParent(Equip equip) {
            this.Equip = equip;
            this.EquipCode = equip.IdentityInfo.EquipCode;
            return this;
        }

在一的端修改会报错，如下

dbEquip.Experience.AddRange(equip.Experience);

例：整合重复数据

场景：设备与维护履历，一条设备对应多条维护履历，一条设备对应一个设备代码，但是最开始没有进行唯一性校验，导致一个设备代码有多条设备，现需要将不同设备合并到最新的设备下（主要需要将旧的设备对应的维护履历挂在新的设备下）

public async Task<Equip[]> AggregateRecords() {
    var deletedEquips = new List<Equip>();
    var equipAll =await ctx.Equips.Include(e=>e.Experience).ToListAsync();
    foreach (var e in equipAll.GroupBy(e => e.IdentityInfo.EquipCode)) {
        //先筛选出最新设备代码的一条记录，和剩余的设备
        var equips = e.OrderByDescending(ex => ex.RecordUpdateInfo.DateTime);
        var newlyEquip = equips.FirstOrDefault();
        if (newlyEquip == null) continue;
        var restEquips = equips.Skip(1);
        //查出数据库中的这条设备
        var dbEquip = await ctx.Equips.FindAsync(newlyEquip.Id);
        //将其余的维护履历都添加到最新的设备中
        foreach (var equip in restEquips) {
            //在进度端处理
            foreach(var exp in equip.Experience) {
                exp.ChangeParent(dbEquip);
            }
        }
        deletedEquips.AddRange(restEquips);
        //删除重复的旧记录
        ctx.Equips.RemoveRange(restEquips);
    }
    return deletedEquips.ToArray();
}

删除

必须先查询出来然后删除，使用Remove删除实体

注：RemoveRange仍是一行行地删，并非高效删除

var d=ctx.Dogs.Single(b=>b.Id==3);
ctx.Dogs.Remove(d);

批量操作

第三方库

Install-Package Zack.EFCore.Batch.MSSQL_NET6

1）在DbContext类的OnConfiguring方法中引用

optionsBuilder.UseBatchEF_MSSQL();

2）进行批量操作

//批量删除
await ctc.DeleteRangeAsync<Book>(b=>b.Price>n||b.AuthorName=="zack yang");

//批量更新
await ctc.BatchUpdate<Book>()
    .Set(b=>b.Price,b=>b.Price+3)//b.Price+3类型必须于字段完全一致
    .Set(b=>b.PubTime,b=>DateTime.Now)
    .Where(b=>b.Id>n||b.AuthorName.StartWith("Zack"))
    .ExcuteAsync();
    
//批量插入
using (TestDbContext ctx = new TestDbContext())
{
	ctx.BulkInsert(books);
}

坑

不支持对于含有嵌套列的数据的批量插入，如下面的实体

{
  "identityInfo": {
    "equipCode": "BT002HZ20127-03-0004-01-01",
    //...
  },

报错信息

Microsoft.Data.SqlClient.SqlException (0x80131904): 不能将值 NULL 插入列 'IdentityInfo_EquipCode' 列不允许有 Null 值。INSERT 失败。

avatar
文章
阅读
粉丝