Using Entity Framework Core in-memory database for unit testing
ASP.NET Core applications can be tested with different testing frameworks and Entity Framework Core makes testing specially easy by removing different technical problems from our way by using in-memory data provider. This blog posts shows how to unit test controllers that use data from Entity Framework Core.
NB! The code and tests given here are really minimal and illustrative. In real situations there are probably at least some service classes that carry use cases initiated from controllers. Still the code similar to what we have here can be used in smaller utility applications. For basic about ASP.NET Core and xUnit please see my blog post Using xUnit with ASP.NET Core.
In-memory database
Entity Framework Core makes testing of data related controllers easier. We don’t have to necessarily mock or fake database context as context is not affected by selection of database. We build our enitites and database context and what database we use we decide in application startup class. Of course there can be differences in different database providers and their capabilities but in the light of unit testing it’s the matter of integration tests.
To avoid mocking and faking Entity Framework Core provides us with in-memory data provider. It has no database back-end and its only purpose is to support scenarios where persistent data storage is not needed. One of the scenarios is unit testing.
Simple product catalog
This blog post builds on simple product catalog that uses SQL Server as database. We start with defining product and category entities.
public class Category
{
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
}
public class Product
{
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
[Required]
public Category Category { get; set; }
}
To use these classes with databases and EntityFramework Core we need also database context class.
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
public DbSet<Category> Categories { get; set; }
public DbSet<Product> Products { get; set; }
}
To make application use our database context with SQL Server we have to add the following block of code to ConfigureServices() method of Startup class.
services.AddDbContext<MyDbContext>(options =>
{
options.UseSqlServer(Configuration["ConnectionStrings:Default"]);
});
This code expects that we have connection string called Default defined in application settings file.
Now let’s write simple controller to display data from product catalog.
public class HomeController : Controller
{
private readonly MyDbContext _context;
public HomeController(MyDbContext context)
{
_context = context;
}
public IActionResult Index()
{
var model = new FrontPageModel();
model.Top5 = _context.Products.Take(5);
model.Items = _context.Products.Take(10);
return View(model);
}
public IActionResult Category(int id)
{
var category = _context.Categories.FirstOrDefault(c => c.Id == id);
if(category == null)
{
return NotFound();
}
var products = _context.Products.Where(p => p.Category.Id == id);
return View(products);
}
protected override void Dispose(bool disposing)
{
if(!disposing)
{
_context.Dispose();
}
base.Dispose(disposing);
}
}
To show data on front-page we need view model for top 5 products and list of products.
public class FrontPageModel
{
public IEnumerable<Product> Top5;
public IEnumerable<Product> Items;
}
When instance of controller is created the database context is injected to its constructor by framework. Index method asks five products for products top five and first ten products for products list. Category method takes category id as argument and displays ten products from given category. If category is not found then error 404 is returned.
Preparing for testing
For testing we need project for tests and some testing framework. I went with xUnit as it works also with .NET Core and we can also use it on Linux and Apple platforms. For unit tests we add new xUnit Test Project to our solution.
For tests there is empty class with one dummy method that shows how test method should be written so it is understandable for test framework. In xUnit world methods decorated with Fact attribute are tests.
public class UnitTest1
{
[Fact]
public void Test1()
{
}
}
As our controller needs database context we have to provide it with one. But there’s one trick – we don’t want SQL Server to be involved as we are not writing integration tests. In this point EntityFramework Core makes us huge favor. Without changing anything in database context we can make Entity Framework Core use in-memory data store that is design for testing and other scenarios where we don’t need persistent storage for data.
Looking at Index() and Category methods we also see that we need some test data. As we need database context with test data probably in more than one test we will add GetContextWithData() method to our test class.
private MyDbContext GetContextWithData()
{
var options = new DbContextOptionsBuilder<MyDbContext>()
.UseInMemoryDatabase(Guid.NewGuid().ToString())
.Options;
var context = new MyDbContext(options);
var beerCategory = new Category { Id = 1, Name = "Beers" };
var wineCategory = new Category { Id = 2, Name = "Wines" };
context.Categories.Add(beerCategory);
context.Categories.Add(wineCategory);
context.Products.Add(new Product { Id = 1, Name = "La Trappe Isid'or", Category = beerCategory });
context.Products.Add(new Product { Id = 2, Name = "St. Bernardus Abt 12", Category = beerCategory });
context.Products.Add(new Product { Id = 3, Name = "Zundert", Category = beerCategory });
context.Products.Add(new Product { Id = 4, Name = "La Trappe Blond", Category = beerCategory });
context.Products.Add(new Product { Id = 5, Name = "La Trappe Bock", Category = beerCategory });
context.Products.Add(new Product { Id = 6, Name = "St. Bernardus Tripel", Category = beerCategory });
context.Products.Add(new Product { Id = 7, Name = "Grottenbier Bruin", Category = beerCategory });
context.Products.Add(new Product { Id = 8, Name = "St. Bernardus Pater 6", Category = beerCategory });
context.Products.Add(new Product { Id = 9, Name = "La Trappe Quadrupel", Category = beerCategory });
context.Products.Add(new Product { Id = 10, Name = "Westvleteren 12", Category = beerCategory });
context.Products.Add(new Product { Id = 11, Name = "Leffe Bruin", Category = beerCategory });
context.Products.Add(new Product { Id = 12, Name = "Leffe Royale", Category = beerCategory });
context.SaveChanges();
return context;
}
Notice how we name in-memory database by GUID. This is to make sure that every test run has new database that is not affected by previous runs anyhow.
Writing controller tests
Now let’s write our first test. Our first test makes sure that Index() method returns correct view. View is correct when its name is not given or if its name is Index.
[Fact(DisplayName = "Index should return default view")]
public void Index_should_return_default_view()
{
using (var context = GetContextWithData())
using (var controller = new HomeController(context))
{
var result = controller.Index() as ViewResult;
Assert.NotNull(result);
Assert.True(string.IsNullOrEmpty(result.ViewName) || result.ViewName == "Index");
}
}
Now let’s write test that checks model given to Index view. We don’t focus on actual in model attributes. We just want to make sure that there is data like expected.
[Fact(DisplayName = "Index should return valid model")]
public void Index_should_return_valid_model()
{
using (var context = GetContextWithData())
using (var controller = new HomeController(context))
{
var result = controller.Index() as ViewResult;
var model = result.Model as FrontPageModel;
Assert.NotNull(model);
Assert.NotNull(model.Top5);
Assert.NotNull(model.Items);
Assert.Equal(5, model.Top5.Count());
Assert.Equal(10, model.Items.Count());
}
}
This test makes sure that model is given to view and it is not null. It also checks that top five and items collections are not null and these collections contain data.
If category is not found then Category action returns status code 404. In our case it is represented as NotFoundResult that action returns. Here is the test.
[Fact(DisplayName = "Category should return 404 for missing category")]
public void Category_should_return_404_for_missing_category()
{
using (var context = GetContextWithData())
using (var controller = new HomeController(context))
{
var result = controller.Category(-1) as NotFoundResult;
Assert.NotNull(result);
}
}
Running tests
Visual Studio opens test runner and runs tests one by one. Image on right shows the Test Explorer with our tests. As we used DisplayName parameter with fact attributes our tests in Test Explorer have well readable names. Also we can see how much time it took for tests to run.
Normally unit tests should run fast. It shouldn’t take more than few milliseconds. It holds true for index tests but for some reason category test takes almost second. Things get mopre interesting when we look at summary: it took almost five seconds to run these tests. What’s the trick? Well, building and loading tests takes also time and these times are also included in summary.
Wrapping up
Writing unit tests for controllers is easy when EnitiyFramework Core is in use. We can avoid mocks and fakes of database context as database context doesn’t depend on database we are using. In-memory database is non-persistent implementation of data provider that we can use when testing Entity Framework related code. As it doesnät make any requests to network or other local processes it is fast as we saw from test running times.
There is a lot of problem with this tool, for instance your unit tests won’t check if your expressions can be translated to SQL by EF. For instance if you add a “Where(e => e.IsSomething())” it will work with in memory DB but won’t work once in production. IMHO it’s way better to use SQL lite or SQL express.
Unit tests test only the functionality of given code unit and in this case there is no matter what database you are actually using. If you start using SQL Express then you can be sure that SQL Server data provider translates expression tree to valid SQL. It tells nothing about what are doing all MySQL providers. Worse yet, suddenly you are testing not only functionality but also integration with database.
When real services like databases, web services etc come to play we write separate set of tests called integration tests.
Using SQLite in memory is the BEST option to go. Fast and you don’t even have to think about the annoying integration vs pure unit test dispute.
But still you are testing also how one database system works :)
The comments about using some sort of actual database for unit tests clearly shows the lack of understanding of what a unit test is, its scope, and what it should test. I have to struggle with this often among other co-workers.
Very good article! Thanks for explaining it.
I am getting an exception. “Message: System.InvalidOperationException : Unable to resolve service for type ‘Microsoft.EntityFrameworkCore.Storage.IRelationalConnection’. This is often because no database provider has been configured for this DbContext.
My ConfigureServices has the following entry:
services.AddDbContext(options => options.UseSqlServer(conn));
Do I need to add a new entry in ConfigureServices method like,
services.AddDbContext(opt => opt.UseInMemoryDatabase());
It works fine. Nothing wrong with the above implementation. I found out the problem in my program and resolved it.
The context’s constructor was invoking ‘Database.OpenConnection()’. And I realized that it was not needed. When I removed that line of code, the tests working fine now.
Pingback:UnitTesting In Memory EF Core – ejgeneral
Paramesh says here
Hi Guys
I am unable to call appsettings connection string from my .net core 2.0 webapi project to .net core unit testing project so here i am using dapper orm but so many examples are there in entityframe work but here i am getting db null (null refference exception) in the entity repository class library and also the break point not go to that entity repository class library project i am added refference also
can you tell me how it can fix
Thank you
Hi!
How do you set up database context for unit testing?
Pingback:How To: Use EntityFramework Core In-Memory Database For Unit Testing - Matheus Rodrigues
Hi, i saw an example with HomeController class, witch inject only database context, but i have an AccountController class, and its Ctor have a UserManager, RoleManager, database context, and other services.
How can i simulate UserManager, RoleManager and my own services. Thanks
You can initiate these same way as it is done in AccountController class, I think. Some services get automatically registered with framework level dependency injection while for others you should probably take care by yourself.
If you show ctor of your AccountController, I can give better advice maybe.
Ok, Thanks
public AccountController(
UserManager userManager,
SignInManager signInManager,
RoleService roleService,
IConfiguration configuration)
{
_userManager = userManager;
_signInManager = signInManager;
_roleService = roleService;
_configuration = configuration;
}
Here is AccountController ctor
UserManager and also SingInManager, at insertion lost “User”
Or also i need to test my RoleService class, here is its ctor:
#region Private Fields
private readonly UserManager _userManager;
private readonly RoleManager _roleManager;
#endregion
// ===== Methods =====
#region Init
public RoleService(
UserManager userManager,
RoleManager roleManager)
{
this._userManager = userManager;
this._roleManager = roleManager;
}
You need to register your service to framework-level dependency injection (DI) in application start-up class. There is ConfigureServices method for this. It’s important to select correct scope for your dependencies. More about the topic here: http://gunnarpeipman.com/aspnet/dependency-injection-in-asp-net-5/
If you service is registered with DI then you can inject it to classes like controllers and other services. If it uses only known dependencies in ctor then your class will get correct instances to it automatically.
I registered this services in startup Class, but i don’t understand how can i use it in Test classes. Should i create like this:
new AccountController(params), and if so, how do I get these parameters?
Here is all official information about testing controllers: https://docs.microsoft.com/en-us/aspnet/core/mvc/controllers/testing?view=aspnetcore-2.1
I am doing similar testing, but instead of hardcoding the test data (very tedious) I export data from our database using “FOR JSON AUTO” then load those files into my in-memory DbContext.
It is very nice article for Mock Testing in asp.net core.
This website makes available various medications for ordering online.
Anyone can quickly buy essential medicines without leaving home.
Our product list includes both common drugs and specialty items.
All products is supplied through trusted suppliers.
amoxil liquid suspension
We ensure customer safety, with secure payments and fast shipping.
Whether you’re looking for daily supplements, you’ll find affordable choices here.
Begin shopping today and get stress-free online pharmacy service.
Aviator blends adventure with high stakes.
Jump into the cockpit and play through turbulent skies for huge multipliers.
With its retro-inspired design, the game evokes the spirit of pioneering pilots.
https://www.linkedin.com/posts/robin-kh-150138202_aviator-game-download-activity-7295792143506321408-81HD/
Watch as the plane takes off – withdraw before it flies away to grab your rewards.
Featuring instant gameplay and dynamic sound effects, it’s a top choice for gambling fans.
Whether you’re testing luck, Aviator delivers uninterrupted action with every spin.
Thank you for the good writeup. It in fact was a amusement account it.
Look advanced to more added agreeable from you!
By the way, how could we communicate?
If some one desires expert view regarding blogging after that i suggest him/her to pay a quick visit this webpage, Keep up the fastidious job.
By understanding the culture and traditions of Ukraine, creating an appealing profile on Dream Singles, and following the do’s and don’ts of dating Ukrainian women, you can increase your chances of discovering love. The route to this lifelong dedication carries many features of Ukrainian tradition and traditions that create an intricate, yet heartwarming tapestry. There is no limit to the extent of culture that you accumulate while you date a Ukrainian woman. Ask questions, remember the answers, and all the time discover time for a date. So if you want somebody who loves being outdoors and loves socializing, then don’t hesitate to go on a date with an Irish man. Mr Li, who is from Shaanxi Province, China, said European girls are more out-going and innocent – compared to Chinese girls. Compared to Ukrainian males, Chinese males are higher husbands,’ said Mei Aisi, who referred to Ukraine as ‘a land of beauties’. Kharkiv-born Karalina, 21, who found a Chinese boyfriend through the club told MailOnline that in comparison with Ukrainian men, Chinese males are more caring.
These features make them very fashionable among the many overseas males, who’re looking for conventional lifelong partners. AmourFactory provides plenty of free features that will help you message potential matches! GoldenBride also provides many special features like telephone calls, presents supply, and personal pictures and videos to spice up long-distance romance. Along with private info and social background details, you possibly can entry public and personal media content material. 2. Within the occasion a feminine Member had requested her profile to be deleted from the Websites for unspecified private causes, the Correspondent shall not have the appropriate to obtain any info with respect to the deletion of such feminine Member’s profile, as same could be in violation charmdate review of AnastasiaDate’s Privacy Policy. While registering a profile, try to create an applicable user identify. So what should a guy keep in thoughts, whereas dating Ukrainian women on-line? This site gives you an unforgettable courting experience, whereas useful features and a handy interface will make the method easy and fulfilling. With its user-friendly interface and advanced search instruments, UkraineDate makes it simple to search out your good match.
UAbrides options an advanced search algorithm for you to search out your stunning bride as soon as potential on this one of the coolest Ukrainian women dating sites. Ukrainian dating sites are a great way for Canadian individuals to meet these exceptional women. Some of the perfect issues to search for in these sites are a report abuse feature and strong vetting policies for the guys and the women using the positioning. Everyone sees the pure charm of Ukraine girls. We examined each Ukrainian dating site on the listing for greater than 1 week, and JollyRomance isn’t an exception: we spent more than 10 days on it and chatted with more than 20 women on this Ukrainian dating webpage. The primary objective behind our courting platform is to provide a reliable courting experience in a safe setting. In case you are severe about dating them, be sure you know easy methods to deal with their dad and mom and household through the meetup. How do you make a relationship with one in every of them? Considered one of the biggest issues guys have is brides utilizing Google Translate to communicate and never really speaking any English at all. Ukrainian men usually have male chauvinism. Mr Mei said Ulove Club presently has round seventy five male members, who’re mainly middle-class businessmen from major Chinese cities, and more than 300 female members, who are all Ukrainian.
Mr Mei, the father of a 3-year-outdated lady, confessed that a couple of male members, though rich, haven’t got a lot taste and don’t know the right way to woo ladies. They’re way more vital within the life of Ukrainian individuals than in other international locations. First of all, attempt not to write down too much about yourself. Deep-pocketed bachelors first pay a 96,000 yuan (£11,000) annual membership charge – or 300,000 yuan (£34,600) as VIPs – earlier than spending an extra 69,000 yuan (£8,000) on each relationship occasion held at a luxurious golf membership in Kharkiv. She said: ‘In their culture, family comes first. Make an engaging profile: Create a profile that displays your interest in Ukrainian culture, showcasing your compatibility and intentions. Ukraine has at all times been a country rich in tradition, tradition, and, most significantly, stunning souls looking for significant connections. He mentioned he had labored in Europe for a very long time before and was in Ukraine studying when he obtained to know Karalina. Maybe it’s time to stage up. As long as you’re leading the interplay with a fun and flirty vibe, it’s easier for you to establish a man-to-girl premise which is very important in attraction.
Today, I went to the beach with my kids. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!