database operations as a pre-requisite to unit tests?

Question

How do I create a unit test that partially makes use of database unit tests but is called from a regular unit test?

Yes, perhaps they may not be unit tests; you may wish to call them integration tests. Regardless of what you would like to label them, they are tests.

Within my nunit tests, I am using helper constants:

private const string NumericSerial="123123";
private const string NonNumericSerial="123123 bad serialnumber";
private const int ValidPatientId=123;
private const int InvalidPatientId=-1;
private const int PatientIdThatDoesNotExistInppp=111291;
private const string SerialNumberThatDoesNotExistInshmk="123123";
private const string SerialNumberThatDoesExistInshmk="1015873";
private const byte InvalidFirmwareVersion=0;
private const int FacilityThatDoesntExistInAAA=Int32.MaxValue;

Prior to running any tests that make use of these constants, I would like to verify that these constant are correctly defined.

For example I can Assert that NumericSerial is indeed numeric without having to do any mocking or injecting of any dependencies - I would simply tryparse it.

However, other constants such as PatientIdThatDoesNotExistInppp will need to be verified that it indeed does not exist in the ppp database. In order to do this, I can follow several routes:

1. Implement a linq query within entity framework
2. Explicitly send a select statement to the database
3. Or I could a database unit test by first creating the necessary record (in our case it would make sure that 111291 does not exist in the database.

Unless you advise strongly against option #3, I am inclined to implement that. How do I create a unit test that partially makes use of database unit tests but is called from a regular unit test?

I am looking for something like the following:

[Test]
public response_from_database_unit_test_equals_111291()
{
//call the database unit test
//retrieve value from database unit test
}

Answer 1

If you want to do database related testing, then there are a couple of options that make it easier for you. (it won't be 100% wrinkle free since DB testing is not trivial)

1. in the [Setup] method of your test class, setup a new Transaction Scope and setup any pre-requisites you need. (presence or absence of a record)
2. in the actual test case, do your verification using Linq2Sql or EF LINQ. it is very productive and avoids nasty SQL/Stored procedures.
3. in the [Teardown] method, complete the scope to commit the transaction.

on any exception, the transaction will be naturally rolled back.

And instead of one unit test calling database unit tests, extract the common code into common methods and call them both your database tests and these tests. (e.g. GetRecordWithId(5678))

That way, your tests don't really have a dependency on database unit tests, but share data access code.

And as you mentioned correctly, it doesn't matter what you call these.. they maybe data testing or integration testing etc. (not necessarily unit tests)

We can do our best to keep the state of the database clean using transaction scope, but ultimately connectivity issues, parallel test execution, test execution on dev servers connecting to a common data server (or local sql server pre-requisites), test execution on build server etc. create issues when it comes to database testing, when an actual database is involved.

Many teams employ a strategy of standing up a new database specific to a test run [prefix+timestamp], so that it doesn't collide with other runs. And tear down the db at the end of it. (worst case, there is a background process, which monitors databases with a specific prefix name and cleans it up every midnight, based on the timestamp. as you can see, there is a lot of peripheral work to do, for the value of testing a data layer.

Answer 2

And here is the additional answer you requested based on purely Entity Framework

    [TestMethod]
    public void GetAllBlogs_Orders_By_Name()
    {
        var data = new List<Blog>
        {
            new Blog { Name = "BBB" },
            new Blog { Name = "ZZZ" },
            new Blog { Name = "AAA" },
        }.AsQueryable();

        var mockSet = new Mock<DbSet<Blog>>();

        mockSet.As<IQueryable<Blog>>().Setup(m => m.Provider).Returns(data.Provider);
        mockSet.As<IQueryable<Blog>>().Setup(m => m.Expression).Returns(data.Expression);
        mockSet.As<IQueryable<Blog>>().Setup(m => m.ElementType).Returns(data.ElementType);
        mockSet.As<IQueryable<Blog>>().Setup(m => m.GetEnumerator()).Returns(0 => data.GetEnumerator());

        // we tell EF that treat our data list as the table of Blogs.
        var mockContext = new Mock<BloggingContext>();
        mockContext.Setup(c => c.Blogs).Returns(mockSet.Object);

        // make EF query the blogs table (i.e. our list)
        var context = mockContext.Object;

        // EF has queried our list here
        // you could run Select, OrderBy, Where etc. as well
        var blogs = context.Blogs.ToList(); 

        Assert.AreEqual(3, blogs.Count);
        Assert.AreEqual("AAA", blogs[0].Name);
        Assert.AreEqual("BBB", blogs[1].Name);
        Assert.AreEqual("ZZZ", blogs[2].Name);
    }

i didn't write this sample. it is straight from https://msdn.microsoft.com/en-us/library/dn314429.aspx (so all credit to MSDN)

the point is that, with 0% involvement of a database, we have let Entity Framework know that, "hey EF! treat my List as the table." EF then runs all the actual queries (Where, Select, OrderBy, GroupBy etc.) on your list. And it is so trivial to setup this list. (including nested links)

Your data layer code will always be encompassed with this unit testing. And you can trust EF to make the right SQL calls, when the actual table is involved.

p.s. the only 2 things, that i look out for is Include clauses and DbFunctions. These 2 behave differently between local Lists and actual tables. But once you are careful of those 2 things, it is a delightful thing to unit test your data layer to this extent, without worrying about a real database at all.