Creating integration tests with Entity Framework and MSTest

I have used in memory EF tests, however they do have some limitations. In the past I have also mocked out the Entity Framework part of my tests but this can only take you so far especially if you want some comfort in knowing that your EF statements you put together are working correctly with your code.

So here’s the problem I am solving

  • I want a full integration test of code down to the database
  • I need the ability to reset the database being used each time so my tests are built up from scratch.

How I solved it.

  • I made use of MS SQL Express localdb this is a light weight version of SQL Server targeted towards developers, it only contains the minimal amount of files to start a SQL Server Database instead of needing a full SQL Server instance.
  • A base class used by my Integration test MSTest class.

Test Base Class

The class below is used by my MSTest class. It takes care of disposing of the database if it exists and creating it. It is by no means perfect, if there are better ways, I am open to recommendations. The class is also hardcoded to my db context in this case MyDBContext.

1 using System; 2 using System.Collections.Generic; 3 using System.Data.Entity.Infrastructure; 4 using System.Data.Entity.Migrations; 5 using System.Data.SqlClient; 6 using System.IO; 7 using System.Linq; 8 using System.Text; 9 using System.Threading.Tasks; 10 using Microsoft.VisualStudio.TestTools.UnitTesting; 11 12 namespace My.IntegrationTests 13 { 14 public class TestDatabase 15 { 16 protected static MyDBContext _dbContext; 17 protected static string databaseName = "MyTestDB"; 18 19 20 protected static string databasePath; 21 22 protected static string databaseLogFilePath; 23 24 protected static string dbConnectionString; 25 26 27 public TestDatabase(string databaseNameSet) 28 { 29 databaseName = databaseNameSet; 30 } 31 32 33 public static void SetUpTest(TestContext context) 34 { 35 databasePath = Path.Combine(context.DeploymentDirectory, databaseName + ".mdf"); 36 37 databaseLogFilePath = Path.Combine(context.DeploymentDirectory, databaseName + ".ldf"); 38 39 dbConnectionString = @"server=(localdb)\v11.0;Database=" + databaseName; 40 41 DropSqlDatabase(); 42 CreateSqlDatabase(); 43 44 _dbContext = new MyDBContext(); 45 46 // Basically we are creating a datbase on the fly and we want EF to init the database for us and to update it 47 // with the latest migrations. We do this by enabling automatic migrations first 48 // Then we give it our connection string to our new database we have created for the purpose. 49 DbMigrationsConfiguration configuration = new DbMigrationsConfiguration<MyDBContext>(); 50 configuration.AutomaticMigrationsEnabled = true; 51 configuration.TargetDatabase = new DbConnectionInfo(dbConnectionString,"System.Data.SqlClient"); 52 var migrator = new DbMigrator(configuration); 53 migrator.Update(); 54 55 } 56 57 private static void DropSqlDatabase() 58 { 59 //Note: We do not care if we get a SQL Server exception here as the DB file it is looking for is probably long gone. 60 try 61 { 62 SqlConnection connection = new SqlConnection(@"server=(localdb)\v11.0"); 63 using (connection) 64 { 65 connection.Open(); 66 67 string sql = 68 string.Format( 69 @"alter database [{0}] set single_user with rollback immediate; IF EXISTS(select * from sys.databases where name='{0}') DROP DATABASE {0}", 70 databaseName); 71 72 SqlCommand command = new SqlCommand(sql, connection); 73 command.ExecuteNonQuery(); 74 connection.Close(); 75 } 76 77 } 78 catch (System.Data.SqlClient.SqlException) 79 { 80 // Yeah yeah I know! 81 //throw; 82 } 83 } 84 85 private static void CreateSqlDatabase() 86 { 87 SqlConnection connection = new SqlConnection(@"server =(localdb)\v11.0"); 88 using (connection) 89 { 90 connection.Open(); 91 92 string sql = string.Format(@" 93 CREATE DATABASE 94 [{2}] 95 ON PRIMARY ( 96 NAME=Test_data, 97 FILENAME = '{0}' 98 ) 99 LOG ON ( 100 NAME=Test_log, 101 FILENAME = '{1}' 102 )", 103 databasePath, databaseLogFilePath, databaseName 104 ); 105 106 SqlCommand command = new SqlCommand(sql, connection); 107 command.ExecuteNonQuery(); 108 connection.Close(); 109 } 110 } 111 112 } 113 } 114

The MSTest Class

This is where we do the actual testing. Below I have created a hypothetical test checking to see if userA can get access to UserB’s organisation. In order for the test to work we need to create these organisations in our database first with their various users. When we do this we also make sure that the organisations don’t exist in the database first and if they do we delete them so we can start our test from scratch.

1 using System; 2 using System.Linq; 3 using System.Text; 4 using Microsoft.VisualStudio.TestTools.UnitTesting; 5 using Newtonsoft.Json.Converters; 6 7 namespace My.IntegrationTests 8 { 9 [TestClass] 10 public class SessionSummaryTests : TestDatabase 11 { 12 // We name our test database 13 public SessionSummaryTests() : base("SessionUnitTestDB3") 14 { 15 16 } 17 18 public SessionSummaryTests(string databaseNameSet) : base(databaseNameSet) 19 { 20 } 21 22 [ClassInitialize] 23 public static void SetUp(TestContext context) 24 { 25 SetUpTest(context); 26 } 27 28 /// <summary> 29 /// Hypothetical test case. We test making sure user A cannot get access to User B's organisation 30 /// </summary> 31 [TestMethod] 32 public void CheckIfUserACanAccessUserBsOrganisation() 33 { 34 // **** Start Test Scaffold 35 string userAccountA = "userA"; 36 string userAccountB = "userB"; 37 38 var orgs = _dbContext.Organisation.Where(x => x.OwnerEmail.Equals(userA) || x.OwnerEmail.Equals(userAccountB)); 39 40 41 if (orgs.Any()) 42 { 43 _dbContext.Organisation.RemoveRange(orgs); 44 } 45 46 47 _dbContext.SaveChanges(); 48 49 _dbContext.Organisation.Add(new OrganisationModel() 50 { 51 Name = "The Organisation A", 52 OwnerEmail = userAccount, 53 }); 54 55 56 _dbContext.Organisation.Add(new OrganisationModel() 57 { 58 Name = "The Organisation B", 59 OwnerEmail = outsideUser, 60 61 }); 62 63 64 _dbContext.SaveChanges(); 65 66 var orgA = _dbContext.Organisation.FirstOrDefault(x => x.OwnerEmail.Equals(userAccountA)); 67 68 var orgIdA = org.Id; 69 70 var orgB = _dbContext.Organisation.FirstOrDefault(x => x.OwnerEmail.Equals(userAccountB)); 71 72 var orgIdB = orgB.Id; 73 74 _dbContext.UserDetails.Add(new UserDetailsModel() 75 { 76 Email = userAccountA, 77 FirstName = "User1FirstName", 78 LastName = "User1LastName", 79 OrganisationId = orgIdA 80 } 81 ); 82 83 _dbContext.UserDetails.Add(new UserDetailsModel() 84 { 85 Email = userAccountB, 86 FirstName = "User2 FirstName", 87 LastName = "User2 LastName", 88 OrganisationId = orgIdB 89 } 90 ); 91 92 93 _dbContext.SaveChanges(); 94 95 // *** End of test scafold 96 97 // Our actual Test 98 var result = OrganisationMethods.GrantAccess(orgIdB, userAccountA,_dbContext); 99 100 Assert.AreEqual(false,result); 101 102 103 } 104 105 } 106 } 107

A Few Notes

You may have noticed that if I had used dependency injection I could have mocked the DB implementation if I had encapsulated it. But the purpose of the test was to ensure everything worked correctly right down to the database.

My TestDatabase class ignores an exception (sinful) I have had various issues here especially if the DB does not exist. Which is fine as we don’t want it to exist. But once again I am open to recommendations.

Using Microsoft Shims with Instance Methods and Async calls

I started using Microsoft Fakes for some code I was not able to encapsulate and use Interfaces for (my preferred approach) . One of the issues I had was the documentation didn’t appear to be all that good compared to the wealth of information available for RhinoMocks and MOQ especially around Instance methods.

Here is my scenario. I was calling an external class from my code and wanted to test some code that handled an error if the code was called on the second attempt. In Rhino Mocks or MOQ this type of expectation was very easy to code but with Microsoft Fakes the majority of examples appeared to be around static methods.

I knew you could use the AllInstances method for all Instances however I was not clear how I could have an instance do something different when called the next time. My approach was to store the amount of times it was called in a variable and then do something based on that variable count.

Anyway to cut a long story short here is my approach.

    using (ShimsContext.Create())
		int shimCalled =0;
		ShimExternalServiceHttpClientBase.AllInstances.GetTransactions =(x,y,z)
				 return Task.FromResult(new List<TransItem>() {new TransItem() {Id = 99}, new TransItem() {Id = 33}});
			return Task.FromException<List<TransItem>>(new TransException());
		var transcalculator = new TransCalculator();
		var results = transcalculator.CalculateResultsForBatch(1);

This approach works well for me. Basically on the first call I want data to be returned and on the second call I want to raise an error to check that my code can handle this type of error correctly.  I must also point out that this method I am shimming uses async calls hence the Task.FromResult and Task.FromException being used.

I am not entirely sure if the above is the best approach to use however I was unable to find another way I could use an instance method in this way.

Encouraging a test driven mind-set

I’ve recently completed a project working with a great development team. Every time you work on a new project even in a software coaching role you hope people learn new things from you and that you also importantly learn new things from them. 

One of the things I found interesting was getting the team to follow Test Driven Development. The reason I was so adamant about it even down to being called “Mr Test” by the team, is that I like many other developers have been on projects where any kind of automated testing had been thrown out of the window or not been given the attention it deserves. All too many times teams have said they are following test driven development and not really know what it actually means. You may have heard the following.

  • “Its only a small change there’s no need to test it”
  • “I’ll write the tests later I just want to get it done.”
  • “I’ll put a task in the backlog to write the tests”
  • “I’ll write the code and you write the tests”

TDD aside, I think the first thing that has to be understood, is why unit testing is actually important. This is the example I like to use. When we build our application, its a bit like building a house, you need to ensure you have good strong foundations before you start building those brick walls. Software development is very similar in the example below our foundation is the code we have previously written and to ensure this code does what it is supposed to we protect what we have previously done with unit tests.

Our tests are our insurance policy. They ensure that our code behaves the way it was intended long after we have left it and moved along to the next piece of development.


Just like building the foundations of a house you don’t necessarily realise the consequences of doing it incorrectly until later. Unit testing is very much the same, its not until later that you realise your application is falling apart and you have absolutely no idea why. Every change you make to the application fills you with dread because you have no idea what you will end up breaking next. It can also be one of the reasons a team starts off with a high velocity and then suddenly slows down and stops delivering each sprint. 

When teams start to understand the picture above, the rest usually comes together quite easily. Developers start to realise that even if they are working alone in a team of one on some internal code the value of unit testing still applies to them.

TDD should then be a natural extension after the value of unit testing is fully grasped. Test Driven Development helps us think about the intent of our code, how we are going to test our application upfront, it makes us think about the design and can encourage good design at the same time.