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.
.