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.

Quick look at Visual Studio Code

One of the most exciting things I took away from Microsoft’s Build 2015 event on day 1 apart from the fact they were embracing all platforms this time was how they proved that by releasing their first code editor that works for all environments Mac OS X, Linux and Windows. Visual Studio Code was a bit of wow moment for me it kind of said “They really mean it”


Visual Studio Code seems to have quite a light footprint. The install was pretty quick and as soon as the app launched I realised it looked strikingly familiar to Microsoft’s online code editor I have been using for an Azure website called Monaco (below)


Visual Studio Code (below)


I would imagine that this is intentional so users have an online and offline version of the editor. To get started with development Microsoft have some tutorials setup specifically for Visual Studio Code. You can get started with a ASP.NET 5 app in Visual Studio Code using the tutorial found here. As Visual Studio Code is so light weight all scaffolding for your projects is done from the command line.

To get started with the tutorial you will need to install:

If you haven’t installed VS 2015 before you will need to install DNVM (.NET Version Manager) which you will later use to install dnx (this is covered in the tutorial)

NodeJS will also need to be installed which will install npm (the package manager for Node.JS) if you haven’t used npm before this, its not that clear from the tutorial.

After you have followed the tutorial you should have a scaffolded project you can view in Visual Studio Code. If you don’t want to go through the above tutorial you can always create the project in VS 2015 and then view it in Visual Studio Code.

What I like about this and saw quite a lot of this mentioned is how Microsoft is embracing other initiatives instead of reinventing the wheel and making everything the “Microsoft way” as they have previously done in the past.




Editing code in Visual Studio Code is a breeze and I quite like how light weight it is and if you have been using Monaco it shouldn’t seem too different to you especially the integrated Git access. You can also launch kestrel (the web server) from the command line. However while I was able to get most things working with Visual Studio Code I was unable to get Debugging working for my MVC application. The documentation does state that debugging is not yet supported in Mac OS X and Linux although I was using a Windows machine at the time.