Domain Driven Design with Web API revisited Part 11: database creation and initial tests

Introduction

In the previous post we created the load test specific database context in a new repository layer. We also implemented the abstract timetable repository interface.

In this post we’ll create the database and fill it up with some initial values. The purpose of this post and the next is to see how our domain layer is reflected in the data store and to discover potential shortcomings and errors. I want to avoid the situation where we come to the final post where we test the web service layer and nothing works as it should. The tester application will be a simple C# console application.

The tester app and the local SQL server

We’ll reuse most of what is already written in another series devoted to the basics of EF code-first starting here. I won’t repeat all the details here.

Add a C# console application to the solution and call it DemoDatabaseTester. Add the following EntityFramework NuGet package to the project:

Right-click the console project and select “set as StartUp project”. We currently have two different repository layers in the solution. Add a project reference to the WebSuiteDDD.Repository.EF project. Later on we’ll add other project references but this post concentrates on the overall database context. We’ll be working with the database objects Agent, Customer etc. and I’d like to avoid any reference issues with their Domain counterparts that have the same names.

Let’s add our connection string already now. Open app.config of the console app and add the following section just below the closing “configSections” tag:

<connectionStrings>
		<add name="WebSuiteContext" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0;Initial Catalog=WebSuiteDDD.Repository.EF.WebSuiteContext;Integrated Security=True;MultipleActiveResultSets=True" />		
</connectionStrings>

Recall that we provided “WebSuiteContext” as the connection string name to our WebSuiteContext class in the WebSuiteDDD.Repository.EF layer. This connection string will be used by the EntityFramework code-first mechanism to create the database.

What is this LocalDb\v11.0 data source? Here’s an explanation from this thread on StackOverflow:

“LocalDB was introduced in SQL Server 2012 CTP3. It’s basically a new version of SQL Express, with same functionality, dedicated to developers. So you don’t need to install any SQL server. If you have installed SQL 2012 or Visual Studio 11 then you already have it, and it runs over .Net 4 or higher. If you are using Visual Studio 11 and have been playing with the new EntityFramework and MVC, you can see it on the default connection string.”

How can we open it? Select “Sql Server Object Explorer” from the View menu. You might already see (localdb)\v11.0 there by default:

If not, then right-click the “SQL Server” node and select Add SQL Server. The standard Sql Server connection window will open. Insert “(localdb)\v11.0” as the Server name, leave the other options untouched and click Connect. The local Sql server v11.0 should appear under the Sql Server node. You can open it and view the existing databases. You might already have some local databases there. Our WebSuite database will also be created here.

EF code-first database creation

We first need to enable migrations. Open the Package Manager (PM) Console – View, Other Windows, Package Manager Console in the main menu – and set the Default project to the database tester:

Next, run the following command in the PM:

Enable-Migrations

You should get an error message saying that there’s no context type in the assembly ‘DemoDatabaseTester’. That’s right, we’ll need to point out the overall DB context.

Add a folder called WebSuiteDataMigrations to the console app and execute the following command in the PM window:

Enable-Migrations -ContextProjectName WebSuiteDDD.Repository.EF -MigrationsDirectory WebSuiteDataMigrations

If all went well you should get a confirmation:

Checking if the context targets an existing database…
Code First Migrations enabled for project DemoDatabaseTester.

There should also be a class called Configuration in the WebSuiteDataMigrations folder. It has a constructor with AutomaticMigrationsEnabled set to false. Change that to true so that any change we introduce in the database objects will be propagated to the database for each new migration. You may not want to do this in a production environment but here it’s fine.

You can add a using statement to WebSuiteDDD.Repository.EF and shorten the class names. Also, remove the code in the Seed method that was commented out:

namespace DemoDatabaseTester.WebSuiteDataMigrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;
	using WebSuiteDDD.Repository.EF;

    internal sealed class Configuration : DbMigrationsConfiguration<WebSuiteContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            MigrationsDirectory = @"WebSuiteDataMigrations";
        }

        protected override void Seed(WebSuiteContext context)
        {
            
        }
    }
}

The Seed method is where we can populate the database tables with some initial values.

We’ll do that now. Add the following content to the body of the Seem method:

protected override void Seed(WebSuiteContext context)
{
	List<Agent> agents = new List<Agent>();
	Agent amazon = new Agent();
	amazon.Id = Guid.NewGuid();
	amazon.Location = new Location()
	{
		City = "Seattle",
		Country = "USA",
		Latitude = 123.345,
		Longitude = 135.543
	};
	Agent rackspace = new Agent();
	rackspace.Id = Guid.NewGuid();
	rackspace.Location = new Location()
	{
		City = "Frankfurt",
		Country = "Germany",
		Latitude = -123.654,
		Longitude = 121.321
	};
	Agent azure = new Agent();
	azure.Id = Guid.NewGuid();
	azure.Location = new Location()
	{
		City = "Tokyo",
		Country = "Japan",
		Latitude = 23.45,
		Longitude = 12.343
	};
	agents.Add(amazon);
	agents.Add(rackspace);
	agents.Add(azure);
	context.Agents.AddRange(agents);

	List<Customer> customers = new List<Customer>();
	Customer niceCustomer = new Customer();
	niceCustomer.Id = Guid.NewGuid();
	niceCustomer.Address = "New York";
	niceCustomer.MainContact = "Elvis Presley";
	niceCustomer.Name = "Nice customer";

	Customer greatCustomer = new Customer();
	greatCustomer.Id = Guid.NewGuid();
	greatCustomer.Address = "London";
	greatCustomer.MainContact = "Phil Collins";
	greatCustomer.Name = "Great customer";

	Customer okCustomer = new Customer();
	okCustomer.Id = Guid.NewGuid();
	okCustomer.Address = "Berlin";
	okCustomer.MainContact = "Freddie Mercury";
	okCustomer.Name = "OK Customer";

	customers.Add(niceCustomer);
	customers.Add(greatCustomer);
	customers.Add(okCustomer);
	context.Customers.AddRange(customers);

	List<Engineer> engineers = new List<Engineer>();
	Engineer john = new Engineer();
	john.Id = Guid.NewGuid();
	john.Name = "John";
	john.Title = "Load test engineer";
	john.YearJoinedCompany = 2013;

	Engineer mary = new Engineer();
	mary.Id = Guid.NewGuid();
	mary.Name = "Mary";
	mary.Title = "Sr. load test engineer";
	mary.YearJoinedCompany = 2012;

	Engineer fred = new Engineer();
	fred.Id = Guid.NewGuid();
	fred.Name = "Fred";
	fred.Title = "Jr. load test engineer";
	fred.YearJoinedCompany = 2014;

	engineers.Add(john);
	engineers.Add(mary);
	engineers.Add(fred);
	context.Engineers.AddRange(engineers);

	List<LoadtestType> testTypes = new List<LoadtestType>();
	LoadtestType stressTest = new LoadtestType();
	stressTest.Id = Guid.NewGuid();
	stressTest.Description = new Description()
	{
		ShortDescription = "Stress test",
		LongDescription = "To determine or validate an application’s behavior when it is pushed beyond normal or peak load conditions."
	};

	LoadtestType capacityTest = new LoadtestType();
	capacityTest.Id = Guid.NewGuid();
	capacityTest.Description = new Description()
	{
		ShortDescription = "Capacity test",
		LongDescription = "To determine how many users and/or transactions a given system will support and still meet performance goals."
	};

	testTypes.Add(stressTest);
	testTypes.Add(capacityTest);
	context.LoadtestTypes.AddRange(testTypes);

	List<Project> projects = new List<Project>();
	Project firstProject = new Project();
	firstProject.Id = Guid.NewGuid();
	firstProject.DateInsertedUtc = DateTime.UtcNow;
	firstProject.Description = new Description()
	{
		ShortDescription = "First project",
		LongDescription = "Long description of first project"
	};

	Project secondProject = new Project();
	secondProject.Id = Guid.NewGuid();
	secondProject.DateInsertedUtc = DateTime.UtcNow.AddDays(-5);
	secondProject.Description = new Description()
	{
		ShortDescription = "Second project",
		LongDescription = "Long description of second project"
	};

	Project thirdProject = new Project();
	thirdProject.Id = Guid.NewGuid();
	thirdProject.DateInsertedUtc = DateTime.UtcNow.AddDays(-10);
	thirdProject.Description = new Description()
	{
		ShortDescription = "Third project",
		LongDescription = "Long description of third project"
	};
	projects.Add(firstProject);
	projects.Add(secondProject);
	projects.Add(thirdProject);
	context.Projects.AddRange(projects);

	List<Scenario> scenarios = new List<Scenario>();
	Scenario scenarioOne = new Scenario();
	scenarioOne.Id = Guid.NewGuid();
	scenarioOne.UriOne = "www.bbc.co.uk";
	scenarioOne.UriTwo = "www.cnn.com";

	Scenario scenarioTwo = new Scenario();
	scenarioTwo.Id = Guid.NewGuid();
	scenarioTwo.UriOne = "www.amazon.com";
	scenarioTwo.UriTwo = "www.microsoft.com";

	Scenario scenarioThree = new Scenario();
	scenarioThree.Id = Guid.NewGuid();
	scenarioThree.UriOne = "www.greatsite.com";
	scenarioThree.UriTwo = "www.nosuchsite.com";
	scenarioThree.UriThree = "www.neverheardofsite.com";

	scenarios.Add(scenarioOne);
	scenarios.Add(scenarioTwo);
	scenarios.Add(scenarioThree);
	context.Scenarios.AddRange(scenarios);

	List<Loadtest> loadtests = new List<Loadtest>();
	Loadtest ltOne = new Loadtest();
	ltOne.Id = Guid.NewGuid();
	ltOne.AgentId = amazon.Id;
	ltOne.CustomerId = niceCustomer.Id;
	ltOne.EngineerId = john.Id;
	ltOne.LoadtestTypeId = stressTest.Id;
	ltOne.Parameters = new LoadtestParameters() { DurationSec = 60, StartDateUtc = DateTime.UtcNow, UserCount = 10 };
	ltOne.ProjectId = firstProject.Id;
	ltOne.ScenarioId = scenarioOne.Id;

	Loadtest ltTwo = new Loadtest();
	ltTwo.Id = Guid.NewGuid();
	ltTwo.AgentId = azure.Id;
	ltTwo.CustomerId = greatCustomer.Id;
	ltTwo.EngineerId = mary.Id;
	ltTwo.LoadtestTypeId = capacityTest.Id;
	ltTwo.Parameters = new LoadtestParameters() { DurationSec = 120, StartDateUtc = DateTime.UtcNow.AddMinutes(20), UserCount = 40 };
	ltTwo.ProjectId = secondProject.Id;
	ltTwo.ScenarioId = scenarioThree.Id;

	Loadtest ltThree = new Loadtest();
	ltThree.Id = Guid.NewGuid();
	ltThree.AgentId = rackspace.Id;
	ltThree.CustomerId = okCustomer.Id;
	ltThree.EngineerId = fred.Id;
	ltThree.LoadtestTypeId = stressTest.Id;
	ltThree.Parameters = new LoadtestParameters() { DurationSec = 180, StartDateUtc = DateTime.UtcNow.AddMinutes(30), UserCount = 50 };
	ltThree.ProjectId = thirdProject.Id;
	ltThree.ScenarioId = scenarioTwo.Id;

	loadtests.Add(ltOne);
	loadtests.Add(ltTwo);
	loadtests.Add(ltThree);
	context.Loadtests.AddRange(loadtests);

	context.SaveChanges();
}

This is nothing special really. We simply create a couple of records for each database model. Make sure that the objects, such as Agent, Customer, Engineer etc. all point to the WebSuiteDDD.Repository.EF.DataModel namespace.

As we have no database yet at all let’s create it using the add-migration command as the first step. It accepts a parameter called ConfigurationTypeName which specifies the Configuration class for the migration you want to run. This is the fully qualified name of the Configuration class. Execute the following command in the package manager console:

add-migration -ConfigurationTypeName DemoDatabaseTester.WebSuiteDataMigrations.Configuration “FirstInitialisation”

That’s the fully qualified name of Configuration in the WebSuiteDataMigrations folder. FirstInitialisation is the name of the migration. If all went well then you’ll be presented a class called FirstInitialisation in a file called [some date value]_FirstInitialisation.cs. It derives from DbMigration which is a base class for code-based migrations. It’s normal C# code and quite straightforward to follow. You’ll see field declarations with their types, lengths, primary keys, foreign keys, indexes etc., e.g.:

CreateTable(
    "dbo.Agents",
    c => new
        {
            Id = c.Guid(nullable: false),
            Location_City = c.String(),
            Location_Country = c.String(),
            Location_Longitude = c.Double(nullable: false),
            Location_Latitude = c.Double(nullable: false),
        })
    .PrimaryKey(t => t.Id);

We can accept most of the default settings. We’ll just set some string fields non-nullable ones. In fact you can go ahead and change all string fields to non-nullable except for UriTwo and UriThree of the Scenario object. Here comes an example.

Change…

CreateTable(
                "dbo.LoadtestTypes",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Description_ShortDescription = c.String(),
                        Description_LongDescription = c.String(),
                    })
                .PrimaryKey(t => t.Id);

…to the following…

CreateTable(
                "dbo.LoadtestTypes",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Description_ShortDescription = c.String(nullable: false),
                        Description_LongDescription = c.String(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

Apply this to every field of type string except for Scenarios which will look like this:

CreateTable(
                "dbo.Scenarios",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        UriOne = c.String(nullable: false),
                        UriTwo = c.String(),
                        UriThree = c.String(),
                    })
                .PrimaryKey(t => t.Id);

Notice that there’s still no database that reflects our database models. The update-database package console command can do that. It will first check if there’s any existing database and apply all migration scripts that had not been processed up to that point. Otherwise it will create a new database and run all available migration scripts. The ConfigurationTypeName parameter can again be set to show which migration to run. Run the following command to create the tables:

update-database -ConfigurationTypeName DemoDatabaseTester.WebSuiteDataMigrations.Configuration

If all goes well then the following should happen. The PM window should print the following messages:

Applying explicit migrations: [somedatefield_FirstInitialisation].
Applying explicit migration: somedatefield_FirstInitialisation.
Running Seed method.

Go back to the Sql Server Object Explorer window, right-click the SQL Server node and select the Refresh option. A new database called WebSuiteDDD.Repository.EF.WebSuiteContext should be there with all our tables as DB sets:

Expand the tables to view the columns. Check especially if the “not null” logic was applied to the appropriate columns, like here:

Right-click any of the tables and select View Data. The records in the Seed method should all be there. Here’s an example for the Agents table:

It’s looking good so far. Let’s test the WebSuiteContext object. Add the following code to the Main function in Program.cs of the C# console app:

WebSuiteContext context = new WebSuiteContext();
List<Agent> dbAgents = context.Agents.ToList();			
foreach (Agent agent in dbAgents)
{
	Debug.WriteLine(string.Format("Agent ID: {0}, city: {1}, country: {2}", agent.Id, agent.Location.City, agent.Location.Country));
}

You should get the following values printed in the Debug window:

Agent ID: 751ec485-437d-4bae-9ff1-1923203a87b1, city: Seattle, country: USA
Agent ID: 23b83ac5-c29f-420f-bc9a-48906b243693, city: Frankfurt, country: Germany
Agent ID: 3e953948-8d0a-46df-8ffb-9beef9991a9b, city: Tokyo, country: Japan

The IDs will vary of course from what you have in your local database.

Let’s also test inserting a new object, say a new Engineer:

Engineer dbEngineer = new Engineer();
dbEngineer.Id = Guid.NewGuid();
dbEngineer.Name = "Jane";
dbEngineer.Title = "Jr. Load test engineer";
dbEngineer.YearJoinedCompany = 2012;
context.Engineers.Add(dbEngineer);
context.SaveChanges();

Run the code and Jane should be available in the Engineers table:

Finally we’ll test whether we can enter an invalid object into the database. Change the Name property in the above code to null:

dbEngineer.Title = null;

…and run the code. You should get a DbUpdateException. If check the inner exceptions you’ll see the reason:

Cannot insert the value NULL into column ‘Title’, table ‘WebSuiteDDD.Repository.EF.WebSuiteContext.dbo.Engineers’; column does not allow nulls. INSERT fails.
The statement has been terminated.

OK, we’re done with the data store foundation of our demo application. In the next post we’ll test the reduced load test context as well.

View the list of posts on Architecture and Patterns here.