首页 新闻 搜索 专区 学院

@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,请问下有木有思路啥的,再次感谢!

新世界的三温暖的主页 新世界的三温暖 | 初学一级 | 园豆:184
提问于:2022-05-18 11:49

建议提供一些具体的报错信息

dudu 1个月前

@dudu: 补充了一下问题,感谢大神回复~

新世界的三温暖 1个月前
< >
分享
最佳答案
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
dudu | 高人七级 |园豆:36444 | 2022-05-18 12:14

好的,非常感谢,我尝试一下

新世界的三温暖 | 园豆:184 (初学一级) | 2022-05-18 12:19

可能是我没有截图完整,那个 list 是手动 new 的对象集合,本身没有id,也不是从库里查出来的。我尝试了这样

db.t_pollution_monitor_gas.Add(new t_pollution_monitor_gas(){
id=0,
//...将item其他字段手动赋值
});
db.SaveChanges();

仍然会出现同样的错误,错误提示也仍然是第一次出现的那个错误,即使本身数据没有唯一索引冲突,也会走catch,不插入数据

请问下还有别的排查方法嘛

新世界的三温暖 | 园豆:184 (初学一级) | 2022-05-18 13:01

我尝试了将 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();
    }

}
新世界的三温暖 | 园豆:184 (初学一级) | 2022-05-18 13:30

@新世界的三温暖: 这样是可以的,有数据库连接池

dudu | 园豆:36444 (高人七级) | 2022-05-18 14:00

@dudu: 好嘞,非常感谢~

新世界的三温暖 | 园豆:184 (初学一级) | 2022-05-18 14:18
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册