dbContext tracking error

Question

Hi,

I have this error on add record event, 'The instance of entity type 'MyRecords' cannot be tracked because another instance with the same key value for {'Field1', 'Field2', 'Field3', 'Field4', 'Field5'} is already being tracked.

All these field are primary key in the table, but i have another field to include that could be null(Field6).

This is the code:

var dbValue = _dbContext.MyRecords.AsNoTracking().Any
                                (x =>
                                 x.Field1 == destinationRecord.Field1 &&
                                 x.Field2 == destinationRecord.Field2 &&
                                 x.Field3 == destinationRecord.Field3 &&
                                 x.Field4 == destinationRecord.Field4 &&
                                 x.Field5 == destinationRecord.Field5 &&
                                 x.Field6 == destinationRecord.Field6);
              
if (dbValue==false)
{                       
    _dbContext.MyRecords.Add(destinationRecord);        
}
else
{                   
    _dbContext.MyRecords.Update(destinationRecord);                  
}

_dbContext.SaveChanges();

The only way i have found is to add an autoincrement primary key but with this filed i can only add record...

Thanks to everyone

Mohamed Azarudeen Z · Answer

To resolve this issue, you can try the following approach: Remove the AsNoTracking() method when checking for the existence of the record. By default, Entity Framework Core tracks entities retrieved from the database, so you don't need to explicitly disable tracking in this case. Check if the record exists in the database using the primary key fields only. Since the Field6 field can be null, you can modify the condition to handle this case separately. Here's an updated version of your code: var dbValue = _dbContext.MyRecords.Any(x => x.Field1 == destinationRecord.Field1 && x.Field2 == destinationRecord.Field2 && x.Field3 == destinationRecord.Field3 && x.Field4 == destinationRecord.Field4 && x.Field5 == destinationRecord.Field5); if (!dbValue) { _dbContext.MyRecords.Add(destinationRecord); } else { var existingRecord = _dbContext.MyRecords.FirstOrDefault(x => x.Field1 == destinationRecord.Field1 && x.Field2 == destinationRecord.Field2 && x.Field3 == destinationRecord.Field3 && x.Field4 == destinationRecord.Field4 && x.Field5 == destinationRecord.Field5); if (existingRecord != null) { existingRecord.Field6 = destinationRecord.Field6; _dbContext.MyRecords.Update(existingRecord); } } _dbContext.SaveChanges();

Tuhin Paul · Answer

The code assumes that the Field6 property can be null. If this is not the case and Field6 is expected to always have a non-null value, you should modify the code accordingly to handle this constraint.

Tuhin Paul · Answer

If multiple users or processes are executing this code concurrently, there is a possibility of a race condition. Two or more processes might check for the existence of the record at the same time, find that it doesn't exist, and attempt to add it, resulting in duplicate records.

Tuhin Paul · Answer

The code performs two separate database queries to check for the existence of the record and to retrieve the existing record if it exists. Depending on the size of the table and the complexity of the queries, this could potentially impact performance, especially if the table contains a large number of records.

cjard · Answer

Like this: var dbValue = _dbContext.MyRecords.FirstOrDefault (x => x.Field1 == destinationRecord.Field1 && x.Field2 == destinationRecord.Field2 && x.Field3 == destinationRecord.Field3 && x.Field4 == destinationRecord.Field4 && x.Field5 == destinationRecord.Field5); if (dbValue==default) { _dbContext.MyRecords.Add(destinationRecord); } else { dbValue.Field6 = destinationRecord.Field6; } _dbContext.SaveChanges(); You want to match on the first 5 fields only, otherwise there will never be a case to update, only add. There isn't any point asking the database if the record exists using Any, then if it does, downloading it. You should just ask once (first or default) and then act appropriately depending on if you got a record or not. You can also use the Find method in place of FirstOrDefault; it's simpler to write and if your source data has repeated records then it will lookup locally first rather than hitting the db every time- that may be useful to you or may not (you decide) (I think the accepted answer was pasted straight from ChatGPT...)

Mic Got · Answer

I have make some test and i have see that some record go into update condition and not in add. Example: First record: (PK) Field1 =1 (PK) Field2= 1 (PK) Field3= 1 (PK) Field4= 1 (PK) Field5= 1 (Nullable) Field6= 1 Go in Add ------------------------ Secon record: (PK) Field1 =1 (PK) Field2= 1 (PK) Field3= 1 (PK) Field4= 1 (PK) Field5= 1 (Nullable) Field6=2 Go in update as the PK field are the same the goal is to add the record if Field6 is different. If i modify the if statement adding condition: if (!dbValue) && destination.record !=null i obtain the error on add record for dbcontext tracking.... Any idea?

Mic Got · Answer

Great solution! Thanks for code and explanation. Problem solved.