I've been trying to get a EntityFramework solution working for an upcoming project connecting to a Postgres database, and a majority of the work has been completed. However, when trying to add/update entities in bulk, I've been having some issues and I'm unsure as to whether the error below is masking the actual error with my process:
Database operation expected to affect 1 row(s) but actually affected 0 row(s).
Data may have been modified or deleted since entities were loaded.
See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
I am using the design time context factory pattern to allow my Azure function app to call this, this is my current implementation:
public class ContextFactory : IDesignTimeDbContextFactory<Context>
{
public Context CreateDbContext(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder<Context>();
optionsBuilder.UseNpgsql("{ConnectionString}");
optionsBuilder.EnableDetailedErrors();
optionsBuilder.EnableSensitiveDataLogging();
return new Context(optionsBuilder.Options);
}
}
And my current addition to the services of the function app via Startup.cs:
builder.Services.AddDbContext<ESRContext>(options =>
options.UseNpgsql(Environment.GetEnvironmentVariable("PostgreDBConnectionString")));
There is currently nothing within the table that I'm trying to work with when trying to call the context.SaveChanges() method. Below is my entity model that I've been using:
public class Record
{
public int UserId { get; set; }
public int CourseID { get; set; }
[ConcurrencyCheck]
public int CourseSessionID { get; set; }
public string Code { get; set; }
[ConcurrencyCheck]
public DateTime DateFrom { get; set; }
[ConcurrencyCheck]
public DateTime DateTo { get; set; }
}
(Excuse the formatting, couldn't quite get it to line up!)
I'm working through a file that maps to this entity by row, and adding/updating based on whether this row exists in context or not. For example:
If rows within the same file first add a completion of a course to the context using Context.Records.Add(), and then subsequent rows aim to update it for the user/course combination further down in the file by first getting the entity from the context, modifying that entity with the new properties, and then saving it back using Context.Records.Update(record). Firstly, is this the correct way of doing it? I'll add a snippet of code that follows the basic flow that I've implemented:
try
{
//File rows based through as Records
foreach (var record in fileRecords)
{
var existingRecord = GetRecord(int.Parse(record.UserId), int.Parse(record.CourseId));
if (existingRecord != null)
{
if (existingRecord.DateFrom < DateTime.ParseExact(record.DateFrom, "yyyyMMdd", CultureInfo.InvariantCulture))
{
UpdateRecord(existingRecord, record);
}
}
else
{
_logger.LogInformation(
"User with userId of {userId} on competence {courseId} could not be found in reference store. Adding.",
record.userId, record.courseId
);
var addedRecord = new Data.Entities.Record
{
UserId = int.Parse(record.UserId),
CourseSessionId = int.Parse(record.CourseSessionId),
Code = code,
CourseId = int.Parse(record.CourseId),
DateFrom = string.IsNullOrWhiteSpace(record.DateFrom) ? DateTime.MinValue : DateTime.ParseExact(record.DateFrom, "yyyyMMdd", CultureInfo.InvariantCulture),
DateTo = string.IsNullOrWhiteSpace(record.DateTo) ? DateTime.MinValue : DateTime.ParseExact(record.DateTo, "yyyyMMdd", CultureInfo.InvariantCulture)
};
_context.Records.Add(addedRecord);
}
}
_context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
foreach (var entry in ex.Entries)
{
var record = (Data.Entities.Record)entry.Entity;
_logger.LogInformation("Concurrency issue found: \n"
"UserId {userId} \n"
"CourseId {courseId} \n"
"CourseSessionId {courseSessionId} \n"
"DateFrom: {dateFrom} \n"
"DateTo: {dateTo}",
record.UserId,
record.CourseId,
record.CourseSessionId,
record.DateFrom,
record.DateTo
);
}
}
private void UpdateRecord(Data.Entities.Record existingRecord, FileRecord updatedRecord)
{
existingRecord.CourseSessionId = int.Parse(updatedRecord.CourseSessionId);
existingRecord.DateFrom = string.IsNullOrWhiteSpace(updatedRecord.DateFrom) ? DateTime.MinValue : DateTime.ParseExact(updatedRecord.DateFrom, "yyyyMMdd", CultureInfo.InvariantCulture);
existingRecord.DateTo = string.IsNullOrWhiteSpace(updatedRecord.DateTo) ? DateTime.MinValue : DateTime.ParseExact(updatedRecord.DateTo, "yyyyMMdd", CultureInfo.InvariantCulture);
_context.Records.Update(existingRecord);
}
private Data.Entities.Record GetRecord(int userId, int courseId)
{
var returnedObject = _context.Records.SingleOrDefault(er => er.UserId == userId && er.CourseId == courseId);
return returnedObject;
}
This question has probably gone all over the place with what I'm trying to ask, so apologies! Essentially, I am receiving this error noted above, and I'm unsure as to whether I am missing any best practices or something else, as the error seems really generic and doesn't contain an InnerException when caught.
I will monitor and update this question based on comments received. Hope you guys can help with this issue!
CodePudding user response：
Glad that @JakeRoper could able to solve the issue. Thank you @jdweng for your suggestions that helped the op to fix the issue. Posting this on behalf of your discussion so that it could help other community members.
You are receiving the below error message because you are using both the update() and savechanges() which causes concurrency conflicts. You can use either of one of the functions which can update the record.
Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on >understanding and handling optimistic concurrency exceptions.
If the error still persists the other approach is that the DbUpdateConcurrencyException should be caught during SaveChanges. For more information related to concurrency exceptions, you can refer to Handling Concurrency Conflicts.