Would a Unit Test add any value to this example of a DAL provider beyond an Integration Test?

public List<int> GetPortfolioList()
{
    using (var connection = new SqlConnection("<connectionString>"))
    using (var command = new SqlCommand("SELECT * FROM Portfolio", connection))
    {
        connection.Open();
        var portfolioTable = SqlHelper.GetDataTable(command);
        var portfolios = from DataRow row
                            in portfolioTable.Rows
                            select int.Parse(row["Portfolio"].ToString());

        return portfolios.ToList();
    }
}

Take this method in a SQL DAL provider to retrieve a list of portfolios, as the name (and code) suggests. Because the database table for integration testing contains a fairly static set of data we can Assert against several expectations. e.g. The list of portfolios will: - not be empty - contain certain known values - contain no duplicates

Following a peer review, someone insisted that this code isn't being properly tested (in isolation) because it relies on database access. In the case that most of the value is found in ensur开发者_运维问答ing that this method returns data from a database whose state is guaranteed, I've been unable to see the value in mocking away the database call in order to write a unit test for this method. Am I missing something?

I'll take the contrary view because I just finished writing a fake db (using in memory lists) to make linq to sql (or linq to anything) unit testable.

This is one question I used to pick a suitable way to fake/mock the database. (from reading your code though, the embedded "SELECT * FROM" means you are leaning on SQL more than linq, which will make it harder to factor your code into stuff that has to be executed by SQL Server and stuff that linq is capable of dealing with.

How are people unit testing code that uses Linq to SQL

I can now run unit tests that will succeed or fail depending on the suitability of my linq query even if the database is unplugged from the wall.

For example, how is your code to react if row["Portfolio"].ToString() is null, how does the code react when this doesn't return any rows, or returns 2?

And even if you are only doing integration tests, nunit is not a bad way to integration tests, just be careful not to call them unit tests, lest a purist get upset about it.

The method uses Linq to project some values from the DB into a list of integers - you may want to test that it does that correctly.

I would split the code into two - the data retrieval and the projection (Linq query) - you could then test the linq query with mock data, without needing to mock the database.

I would also say there is little value in unit testing data access code.

As a testing purist, I believe that I cannot "unit test" a DAL because I cannot do it in isolation. This means that a portion of my code, the one interacting with the database, goes without unit testing. The code above looks fine; just make sure that it exists as part of a larger contract that you can test in other objects.

I do perform integration testing, though, by building a database, seeding it, and ensuring that my DAL works.

I would agree with you, the only value here will be integration testing, there's really nothing to unit test without being pedantic.

The unit test code for any callers of this method should mock this method away, of course.

e: The exception cases MathewMartin mentions above would be the only things I would consider worth unit testing in this scenario.