c# - 更新大量数据时重复键值错误

Question

当我想在数据库(PostgreSQL 12 和 Entity Framework Core (C#))中保存大量数据时，出现以下异常:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (197ms) [Parameters=[@p0='?', @p1='?', @p2='?' (DbType = DateTimeOffset), @p3='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "FileInfos" ("FileId", "FileName", "LastModifiedDateTime", "Path")
      VALUES (@p0, @p1, @p2, @p3);
fail: Microsoft.EntityFrameworkCore.Update[10000]
  An exception occurred in the database while saving changes for context type 'PostgreSQLConnect.ContextModels.WebhookContext'.
  Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
   ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_FileInfos 
    Severity: FEHLER
      SqlState: 23505
      MessageText: double key value violates unique constraint »PK_FileInfos«
      Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
      SchemaName: public
      TableName: FileInfos
      ConstraintName: PK_FileInfos
      File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\access\nbtree\nbtinsert.c
      Line: 570
      Routine: _bt_check_unique

有些数据需要更新，有些需要先创建。此方法适用于某些数据，但如果我一次添加多个数据，则会出现上述异常。

    private async Task SaveFileInfos(FileInfo fileInfo)
    {
        var foundFileInfo = _context.FileInfos.
        Where(f => f.FileId == fileInfo.FileId).FirstOrDefault();

        if (foundFileInfo == null)
        {
            await _context.FileInfos.AddAsync(fileInfo);
        }
        else
        {
            foundFileInfo.FileName = fileInfo.FileName;
            foundFileInfo.LastModifiedDateTime = fileInfo.LastModifiedDateTime;
            foundFileInfo.Path = fileInfo.Path;
        }

        await _context.SaveChangesAsync();
    }
     

实体类:

    public class FileInfo : IFileInfo
    {

    [Key]
    public string FileId {get; set;}

    public string FileName {get; set;}

    public DateTimeOffset? LastModifiedDateTime {get; set;}

    public string Path {get; set;}
    }

上下文类:

   public class WebhookContext : DbContext
   {
    public WebhookContext(DbContextOptions<WebhookContext> options) : base(options) { }

    public DbSet<FileInfo> FileInfos { get; set; }
    }

调用保存方法的循环:

 private async Task ConvertAndSaveFiles(IDriveItemDeltaCollectionPage files)
  { 

    foreach (var file in files)
    {
         await SaveFileInfos(file.Name, file.Id, file.LastModifiedDateTime,
                    file.ParentReference.Path);
    }
           
  }

顺便说一句:该 ID 已由另一个应用程序生成，长度为 34 个字符。

作为初学者，我犯了什么错误？ :-)

Answer 1

• 使用FirstOrDefaultAsync
• Where子句是多余的，你也可以去掉它

    private async Task SaveFileInfos(FileInfo fileInfo)
    {
        //update your code to use FirstOrDefaultAsync
        var foundFileInfo = await _context.FileInfos
        .FirstOrDefaultAsync(f => f.FileId == fileInfo.FileId);

        if (foundFileInfo == null)
        {
            await _context.FileInfos.AddAsync(fileInfo);
        }
        else
        {
            foundFileInfo.FileName = fileInfo.FileName;
            foundFileInfo.LastModifiedDateTime = fileInfo.LastModifiedDateTime;
            foundFileInfo.Path = fileInfo.Path;
        }

       // move this outside the for loop.
       // this will round trip to Db in EVERY fileInfo, not an optimal solution.
        await _context.SaveChangesAsync(); 
    }

• 考虑调用 await _context.SaveChangesAsync();在 for 循环之外

 private async Task ConvertAndSaveFiles(IDriveItemDeltaCollectionPage files)
  { 

    foreach (var file in files)
    {
         await SaveFileInfos(file.Name, file.Id, file.LastModifiedDateTime,
                    file.ParentReference.Path);
    }

    // this will save everything to Db in just 1 round trip
    await _context.SaveChangesAsync(); 
}