Derik Whittaker



Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at
Using Linq's DataContext to (re)create your database for testing

If you happen to be using Linq2Sql and are also someone who tests, I have something for you.  You can use Linq's DataContext to create your database and then populate it with test data prior to each test run.  Note that you can do this same thing when using other OR/M tools such as NHIbernate, so this is nothing new or revolutionary.

Before I get into the 'How To' on doing this, I would like to go over the 'Why' I should do this.

When building out your test suit on any data driven application you are going to need to test data access at some point.  There are really 3 solutions to this problem.

  1. Simply query for known, existing data
    Although this works, this leads to very weak and brittle tests for obvious reasons.
  2. Create/use/delete the data for each of your tests
    This is a better choice, but leads to very heavy tests and can also lead to bad or orphaned data.  Which can be a major pain point if this is done in your development environment.
  3. Rebuild the db and test data for each test run.
    To me, this is the best choice as you get a clean slate and any orphaned data will be destroyed after each run.  No harm, no foul

Ok, on to the How to of this post.

The first thing you need to do is setup your tests to have your test fixture have FixtureSetup/FixtureTearDown methods to perform building/destroying of the database.

private TestsInit _testsInit;

public void FixtureSetup()
    _testsInit = ObjectFactory.GetInstance< TestsInit >();

public void FixtureTeardown()

Once you have your Setup/TearDown implemented you need to create the logic to build the database and insert test data.

public void InitDatabase()
    var dbContext = new DBContextDataContext( ConfigurationReader.ConnectionString_ForTests );
    CreateDatabase( dbContext );
    CreateTypeData( dbContext );
    CreateTestEpisode( dbContext );

public void DestroyDatabase()
    var dbContext = new DBContextDataContext( ConfigurationReader.ConnectionString_ForTests );

    DeleteDatabase( dbContext );

private void CreateDatabase( DataContext dbContext )
    DeleteDatabase( dbContext );


public void CreateTypeData( DBContextDataContext dbContext )
    dbContext.ExecuteCommand( "INSERT INTO [LevelType]( Name ) VALUES ( 'Type 1' )" );
    dbContext.ExecuteCommand( "INSERT INTO [TagTypes]( Name ) VALUES ( 'Type 4' )" );

private void CreateTestEpisode( DBContextDataContext dbContext )
    Episode episode = new Episode {LevelTypeID = 1, Name = "Test 1", Description = "Desc 1", EpisodeNumber = 1, EpisodeDate = DateTime.Now};

    episode.EpisodeDownloadInformations = new EntitySet< EpisodeDownloadInformation >
                                                  new EpisodeDownloadInformation{ FileName = "Foo.wmv", Size = 9.25m, Time = "10:00" }

    dbContext.Episodes.InsertOnSubmit( episode );

private void DeleteDatabase( DataContext dbContext )
    if ( dbContext.DatabaseExists() )

One thing to be careful of here is that you are using an alternate connection string, not the one for your dev/stage/production environment or you will NOT be a happy developer.  Also notice that I both using the ExecuteCmmand statement as well as the object model to insert the test data.  This is done simply to show the concept.  You could also simple script up all the inserts and put them into an external file.  The way you do it is up to you.

So there you go, you now know how you can use the DataContext to build/destroy your db structure for each test run.

Till next time,

Posted 05-21-2008 11:24 AM by Derik Whittaker
Filed under: ,



Sidar Ok wrote re: Using Linq's DataContext to (re)create your database for testing
on 05-22-2008 7:02 AM

Hi Derik, thanks for the great post.

I am just wondering, you find real time CRUD for testing heavy (so do I ) and do you really find creating DB each time lighter ?

I really doubt it.

The approach that I would go for would be to try to mock the DataContext and put some expectations & behaviors for the mock. It is not a trivial task since DataContext does not implement an interface like IDataContext making it easier to mock, but here is an approach.

Derik Whittaker wrote re: Using Linq's DataContext to (re)create your database for testing
on 05-22-2008 8:15 AM


I only like to do this for actual data testing (ie testing my CRUD logic).  When I doing non-data testing I do mock out the db calls.  But since I follow the repository pattern, there is no need to mock the DataContext itself, just my IRepository.

Dew Drop - May 22, 2008 | Alvin Ashcraft's Morning Dew wrote Dew Drop - May 22, 2008 | Alvin Ashcraft's Morning Dew
on 05-22-2008 10:24 AM

Pingback from  Dew Drop - May 22, 2008 | Alvin Ashcraft's Morning Dew

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Google Reader or Homepage Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of
Red-Gate Tools For SQL and .NET


SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
NHibernate Profiler
Balsamiq Mockups
JetBrains - ReSharper
Web Sequence Diagrams
Ducksboard<-- NEW Friend!


Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers


Community Server (Commercial Edition)