Testing a (big) collection retrieved from a db

Question

I'm currently doing integration testing on a database and I have the following sql statement:

var date = DateTime.Parse("01-01-2010 20:30:00");
var result = datacontext.Repository<IObject>().Where(r => r.DateTime > date).First();
Assert.IsFalse(result.Finished);

I need to test if the results retrieved from the statement, where the given date is less then the date of the object, have Finished set to False. I do not know how many results I get back and currently I'm getting the first object of the list and check if that object has Finished set to false. I know testing only the first item of the list is not valid testing, as a solution for that I could iterate through the list and check all items on Finished, but putting logic in a Test is kinda going against the concept of writing 'good' tests.

So my question is:
Does anyone have a good solution of how to properly test the results of this list?

Answer 1

You can go the other way around, check if you have any result in that date range that is finished.

If there is any, then your test should fail.

I can't test any fancier code, so I'll give you a simple way:

int noOfResultsFinished = datacontext.Repository<IObject>()
      .Where(r => r.DateTime > date && r.Finished).Count();

if (noOfResultsFinished > 0)
{
     // fail test
}

Answer 2

A bit off topic perhaps, but in general it isn't advisable to use a live database for integration testing. A better approach to get trust-worthy tests is to use a dedicated test database. In general it is important to know exactly what data is in the test database. When the data in your database changes, it is hard to ensure that the result of an integration test is consistent. When it fails sometimes, and succeeds at other times only because of data changes in your database, the test becomes useless. By wrapping each integration test in a transaction that you will rollback at the end of the test, you ensure your data doesn't change. However, I can imagine running your integration tests on a copy of a production database as an automated acceptance test.