How to prevent database effect in C# MVC Unit testing?

Ideally you should be using mocking for scenarios where db interaction is there. Make sure you have separate out your db interaction logic and it is interface driven. Then you can create mock objects, wherein you define the expectation of your db interaction interfaces. So for example, if for example InsertSomething() method is called, what should be returned from this method? and so on. Sharing some links on details about unit testing and mocking.

https://msdn.microsoft.com/en-us/library/ff650441.aspx

https://github.com/Moq/moq4

http://www.developerhandbook.com/unit-testing/writing-unit-tests-with-nunit-and-moq/

Testing a MVC Controller fails with NULL reference exception

As another option, you can go for separate real database for testing purpose, if it is essential to execute tests against database. You can also, execute sql script at the start and after running the test to seed and clean data respectively to keep the database pristine

It is possible either by mocking your database (used in unit tests) or creating new database used only for testing purpose (integration tests). I would recommend using these two approaches together. Remember, that amount of unit tests should be much bigger than integration tests.

Here is simple example how to mock database (using Moq library).

    public class HomeController : Controller
    {
        private IUserRepository _repository;
        public HomeController(IUserRepository repository)
        {
            _repository = repository;
        }

        public ActionResult AddNewUser(User newUser)
        {
            _repository.AddUser(newUser);

            return View();
        }
    }

    public interface IUserRepository
    {
        void AddUser(User newUser);
    }

    public class UserRepository : IUserRepository
    {
        private DBContext _context;
        public UserRepository(DBContext context)
        {
            _context = context;
        }

        public void AddUser(User newUser)
        {
            _context.Users.Add(newUser);
        }
    }


    [Test]
    public void ShouldMockRepository()
    {
        // Given
        var repository = new Mock<IUserRepository>();
        var controller = new HomeController(repository.Object);

        // When
        controller.AddNewUser(new User());

        // Then
        repository.Verify(r => r.AddUser(It.IsAny<User>()), Times.Once);
    }