Does anybody have experience of using SQLite to write integration tests?

We're using MVC, Entity Framework 4.1 Code First, SQL Server in our project.

Please share your experience: how do you unit test your data service layer? By data service layer I mean services supposed to be run by MVC controllers that have some kind of DbContext derived class declaration inside, so that they depend on开发者_C百科 this EF DbContext, and encapsulate some business\data logic to fetch and store the data.

After reading few articles and posts, I incline to use separate database to build unit/integration tests on, and I prefer to use in-memory (like SQLite) rather than SQL Compact. However I'm not even sure if it's possible, if you have such an experience, could be please share few lines of code to show how you achieve this.

Unit testing means testing unit = no database, no external dependency, just testing single testable unit. Once you want to involve database you don't unit test any more - you are doing integration testing.

I wrote multiple answers about unit testing / integration testing of code dependent on EF. The last one is here. So if your service layer creates linq queries on context you cannot reliably unit test them. You need integration tests.

I would use the same database as you expect to use in your real code. Why? Because mapping and behaviour between database provides can differ as well as implementation of LINQ. Also in case of SQL server you can use special EF features which don't have to be available in SQLite. Another reason is that last time I checked it, SQLite's provider didn't support database deletion, recreation, etc. which is something people usually want to use for integration tests. Solution for that can be Devart provider.

I don't use a separate database at all. In fact, my Unit Tests don't use a database at all.

My strategy is to create IEnityRepository interfaces for the DB Entities (replace Entity with the actual name). I then pass those to the constructor for my controllers.

During Unit Testing, I simply use a Mocking library to pass mock implementations of the repositories that I need and have the return some set of known data that I can use in the Unit Tests.