@dudu EF 在foreach中执行savechange异常
0
悬赏园豆：20
[已解决问题]
解决于 2022-05-18 14:19
功能上是这样设计的：为了避免重复数据插入，对mysql的三个字段进行unique索引，然后在代码里不使用 AddRange ，直接用 Add 并且Add 一次 SaveChanges() 一次，有报错直接忽略。
现在有个问题是，一旦有一次异常以后，后续所有数据无论是否报错都会走catch从而不保存数据。请问下应该如何解决，十分感谢！
下面是代码
var conn = config.GetConnectionString("ZzPollutionSpider");
using (var db = new ZzfqSpiderDbContextFactory().CreateDbContextWithConnStr(conn))
{
foreach (var item in list)
{
try
{
var temp = JsonConvert.SerializeObject(item);
var tempObj = JsonConvert.DeserializeObject<t_pollution_monitor_gas>(temp);
//这里是怀疑是不是跟踪了item，因此转了一下，还是报错
db.t_pollution_monitor_gas.Add(tempObj);
db.SaveChanges();
}
catch (Exception ex)
{
//忽略
_ = ex;
}
}
}
问题补充：
每一次的错误如果序列化了都是这样的：
{"ClassName":"Microsoft.EntityFrameworkCore.DbUpdateException","Message":"An error occurred while updating the entries. See the inner exception for details.","Data":null,"InnerException":{"ClassName":"MySqlConnector.MySqlException","Message":"Duplicate entry '2022-05-18 10:00:00---山东申丰水泥集团有限公司峄州' for key 't_pollution_monitor_gas.u_subid_oname_dt'","Data":null,"InnerException":null,"HelpURL":null,"StackTraceString":" at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 50\r\n at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 119\r\n at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 444\r\n at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60\r\n at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 315\r\n at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 256\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)\r\n at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":null,"HResult":-2147467259,"Source":"System.Private.CoreLib","WatsonBuckets":null,"Number":1062,"SqlState":"23000"},"HelpURL":null,"StackTraceString":" at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)\r\n at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)\r\n at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)\r\n at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)\r\n at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)\r\n at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)\r\n at ZzfqSpider.Saver.SavePluData(IConfiguration config, List`1 list) in F:\\OtherProj\\CoreDemo\\Robot_zoazhuang_air\\ZzfqSpider\\ZzfqSpider\\Jobs\\Saver\\Saver.cs:line 666","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":null,"HResult":-2146233088,"Source":"Microsoft.EntityFrameworkCore.Relational","WatsonBuckets":null}
但是我查看ex的时候，会发现后续所有数据都会在 ex.Entries 里出现
每次都会走catch，请问下有木有思路啥的，再次感谢！
最佳答案
1
只要 id 相同就会被 EF 当作同一实体跟踪，下面的代码并没有解决跟踪问题
var temp = JsonConvert.SerializeObject(item);
var tempObj = JsonConvert.DeserializeObject<t_pollution_monitor_gas>(temp);
//这里是怀疑是不是跟踪了item，因此转了一下，还是报错
建议试试
db.Entry(item).State = EntityState.Detached;
或者
foreach (var item in list.AsNoTracking())
{
}
收获园豆：20
好的，非常感谢，我尝试一下
可能是我没有截图完整，那个 list 是手动 new 的对象集合，本身没有id，也不是从库里查出来的。我尝试了这样
db.t_pollution_monitor_gas.Add(new t_pollution_monitor_gas(){
id=0,
//...将item其他字段手动赋值
});
db.SaveChanges();
仍然会出现同样的错误，错误提示也仍然是第一次出现的那个错误，即使本身数据没有唯一索引冲突，也会走catch，不插入数据
请问下还有别的排查方法嘛
我尝试了将 using(db) 这一段放到foreach里面，这样感觉会每次都新建一个数据库连接，但是的确按照预想的功能实现了，不太清楚为什么在using 里面使用 foreach 会导致这样的问题。。
foreach (var item in list)
{
using (var db = new ZzfqSpiderDbContextFactory().CreateDbContextWithConnStr(conn))
{
try
{
db.t_pollution_monitor_gas.Add(item);
db.SaveChanges();
}
catch (Exception ex)
{
//忽略
_ = ex;
}
db.Dispose();
}
}
@新世界的三温暖: 这样是可以的，有数据库连接池
@dudu: 好嘞，非常感谢~
建议提供一些具体的报错信息
– dudu 2年前@dudu: 补充了一下问题，感谢大神回复~
– 新世界的三温暖 2年前