I’m working on an asynchronous method in C# that inserts a list of records into a SQL Server database using Entity Framework Core. The method is intended to bulk insert data into the database and save the changes.
public async Task<int> PersistDataAsync(List<ExportData> dataList)
{
var groupedData = dataList
.SelectMany(data => data.Data)
.SelectMany(customer => customer.ItemData, (customer, item) => new { customer.CustomerId, Item = item })
.GroupBy(x => x.CustomerId)
.ToDictionary(g => g.Key, g => g.Select(x => x.Item).ToList());
int persistedCount = 0;
foreach (var custId in groupedData.Keys)
{
var connectionString = GetConnectionString(_serviceProvider, custId);
if (connectionString == null)
{
_logger.LogError($"Could not retrieve connection string for customer {custId}");
continue;
}
using (var context = CreateDbContext(connectionString, custId, _configuration.GetValue<int>("DbCommandTimeout")))
{
if (!IsDatabaseConnected(context, custId))
{
continue;
}
IDataRepository dataRepository = new DataRepository(context, _dataLogger);
persistedCount += await dataRepository.PersistData(groupedData[custId].ToList());
}
}
return persistedCount;
}
public async Task<int> PersistDataAsync(
IEnumerable<DataRecord> dataList)
{
var data = dataList.ToList();
return await AppendRecords(data);
}
public async Task<int> AppendRecords(List<YourEntityType> records)
{
await _db.YourEntityType.AddRangeAsync(records);
int recordsAdded = 0;
try
{
recordsAdded = await _db.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
_logger.LogError(ex, "{MethodName} error {ExceptionMessage}",
nameof(AppendRecords),
ex.InnerException?.Message);
}
return recordsAdded;
}
The error seems to occur when Entity Framework tries to track multiple instances of the same entity type (YourEntityType) with the same key values. I suspect that the list of records may contain duplicate entities, or there may be another part of the application that is already tracking an instance with the same key.
How can we solve this issue?
9
AddRange is only suitable for very simple and safe insert operations. When using AddRange with entities, those entities must be:
- unique, no duplicates.
- Do not already exist in the database.
- Simple, contain no references to any record already in the database.
What point #3 means is if I have a list of Orders to insert, and the Order has a reference to an OrderStatus entity to an OrderStatuses table for referential integrity and I try to insert orders with a status of “Pending” I can end up getting this error because I want to associate these new orders to the existing “Pending” order status, but the reference to each row’s OrderStatus entity will be un-tracked so it will try to also insert rows for the Pending order status.
To satisfy point #1 you need to first sanitize your input for duplicates. Depending on where this data comes from, if there is the possibility of a duplicate, remove them.
To satisfy point #2 will depend on whether you intend to be only inserting new rows, or updating vs. inserting whether an existing row is found or not. If you are looking to perform an “Upsert” then you will need to split your inserts from your updates based on whether the ID is found/populated or not. Updates involve fetching the existing entity from the DbContext and copying values across. Inserts can be added.
To satisfy point #3 can be the most work depending on how simple or complex the object model is. For any and all other entities that might be referenced you need to ensure the reference is associated to the DbContext before inserting the main entity. For references this can be done via Attach or by fetching the referenced entity from the DbContext. If using Attach you still need to first check the tracking cache. For example if YourEntityType has a reference to a Customer, being an existing customer record in your DB:
foreach (var record in records)
{
var existingCustomer = _context.Customers.Local.FirstOrDefault(x => x.CustomerId == record.Customer.CustomerId);
if (existingCustomer != null)
record.Customer = existingCustomer;
else
_context.Attach(record.Customer);
_context.YourEntityTypes.Add(record);
}
Checking the “.Local” goes to the tracking cache, not the database. If we find an existing customer being tracked we replace the reference since record.Customer may have the same ID but it is an untracked instance which EF will consider as a new Customer record. If we aren’t tracking the Customer then we can Attach it, which will start tracking it and treat it as an existing Customer record. IF we just call Attach without first checking the cache, it will work if there is no tracked entity, but it will fail with an exception if another instance of the Customer is already tracked. Only after all references are tracked can we add the Record.