When I want to save a lot of data in the database (PostgreSQL 12 and the Entity Framework Core (C#)) I get the following exceptions:

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

Some data needs to be updated, some needs to be created first. This method works for some data, but if I add more than one at a time, I get the exception mentioned above.

    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();
    }
     

The entity class:

    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;}
    }

The context class:

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

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

The loop that calls the save method:

 private async Task ConvertAndSaveFiles(IDriveItemDeltaCollectionPage files)
  { 

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

}

BTW: The Id has already been generated by another application and is 34 characters long.

What mistake did I make as a beginner? :-)

CodePudding user response：

Since you mentioned this problem occurs when you are writing to database a lot, the problem might be how are you calling SaveFileInfos(FileInfo fileInfo) method (calling from parallel loop?) if that's the case your foundFileInfo call won't catch duplicates.

Also consider avoiding SaveChangesAsync inside a loop.

CodePudding user response：

• Use FirstOrDefaultAsync
• Where clause is redundant, you can remove it as well

    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(); 
    }

• Consider calling the await _context.SaveChangesAsync(); outside the for loop

 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(); 
}