Unit Testing LINQ to SQL

June 10, 2008 · 2 minute read · unit testing · linq to sql
Unit testing is a vital role of development these days, and with recent development within the .NET framework and the Visual Studio system it is easier than ever to create unit tests.
One pain point with unit testing a database-driven application is always the state of the database prior to the tests and after the tests. You have to make a call as to whether you have a separate database which you run your tests against or use your primary database and potentially fill it with junk results all the time.
I'm fairly familiar with the DataContext in LINQ to SQL, but as with all things there's always more to learn about, which a friend of mine pointed out to me the other day.
More than just a connection
The DataContext is more than just a connection manager for your database, it also contains information about your database and schema, let me introduce two neglected methods of the DataContext:
  • context.DatabaseExists()
  • context.CreateDatabase()

Because a DBML file has the full schema (will, full known schema) your DataContext will know whether or not your database specified in your connection string actually exists, and you can create it yourself if needed.
This is where unit testing comes in.

Oh, and there is one other method which can be used as well if you want to do complete clean up:

  • context.DeleteDatabase()

So... unit testing?

With unit testing you often don't care about the data created during the test, provided that all your Asserts are successful you can just delete it all when your done, but you'll want to make sure that your CRUD is working so you need somewhere to write to, this is when we can pull out te CreateDatabase() method.

Another idea which can be coupled with this is randomly-generated databases purely used for the test execusion. Here's a sample test method I've got:


public void DatabaseTesting()



string connstring = "Data Source=apowell-vm-vist;Initial Catalog=TestDriven_" + new Random().Next() + ";Integrated Security=True"; using (TDDDataContext ctx = new TDDDataContext(connstring))


if (ctx.DatabaseExists())










Oh - CleanDatabase() is an extension method I wrote just as an example, but you could do some Asserts to ensure the lookup data is already in there.

As you can see from the example I'm randomly creating a database name, and creating it if it exists.

So there you have it, simply creating test databases with LINQ to SQL :D

Published: 2017-10-23 19:16:31 +1100 +1100, Version: 7c4a504