instance of entity type cannot be tracked when unit testing ef core

/ bonnu18

Recently I was unit testing a service implementation that handles manipulation of data to and from an API, and I came across this peculiar exception.

System.InvalidOperationException : The instance of entity type 'tblExcludedSellers' cannot be tracked because another instance with the same key value for {'SellerId', 'Username'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

Strangely enough this exception was being thrown only when unit testing and stepping over inside the service endpoint and not during runtime. This happened to me at work but I managed to reproduce the error in the scenario that I have mentioned in my previous post. We have a system that alerts users when PlayStation 5 consoles are back in stock from a list of defined sellers. This system has an API along with a backend service for data transfers. One thing I’d like to point out is that the database is designed the other way round and what I mean by that is that the user will receive a notification from all sellers unless s/he selects the sellers s/he wants to exclude. Yes I know it’s bad database design but this is what I was working with and it wasn’t possible for me to change this. Let’s have a look at our service endpoint which is ran whenever the user would like to update his/her list of sellers.

public class UserSettingsService : IUserSettingsService
{
private readonly StockNotificationContext _dbContext;
public UserSettingsService(StockNotificationContext dbContext)
{
_dbContext = dbContext;
}
public async Task UpdateUserSellerPreferences(string username, IEnumerable<int> sellerIds)
{
var allSellerIds = await _dbContext.tblSellers.Select(x => x.Id).ToListAsync();
var currentExclusions = await _dbContext.tblExcludedSellers.Where(x => x.Username == username).Select(x => x.SellerId).ToListAsync();
// Determine insertions and removals based on provided ids and what's currently in db
var correctExclusions = allSellerIds.Except(sellerIds);
var removals = currentExclusions.Except(correctExclusions)
.Select(x => new tblExcludedSellers
{
Username = username,
SellerId = x,
});
var insertions = correctExclusions.Except(currentExclusions)
.Select(x => new tblExcludedSellers
{
Username = username,
SellerId = x,
});
_dbContext.tblExcludedSellers.AddRange(insertions);
_dbContext.tblExcludedSellers.RemoveRange(removals);
await _dbContext.SaveChangesAsync();
}
view raw efcoretrackedissueone.cs hosted with ❤ by GitHub

The flow can be summarised in the following steps;

  1. Get all the seller IDs.
  2. Get the current user’s excluded sellers.
  3. Compare the list of sellers from Step 1 with the list of IDs from the method parameter. That represents a list of IDs that the user wants to receive notifications from (think along the lines of a user selecting sellers from checkboxes).
  4. Sellers that need to be added or deleted in the database tables are determined by comparing the list of IDs from step 2 and step 3, and then in one transaction the database is updated.

Naturally I wanted to unit test that and this was my first attempt (the one that was giving me the exception).

public class UserSettingsServiceTests
{
private readonly DbContextOptions<StockNotificationContext> _options;
private readonly StockNotificationContext _dbContext;
private readonly UserSettingsService _userSettingsService;
private const string _username = "UnitTestUsername";
public UserSettingsServiceTests()
{
_options = new DbContextOptionsBuilder<StockNotificationContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
_dbContext = new StockNotificationContext(_options);
_userSettingsService = new UserSettingsService(_dbContext);
}
[Fact]
public async Task UpdateUserSellerPreferences_Updates_User_Settings()
{
// Arrange
await _dbContext.Database.EnsureDeletedAsync();
var listOfSellers = new List<tblSellers>();
listOfSellers.Add(new tblSellersBuilder().WithId(1).WithName("Amazon").WithUrl("https://www.amazon.co.uk&quot;).Build());
listOfSellers.Add(new tblSellersBuilder().WithId(2).WithName("Ebay").WithUrl("https://www.ebay.co.uk&quot;).Build());
// other sellers added here
_dbContext.tblSellers.AddRange(listOfSellers);
var listOfExclSellers = new List<tblExcludedSellers>();
listOfExclSellers.Add(new tblExcludedSellersBuilder().WithUsername(_username).WithSellerId(5).Build());
listOfExclSellers.Add(new tblExcludedSellersBuilder().WithUsername(_username).WithSellerId(6).Build());
_dbContext.tblExcludedSellers.AddRange(listOfExclSellers);
await _dbContext.SaveChangesAsync();
var newListOfSellers = new List<int>() { 1, 2, 3, 5 };
// Act
var task = _userSettingsService.UpdateUserSellerPreferences(_username, newListOfSellers);
await task;
// Assert
var updatedList = _dbContext.tblExcludedSellers.Where(x => x.Username == _username).Select(x => x.SellerId).ToList();
Assert.Equal(2, updatedList.Count);
Assert.Contains(4, updatedList);
Assert.Contains(6, updatedList);
}
}
view raw efcoretrackedissuetwo.cs hosted with ❤ by GitHub

This would normally work for me but in this case it didn’t and at first I couldn’t understand why. I googled it up and this is how I understood it. When setting up the unit test I create an in-memory database and add test data to it. When the test data is added it is being tracked (especially since it’s added with the .AsNoTracking() method) and then that same database instance is injected. When the unit test processor attempts to remove or add excluded sellers, the EF core tracker throws the exception as the data is “attached” and is already being tracked. I would like to point out that in my case the database table, tblExcludedSellers, didn’t have a primary key, but a composite key, and didn’t have an identity column (an auto-increment default value). This was highlighted as a potential issue in this thread. If it’s of any help here’s my database table key binding found inside the context’s model creating method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<tblExcludedSellers>(entity =>
{
entity.HasKey(e => new { e.SellerId, e.Username });
});
}
view raw efcoretrackedissuefour.cs hosted with ❤ by GitHub

I solved this by creating a separate in-memory database but using the same database context options and injecting different instance, again same database context options.

public class UserSettingsServiceTests
{
private readonly DbContextOptions<StockNotificationContext> _options;
private readonly StockNotificationContext _dbContext;
private readonly UserSettingsService _userSettingsService;
private const string _username = "UnitTestUsername";
public UserSettingsServiceTests()
{
_options = new DbContextOptionsBuilder<StockNotificationContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
_dbContext = new StockNotificationContext(_options);
_userSettingsService = new UserSettingsService(_dbContext);
}
[Fact]
public async Task UpdateUserSellerPreferences_Updates_User_Settings()
{
// Arrange
await _dbContext.Database.EnsureDeletedAsync();
using (var seedingContext = new StockNotificationContext(_options))
{
var listOfSellers = new List<tblSellers>();
listOfSellers.Add(new tblSellersBuilder().WithId(1).WithName("Amazon").WithUrl("https://www.amazon.co.uk&quot;).Build());
listOfSellers.Add(new tblSellersBuilder().WithId(2).WithName("Ebay").WithUrl("https://www.ebay.co.uk&quot;).Build());
// other sellers added here
seedingContext.tblSellers.AddRange(listOfSellers);
var listOfExclSellers = new List<tblExcludedSellers>();
listOfExclSellers.Add(new tblExcludedSellersBuilder().WithUsername(_username).WithSellerId(5).Build());
listOfExclSellers.Add(new tblExcludedSellersBuilder().WithUsername(_username).WithSellerId(6).Build());
seedingContext.tblExcludedSellers.AddRange(listOfExclSellers);
await seedingContext.SaveChangesAsync();
}
var newListOfSellers = new List<int>() { 1, 2, 3, 5 };
// Act
var task = _userSettingsService.UpdateUserSellerPreferences(_username, newListOfSellers);
await task;
// Assert
var updatedList = _dbContext.tblExcludedSellers.Where(x => x.Username == _username).Select(x => x.SellerId).ToList();
Assert.Equal(2, updatedList.Count);
Assert.Contains(4, updatedList);
Assert.Contains(6, updatedList);
}
}
view raw efcoretrackedissuethree.cs hosted with ❤ by GitHub

What also worked for others was detaching the entities after adding them, as pointed out in this Stack Overflow thread. It seems that this exception is some what common but from what I understood by going through different threads is that this exception can be thrown for different reason (and not specifically for the scenario I created in this post). Having said that the above might work for you and for that reason I uploaded my solution to GitHub for anyone who like to fiddle around with the code. Thanks a lot for reading and feel free to comment below if you feel I might have missed something out.

Until next post,
Bjorn

Leave a comment