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.

Attaching Selenium screen shots to MSTest results

This is more for my own reference but has proved rather useful. The problem it solves for me is that when automated Selenium tests run and fail its usually quite a task to figure out what went wrong. The best way around this is to take a screen shot of the issue. However taking a screen shot can end up with a folder on a tester server full of images you have to look through to find your test result image. The best option is to attach a screen shot your test takes when it fails to the results of the currently running test.

Below is how to do this with MSTest running in VSTS. All of our Selenium tests run as part of a timed VSTS Release Hub release twice a day.

1 [TestMethod] 2 public void BurnUp_86_CheckIterationPathsLoad() 3 { 4 bool isLoaded = false; 5 6 try 7 { 8 _selenium.ShowSelectedData(); 9 10 _selenium.ClickIterationPath(); 11 isLoaded = _selenium.CheckIterationPathsLoad(); 12 } 13 catch (Exception) 14 { 15 16 var testFilePath = _selenium.ScreenGrab("BurnUp_86_CheckIterationPathsLoadERROR"); 17 18 AttachScreenShotFileToTestResult(testFilePath); 19 20 throw; 21 } 22 23 Assert.IsTrue(isLoaded); 24 } 25 26 public TestContext TestContext { get; set; } 27 28 public void AttachScreenShotFileToTestResult(string screenShotPath) 29 { 30 try 31 { 32 if (!string.IsNullOrEmpty(screenShotPath)) 33 { 34 TestContext.AddResultFile(screenShotPath); 35 } 36 } 37 catch (Exception) 38 { 39 40 //We don't want to stop the tests because we can't attach a file so we let it go....let it go.. let it go... 41 } 42 43 }

Lets take a moment to step through the test method above called BurnUp_86_CheckIterationPathsLoad(). The test is contained in a try catch. All of my selenium functionality I keep inside a separate class so it abstracted and encapsulated from the actual unit tests, this helps greatly with maintaining my test as I only need to focus on the Selenium classes if the page layout for example changes. As part of this class it has a base class where I keep functionality common to all tests such as the ScreenGrab function found inside _selenium (more on this later).

If my test fails my try catch will catch the exception this is where I will take a screen grab of the issue and then allow the original exception to bubble up. But after I have taken a screen grab I attach this to the the current running tests results using the AttachScreenShotFileToTestResult function. You can see inside this function, I don’t care if it fails to attach the screen shot to the test results, I’d rather the rests of the tests continue to run. (I can almost sense the shock from my fellow developers Smile). The key piece of functionality to take away here is TestContext.AddResultFile. This is given the path to where we saved our screen grab in the previous step.

So what about that screen grab functionality?

Selenium has had the ability to take screen shots for a while. Below is the function in my _selenium class that takes the screen shot using the current version of the IWebDriver.

1 public class SeleniumBase 2 { 3 protected IWebDriver driver; 4 5 public string ScreenGrab(string test) 6 { 7 string baseDirectory = "C:\\UITests"; 8 string screenGrabs = Path.Combine(baseDirectory, $"{DateTime.Now:yyyy-MM-dd}"); 9 10 if (!Directory.Exists(baseDirectory)) 11 { 12 Directory.CreateDirectory(baseDirectory); 13 } 14 15 if (!Directory.Exists(screenGrabs)) 16 { 17 Directory.CreateDirectory(screenGrabs); 18 } 19 20 21 //Create these folders if not present 22 string filename = Path.Combine(screenGrabs, $"{test}-{DateTime.Now:yyyy-MM-dd_hh-mm-ss-tt}.png"); 23 24 try 25 { 26 Screenshot ss = ((ITakesScreenshot)driver).GetScreenshot(); 27 ss.SaveAsFile(filename, System.Drawing.Imaging.ImageFormat.Png); 28 29 30 } 31 catch (Exception) 32 { 33 34 //We swallow the exception because we want the tests to coninue anyway. Taking a screen shot was just a nice to have. 35 return string.Empty; 36 } 37 38 39 return filename; 40 } 41 }

So what does the result look like?

Below are the results from one of our automatic test runs that are run for us by Visual Studio Team Services Release Hub. If I click on the test that has failed you can see in the results section an attachment has been added which is the screen grab we took when the test failed.


Got a better way of doing the above? Or would like to recommend some changes? Don’t be shy leave a comment, I’d love to hear from you Smile

Migrating from TFS to Visual Studio Team Services notes

Yesterday I migrated one of our TFS collections to VSTS using  Microsoft’s migration guide for moving from TFS to VSTS . I won’t lie, it was a pretty long process and it took a lot of going back and fourth to make sure I fully understood the guide which is a PDF 58 pages long. The guide comes with several checklists and things you need to check and prep before your migrations.

A very rough outline of what happens is that you run a check against your TFS using the tool provided to ensure everything is exportable, if there are problems you go about fixing them following suggestions from the tool and then running the check again until you are ready to go. Next you you will run a prep that will generate some files you will need to map your users across followed by making a database backup as a DACPAC package and entering your import invite codes (provided by Microsoft). These are then uploaded to an Azure storage account and you kick off the migration process which uses these assets to import your data into a brand new VSTS instance.

I won’t go into details about how to do the migration as this is covered in the guide, however I will highlight some things you should take into account before you migrate from TFS to VSTS which is done using a tool provided in the guide called the TFSMigrator.

Azure Active Directory

You are going to have to make sure you have this in place or have at least thought about it. If you use Active Directory in your organisation a good thing to look at is replicating this to Azure, your migration is going to need this. If you are not using Active Directory but just accounts on the box as I did for this migration, you can easily map these across to Azure Active Directory accounts. If you have Office 365, then you already have access to an Azure Active Directory setup (depending on your subscription) and you can make use of this. The reason Azure directory is important, is that this is how VSTS will authenticate your users once you have migrated across to VSTS.

Plan for some downtime to make backups

Even when doing a test migration as I did, you need to plan for some downtime. One of the reasons for this is that you will need to generate a DACPAC project of your TFS Collection. In order to do this you have to take the TFS Collection Offline and then detach it from TFS. If you have not done this before you may be put off by the ominous warnings from the TFS Admin Console asking you to tick a box stating you have made a backup of your TFS databases.

After you have detached your TFS Collection and made a DACPAC of it, you can then reattach your collection so your team can continue working as usual.

Learn what a DACPAC is

Yes I had never used one before. The guide will give you some details with a sample command line to use to create one. Effectively DACPACs are short for Data-tier Application Package. These are generated from SQL Server itself. It is basically a way of exporting your whole TFS Collection database with everything that it needs to be re-created. “tables, views, and instance objects, including logins – associated with a user’s database”. The DACPAC package will be uploaded to an Azure storage blob that the migration tool uses.

Learn about Azure Storage Accounts and SAS

While I have used Azure Storage Accounts before , I found this part quite complicated and it took me a while to get it right. Basically the DACPAC package your create from your TFS Collection database gets uploaded to an Azure Storage account along with a mapping file for user accounts. The hardest part I found was having to workout how to create an SAS token URL to the where I had stored these in an Azure storage account. The guide will provide you with a link to some PowerShell you can sue that will generate this URL for you. I am not sure why Azure couldn’t create this link for you (I did try) but eventually used the PowerShell provided that worked first time.

Azure PowerShell tools

Make sure you have the Azure PowerShell tools installed, you will need these for running some PowerShell to generate an SAS token url to your Azure Storage account (see above).

Final Notes

I would recommend reading the guide fully before getting started. Also note that currently you have to request an import code in order to use the service. You will get two of these, one is for a dry run to ensure it works and the next one is for your production import. This is when you are fully committed and feel confident it all went to plan in the dry run.

Deploying to on Premise IIS using VSTS Release Hub

It has been a while since I last blogged about MS Visual Studio Team Services Release Hub. The last time I blogged about Release Hub the product was very much rough around the edges and quite a few of its parts were in early preview.

Release Hub has matured quite a bit since then, however deploying to production or test environments in the real world can be considerably different compared to the examples of using Release Hub available online. The items I find many customers I go and see have difficulty with is where the documentation is very much sparse or spread around many older versions of the product is:

  • Tokenisation – its surprising how difficult this can sometimes be and documentation following the whole process from start to finish isn’t readily available. (I will cover some of this in this article)
  • WINRM – Setting up Windows Remoting  which is used for many of the deployment tasks on a test environment is easy. On a production environment where everything has to be very carefully managed through change control processes this can be more challenging.  (I hope to cover some of this in a future article)

The sample I have put together here is more for my own reference. But if you have any suggestions or improvements I would love to hear from you. I will try to expand on this article a bit more with more examples that don’t fit the norm in future blog articles.

The scenario I am going through here is an ASP.NET website that is created from a build and that same build needs to be deployed to more than one environment with its configuration changed for each environment.

The steps involved will be to

  1. Prepare an ASP.NET project for web deployment and tokenisation
  2. Prepare a build to produce the assets needed for deployment
  3. Create a Release that consumes the build mentioned above and replaces configuration variables based on the environment being deployed to

Preparing an existing ASP.NET web project for Release Hub

Before you can deploy a web project you need to prepare your project for deployment.  You may have already used this functionality to deploy directly to an Azure website from Visual Studio or to an on premise server. This functionality can also be used to help create deployment packages that we will use later with Release Hub.

Step 1
Right click on your web project and select Publish. Don’t worry this wont publish your site but it will enable us to setup a deployment profile for it that we will use later.


Step 2
From the dropdown that appears select “New Custom Profile” and type in a name for your new profile and select Ok. In this case our profile is called “Website1WebPackage”


Step 3
In the dropdown box that appears next select “Web Deploy Package”, here type in a name for your deployment package. We will be using MS Web Deploy to deploy our site later but in order to do this we need to set our site up to create a deployment package. In addition to this we are placing in a token called __SITENAME__ this will be replaced later at deployment time when we actually deploy our application. I will talk more about this later.


Step 4
Here the publish wizard will display any database connections string which you can also replaces with tokens of your own. Tokens start with “__” and end with “__” and are in capitals.


Step 5
You can now hit the publish button. All this will do is create a webdeploy zip package in the root of your project . You should now see the following files


We are only really interested in the website1.webpackage.SetParameters.xml and in These files when using the correct switches on your build, will be generated each time. if you open up the parameters file you will notice it contains the tokens we created earlier.


Step 6
In the root of your web project create a parameters.xml file . You will see in our parameters file we are using an xpath match to replace settings in our web.config file. The scope is basically looking for a database connection string called DefaultConnecction and we are saying that when you find that value replace it with __DBCONNECTION__  we are doing the same with another key in our web.config called MailAddress.

<?xml version="1.0" encoding="utf-8" ?>
<parameter name="DefaultConnection" description="DB Connection" defaultValue="__DBCONNECTION__" tags="">
  <parameterEntry kind="XmlFile" scope="\\web.config$" match="/configuration/connectionStrings/add[@name='DefaultConnection']/@connectionString"  />

<parameter name="EmailAddress" description="MailAddress" defaultValue="__EMAILADDRESS__" tags="">
  <parameterEntry kind="XmlFile" scope="\\web.config$" match="/configuration/appSettings/add[@key='MailAddress']/@value"  />

You can see how the parameters above relates to the web.config below



Step  7
Publish your project again by right clicking on the project and selecting publish and then the profile you created earlier. If you know check the setparamters file. You will notice the new tokens we added in the parameters.xml file are also in here. This file automatically updates with these tokens when you run the publish profile and is key to how we can replace variables in our configuration files.


We are now ready check in our code and to create a build. Ensure you check in the parameters.xml file and your new publish profile (highlighted below)



Create a build

Step 1
You may already have a build for your solution, if so you can alter this build to produce the assets you need for deploying your solution.  Below I have setup an out of the box Visual Studio build pointing to my solution however I have added some arguments to my build.


Those arguments are:

/p:DeployOnBuild=true;PublishProfile=Website1WebPackage /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true

Note above that our publish profile is set to the one we created earlier in the tutorial when we prepared our ASP.NET project with a publish profile we called it “Website1WebPackage”. We are also telling MSBuild that we want it to create a package for us and that we want everything to be in a single file.

Step 2
Click on the Copy Files to task and in the contents textbox you will see we have two entries. We are telling this task that all we want from the finished build is and the website1webpackage.SetParameters.xml file we covered in the earlier steps. These files are automatically generated by our build after we had setup a publish profile on our build to create them in the earlier steps.


Step 3
Run your build and at the end of the build if you look at its artefacts you should have the following files. We will use these in our release to help with tokenisation.


Create a Release

Step 1
Go into Release Hub and create an empty release.


In this example I am using the build I created in the previous steps.


Step 2
If you don’t already have it, you will need to go to the VSTS market place and select a Tokenisation task. I like to use the following but there are several more you can choose from.

Step 3
Add your tokenisation task. In mine I have set the working directory of my solution as the target path using the following VSTS token $(System.DefaultWorkingDirectory). I have set the Target Filenames to the SetParameters file that our build we created in the previous steps is generating.


Step 4
In the environment I was working we weren’t allowed to use Windows File Copy as it was considered insecure. However we did have WINRM available to us. Provided you have PowerShell 5 installed it is possible to copy files from a PowerShell command line to your destination server. You can skip this task and use the Windows FileCopy task if this is open on your network.


In my example I have done just that using the PowerShell task. The PowerShell I use is below and tokenised by variables stored in VSTS in the variables tab.

$password = ConvertTo-SecureString "$(password)" -AsPlainText -Force
$cred= New-Object System.Management.Automation.PSCredential ("$(username)", "$password")
$session = New-PSSession -ComputerName myserver01
Copy-Item '$(System.DefaultWorkingDirectory)\WebApp1 Build\drop\WebApplication1\' -Destination 'c:\drops' -ToSession $session
Copy-Item '$(System.DefaultWorkingDirectory)\WebApp1 Build\drop\WebApplication1\website1webpackage.SetParameters.xml' -Destination 'c:\drops' -ToSession $session

I am basically using PowerShells Copy-Item command here to get the files to the server for me to a folder on the c drive of the server called “drops”. I get the path to the files by temporarily making use of a Windows File Copy task to show me the path variables and then delete it after


Step 5
Now that I have setup my WinRM file copy I can then use the IIS WInRM Task to deploy to my web server


In the example I am using the package files that were copied to the web server in the previous step.

Step 6
Remember those tokens you setup in the previous steps? Now is the time to start giving them values. Click on the Variables tab and start putting some entries in for those tokens. You will also notice that the username and password we use in our release tasks are also stored here and we can refer to them as $(username) or $(password).


Step 6
You should now be able to run your Release and deploy.

Step 7 (Optional)
If you have more than one environment, you can clone the existing environment and then replace the server names with the next environments server names.


The remote server returned an error: (403) Forbidden. (in function: Get-AzStorageKeys)

I ran across this error when installing a new Release Agent and it got to the Azure File Copy stage. Many of the solutions on the Internet to this problem point to it being caused by incorrect times on the Agent machine or the target server. However all servers had the correct time and were in the same time zone.

My problem appeared to be caused by my token Endpoint from VSTS connecting me to Azure. When I renewed this endpoint certificate the Azure File Copy task magically worked. The only difference I could see from the previous agent I had, was that my new Agent was a new Virtual Machine compared to the older one which was a Classic Azure Virtual Machine.

I hope this helps someone.