The Rain and The Shade

August 17, 2011

Unit Testing Database driven functionality with ADO.Net Entity Framework Code First

Filed under: ADO.Net Entity Framework,Unit Testing — ovaisakhter @ 5:39 am

The basic principal of writing a unit tests is that when you do a transaction you know the “expected” result and then you compare with the actual result that was generated, and if these results match you have a passed unit test. 

It is always interesting to write unit tests for Database services. Usually the problem that comes to mind is how to exactly predict the state of the database at the time when unit tests are running, so that the unit tests know what to expect. Some people may argue that you can mock out all the database functionality (mock the repositories), this idea may be very applicable in some scenarios but I usually do not go this way because of two reasons, 

  • It seems like to much work
  • Our applications are mostly data driven applications so there is not much to test anyway other than database transactions.

One way of knowing the exact state of the database can be to initialize(create) the database every time the unit tests are run. Once the database is created we might want to insert some initial data which is required for the unit tests.

I have been using Entity Framework Code first in my recent projects. Entity Framework provides you a very good way of achieving the two objectives I have mentioned in the previous paragraph.

EF provides a concept of Database initializers, these are implementations which define the strategy of how (rather when) the database will be created using our model (if you are a bit lost then I recommend to watch Code First Development with Entity Framework). These implementations implement an Interface called IDatabaseInitializer. There are some pre-backed strategies for database creation are provided with the EF. The one we are interested here is DropCreateDatabaseAlways. The name says it all, when using this strategy a database will be created every time code is executed(unit tests are run). To use the Initializers all you need to do is to write the following code before your unit tests are executed

System.Data.Entity.Database.SetInitializer<MyDbContext>(new DropCreateDatabaseAlways<MyDbContext>());

(Make sure you are using a local database server(SQL Express), things can really stressed if you are using a shared development server Smile.)

So now our database is created every time unit tests are run, now we need to create some data each time the database is created. For this you can inherit the  DropCreateDatabaseAlways class and override a method called Seed. using this method you add the initial records you want to create every time database is created.

Here is how the code will look like

public class MyUnitTestDatabaseInitializer : DropCreateDatabaseAlways<MyDbContext>
       protected override void Seed(WilkeDbContext context)
  //you can add any data using the context objected provided in the parameter

The seed method is called just after the database is created by the the framework. Now all you need to do it to tell the framework to use your Database Initializer, and that you will do by modifying the code I wrote above.

System.Data.Entity.Database.SetInitializer<MyDbContext>(new MyUnitTestDatabaseInitializer <MyDbContext>());

so here you have it, now you can write the unit tests which are totally predictable and can run on any environment, be it your local machine or your Build Server. 


  1. A friend of mine Rizwan Ahmed commented on this post on the my Facebook I am copying his comments and my response here for the record.

    Rizwan Ahmed: I’m not a big fan of unit testing databases. I like to Unit test my BL and ViewModels only. These are the places all the logic should be anyway not the database (only sometimes it makes more sense to have some of the logic in SPs or views). Every other thing should be mocked and be substituted through IoC. There are a lot of Mock frameworks out there which save you all that work which you mentioned as “its too much work”. DB should mainly be just for CRUD operations.
    My 2 cent.

    Thank you very much for the feedback Rizwan Ahmed . Here are my thoughts.
    1) When I mentioned using of EF especially code first you can imagine I am not a big fan of keeping any logic in databases. When I am talking things like the database is created using the “model” this means that database is used just as a data store and nothing further. So here we do agree.
    2) I do most of my unit tests on the application controller services and repositories which I would believe will be a something closer to your approach also. I do have idea about the mocking frameworks and I have actually mentioned one in one of the posts. But I think that even if we are mocking we still need to create an in memory datastore to serve the correct data to the services (or BLs and Viewmodels in your case). and the data is not just simple CRUDs it could be fairly complex queries and for this we will need to write code to deliver data and in correct form according to the business rule as I mentioned. Even if we do that, our DL code will never be tested until we write separate unit tests for them.
    So I believe that with frameworks like EF we have the opportunity to add the possibility to test our database no differently than the rest of the code then why not?. I do not see any disadvantage of this. and then there are services or data sources which you do not have any control of like external web service other production database you can and should use mock services for these.

    Comment by ovaisakhter — August 17, 2011 @ 11:03 am | Reply

  2. Thanks for the detailed reply boss.

    When I said I’m not a big fan of unit testing database, I meant it does not add a lot of value to the project. If DAL is just a vehicle to move data back and forth between database and BL (typical Select, Updates), how worried we should be to test that? (I wouldn’t)

    “But I think that even if we are mocking we still need to create an in memory datastore to serve the correct data to the services.”

    Agreed, and that can be as simple as in memory data structure initialized before the test run?

    “and the data is not just simple CRUDs it could be fairly complex queries and for this we will need to write code to deliver data and in correct form according to the business rule as I mentioned.”

    I agree but most of the times it is and it should be just CRUD (assuming we are not considering Joins and Wheres as business logic).

    Bottom line, if DAL does not have any logic (other than joins and where clauses), why would someone want to test it?

    I’m not saying that people should not do it but I have not run into any situation where I saw value in doing it because our DAL is usually a thin layer on top of the DB.

    Thanks & Best Regards,

    Comment by Rizwan Ahmed — August 17, 2011 @ 11:23 pm | Reply

  3. thanks for the comment 🙂

    Please consider this.

    I may agree that may be it could be a good idea to mock the DLs while unit testing as that is unit test(so one unit at a time :)). But I still believe we need to have unit tests for the DAL or repositories I need this regression to be sure that the last work done by developer has not screwed up something a join or a where clause in a very complex Linq statement. I just can not imagine how can we live without it.

    Then as long as you are working with all stable Mr. MS SQL you are good to go(arguably), but if you jump into the world of No-SQL databases which are mainly open source and builds are coming every few weeks with much needed bug fixes you need to have regression to see if they have not broken anything else while fixing something. I have seen this happen many time for example when we were working with Ravendb.

    Comment by ovaisakhter — August 18, 2011 @ 10:41 am | Reply

  4. How can you not consider ‘joins and wheres’ as business logic? The ‘joins’ defines the relationships between you tables which is business logic. And ‘where’ is used to filter data, and that filter is defined by some business rule (i.e., show me all customers with past due accounts). If you don’t test that the ‘where’ is returning the correct rows, then the rest of your testing stack doesn’t matter.

    Comment by Bob — January 8, 2013 @ 2:03 pm | Reply

    • What you have mentioned may be taken as the crux of my post, we can exactly test the where and joins with this approach. I am not sure if I have mentioned that where and joins are not business logic. If I have then it is a mistake.

      Comment by ovaisakhter — January 8, 2013 @ 2:20 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: