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

12/14/202012/14/2020 / bonnu18 / Leave a comment

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;

Get all the seller IDs.
Get the current user’s excluded sellers.
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).
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").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(2).WithName("Ebay").WithUrl("https://www.ebay.co.uk").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").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(2).WithName("Ebay").WithUrl("https://www.ebay.co.uk").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

using entity framework core in-memory database provider to create a database on the fly

12/11/202012/11/2020 / bonnu18 / 1 Comment

Were you ever in a situation where you needed a quick, handy database but didn’t want to spend a lot of time connecting everything up? Maybe you just need to test a small database table but importing the entire schema takes ages? Well, that was my case and after managing to get one up and running, I wanted to share with you how I got there. The technologies I’m currently working on are .NET Core v3.1 and using Entity Framework Core v5.0 (Nuget package Microsoft.EntityFrameworkCore v5.0.1). Additionally I also had to install the Nuget package Microsoft.EntityFrameworkCore.InMemory.

The scenario in this case is the following; imagine we have a system that notifies users when a PlayStation 5 is in stock at a seller’s store (it would be a million dollar idea right now 😀). There’s a defined list of PS5 seller (Amazon, Ebay, etc) and a user can select to receive stock notifications from this list of sellers. For the sake of this blogpost there’s no front end technologies but just an API. In fact in this case I create a new Visual Studio solution and selected the ASP.NET Core Web Application template and left the standard API option.

Next thing I did was add a new class library project and this will serve as the data layer. I then added a new model with properties to mimic a database table, and how it would be created by the EF model creation process. Similarly I created another class to act as the database context. These implementation can be found below.

	public partial class tblSellers
	{
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int Id { get; set; }
	public string Name { get; set; }
	public string Url { get; set; }
	}

view raw efcoreinmemoryone.cs hosted with ❤ by GitHub

	public partial class StockNotificationContext : DbContext
	{
	public StockNotificationContext()
	{
	}

	public StockNotificationContext(DbContextOptions<StockNotificationContext> options) : base(options)
	{
	}

	public virtual DbSet<tblSellers> tblSellers { get; set; }
	}

view raw efcoreinmemorytwo.cs hosted with ❤ by GitHub

Then I added another class to generate and build the data. All the individual property methods and randomisation of data aren’t really necessary but it’s a practice that we follow at work and I kind of picked up this good habit. I find them useful when working on unit tests as you can play around with data to satisfy test criteria.

	public class tblSellersBuilder
	{
	private readonly tblSellers _tblSellers;
	private readonly Random _random;

	public tblSellersBuilder(Random random = null)
	{
	_random = random ?? new Random();
	_tblSellers = new tblSellers
	{
	Id = _random.Next(),
	Name = _random.Next().ToString(),
	Url = _random.Next().ToString(),
	};
	}

	public tblSellers Build()
	{
	return _tblSellers;
	}

	public tblSellersBuilder WithId(int id)
	{
	_tblSellers.Id = id;
	return this;
	}

	public tblSellersBuilder WithName(string name)
	{
	_tblSellers.Name = name;
	return this;
	}

	public tblSellersBuilder WithUrl(string url)
	{
	_tblSellers.Url = url;
	return this;
	}

	public static void Initialize(StockNotificationContext stockNotificationContext)
	{
	var listOfSellers = new List<tblSellers>();
	listOfSellers.Add(new tblSellersBuilder().WithId(1).WithName("Amazon").WithUrl("https://www.amazon.co.uk").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(2).WithName("Ebay").WithUrl("https://www.ebay.co.uk").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(3).WithName("Currys PC World").WithUrl("https://www.currys.co.uk").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(4).WithName("Argos").WithUrl("https://www.argos.co.uk").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(5).WithName("Smyths").WithUrl("https://www.smythstoys.com").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(6).WithName("Target").WithUrl("https://www.target.com").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(7).WithName("Best Buy").WithUrl("https://www.bestbuy.com").Build());
	listOfSellers.Add(new tblSellersBuilder().WithId(8).WithName("Walmart").WithUrl("https://www.walmart.com").Build());

	stockNotificationContext.tblSellers.AddRange(listOfSellers);
	stockNotificationContext.SaveChanges();
	}
	}

view raw efcoreinmemorythree.cs hosted with ❤ by GitHub

I then registered the database and context in the Startup.cs file, ConfigureServices method. After that I added a call to the data generator class in the Configure service so that when my application loads it would have some data to work with.

	public void ConfigureServices(IServiceCollection services)
	{
	services.AddControllers();
	services.AddDbContext<StockNotificationContext>(options => options.UseInMemoryDatabase(databaseName: "StockNotification"));
	services.AddScoped<IUserSettingsService, UserSettingsService>();
	}

	public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	{
	// this should be here when you create the solution
	app.UseHttpsRedirection();
	app.UseRouting();
	app.UseAuthorization();
	app.UseEndpoints(endpoints =>
	{
	endpoints.MapControllers();
	});

	using (var serviceScope = app.ApplicationServices.CreateScope())
	{
	var dbContext = serviceScope.ServiceProvider.GetService<StockNotificationContext>();
	tblSellersBuilder.Initialize(dbContext);
	}
	}

view raw efcoreinmemoryfour.cs hosted with ❤ by GitHub

I created another class library project to act as the business layer. In here I added a new service and a service endpoint to get a list of sellers (the same that I generate on application start up). This service is the same one registered in the Startup.cs above. Finally to wrap it up, I created a new controller in the original API project and referenced the service I have just created to be able to provide the user with the list of sellers. Implementations below. I also created an interface for the service and a DTO to return a list of that instead of the database model but omitting them here to keep it short(ish).

	public class UserSettingsService : IUserSettingsService
	{
	private readonly StockNotificationContext _dbContext;

	public UserSettingsService(StockNotificationContext dbContext)
	{
	_dbContext = dbContext;
	}

	public async Task<IEnumerable<Seller>> GetSellers()
	{
	return await _dbContext.tblSellers
	.Select(x => new Seller
	{
	Id = x.Id,
	Name = x.Name,
	Url = x.Url
	})
	.ToListAsync();
	}
	}

view raw efcoreinmemoryfive.cs hosted with ❤ by GitHub

	[ApiController]
	[Route("[controller]")]
	public class SettingsController : ControllerBase
	{
	private readonly IUserSettingsService _userSettingsService;

	public SettingsController(IUserSettingsService userSettingsService)
	{
	_userSettingsService = userSettingsService;
	}

	[HttpGet]
	public ObjectResult GetSellers()
	{
	return Ok(_userSettingsService.GetSellers().Result);
	}
	}

view raw efcoreinmemorysix.cs hosted with ❤ by GitHub

And that should be enough to have a database working in memory during runtime! At the beginning of this post I came up with this stock notification scenario and that is tied to another post which I will be writing in the coming days. I will discuss an EF related issue I came across and how to fix it. I will also be putting a link to the entire solution on GitHub. If this post helps and would like to donate a PS5 please get in touch 😀

Until next post,
Bjorn

installing dotnet ef tool in order to scaffold entity framework database context

09/16/202009/16/2020 / bonnu18 / Leave a comment

In this blog post we’re going to be looking at creating the database context and models in Entity Framework Core specifically using command-line interface (CLI) tools. At the time of writing the target framework of my project was .NET Core 3.1 along with Entity Framework Core 3.1.4. I like to use the Package Manager Console as my CLI since, as a software developer, I’ll be using Visual Studio as my IDE so that’s an assumption I’ll be making for this post. So first thing I tried was to execute the following command.

dotnet ef dbcontext scaffold {Connection_String} --project {Project_Name} Microsoft.EntityFrameworkCore.SqlServer --use-database-names --output-dir {Output_Directory_Name} --context {Context_Name} --verbose --force

Problem was that as soon as I tried to execute that command I got the following error.

dotnet : Could not execute because the specified command or file was not found.
At line:1 char:1
+ dotnet ef dbcontext scaffold "Connection_String_In_Error, 1 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (Could not execu... was not found.:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError

Possible reasons for this include:
* You misspelled a built-in dotnet command.
* You intended to execute a .NET Core program, but dotnet-ef does not exist.
* You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.

First hiccup! Ok turns out that the dotnet ef tool is no longer part of the .NET Core SDK. I discovered this after a bit of digging and I even found the announcement by Microsoft themselves. So the next step was to install the dotnet ef tool and I’ve done that by executing this command

dotnet tool install --global dotnet-ef

That should install the latest version of the dotnet ef tool but for some reason it threw this error for me.

The tool package could not be restored.
At line:1 char:1
+ dotnet tool install --global dotnet-ef
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (The tool package could not be restored.:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Tool 'dotnet-ef' failed to install. This failure may have been caused by:
* You are attempting to install a preview release and did not use the --version option to specify the version.
* A package by this name w
as found, but it was not a .NET Core tool.
* The required NuGet feed cannot be accessed, perhaps because of an Internet connection problem.
* You mistyped the name of the tool.

Second hiccup! Again, turns out that that command does not download and install the latest verison of the dotnet ef tool and in order to successfully install the tool you need to specify a version. Here’s the command including the latest version at the time of writing.

dotnet tool install --global dotnet-ef --version 3.1.8

Here’s a full list of versions of the dotnet ef tool. With our tool installed we can now go back to our original goal and scaffold our database in order to get the latest database changes. Well that’s a wrap and I hope this blog post has helped you as much as it has helped me.

Until next blog post,
Bjorn

using nameof operator in entity framework

11/12/201702/27/2020 / bonnu18 / Leave a comment

Recently while working on piece of code I wanted to delete a set of data satisfying a condition and therefore it would be a simple Delete statement with a Where clause. Since the data would be retrieved using Entity Framework the SQL statement would be written in a string and then executed using the ExecuteSqlCommand method. The idea was to have the SQL statement written in bits and pieces and then by means of method parameters I could execute it on different tables. Something like the following.

	public void ClearTableWWhereClause(string strConnectionString, string strTableName, string strColumnName, string strValue)
	{
	string strQuery = "DELETE FROM [" + strTableName + "] WHERE " + strColumnName + " = '" + strValue + "'";
	using (StatementsBenchmarksEntities dbContext = new StatementsBenchmarksEntities(strConnectionString))
	{
	dbContext.Database.ExecuteSqlCommand(strQuery);
	}
	}

view raw

nameofentityframeworkone.cs

hosted with ❤ by GitHub

So far so good. The problem I faced was when trying to get the column name dynamically instead of writing it hard coded in the parameter field when calling the method. Let’s say we have the following class called Blog which is a model of the database table with the same name.

	public partial class Blog
	{
	public int ID_PK { get; set; }
	public string Title { get; set; }
	public string Author { get; set; }
	public string Content { get; set; }
	}

view raw

nameofentityframeworktwo.cs

hosted with ❤ by GitHub

Getting the table name was fairly easy and this was done by first creating an instance of the class and then calling the GetType method like so – objBlog.GetType().Name. On the other hand getting the column name proved to be a little bit more difficult. A quick Google search and visit to StackOverflow suggested that I should use a loop and get all column names but I wasn’t satisfied with that solution. I found other solutions but again they didn’t really impress me. I then started to think and treated the model as a class rather than a DB object. I then realised that I was interested in a property of a class and not in a column name of a table. The simple solution that worked for me was to use the “newly” introduced nameof operator. This operator is, and quoting from Microsoft’s own website, used to obtain the simple (unqualified) string name of a variable, type, or member. Implemented it my own solution and the end result looked something like the following line.

ClearTableWWhereClause(ConnectionString, objBlog.GetType().Name, nameof(objBlog.Author), "Bjorn Micallef");

view raw

nameofentityframeworkthree.cs

hosted with ❤ by GitHub

Nice, neat and effective. It surely did the trick for me and will certainly implement it again in the future.

Bjorn

entity framework model did not update

10/13/201710/13/2017 / bonnu18 / 1 Comment

Recently I was working on this project and I was asked to create a new module to create a new feature. The C# solution, as most technologies, makes use of a database in order to fetch and store data. Basic CRUD operations. As you might have already guessed from the title of this post the project makes use of ADO.NET and Entity Framework to perform such processes. I will have to admit that I am not a fan of ADO.NET or Entity Framework. Personally speaking I prefer to create scripts, stored procedures, views, etc and then open connection strings and retrieve the data as data tables/data sets. Having said that, that was my opinion, and since the project was already in a stable running state I had to adapt and work with Entity Framework, which I’m also comfortable with.

The problem happened when I created a new table via SQL Server Management Studio, let’s call it tbl_weather. This new table would have historical data and store “raw” data that would be processed later on. tbl_weather would have three columns and roughly this type of data.

tbl_weather

I then switched to Visual Studio and tried to update my Entity Framework model. Right click > Update model from database > Next > Next > Finish … job done. But wait, where’s tbl_weather?? I tried this step again and again and nothing happened. The connection string was good because my new table was being detected when attempting to update my model from the database. So what is wrong with it?

After a while I tried to delete my table and re-create it but this time round I added another column, ID, and set it as unique primary key. I tried to update my model once more and this time round the table was added to my model straight away. To my understanding it seems that Entity Framework would not accept to add “basic” tables but it would require the new tables to be somewhat more structured. In this case it seemed that not having one of the most basic SQL standard, a primary key, was preventing me from adding my table to my model.

Now, I know that a primary key is a must and that a unique identifier must be present but in this case I was fetching the data from a source and bulk insert it into this table. This data would then be retrieved again, processed and insert in yet another new, better structured table. Therefore, I didn’t really worry the primary key.

Anyways, that’s my two pence that I wanted to share with you guys, until next time, adios!

Bjorn

It's not a bug, it's a feature

from the average coder to the average coder

entityframework