EntityFramework的天坑
先看以下代码:
public void Index() { //新增一条数据 _testDbContext.Add(new User { UserName = "admin_test" }); //从数据库查询,并修改UserName字段的值,但不显式提交修改 var model = _testDbContext.Users.FirstOrDefault(m => m.Id == 1); model.UserName = "我的测试2"; //保存 _testDbContext.SaveChanges(); }
按照常理来说,从数据库查询到的数据,虽然对其字段做了修改,但没显式提交,应该不会保存。但实际是保存了。
那么为何会这样?我们先把sql语句打印出来,看下都执行了什么语句。要想打印sql,需要做一些配置。
在Startup类中定义一个字段:
public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { #if DEBUG builder.AddConsole(); #endif });
然后修改ConfigureServices方法,添加UseLoggerFactory:
var connection = Configuration.GetConnectionString("MysqlConnection"); services.AddDbContext<TestDbContext>(options => options .UseMySql(connection, ServerVersion.AutoDetect(connection)) .UseLoggerFactory(MyLoggerFactory)//输出sql日志 );
主要是.UseLoggerFactory(MyLoggerFactory) 这一句,然后运行程序,查看打印结果:
info: Microsoft.Hosting.Lifetime[0] Now listening on: http://localhost:5001 info: Microsoft.Hosting.Lifetime[0] Application started. Press Ctrl+C to shut down. info: Microsoft.Hosting.Lifetime[0] Hosting environment: Development info: Microsoft.Hosting.Lifetime[0] Content root path: C:\Users\Ben\Desktop\EFTest\EFTest info: Microsoft.EntityFrameworkCore.Infrastructure[10403] Entity Framework Core 5.0.10 initialized 'TestDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 5.7.22-mysql info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (45ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT `u`.`Id`, `u`.`UserName` FROM `Users` AS `u` WHERE `u`.`Id` = 1 LIMIT 1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (40ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30'] UPDATE `Users` SET `UserName` = @p0 WHERE `Id` = @p1; SELECT ROW_COUNT(); info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (23ms) [Parameters=[@p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30'] INSERT INTO `Users` (`UserName`) VALUES (@p0); SELECT `Id` FROM `Users` WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();
上面的打印结果,显示:
1.查询id==1的数据
2.额外执行了一条update语句
3.执行了一条insert语句
由此可以得出结论:被dbcontext追踪的数据,在被修改后,即使不显式调用update方法,ef也会自动保存数据。
代码不变,再运行一次,看结果:
info: Microsoft.Hosting.Lifetime[0] Now listening on: http://localhost:5001 info: Microsoft.Hosting.Lifetime[0] Application started. Press Ctrl+C to shut down. info: Microsoft.Hosting.Lifetime[0] Hosting environment: Development info: Microsoft.Hosting.Lifetime[0] Content root path: C:\Users\Ben\Desktop\EFTest\EFTest info: Microsoft.EntityFrameworkCore.Infrastructure[10403] Entity Framework Core 5.0.10 initialized 'TestDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 5.7.22-mysql info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (46ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT `u`.`Id`, `u`.`UserName` FROM `Users` AS `u` WHERE `u`.`Id` = 1 LIMIT 1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (24ms) [Parameters=[@p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30'] INSERT INTO `Users` (`UserName`) VALUES (@p0); SELECT `Id` FROM `Users` WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();
怎么没有修改语句了?原因是:model.UserName虽然赋值了,但是实际值和数据库中的值相同,没有发生变化,所以ef不会修改。
个人建议:在不了解ef原理的情况下不要这么写:
public void Index() { //新增一条数据 _testDbContext.Add(new User { UserName = "admin_test" }); //从数据库查询,并修改UserName字段的值,但不显式提交修改 var model = _testDbContext.Users.FirstOrDefault(m => m.Id == 1); model.UserName = "我的测试2"; //保存 _testDbContext.SaveChanges(); }
如果想避免这种情况,其实也是可以的,只要在查询的时候,添加AsNoTracking就行了:
public void Index() { //新增一条数据 _testDbContext.Add(new User { UserName = "admin_test" }); //从数据库查询,并修改UserName字段的值,但不显式提交修改 var model = _testDbContext.Users.AsNoTracking().FirstOrDefault(m => m.Id == 1); model.UserName = "我的测试22"; //保存 _testDbContext.SaveChanges(); }
AsNoTracking的作用是:告诉EntityFramework不对查询结果进行追踪。
测试代码:https://gitee.com/subendong/EFTest
有篇文章写的很好,可以仔细阅读:https://www.cnblogs.com/sheng-jie/p/7416302.html