# Wednesday, 18 April 2018

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.



.
Tags: TDD | Visual Studio | VSTS

Wednesday, 18 April 2018 11:58:18 (GMT Daylight Time, UTC+01:00)  #    Comments [0]