# Monday, 12 December 2011

Integration Testing is a necessary evil that often gets overlooked or is implemented, ran once, and then not maintained.  The reasoning behind this is that by definition Integration Testing relies on external resources that are often outside of our control and some times completely hands off.  Also these external resources are in constant use by a large number of people making whatever resource the external systems are serving very volatile.  In this post I am going to focus on the most common external resource; the Database.

Integration Testing when you have a Database as an external resource can be a nightmare.  First off you need to have access to the database.  Once that is accomplished you need to have elevated access to the database in order to run scripts.  Scripts you say?  Yes scripts!  Scripts that can put the database in an expected state every time you run the Integration Tests.  Oh, yeah then you have to make sure that the script is run.

Luckily if you are using Entity Framework Code First you more than likely already have elevated access to the Database and are probably running a local instance of SQL Server on your dev box that you are in control of.  But in the typical scenario you would still have to write the SQL Scripts, which if you are like me you don’t enjoy, and then you have to make sure they are run and that they don’t impact the data that you use to manually UI test your application…we all have that one piece of data that rely on…don’t deny it!

So how does EF Code First help me with this overwhelming problem?

All example code is from my EFCodeFirstDemo.

Seeded Data:

In Entity Framework Code First you are given the ability to hook into the database generation process and seed the database with relevant data for testing.  The best part about this is that you do it all using your POCO domain entities and LINQ to Entities…NO SQL SCRIPTS!  WOOT!

The first step in the process is to create a custom data context initializer that implements an implementation IDatabaseInitializer.

  1. public class TestDataContextInitializer : DropCreateDatabaseIfModelChanges<DataContext> {}

The next step is to override the Seed method that hooks into the generation of the database.

  1. protected override void Seed(DataContext context) {
  2.     base.Seed(context);
  3. }

Finally you can use this Seed method to build up your test data and populate the database either every time your model changes (DropCreateDatabaseIfModelChanges) or if you choose every time you spin up a new session that accesses the database (DropCreateDatabaseAlways), I don’t recommend the latter.

In the code below I am building up object graphs using my standard POCO entities and LINQ to Entities.

  1. public class TestDataContextInitializer : DropCreateDatabaseIfModelChanges<DataContext> {
  2.  
  3.     private Manufacturer _astonMartin;
  4.     private Model _astonMartinDB9;
  5.     private Engine _astonMartinV12;
  6.     private Model _astonMartinV12Vantage;
  7.     private Manufacturer _ferrari;
  8.     private Model _ferrari458Italia;
  9.     private Model _ferrariScaglietti;
  10.     private Engine _ferrariV12;
  11.     private Engine _ferrariV8;
  12.     private Manufacturer _lamborghini;
  13.     private Model _lamborghiniAventador;
  14.     private Model _lamborghiniGallardo;
  15.     private Engine _lamborghiniV10;
  16.     private Engine _lamborghiniV12;
  17.     private Manufacturer _porsche;
  18.     private Model _porsche911GT2R2;
  19.     private Engine _porscheStraight6;
  20.  
  21.     protected override void Seed(DataContext context) {
  22.         SeedManufacturer(context);
  23.         SeedEngine(context);
  24.         SeedModel(context);
  25.         base.Seed(context);
  26.     }
  27.  
  28.     private void SeedManufacturer(DataContext context) {
  29.         _ferrari = new Manufacturer {Name = "Ferrari", Country = "Italy"};
  30.         _lamborghini = new Manufacturer {Name = "Lamborghini", Country = "Italy"};
  31.         _astonMartin = new Manufacturer {Name = "Aston Martin", Country = "United Kingdom"};
  32.         _porsche = new Manufacturer {Name = "Porsche", Country = "Germany"};
  33.         _ferrari.Location = DbGeography.Parse("POINT(44.53244    10.86412)");
  34.         _lamborghini.Location = DbGeography.Parse("POINT (44.65934    11.12693)");
  35.         _astonMartin.Location = DbGeography.Parse("POINT (52.18708    -1.48351)");
  36.         _porsche.Location = DbGeography.Parse("POINT (48.83498    9.15231)");
  37.         new List<Manufacturer> {_ferrari, _lamborghini, _astonMartin, _porsche}.ForEach(x => context.Manufacturers.Add(x));
  38.     }
  39.  
  40.     private void SeedEngine(DataContext context) {
  41.         _astonMartinV12 = new Engine {Name = "6.0L V12", BreakHorsepower = 510, NumberOfCylinders = 12, Liters = 6.0m};
  42.         _ferrariV8 = new Engine {Name = "5.4L V8", BreakHorsepower = 570, NumberOfCylinders = 8, Liters = 5.4m};
  43.         _ferrariV12 = new Engine {Name = "5.7L V12", BreakHorsepower = 532, NumberOfCylinders = 12, Liters = 5.7m};
  44.         _lamborghiniV10 = new Engine {Name = "5.2L V10", BreakHorsepower = 562, NumberOfCylinders = 10, Liters = 5.2m};
  45.         _lamborghiniV12 = new Engine {Name = "6.5L V12", BreakHorsepower = 700, NumberOfCylinders = 12, Liters = 6.5m};
  46.         _lamborghiniV12 = new Engine {Name = "6.5L V12", BreakHorsepower = 700, NumberOfCylinders = 12, Liters = 6.5m};
  47.         _porscheStraight6 = new Engine {Name = "3.6L Straight 6", BreakHorsepower = 620, NumberOfCylinders = 6, Liters = 3.6m};
  48.         new List<Engine> {_astonMartinV12, _ferrariV8, _ferrariV12, _lamborghiniV10, _lamborghiniV12, _porscheStraight6}.ForEach(x => context.Engines.Add(x));
  49.     }
  50.  
  51.     private void SeedModel(DataContext context) {
  52.         _ferrari458Italia = new Model {Name = "458 Italia", BasePrice = 220000, Year = 2012, Manufacturer = _ferrari, AvailableEngines = new List<Engine> {_ferrariV8, _ferrariV12}, EngineLocation = EngineLocationType.Mid};
  53.         _ferrariScaglietti = new Model {Name = "Scaglietti", BasePrice = 313000, Year = 2012, Manufacturer = _ferrari, AvailableEngines = new List<Engine> {_ferrariV12}, EngineLocation = EngineLocationType.Front};
  54.         _lamborghiniGallardo = new Model {Name = "Gallardo LP 570-4 Superleggera", BasePrice = 237600, Year = 2012, Manufacturer = _lamborghini, AvailableEngines = new List<Engine> {_lamborghiniV10}, EngineLocation = EngineLocationType.Mid};
  55.         _lamborghiniAventador = new Model {Name = "Aventador LP 700-4", BasePrice = 387000, Year = 2012, Manufacturer = _lamborghini, AvailableEngines = new List<Engine> {_lamborghiniV12}, EngineLocation = EngineLocationType.Mid};
  56.         _astonMartinDB9 = new Model {Name = "DB9", BasePrice = 185000, Year = 2012, Manufacturer = _astonMartin, AvailableEngines = new List<Engine> {_astonMartinV12}, EngineLocation = EngineLocationType.Front};
  57.         _astonMartinV12Vantage = new Model {Name = "V12 Vantage", BasePrice = 180000, Year = 2012, Manufacturer = _astonMartin, AvailableEngines = new List<Engine> {_astonMartinV12}, EngineLocation = EngineLocationType.Mid};
  58.         _porsche911GT2R2 = new Model {Name = "911 GT2 R2", BasePrice = 245000, Year = 2012, Manufacturer = _porsche, AvailableEngines = new List<Engine> {_porscheStraight6}, EngineLocation = EngineLocationType.Rear};
  59.         new List<Model> {_ferrari458Italia, _ferrariScaglietti, _lamborghiniAventador, _lamborghiniGallardo, _astonMartinDB9, _astonMartinV12Vantage, _porsche911GT2R2}.ForEach(x => context.Models.Add(x));
  60.     }
  61. }

The result of this process is that every time my database is dropped and recreated it is also seeded with test data. 

I know what your saying…”But I don’t use the same data for manually walking through my UI that I would use for Integration Testing!”  Luckily I can easily specify a different Connection String and Database Initializer to use when running my Integration Tests.

The first thing you have to do is modify the connection string in the AppConfig of your Integration Test project to use a different initial catalog.  As you can see all I did was suffix mine with “_TEST”…clever I know!  This is going to setup a new database for you that will only be used for testing.

  1. <connectionStrings>
  2.     <add name="EFCodeFirst" connectionString="Data Source=(local);Initial Catalog=EFCodeFirst_TEST;Integrated Security=True;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient"/>
  3.   connectionStrings>
  4.  
  5.  

Next you need to tell EF what Database Initializer to use.  This can be accomplished two ways.  The first way is to explicitly set it in code.  In the example below I am doing this in the ClassInitialize of my Integration Test class.

  1. [ClassInitialize]
  2. public static void ClassInitialize(TestContext context) {
  3.     Database.SetInitializer(new TestDataContextInitializer());
  4. }

The second way is to set it configure it in the App/Web Config.

  1. <appSettings>
  2.     <add key="DatabaseInitializerForType EFCodeFirst.DataContext.DataContext, EFCodeFirst" value="EFCodeFirst.DataContext.TestDataContextInitializer, EFCodeFirst" />
  3.   appSettings>
  4.  
That’s it!  Now you have a separate Database instance just for Integration Testing that will always contain the clean fresh data that is required to exercise your Integration Test suite.  Better yet, every developer that runs your Integration Tests will also have this data and everything will just work.
Monday, 02 January 2012 20:14:01 (Central Standard Time, UTC-06:00)
Instead of using DropCreateDatabaseIfModelChanges Better use DropCreateDatabaseAlways??
Rod
Thursday, 05 January 2012 06:22:06 (Central Standard Time, UTC-06:00)
Rod,
It really depends on what you are doing. In my experience I found that DropCreateDatatabaseAlways was overkill.

Thanks for reading!
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview