Integration tests with Entity Framework 4 Code First using SQL Server CE 4 or SQLite

Question

I would like to start with integration testing. I am using an ASP.NET MVC 3 app. And I am using Entity Framework 4 Code First CTP5. My integration tests to the database is in a separate project something like MyProject.Data.IntegrationTests.

I am planning on using SQL Server CE 4 or SQLite. Any recommendations/tips/opinions on using any one of these for what I am trying to accomplish?

Does anyone know of any decent articles that I can read on what I am trying to accomplish? And help/feedback would be appreciated.

I am using SQL Server 2008 for my database. But when testing my repositories I would like to test them against one of these database mentioned above, so I will need to specify the connection string.

UPDATE

I work from a service layer (called from my controller) and then the service layer will call my repository. For examples, below is how I would add a news item:

Service class:

public class NewsService : INewsService
{
   private INewsRepository newsRepository;

   public NewsService(INewsRepository newsRepository)
   {
      this.newsRepository = newsRepository;
   }

   public News Insert(News news)
   {
      // Insert news item
      News newNews = newsRepository.Insert(news);

      // Insert audit entry

      // Return the inserted news item's unique identifier
      return newNews;
   }
}

Repository class:

public class NewsRepository : INewsRepository
{
   MyContext context = new MyContext();

   public NewsRepository()
   {
   }

   public News Insert(News news)
   {
      int newsId = context.Database.SqlQuery<int>("News_Insert @Title, @Body, @Active",
         new SqlParameter("Title", news.Title),
         new SqlParameter("Body", news.Body),
         new SqlParameter("Active", news.Active)
      ).FirstOrDefault();

      news.NewsId = newsId;

      // Return the inserted news item
      return news;
   }
}

I am using Entity Framework 4 Code First CTP5 and NUnit. Does NUnit has something similar to the roll back in XUnit?

Answer 1

If you use a testing framework like XUnit (http://xunit.codeplex.com/), it comes with a feature called [AutoRollback] and that will rollback the transaction ran in the test so none of your data will change!

As far as how to setup the tests, I need to see more of how you setup your data access. Did you use the Repository Pattern? (Entity Framework 4 CTP 4 / CTP 5 Generic Repository Pattern and Unit Testable). If I could see some of your code it would help. Below is a sample integration test with XUnit:

private readonly IUserRepository _repository;

public UserRepositoryTests()
{
    _repository = new UserRepository(base._databaseFactory);
}
    
[Fact, AutoRollback]
public void Should_be_able_to_add_user()
{
    var user = new User{Name = "MockName"};
    _repository.Add(user);
    base._unitOfWork.Commit();
    
    Assert.True(user.Id > 0);
}

So the above test adds a user to my database, then checks its Id property to check that SQL Server auto generated an Id for it. Because the method is decorated with the AutoRollback attribute, the data is then removed from my database after the method ends!